Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unhappy Unanswered: Cursor parameter

    My problem:
    I`d like to use parameter for opening cursor. This parameter should be used for where codition IN, as in example below.

    cursor c(param type) is
    select *
    from table
    where table.column in ( select param.col1
    from param )

    I try to declare type as a table, but compiler don't understand param in select of cursor.

    Thanks for help

    (sorry for my english)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Cursor parameter

    Here is an example:

    PHP Code:
    -- create type number_table is table of number;

    SQL> declare
      
    2    t number_table := number_table(10,20);
      
    3    cursor ccp number_table is
      4      select 
    from emp where deptno in
      5        
    (select from table(select cast(cp as number_tablefrom dual));
      
    6  begin
      7    
    for r in c(tloop
      8      say
    (r.ename);
      
    9    end loop;
     
    10end;
    SQL> /
    SMITH
    JONES
    CLARK
    SCOTT
    KING
    ADAMS
    FORD
    MILLER

    PL
    /SQL procedure successfully completed

  3. #3
    Join Date
    Feb 2004
    Posts
    3

    Thumbs down Re: Cursor parameter

    Hmm... I customized code for my tables, but it doesn't work. Compiler wrote "PLS-00382: expression is of wrong type" and placed cursor on start of "case".

    zdenal



    Here is an example:

    PHP Code:
    -- create type number_table is table of number;

    SQL> declare
      
    2    t number_table := number_table(10,20);
      
    3    cursor ccp number_table is
      4      select 
    from emp where deptno in
      5        
    (select from table(select cast(cp as number_tablefrom dual));
      
    6  begin
      7    
    for r in c(tloop
      8      say
    (r.ename);
      
    9    end loop;
     
    10end;
    SQL> /
    SMITH
    JONES
    CLARK
    SCOTT
    KING
    ADAMS
    FORD
    MILLER

    PL
    /SQL procedure successfully completed
    [/SIZE][/QUOTE]

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Cursor parameter

    1) Can you make my example work using the standard EMP table? You need to create the NUMBER_TABLE type first of course.

    2) What does your modified code look like?

  5. #5
    Join Date
    Feb 2004
    Posts
    3

    Re: Cursor parameter

    ad 1)

    No. I of course tryed it with no changes with standard EMP table too. But the result is the same:
    The following error has occurred: "PLS-00382: expression is of wrong type" (on begin of CAST).

    ad 2)

    I customized code for my tables, like this:

    declare
    type my_table is table of varchar2(10);
    t my_table := my_table('start', 'stop');
    cursor c( cp my_table ) is
    select * from PARTIC_ACTS where PARTIC_ACTS.ACT_CODE in
    (select * from table(select cast(cp as my_table) from dual));
    ....

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Cursor parameter

    You can't use nested tables declared in PL/SQL in SQL statements, only nested tables defined in the server using the CREATE TYPE statement.

  7. #7
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    You may have to use a REF CURSOR to do this.

    Code:
    CREATE OR REPLACE PROCEDURE xyz( param VARCHAR2)
    IS
       TYPE curvar_type IS REF CURSOR;
       curvar                        curvar_type;
       currec                         table%ROWTYPE;
       string_v   VARCHAR2(1000);
    
    BEGIN
        string_v := 'SELECT * FROM table ' ||
           'WHERE table.column IN ( ' ||
           'SELECT col1 FROM ' || param || ')';
    
        OPEN curvar FOR string_v;
        LOOP
            FETCH curvar INTO currec;
            EXIT WHEN curvar%NOTFOUND;
            ...
        END LOOP;
    END;
    By the way: since you are doing a "SELECT * FROM table" you can use the "currec table%ROWTYPE" declaration to accept row data from the cursor.

    JoeB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •