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!

Variable in cursor for loop

Discussion in 'SQL PL/SQL' started by Sparamanga, Feb 10, 2017.

  1. Sparamanga

    Sparamanga Active Member

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

    I have a script I've been using in an anonymous PL/SQL block that searches some tables for a string value.

    I want to change this so that it is a Stored Procedure where the user that calls the procedure can supply the table_owner info.


    FOR t IN (SELECT owner, table_name, column_name
    FROM all_tab_columns
    WHERE owner = 'BLAH' and data_type LIKE '%CHAR%')
    LOOP

    so can i substitute the 'BLAH' text for a variable as in ..

    DECLARE
    OWNER_VARIABLE NVARCHAR2;
    BEGIN

    FOR t IN (SELECT owner, table_name, column_name
    FROM all_tab_columns
    WHERE owner = OWNER_VARIABLE and data_type LIKE '%CHAR%')
    LOOP

    I have tried this but it doesn't work. I'm not sure of the syntax to substitute text for a variable in a for loop. Any help would be appreciated.

    Thanks,

    Rob.
     
  2. Sparamanga

    Sparamanga Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    So I think I Need to create an explicit Cursor as this allows me to create a variable parameter for table_owner, but then need to convert this to a stored procedure.

    Also I tried to substitute the string 'WALES' with a variable as I would like this to be a user submitted parameter aswell. However declaring a variable and then using the variable in the place of the 'WALES' text does not seem to work..

    I've pasted my code below,

    thanks,

    Rob.

    DECLARE

    ROB_COUNT INTEGER;
    CURSOR ROB(RR_OWNER NVARCHAR2)
    IS
    SELECT DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
    FROM all_tab_columns
    WHERE owner = RR_OWNER
    AND data_type LIKE '%CHAR%';

    BEGIN

    FOR RRR IN ROB('FLT_DB') LOOP
    EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM ' || RRR.OWNER || '.' || RRR.TABLE_NAME || ' WHERE
    UPPER(' || RRR.COLUMN_NAME || ') = :1'
    INTO ROB_COUNT
    USING 'WALES';

    IF ROB_COUNT > 0 THEN
    dbms_output.put_line( RRR.table_name ||' '||RRR.column_name||' '||ROB_COUNT );
    END IF;

    END LOOP;
    END;
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,533
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You want a procedure so you need to create one:

    Code (SQL):
    CREATE OR REPLACE PROCEDURE myproc (p_ownname IN nvarchar2) AS

    BEGIN

    FOR t IN (SELECT owner, TABLE_NAME, column_name
    FROM all_tab_columns
    WHERE owner = UPPER(p_ownname) AND data_type LIKE '%CHAR%')
    LOOP
         ...
    END LOOP;

    ...

    END;
    /
     
    You would pass the owner to the procedure call:

    exec myproc('bubba')

    and the c0de would use the passed-in value at run time. Simply declaring a variable without populating it provides no values for the query to work with.
     
  4. Sparamanga

    Sparamanga Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Thanks Zargon,

    I have created the procedure. When it is called you can supply the 'owner' variable.

    However I want to be able to call the procedure with another parameter.. I want to be able to supply the string to be searched for.

    I'm not quite sure about how to replace that in the current code as just substituting the string 'WALES' with a declared variable didn't seem to work. I was reading about bind variables, is this something to be aware of in this situation?

    How would I subsititute 'WALES' for a variable in the following script?

    Thanks,

    Rob.


    Code (SQL):
    CREATE PROCEDURE SEARCH_STRING(RR_OWNER IN NVARCHAR2) AS

    ROB_COUNT INTEGER;
    CURSOR ROB(RR_OWNER NVARCHAR2)
    IS
    SELECT DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
    FROM all_tab_columns
    WHERE owner = RR_OWNER
    AND data_type LIKE '%CHAR%';

    BEGIN

    FOR RRR IN ROB(RR_OWNER) LOOP
    EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM ' || RRR.OWNER || '.' || RRR.TABLE_NAME || ' WHERE
    UPPER('
    || RRR.COLUMN_NAME || ') = :1'
    INTO ROB_COUNT
    USING 'WALES';

    IF ROB_COUNT > 0 THEN
    dbms_output.put_line( RRR.TABLE_NAME ||' '||RRR.column_name||' '||ROB_COUNT );
    END IF;

    END LOOP;
    END;
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,533
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You simply add the second parameter to the procedure declaration; something like this should work:

    Code (SQL):
    CREATE PROCEDURE SEARCH_STRING(RR_OWNER IN NVARCHAR2, RR_SRCH IN NVARCHAR2) AS

    ROB_COUNT INTEGER;
    CURSOR ROB(RR_OWNER NVARCHAR2)
    IS
    SELECT DISTINCT OWNER, TABLE_NAME, COLUMN_NAME
    FROM all_tab_columns
    WHERE owner = RR_OWNER
    AND data_type LIKE '%'||RR_SRCH||'%';

    BEGIN

    FOR RRR IN ROB(RR_OWNER) LOOP
    EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM ' || RRR.OWNER || '.' || RRR.TABLE_NAME || ' WHERE
    UPPER('
    || RRR.COLUMN_NAME || ') = :1'
    INTO ROB_COUNT
    USING 'WALES';

    IF ROB_COUNT > 0 THEN
    dbms_output.put_line( RRR.TABLE_NAME ||' '||RRR.column_name||' '||ROB_COUNT );
    END IF;

    END LOOP;
    END;
    /
     
    Give this a try and see if you generate any errors or incorrect results; as I don't have a database to play with I can't verify the code.