Error using INTO clause in execute immediate [message #637639] |
Thu, 21 May 2015 04:43 |
|
jgjeetu
Messages: 373 Registered: July 2013 Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
SQL> CREATE OR REPLACE PROCEDURE p_drop_empty_tables
IS
CURSOR tab_cur
IS
SELECT object_name
FROM all_objects
WHERE owner = 'SCOTT'
AND object_type = 'TABLE'
AND object_name LIKE 'T%';
lv_tab_name VARCHAR2 (100);
lv_count NUMBER;
BEGIN
FOR i IN tab_cur
LOOP
lv_tab_name := i.object_name;
EXECUTE IMMEDIATE 'select count(*) into lv_count from ' || lv_tab_name;
IF lv_count = 0
THEN
EXECUTE IMMEDIATE 'Drop Table ' || lv_tab_name;
DBMS_OUTPUT.put_line (lv_tab_name || ' is dropped');
END IF;
END LOOP;
END;
/
Procedure created.
SQL> EXECUTE P_DROP_EMPTY_TABLES
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "SCOTT.P_DROP_EMPTY_TABLES", line 18
ORA-06512: at line 1
Lalit : Edited topic title
[Updated on: Thu, 21 May 2015 05:32] by Moderator Report message to a moderator
|
|
|
|
|
|
Re: Error while using Execute Trigger [message #637645 is a reply to message #637641] |
Thu, 21 May 2015 05:12 |
|
jgjeetu
Messages: 373 Registered: July 2013 Location: www.Orafaq.com/Forum
|
Senior Member |
|
|
@cm issue resolved thanks, thanks @michel @flyback for the links
@admin please change the topic name ,current one is wrong .
SQL> CREATE TABLE T1(ID NUMBER)
2 /
Table created.
SQL> CREATE TABLE T2(ID NUMBER)
2 /
Table created.
SQL> SELECT * FROM TAB
2 WHERE TNAME LIKE 'T%'
3 /
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T1 TABLE
T2 TABLE
SQL> CREATE OR REPLACE PROCEDURE p_drop_empty_tables
2 IS
3 CURSOR tab_cur
4 IS
5 SELECT object_name
6 FROM all_objects
7 WHERE owner = 'SCOTT'
8 AND object_type = 'TABLE'
9 AND object_name LIKE 'T%';
10 lv_tab_name VARCHAR2 (100);
11 lv_count NUMBER;
12 BEGIN
13 FOR i IN tab_cur
14 LOOP
15 lv_tab_name := i.object_name;
16 EXECUTE IMMEDIATE 'select count(*) from ' || lv_tab_name into lv_count;
17 IF lv_count = 0
18 THEN
19 EXECUTE IMMEDIATE 'Drop Table ' || lv_tab_name;
20 DBMS_OUTPUT.put_line (lv_tab_name || ' is dropped');
21 END IF;
22 END LOOP;
23 END;
24 /
Procedure created.
SQL> EXECUTE p_drop_empty_tables
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TAB
2 WHERE TNAME LIKE 'T%'
3 /
no rows selected
[Updated on: Thu, 21 May 2015 05:14] Report message to a moderator
|
|
|