Hi Team,

In my project we have testing suite built for PL/SQL and it tests basic test cases like input/output comparision.

So in the tool if I configure procedure P1 (p_in IN number, P_out OUT number) with input and and expected output, the tool which is built using apex runs the configured test cases and comapares the input and output and gives a report. This will work as long as i have simple input output datatypes.

how can I extend this to an out parameter which is a cursor. (for the sake of simplicity i'm not considering dynamic sql for ref curosrs).

I'm thinking of extracting the select used in the procedure from all_source, and using that in a cursor for loop to get the rows returned by the cursor. So for this simple use case, user will be testing a procedure which returns a cursor and he'll configure the expected rows count. and when the test suites runs it will take the query form code runs and compares the count value to what user is expecting and passes/fails the test case based on the matching.

so now, is there any better approaches to do some basic automated testing for procedure which returns cursors?
any suggestion is greatly valued.

Thanks.

create or replace function f 
  return sys_refcursor as
  cur sys_refcursor;
begin
  open cur for
    select * from dual;
    
  return cur;
end f;
/

declare
  ctx  dbms_xmlgen.ctxhandle; 
  doc  xmltype;
  
  expected_xml varchar2(1000) := '<ROWSET>
 <ROW>
  <DUMMY>X</DUMMY>
 </ROW>
</ROWSET>
';
begin

  ctx := dbms_xmlgen.newContext(f);
 
  doc := dbms_xmlgen.getxmltype (ctx, dbms_xmlgen.none); 
  
  dbms_output.put_line(doc.getStringVal());
    
  if doc.getStringVal() = expected_xml then
    dbms_output.put_line('*** SUCCESS ***');
  else
    dbms_output.put_line('*** FAILURE! ***');
  end if;

end;
/

<ROWSET>
 <ROW>
  <DUMMY>X</DUMMY>
 </ROW>
</ROWSET>

*** SUCCESS ***