1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Paramaterised cursor

Discussion in 'SQL PL/SQL' started by Sparamanga, Mar 15, 2019.

  1. Sparamanga

    Sparamanga Active Member

    Messages:
    29
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi,

    I am trying to create a cursor that I can resuse with a range of different input variables.

    However I am having trouble with the variable string which is a range of comma separated values.

    I have tested with the variable containing one value and it works fine, so hoping this is a syntax issue.

    This works..

    Code (SQL):
    DECLARE
    VAR1 CHAR(100):='E6';

    CURSOR C1 (TT IN MSF010.TABLE_TYPE%TYPE) IS
    SELECT TABLE_CODE, TABLE_DESC FROM MSF010
    WHERE TABLE_TYPE IN TT;

    BEGIN
    --
    FOR RRR IN C1(VAR1)
    LOOP

    INSERT INTO TEST2(TABLE_CODE, TABLE_DESC)
    VALUES (RRR.TABLE_CODE, RRR.TABLE_DESC);
    END LOOP;

    END;

    This doesn't work...

    Code (SQL):
    DECLARE
    VAR1 VARCHAR2(100):=q'[('E6', 'E10')]';

    CURSOR C1 (TT IN VARCHAR2) IS
    SELECT TABLE_CODE, TABLE_DESC FROM MSF010
    WHERE TABLE_TYPE IN TT;

    BEGIN
    --
    FOR RRR IN C1(VAR1)
    LOOP

    INSERT INTO TEST2(TABLE_CODE, TABLE_DESC)
    VALUES (RRR.TABLE_CODE, RRR.TABLE_DESC);
    END LOOP;


    END;

    The dbms_output.put_line for the declared variable var1 is .. ('E6', 'E10')

    so I was hoping that the cursor would run the sql statement ..

    SELECT TABLE_CODE, TABLE_DESC FROM MSF010
    WHERE TABLE_TYPE IN ('E6', 'E10');

    I get the message anonymous block completed but there is no data in the Test2 Table. There should be data that corresponds to values for E6 and E10.

    What is the reason this doesnt work?

    Thanks,

    Rob.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,667
    Likes Received:
    375
    Trophy Points:
    1,430
    Location:
    Aurora, CO