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 

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.