Good afternoon community,
I have a PLSQL script executed against ORACLE 19 that is displaying an error that I don't understand.
The script drops any existing index before recreate them. The objects are defined in a kind-off list to prevent statement repetition
My PLSQL script is
set serveroutput on;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
DECLARE
index_not_exists EXCEPTION;
table_not_exists EXCEPTION;
PRAGMA EXCEPTION_INIT (index_not_exists, -1418);
PRAGMA EXCEPTION_INIT (table_not_exists, -942);
TYPE ind_list IS TABLE OF VARCHAR(100) NOT NULL;
ind_to_drop ind_list := ind_list('sst_ind1', 'sst_ind2');
/* first index is wrong because col0 does not exist */
ind_to_create ind_list := ind_list('create index sst_ind1 on sst_table(col0)', 'create index sst_ind2 on sst_table(col2)');
sql_to_exec varchar(200);
is_all_index_created boolean := TRUE;
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table sst_table cascade constraints purge';
EXCEPTION
WHEN table_not_exists THEN
NULL;
END;
EXECUTE IMMEDIATE 'create table sst_table (col1 number, col2 number)';
EXECUTE IMMEDIATE 'create index sst_ind1 on sst_table (col1)';
EXECUTE IMMEDIATE 'create index sst_ind2 on sst_table (col2)';
FOR l_index IN ind_to_drop.FIRST..ind_to_drop.LAST LOOP
BEGIN
sql_to_exec := 'drop index ' || ind_to_drop(l_index);
dbms_output.put_line(sql_to_exec);
EXECUTE IMMEDIATE sql_to_exec;
EXCEPTION
WHEN index_not_exists THEN
dbms_output.put_line('no index ' || ind_to_drop(l_index));
END;
END LOOP;
FOR l_index IN ind_to_create.FIRST..ind_to_create.LAST LOOP
BEGIN
dbms_output.put_line(ind_to_create(l_index));
EXECUTE IMMEDIATE ind_to_create(l_index);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR WITH ' || ind_to_create(l_index));
is_all_index_created := FALSE;
END;
END LOOP;
IF NOT is_all_index_created THEN
RAISE_APPLICATION_ERROR(-20001, 'PROBLEM CREATING INDEX');
END IF;
END;
/
exit
When I execute my script through sqplus I have the ERROR below I don't understand
sqlplus xxxxx/yyyyy@zzzzzz @ubscls_6711.sql
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 25 17:04:11 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
drop index sst_ind1
drop index sst_ind2
create index sst_ind1 on sst_table(col0)
ERROR WITH create index sst_ind1 on sst_table(col0)
create index sst_ind2 on sst_table(col2)
DECLARE
*
ERROR at line 1:
ORA-20001: PROBLEM CREATING INDEX
ORA-06512: at line 49
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Could anyone help me with fixing my issue. It's probably simple but just can't find the reason.
Thanks
It is doing what you said it should... your question states that:
- create an index that is failing should continue to create the remaining indexes to th the end
There is a comment in the code that says:
first index is wrong because col0 does not exist
The output shows:
create index sst_ind1 on sst_table(col0)
ERROR WITH create index sst_ind1 on sst_table(col0)
... so that index creation fails as expected (hiding the real error, which is unhelpful, as @Koen said); then it continues, and shows:
create index sst_ind2 on sst_table(col2)
... which apparently succeeds. So it has continued to create the remaining indexes.
You are using the flag is_all_index_created to keep track of any issues. That is being set to false by the expected failure. Then at the end of your code you have:
IF NOT is_all_index_created THEN
RAISE_APPLICATION_ERROR(-20001, 'PROBLEM CREATING INDEX');
END IF;
and that is the error you are seeing:
DECLARE
*
ERROR at line 1:
ORA-20001: PROBLEM CREATING INDEX
ORA-06512: at line 49
You are getting an error because your code says to raise that error, so there isn't anything to fix, given that you want/expect the first index creation to fail.
At the start of the script you have
WHENEVER SQLERROR EXIT SQL.SQLCODE;
so your script will exit with code -20001, indicating a failure, rather then zero, indicating success. You might not actually see -20001 though, it may be mod'd to a smaller range; Unix-y scripts wrap around if the exit code is outside the allowed range, so you might see -33 or 233, for instance. (Which is a reason to avoid using that, as some actual error codes would wrap to zero and look like success... its safer to ... EXIT FAILURE)
If you just want your script to exit with a non-zero code but not raise/show the exception then you can use a bind variable to track the overall status, something like:
set serveroutput on;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
variable exit_code number;
DECLARE
index_not_exists EXCEPTION;
...
sql_to_exec varchar(200);
--is_all_index_created boolean := TRUE;
BEGIN
:exit_code := 0;
...
FOR l_index IN ind_to_create.FIRST..ind_to_create.LAST LOOP
BEGIN
dbms_output.put_line(ind_to_create(l_index));
EXECUTE IMMEDIATE ind_to_create(l_index);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR WITH ' || ind_to_create(l_index));
--is_all_index_created := FALSE;
:exit_code := 1;
END;
END LOOP;
-- IF NOT is_all_index_created THEN
-- RAISE_APPLICATION_ERROR(-20001, 'PROBLEM CREATING INDEX');
-- END IF;
-- optional
IF :exit_code != 0 THEN
dbms_output.put_line('PROBLEM CREATING INDEX');
END IF:
END;
/
--exit
exit :exit_code;
Instead of your current boolean flag, this sets the exit_code bind variable to 1 if an error occurs, and exists with that; and you'll still have the index-specific dbms_output messages you can review. The PL/SQL block no longer raises an exception, so you won't get the ERROR AT LINE 1 in the output.