Difference between revisions of "SQL Developer"

Latest revision as of 20:10, 6 February 2017

SQL Developer is the new interactive SQL GUI tool for the Oracle Database. SQL Plus scripts are mainly compatible with it.

Copying SQL Text

In SQL Developer when copying certain columns, the text is truncated. For instance, the SQL Text column of v$SqlText contains the ASCII code 0 as the terminator. If the results of the SQL_TEXT column are copied and pasted, then only up to the first terminator will be copied. This can be shown with this SQL:

select 
ADDRESS, HASH_VALUE, SQL_ID, COMMAND_TYPE, PIECE, SQL_TEXT, dump(SQL_TEXT)
from v$sqltext
where
order by hash_value,piece
;

The solution is to remove the terminators:

select 
ADDRESS, HASH_VALUE, SQL_ID, COMMAND_TYPE, PIECE, replace(SQL_TEXT,chr(0),'')
from v$sqltext
where
order by hash_value,piece
;

Further Reading

SQL Plus