Home » SQL & PL/SQL » SQL & PL/SQL » Error using INTO clause in execute immediate (oracle 10g)
Error using INTO clause in execute immediate [message #637639] Thu, 21 May 2015 04:43 Go to next message
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 #637641 is a reply to message #637639] Thu, 21 May 2015 04:59 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
INTO is PL/SQL keyword, not a SQL one, so you can't use it in dynamic SQL.
Should be:
EXECUTE IMMEDIATE 'select count(*) from '||lv_tab_name into lv_count;;
Re: Error while using Execute Trigger [message #637643 is a reply to message #637639] Thu, 21 May 2015 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Gee, after all these years! Please read PL/SQL User's Guide and Reference and Application Developer's Guide - Fundamentals

Re: Error while using Execute Trigger [message #637644 is a reply to message #637639] Thu, 21 May 2015 05:10 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
It just reminded me this thread: http://www.orafaq.com/forum/t/172580/0/

I hope that original poster may understand from it what is behind that code attempt.
Re: Error while using Execute Trigger [message #637645 is a reply to message #637641] Thu, 21 May 2015 05:12 Go to previous message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

@cm issue resolved thanks, thanks @michel @flyback for the links Smile
@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

Previous Topic: DELETING of RECORDS simultaneously.
Next Topic: does any one use oracle v12?
Goto Forum:
  


Current Time: Tue Mar 19 00:39:06 CDT 2024