Using declared variables in SQL Oracle query FOR LOOP

Question

I'm attempting to write a query script where I declare a variable and then use them in a select statement in Oracle SQL.
Basically script should check if parent tables' entries are used in referenced table or not. if not --> delete the entry from REF table. So far I have this

DECLARE

tableToCompare sys_refcursor;

TYPE TableNameType IS RECORD (
        table_name          ALL_TABLES.TABLE_NAME%TYPE
        , ref_table_name ALL_CONSTRAINTS.r_constraint_name%TYPE
        );
        
        tableName TableNameType;
        
    PROCEDURE LOG_MSG(msg VARCHAR2)
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('->'||msg);
    END;

BEGIN
LOG_MSG('[PROCESS] -> starts');

-- find all parent tables for reference tables starting with REF_
    OPEN tableToCompare FOR
            SELECT ac.table_name, substr(ac.r_constraint_name, 0, LENGTH(ac.r_constraint_name) - 3)  as ref_table_name
              FROM all_constraints ac
             WHERE 1 = 1 AND ac.constraint_type = 'R'
                   AND ac.r_constraint_name IN
                            (SELECT constraint_name
                               FROM all_constraints
                              WHERE table_name like 'REF_%');
                                 
    LOOP
        FETCH tableToCompare INTO tableName;
        EXIT WHEN tableToCompare%NOTFOUND;

        LOG_MSG('Parent table: ' || tableName.table_name || ' , of Reference table: ' || tableName.ref_table_name );
        
        DELETE  REF_STYLE_TYPE rst 
            WHERE NOT EXISTS (SELECT TYP_CD from STYLE_FORMAT rp WHERE rp.TYP_CD = rst.TYP_CD)
                AND NOT EXISTS (SELECT TYP_CD from STYLE_REQ rp WHERE rp.TYP_CD = rst.TYP_CD)
                AND NOT EXISTS (SELECT TYP_CD from STYLE_ADD_EX rp WHERE rp.TYP_CD = rst.TYP_CD);
                
     --   DELETE tableName.ref_table_name rst 
    --        WHERE NOT EXISTS (SELECT TYP_CD from tableName.table_name rp WHERE rp.TYP_CD = rst.TYP_CD)
      --          AND NOT EXISTS (SELECT TYP_CD from tableName.table_name+1 rp WHERE rp.TYP_CD = rst.TYP_CD)
       --         AND NOT EXISTS (SELECT TYP_CD from tableName.table_name+2 rp WHERE rp.TYP_CD = rst.TYP_CD);

      LOG_MSG('DONE!');
    
     END LOOP; 
END;

So far LOG_MSG('Parent table: ' || tableName.table_name || ' , of Reference table: ' || tableName.ref_table_name ); I got a msg log that lists me the variables but I have no idea how to combine that in query DELETE above.

Any ideas or suggestions how it could be automized to this ?

     --   DELETE tableName.ref_table_name rst 
    --        WHERE NOT EXISTS (SELECT TYP_CD from tableName.table_name rp WHERE rp.TYP_CD = rst.TYP_CD)
      --          AND NOT EXISTS (SELECT TYP_CD from tableName.table_name+1 rp WHERE rp.TYP_CD = rst.TYP_CD)
       --         AND NOT EXISTS (SELECT TYP_CD from tableName.table_name+2 rp WHERE rp.TYP_CD = rst.TYP_CD);

You cannot use bind variables to reference a table - see this answer: stackoverflow.com/questions/9723931/… — Koen Lostrie
– Koen Lostrie, Commented Feb 15, 2021 at 11:54
@KoenLostrie, is it another option how to check if entry from references tables used without writing hard strings? — cutelittlegnome
– cutelittlegnome, Commented Feb 15, 2021 at 13:46

Koen Lostrie · Accepted Answer · 2021-02-15 14:27:57Z

You can't reference table names as bind variables. In that case, use EXECUTE IMMEDIATE. I'm a big fan of cursor for loops so I used that. Note that this code is not tested.

DECLARE
  PROCEDURE LOG_MSG(msg VARCHAR2)
  IS
  BEGIN
      DBMS_OUTPUT.PUT_LINE('->'||msg);
  END;
  l_delete_stmt VARCHAR2(4000);
BEGIN
  log_msg('[PROCESS] -> starts');
  l_delete_stmt := 
q'!DELETE #TABLE_NAME# rst 
            WHERE NOT EXISTS (SELECT TYP_CD from STYLE_FORMAT rp WHERE rp.TYP_CD = rst.TYP_CD)
                AND NOT EXISTS (SELECT TYP_CD from STYLE_REQ rp WHERE rp.TYP_CD = rst.TYP_CD)
                AND NOT EXISTS (SELECT TYP_CD from STYLE_ADD_EX rp WHERE rp.TYP_CD = rst.TYP_CD)!';
                
  FOR r IN 
  (
    SELECT ac.table_name, substr(ac.r_constraint_name, 0, LENGTH(ac.r_constraint_name) - 3)  as ref_table_name
      FROM all_constraints ac
     WHERE 1 = 1 AND ac.constraint_type = 'R'
           AND ac.r_constraint_name IN
                    (SELECT constraint_name
                       FROM all_constraints
                      WHERE table_name like 'REF_%')  
  ) LOOP
    log_msg('Parent table: ' || tableName.table_name || ' , of Reference table: ' || tableName.ref_table_name );
    log_msg('Delete statement: ' || REPLACE(l_delete_stmt,'#TABLE_NAME#',r.ref_table_name) );
    EXECUTE IMMEDIATE REPLACE(l_delete_stmt,'#TABLE_NAME#',r.ref_table_name);
    log_msg('DONE!');
  END LOOP;
END;

Collectives™ on Stack Overflow

Using declared variables in SQL Oracle query FOR LOOP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related