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;
Advertisement
Answer
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