There are some tables start with same characters. I need to drop those all.
For this I wrote below program, but it throw error. So please fix it.
SQL> CREATE OR REPLACE PROCEDURE drop_tables
2 (v_a IN varchar2)
3 AS
4 v_b VARCHAR2(200) := 'v_a||"%"';
5
6 TYPE tab_ref_cur IS REF CURSOR;
7 tab_ref tab_ref_cur;
8
9 v_t_n user_tables.table_name%TYPE;
10 sql_stmt varchar2(32767);
11 BEGIN
12 OPEN tab_ref FOR 'select table_name from user_tables where table_name LIKE :B' USING v_b;
13 LOOP
14 FETCH tab_ref INTO v_t_n;
15 EXIT WHEN tab_ref%NOTFOUND;
16
17 sql_stmt := 'DROP TABLE '||v_t_n;
18 EXECUTE IMMEDIATE sql_stmt;
19 END LOOP;
20 CLOSE tab_ref;
21 END;
22 /
Warning: Procedure created with compilation errors
SQL> SHOW ERRORS;
Errors for PROCEDURE SCOTT.DROP_TABLES:
LINE/COL ERROR
-------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4/31 PLS-00103: Encountered the symbol "%" when expecting one of the following: ( - + case mod new null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set specification> <an alternatively-quoted SQL