Thursday, February 1, 2018

how to: Procedure returns multiple rows to java with array and cursor


create  type test_obj AS object
(
                                P_Program  VARCHAR2(400)
                           
);

CREATE TYPE test_nt AS TABLE OF test_obj;


Procedure Proc1 (P_Cursor Out SYS_REFCURSOR)
AS

Cursor C1 Is
 Select * from .......;


Cursor C2  (P_Program_In In Varchar2) Is
    Select * from where program = P_Program_In ;

P_Program  VARCHAR2(400);


v_prog_nt test_nt;
i integer := 0;

Begin
    --SELECT test_obj(P_Program ) BULK COLLECT INTO v_prog_nt FROM DUAL;

   v_prog_nt := test_nt();
 
    For Ds In C1
    Loop
 
      For Rs In C2(Ds.Program)
      Loop
   
       v_prog_nt.extend;
       i:= i+1;
        P_Program := Rs.Program;
   
   
        v_prog_nt(i) := test_obj(P_Program  );

        --dbms_output.put_line(i);
        dbms_output.put_line(v_prog_nt(i).P_Program);

      End Loop;
    End Loop;

Open P_Cursor
  For
  Select P_Program  from table(v_prog_nt);

End;

No comments:

Post a Comment

AWS how to delete VPC when it has error with Network interface , Gateway decencies

   how to delete VPC when it has error with Network interface , Gateway decencies  in AWS 1. Check if it is running on EC2 instance then Sto...