I need to export in xls or html format the text of SQL statements belonging to my table "TAB_SALES" captured in the Workload Repository.
I tried this query:
select sql_id, REPLACE(REPLACE(sql_text, CHR(13)), CHR(10)) sql_text
where upper(sql_text) like '%TAB_SALES%'
order by sql_id;
But when I try to save it in csv/xls or html format some queries are truncated after 4000 characters. I tried also with
but I get error:
REPLACE(REPLACE(DBMS_LOB.substr(sql_text, 9000,1), CHR(13)), CHR(10))
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 13
06502. 00000 - "PL/SQL: numeric or value error%s"
I'd like to export sql text from column sql_text (CLOB) from dba_hist_sqltext Oracle view without truncate characters.
How can I avoid truncating characters?