Skip to content
Advertisement

Using PLSQL variables in SQL Query based on FOR LOOP

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:

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:

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

Advertisement

Answer

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

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

(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:

The PL/SQL block above outputs:

db<>fiddle here

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement