Hi Friends,
I am stuck with the error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" while fetching data from CLOB column and sub-string it to 32767 character.
While searching this OTN i did stumble upon this below thread wr the other user had same issue. As suggested in the other post, i did use both dbms_lob.substr and substr while both returned the same error.
I use ref cursor to fetch the data from the table. I use this in a database function which returns value as a ref cursor type to the oracle reports.
select dbms_lob.substr(clob_colum,32767) from Table;
select substr(clob_colum,1,32767) from Table;
10216988
My requirement is, i need to fetch the data from CLOB column substr it to 32767 and return the value as a ref cursor type to the oracle report to generate it as a xml.
Here apart from this function i have many other functions that return data as a ref cursor type so i cannot change the design of the return type.
(note: 1. Since ref cursor has a limit of 32767 i am forced to truncate the data
2. Plain substr(clob_column) works in sql, but it seems to be not working in dynamic sql)
Please help me out to resolve this issue. Also suggestion are welcome if entire data could be fetched without sub-stringing it.
i can provide the actual code if required. Thanks in Advance.....