How do I output a CLOB from APEX rest services through an output parameter. which is of string type

I have an apex rest service, which produces a CLOB.

I wanted to output this CLOB through string parameter.

when I try to do this, I am getting "PLSQL value error"

Below is my code, how do I this..

DECLARE
  l_cursor SYS_REFCURSOR;
  v_stmt_str      VARCHAR2(1000);
      
begin

   if (:status is NULL) then 
      v_stmt_str := 'select FROM_USER_NAME as "from_user_name", to_user_name as "to_user_name",  id as "id", type as "type", subject as "subject", message as "message", thread_id as "thread_id", status as "status", to_char(alert_date, ''YYYY-MM-DD"T"HH24:MI:SS"Z"'') as "alert_date" from user_alert where to_user_name = :username';
      OPEN l_cursor FOR v_stmt_str using :username;
   else
      v_stmt_str := 'select FROM_USER_NAME as "from_user_name", to_user_name as "to_user_name",  id as "id", type as "type", subject as "subject", message as "message", thread_id as "thread_id", status as "status", to_char(alert_date, ''YYYY-MM-DD"T"HH24:MI:SS"Z"'') as "alert_date" from user_alert where to_user_name = :username and status = :status';
      OPEN l_cursor FOR v_stmt_str using :username, :status;
   end if;
   

 APEX_JSON.initialize_clob_output;

  APEX_JSON.open_object;
  APEX_JSON.write('user_alert', l_cursor);
  APEX_JSON.close_object;


 
 
-- :output_val :=APEX_JSON.get_clob_output;;
  APEX_JSON.free_output;
  
end;

Open in new window

output_val is an output parameter which is of string type.
sakthikumarAsked:
Who is Participating?
 
flow01Commented:
try
:output_val := dbms_lob.substring(APEX_JSON.get_clob_output,4000,1);
-- the 4000 is a guess for the max length of the bindvariable of type string
-- if  your clob extents  4000 bytes then data will be lost
-- in a pl/sql block you can define a v1 varchar2(32767)  and assign with v1 := dbms_lob.substring(APEX_JSON.get_clob_output,32767,1);
-- warning: there can be characterset  issues
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.