How to send cursor response from Oracle stored procedure

Question

I have a stored procedure which I want to return cursor as the OUT parameter. I see the output in dbms_output.put_line(as_return_val); like

Loan Purpose can not be Limited ||  /loanPurpose 
THE PURPOSE OF PURCHASE IS NOT VALID ||  /loanPurpose 

I need to read this response as a array of Objects(each ro one Object) in my service layer. Sorry I am not sure I need to send it as a cursor or like a varchar from here. I added as_return_val this variable to see whether I am getting correct response .

Procedure

create or replace PROCEDURE PR_LOGIC_CHECK_TEST 
(in_loan_id IN NUMBER,
in_trans_id  IN NUMBER)AS 

BEGIN
DECLARE
as_errm          VARCHAR2(2000);
curr_cursor_out  SYS_REFCURSOR;
temp_cursor_row  temp_cor_ll_cursor%rowtype;
as_return_val    VARCHAR2(500);
BEGIN
pr_loan_logic_check(in_loan_id, in_trans_id, as_errm,  curr_cursor_out);
LOOP
    FETCH curr_cursor_out INTO temp_cursor_row;
    EXIT WHEN curr_cursor_out%notfound;
    as_return_val := temp_cursor_row.ret_value
                     || ' ||  '
                     || '/'||temp_cursor_row.xpath_name;

    --dbms_output.put_line(as_return_val);
END LOOP;
close curr_cursor_out;
ROLLBACK;
END;
END PR_LOGIC_CHECK_TEST;

Answer 1

I'm still guessing at what you really want to happen since you haven't actually given us a reproducible test case (i.e. something that we can run locally or on dbfiddle or liveSQL that produces a particular output given a particular input).

Architecturally, it seems problematic to have a stored procedure whose only purpose is to format the output of a different procedure for human consumption. Formatting results would more properly be done in the display layer (the view of an MVC application but view means something else when we're talking about databases so I'm using "display layer") of the application not in a stored procedure. It would make more sense for your application to call pr_loan_logic_check directly and to let your display layer decide to concatenate the values from multiple columns together. If someone wants to change how the output is formatted later, you'd then just be changing code in the display layer not in your backend database.

My guess is that you want to return a collection like this. Note that I'm creating the collection type at the SQL level. You could create it in a PL/SQL package as well.

create or replace type varchar2_tbl
  is table of varchar2(500);

create or replace PROCEDURE PR_LOGIC_CHECK_TEST (
  in_loan_id   IN NUMBER,
  in_trans_id  IN NUMBER,
  out_strings OUT varchar2_tbl
)
AS 
  as_errm          VARCHAR2(2000);
  curr_cursor_out  SYS_REFCURSOR;
  temp_cursor_row  temp_cor_ll_cursor%rowtype;
BEGIN
  pr_loan_logic_check(in_loan_id, in_trans_id, as_errm,  curr_cursor_out);

  out_strings := out_strings();
  LOOP
    FETCH curr_cursor_out INTO temp_cursor_row;
    EXIT WHEN curr_cursor_out%notfound;

    out_strings.extend();
    out_strings( out_strings.count ) := 
            temp_cursor_row.ret_value
         || ' ||  '
         || '/'||temp_cursor_row.xpath_name;
  END LOOP;
  close curr_cursor_out;

  -- I have trouble imagining why you'd put a `rollback` here
  ROLLBACK;
END PR_LOGIC_CHECK_TEST;