Re: Execute Immediate, executing procedure at runtime passing Cursor

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 31 May 2003 09:32:49 -0700
Message-ID: <130ba93a.0305310832.55ddd949@posting.google.com>

First of all you are passing a 'record' to the procedure, not a cursor. Second,
the arguments after 'USING' must be of SQL type. 'Record' is a PL/SQL data type, much like a PL/SQL table. So it won't work. You can 'EXECUTE IMMEDIATE' into a record variable, however.

If your procedures were created to accept object data type, then it might work out for you. May or may not suit you, but it will look something like this:

SQL> desc tyu

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 C1                                                 NUMBER(3)
 C2                                                 VARCHAR2(2)
 C3                                                 CHAR(1)

SQL> create type test_type as object (col1 number(3),col2 varchar2(2), col3 char(1));
  2 /

Type created.

SQL> create or replace procedure test_proc2 (obj_in test_type) as   2 begin
  3 insert into tyu values(obj_in.col1,obj_in.col2,obj_in.col3);   4 end;
  5 /

Procedure created.

SQL> truncate table tyu;

Table truncated.

SQL> declare

  2  x test_type:=test_type(0,'X','X');
  3  y test_type:=test_type(1,'Y','Y');
  4  w test_type;

  5 i number:=0;
  6 begin
  7 if i=0 then
  8 w:=x;
  9 else
 10 w:=y;
 11 end if;
 12 execute immediate 'begin test_proc2(:a); end;' using w;  13 end;
 14 /

PL/SQL procedure successfully completed.

SQL> select * from tyu;

        C1 C2 C
---------- -- -

         0 X X

SQL>

• Jusung Yang

bobby_at_caseymusic.net (Bob) wrote in message news:<87d90b1f.0305301650.3cd9aba5_at_posting.google.com>...
> I'm trying to pass a cursor to a Dynamically Executed Procedure. The
> code works, except when I added the cursor "rAPP" to the parameter
> list in my EXECUTE IMMEDIATE line of code. Does anyone know how to
> pass a cursor to a Procedure using Execute Immediate? Thanks a
> million in advance? Here's my code so far:
>
> PROCEDURE VALIDATE_PROGRAM
> (pNumApp_ID IN CCZ.CCZ460_APPLICATION.APP_ID%TYPE,
> pNumPGM_ID IN CCZ.CCZ500_PROGRAM.PGM_ID%TYPE,
> pStrUSER_ID IN CCZ.CCZ100_USER.USER_ID%TYPE,
> pStrReturn OUT VARCHAR2) IS
> /*
>
> */
> plsql_block VARCHAR2(500);
> lstrReturn VARCHAR2(500);
> rAPP CCZ.CCZ460_APPLICATION%ROWTYPE;
> BEGIN
>
> --Get number of occupants and income of household
> OPEN cAPPLICATION(pNumAPP_ID);
> FETCH cAPPLICATION INTO rAPP;
> CLOSE cAPPLICATION;
>
> --LOOP THRU GETTING ALL SP_NAMES IN CCZ210, CCZ200
> FOR rPGM_VALID IN cVALIDATIONS(0,pNumPGM_ID) LOOP
>
> plsql_block := 'BEGIN ' || rPGM_VALID.SP_NAME || '(:rAPP
> :pNumAPP_ID,:pNumPGM_ID,:pStrUSER_ID, :pStrReturn); END;';
>
> EXECUTE IMMEDIATE plsql_block USING IN rAPP, IN pNumApp_ID,IN
> pNumPGM_ID,IN pStrUSER_ID,OUT lstrReturn;
>
> END LOOP;
>
> pStrReturn := 'OK';
>
> END VALIDATE_PROGRAM;
Received on Sat May 31 2003 - 11:32:49 CDT