Thursday, February 1, 2018

Store Procedure PLSQL returns Cursor in Loop to Java


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...