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;

Answer 1

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