PL/SQL Dynamic SQL : Table name not valid

Question

I'm currently learning PL/SQL. I need to create a PL/SQL block to create a backup of all my tables like this : myTable -> myTable_old.

Here's what I got right now :

DECLARE
Cursor c IS SELECT table_name 
            FROM user_tables 
            WHERE table_name NOT LIKE '%_old';
sql_slc VARCHAR2(200);
sql_drp VARCHAR2(200);
sql_crt VARCHAR2(200);
row_count NUMBER;
t_name user_tables.table_name%type;
t_backup_name user_tables.table_name%type;

BEGIN
    sql_drp := 'DROP TABLE :1 CASCADE';
    sql_crt := 'CREATE TABLE :1 AS SELECT * FROM :2';
    sql_slc := 'SELECT COUNT(*) FROM user_tables WHERE table_name = :1';
    OPEN c;
    LOOP
        FETCH c INTO t_name;
        EXIT WHEN (c%NOTFOUND);
        t_backup_name := t_name || '_old';
        dbms_output.put_line(t_name || ' ' || t_backup_name);
        
        EXECUTE IMMEDIATE sql_slc INTO row_count USING t_backup_name;
        IF row_count > 0 THEN
            dbms_output.put_line(t_backup_name || ' dropped');
            EXECUTE IMMEDIATE sql_drp USING t_backup_name;
        END IF;
        dbms_output.put_line(t_backup_name || ' created');
        EXECUTE IMMEDIATE sql_crt USING t_backup_name, t_name;
        COMMIT;
    END LOOP;
    CLOSE c;
END;
/

Here's the error :

OUVRAGE OUVRAGE_old
OUVRAGE_old created
DECLARE
*
ERROR on line 1 :
ORA-00903: table name not valid
ORA-06512: on line 29

I don't understand why this error is coming up, can someone help me ?

It might have failed because t_name's length was about 32 so, adding "_old" made it too long to be accepted by a database. (depends on db version) — ekochergin
– ekochergin, Commented Feb 5, 2021 at 10:09
The name of next table where it crashes is 'AUTEUR_OUVRAGE' which if added _old is still inferior to a VARCHAR2(30) in lenght... I still tried to change t_name and t_backup_name to VARCHAR2(30) and nothing changed. — Jairo
– Jairo, Commented Feb 5, 2021 at 10:19
I see it says error occured on line 29 but still try to comment out the first execute immediate statement and run the block again. Let's see where the error occurs — ekochergin
– ekochergin, Commented Feb 5, 2021 at 10:24
It crashes on the 2nd execute (drop) if row_count > 0 and every time on the 3rd one (create table). — Jairo
– Jairo, Commented Feb 5, 2021 at 10:31
Another test then. Replace the last "execute immediate" with the following begin EXECUTE IMMEDIATE sql_crt USING t_backup_name, t_name; exception when others then dbms_output.put_line('t_backup_name: ' || t_backup_name || ' t_name: ' || t_name); return; end; Let's see what it outputs — ekochergin
– ekochergin, Commented Feb 5, 2021 at 10:38

Aleksej · Accepted Answer · 2021-02-05 11:00:36Z

The issue is that you can not use bind variables for table names; Oracle documentation:

The database uses the values of bind variables exclusively and does not interpret their contents in any way.

You should edit your code to use concatenation instead:

DECLARE
  Cursor c IS SELECT table_name 
              FROM user_tables 
              WHERE table_name NOT LIKE '%_OLD';           /* OLD, upper case */  
  sql_slc VARCHAR2(200);
  --sql_drp VARCHAR2(200);
  --sql_crt VARCHAR2(200);
  row_count NUMBER;
  t_name user_tables.table_name%type;
  t_backup_name user_tables.table_name%type;
BEGIN
--   sql_drp := 'DROP TABLE :1 CASCADE';   
--   sql_crt := 'CREATE TABLE :1 AS SELECT * FROM :2';
   sql_slc := 'SELECT COUNT(*) FROM user_tables WHERE table_name = :1';
   

   OPEN c;

   LOOP
      FETCH c INTO t_name;

      EXIT WHEN (c%NOTFOUND);
      t_backup_name := t_name || '_OLD';                                        /* OLD, upper case */
      DBMS_OUTPUT.put_line (t_name || ' ' || t_backup_name);

      EXECUTE IMMEDIATE sql_slc INTO row_count USING t_backup_name;

      IF row_count > 0
      THEN
         DBMS_OUTPUT.put_line (t_backup_name || ' dropped');

--         EXECUTE IMMEDIATE sql_drp USING t_backup_name;
         EXECUTE IMMEDIATE ' drop table ' || t_backup_name;                     /* concatenation and not bind variables */
      END IF;

      DBMS_OUTPUT.put_line (t_backup_name || ' created');                       /* concatenation and not bind variables */
--      EXECUTE IMMEDIATE sql_crt USING t_backup_name, t_name;
      EXECUTE IMMEDIATE 'create table ' || t_backup_name || ' as select * from ' || t_name;

      COMMIT;
   END LOOP;

   CLOSE c;
END;

Also, notice that, if not double quoted, object names always are uppercase, so you have to look for t_name || '_OLD' and not t_name || '_old'

Collectives™ on Stack Overflow

PL/SQL Dynamic SQL : Table name not valid

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related