Monday 8 September 2014

Convert LONG to VARCHAR2 in an SQL Statement

"Why are you still using Long column types, they've been extinct for ages?"
I'm not but the data dictionary is...

Anyway, I struggled for ages to find a solution to quickly convert Long column types to Varchar2 on the fly in an SQL statement without creating some kind of PLSQL function to do the job, which I didn't want to do. So I came up with converting it to XML then querying the XML. Still not ideal but got the job done without me having to create PLSQL.

WITH xml AS ( SELECT DBMS_XMLGEN.GETXMLTYPE( 'SELECT * FROM dba_triggers where trigger_name like ''%MODTRG''' ) AS xml FROM DUAL ), dbaTriggersData AS ( SELECT xs.trigger_name, xs.trigger_body FROM xml x, XMLTABLE('/ROWSET/ROW' PASSING x.xml COLUMNS trigger_name VARCHAR2(30) PATH 'TRIGGER_NAME', trigger_body VARCHAR2(4000) PATH 'TRIGGER_BODY') xs ) SELECT * FROM dbaTriggersData WHERE trigger_body LIKE '%USER%' AND trigger_body NOT LIKE '%APP_USER%' ;

This particular query was to check my triggers were ok for Apex. Referencing APP_USER if they referenced USER 

Monday 9 June 2014

ORA-00903, ORA-03001 and ORA-01765 When Renaming a Table

Using Oracle 11.2.0.3.0, I was trying to rename a table. Not something I do everyday.

On searching Google for the syntax I found Oracle documentation for "RENAME TABLE table-Name TO new-Table-Name" which gave me the error "ORA-00903: invalid table name".
This turns out to be a mistake in the documentation and the command should not have the TABLE keyword in it. IE:  "RENAME table-Name TO new-Table-Name"

This now gave me error  "ORA-03001: unimplemented feature". Now I am not logged on as the table owner but do have the owner set as my current schema. "ALTER SESSION SET CURRENT_SCHEMA=USERNAME;"

Next logical step was to include the username in the command. "RENAME owner.table-Name TO new-Table-Name", which gave "ORA-01765: specifying owner's name of the table is not allowed"

A bit more searching and I found the following syntax which did work.

ALTER TABLE owner.table-Name RENAME TO new-Table-Name;

Table altered.... Hurray.