Using PLSQL variables in SQL Query based on FOR LOOP

Question

I'm trying to execute some DML queries with PLSQL. I've declare an array having 5 table names and I want to run it under FOR LOOP. In this moment my code looks like:

DECLARE
  c_exist NUMBER;
  test_array dbms_sql.varchar2_table; 
BEGIN 
 test_array(1) := 'TEST_1';
 test_array(2) := 'TEST_2';
FOR i IN test_array.FIRST .. test_array.LAST
 LOOP
  SELECT count(*) INTO c_exist from user_tables WHERE table_name = test_array(i); 
  IF c_exist = 1 THEN 
   EXECUTE IMEDIATE 'ALTER TABLE ' || test_array(i) || 'COMPRESS'; 
  END IF; 
 END LOOP; 
END;

The problem is when script is trying to read test_array variable under SELECT statement. I'm getting an ORA-06512 ERROR. In my opinion script syntax is good. It passed test without SELECT stamtent, but with simply used dbms_output.put_line(test_array(i)) it passed well.

Whole error stack:

ORA-06502: 
ORA-06512: 
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

Additionaly, something like that works perfectly, and dbms_output printing correctly:

DECLARE
  c_exist NUMBER;
  test_array dbms_sql.varchar2_table; 
BEGIN 
 test_array(1) := 'TEST_1';
 test_array(2) := 'TEST_2';
FOR i IN test_array.FIRST .. test_array.LAST
 LOOP
  dbms_output.put_line(test_array(i)); 
 END LOOP; 
END;

syntax is not good. Try - select count(*) into c_exist from user_tables See docs - docs.oracle.com/cd/B19306_01/appdev.102/b14261/… — OldProgrammer
– OldProgrammer, Commented Dec 18, 2020 at 14:50
My bad, it's included in my code obviously, but I rewrote it wrongly. Thanks. In my opinion everything is about 'test_array(i)' at WHERE statement. — Sjevi
– Sjevi, Commented Dec 18, 2020 at 14:52

Popeye · Accepted Answer · 2020-12-18 14:55:13Z

2

Your syntax is not correct in dynamic query.

One space is missing before COMPRESS keyword.

Your code:

EXECUTE IMEDIATE 'ALTER TABLE ' || test_array(i) || 'COMPRESS';

Should be code:

EXECUTE IMEDIATE 'ALTER TABLE ' || test_array(i) || ' COMPRESS';

answered Dec 18, 2020 at 14:55

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sjevi Over a year ago

My code doesn't even pass to that statement. It's getting error at SELECT statement two lines before. Thanks.

Sjevi Over a year ago

@Abra sure, I have tried. I'm getting same error as before.

MT0 · Accepted Answer · 2020-12-18 15:35:55Z

You have misspelt IMMEDIATE and need a space between the table name and COMPRESS.

DECLARE
  c_exist NUMBER;
  test_array dbms_sql.varchar2_table; 
BEGIN 
  test_array(1) := 'TEST_1';
  test_array(2) := 'TEST_2';
  FOR i IN test_array.FIRST .. test_array.LAST LOOP
    SELECT count(*)
    INTO   c_exist
    from   user_tables
    WHERE table_name = test_array(i); 
  
    IF c_exist = 1 THEN 
      EXECUTE IMMEDIATE 'ALTER TABLE ' || test_array(i) || ' COMPRESS';
      DBMS_OUTPUT.PUT_LINE( test_array(i) || ' compressed.' );
    END IF; 
  END LOOP; 
END;
/

Also, you don't need to check if the table exists; just try to compress the table and catch the exception:

DECLARE
  table_not_exists EXCEPTION;
  test_array dbms_sql.varchar2_table;
  i          BINARY_INTEGER;
  
  PRAGMA EXCEPTION_INIT( table_not_exists, -942 );
BEGIN 
  test_array(1) := 'TEST_1';
  test_array(3) := 'TEST_2';
  i := test_array.FIRST;
  WHILE i IS NOT NULL LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || test_array(i) || ' COMPRESS';
      DBMS_OUTPUT.PUT_LINE( test_array(i) || ' compressed.' );
    EXCEPTION
      WHEN table_not_exists THEN
        DBMS_OUTPUT.PUT_LINE( test_array(i) || ' ' || SQLERRM );
    END;
    i := test_array.NEXT(i);
  END LOOP;
END;
/

(Also, dbms_sql.varchar2_table is an associative array and could be sparse so you should not assume that every index between FIRST and LAST exists.)

Then, if you have the table:

CREATE TABLE test_1 ( id NUMBER );

The PL/SQL block above outputs:

TEST_1 compressed.
TEST_2 ORA-00942: table or view does not exist

db<>fiddle here

Thank you so much. Everything works perfectly. Thanks for advice about catching exception.

Collectives™ on Stack Overflow

Using PLSQL variables in SQL Query based on FOR LOOP

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related