Hello All
We are using a pl/sql block to return a SYS_REFCURSOR but the select column has CLOB values
Database version is 12c
Here is the code
==================================================================================================
create or replace
function SURF_DESCRIPTION (P_description in CLOB)
return varchar2 as
l_length number:= 0;
l_desc CLOB;
l_number number:=0 ;
L_position number:= 32767;
ln_cursor NUMBER;
ln_result NUMBER;
ln_sql_id NUMBER := 1;
Begin
ln_cursor := DBMS_SQL.open_cursor;
select dbms_lob.getlength(P_description) into l_length from dual ;
select round(l_length/32767,0) into l_number from dual ;
IF L_LENGTH >32767 THEN
DBMS_OUTPUT.PUT_LINE ( L_NUMBER ||' ---- ' || L_LENGTH) ;
END IF ;
If l_length <32767 then
l_desc := P_description;
Else
For x in 1..l_number
Loop
--l_desc := l_desc || DBMS_LOB.SUBSTR(P_description, 32767, l_position);
l_desc := DBMS_LOB.SUBSTR(P_description, 32767, l_position);
l_position :=l_position+32767;
End Loop;
End if;
return l_desc ;
exception
When others then
dbms_output.put_line (SQLERRM);
End SURF_DESCRIPTION;
if the value of CLOB is more than 32K , how can this be supported if we want to return as string