Like operator with % in oracle plsql cursor

Question

I cannot pass the like operator with % in the cursor sql stmt, getting code error, Can you help

SQL> l
  1  CREATE OR REPLACE PROCEDURE cleanuptab (
  2    isrc_tns VARCHAR2
  3    )
  4  IS
  5  sql_stmt VARCHAR2(500);
  6  errm   VARCHAR2(2000);
  7  refcur   SYS_REFCURSOR;
  8  v_tabs VARCHAR2(50);
  9  BEGIN
 10      OPEN refcur FOR 'SELECT table_name FROM dba_tables where table_name like '%''||isrc_tns||''%' and owner='DBASCHEMA'';
 11      LOOP
 12        BEGIN
 13        FETCH refcur INTO v_tabs;
 14        EXIT WHEN refcur%NOTFOUND;
 15          EXECUTE IMMEDIATE 'drop table DBASCHEMA.'
 16          ||v_tabs
 17          || ' purge';
 18          dbms_output.Put_line('DROPPED '
 19          ||v_tabs);
 20        EXCEPTION
 21        WHEN OTHERS THEN
 22          dbms_output.Put_line( 'TABLE NOT FOUND: '
 23          ||v_tabs);
 24          CONTINUE;
 25        END;
 26      END LOOP;
 27      close refcur;
 28* END;
SQL> /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE CLEANUPTAB:

LINE/COL ERROR                                                                  
-------- -----------------------------------------------------------------      
10/79    PLS-00103: Encountered the symbol "%" when expecting one of the        
         following:                                                             
         * & = - + ; < / > at in is mod remainder not rem                       
         <an exponent (**)> <> or != or ~= >= <= <> and or like like2           
         like4 likec between using || multiset member submultiset               
         The symbol "* was inserted before "%" to continue.                     
                                                                                
10/96    PLS-00103: Encountered the symbol "%" when expecting one of the        
         following:                                                             
         * & = - + ; < / > at in is mod remainder not rem                       
         <an exponent (**)> <> or != or ~= >= <= <> and or like like2           

LINE/COL ERROR                                                                  
-------- -----------------------------------------------------------------      
         like4 likec between using || member submultiset   

I am trying to pass the like stmt & owner condition so that i can get those tables & drop. Pls help to review

Regards Kannan

Answer 1

Why not try it without OPEN/FETCH/NOTFOUND, without all the local variables, without the danger of SQL injection, with less lines of codes:

CREATE OR REPLACE PROCEDURE cleanuptab (
    isrc_tns VARCHAR2
)
IS
BEGIN
    FOR v_tabs IN (SELECT table_name FROM dba_tables WHERE table_name LIKE '%' || isrc_tns || '%' AND owner = 'DBASCHEMA')
    LOOP
        BEGIN
            EXECUTE IMMEDIATE 'drop table DBASCHEMA.' || v_tabs.table_name  || ' purge';
            dbms_output.put_line('DROPPED ' || v_tabs.table_name );
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('FAILED TO DROP ' || v_tabs.table_name);
        END;
    END LOOP;
END;