Home » SQL & PL/SQL » SQL & PL/SQL » DBA_HIST_SQLTEXT string buffer too small (Oracle 12.1)
DBA_HIST_SQLTEXT string buffer too small [message #685264] Sat, 27 November 2021 03:31 Go to next message
Messages: 13
Registered: July 2021
Junior Member
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
from dba_hist_sqltext
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

REPLACE(REPLACE(DBMS_LOB.substr(sql_text, 9000,1), CHR(13)), CHR(10)) 
but I get error:
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?
Re: DBA_HIST_SQLTEXT string buffer too small [message #685266 is a reply to message #685264] Sat, 27 November 2021 04:20 Go to previous message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The first one works in SQL*Plus.
For the second one, just use SUBSTR instead of "DBMS_LOB.substr".

Previous Topic: Oracle regex_substr parsing a clob
Next Topic: to_date in where clause
Goto Forum:

Current Time: Tue Mar 28 23:04:37 CDT 2023