Given below a block of code, this code compiled successfully on one DB but returns error on other.
DB version is same. I know sqlerrm can not be used directly but how it compiled successfully on one DB.
l_procedure_name CONSTANT VARCHAR2(100) := 'copy_device_status_tables';
l_procedure_id CONSTANT INTEGER := 301;
select 1 into x from dual;
WHEN OTHERS THEN
INSERT INTO aerbill_traffic.procedure_status(procedure_id,procedure_name,step_description,process_time, status, procedure_status)
VALUES (l_procedure_id,l_procedure_name,substr(l_step_name || '; ' || SQLERRM,1,200), SYSDATE, 1,'Failed');
ORA-06550: line 12, column 75:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 11, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
In the db where its running fine do the following in SQL Plus
1. select * from v$version
2. execute your anonymous block
3. select * from aerbill_traffic.procedure_status; -- Just filter for the one record inserted in step 2.
Now do a copy past of output obtained into this forum.
Why are you using a WHEN OTHERS in the first place? Why swallowing errors you do not expect? Why dont you RAISE the error back to the caller?
Anyway, move the insert_statement into a separate procedure defined as an autonomous transaction with the desired parameters and call that procedure from your exception block.
The SQLERRM function can't be used directly in a sql statement.
In any case, this code is kind of frightening. In any exception you're going to insert into a table AND commit with no re-raise?
The SQLERRM function can't be used directly in a sql statement
Strange thing is that OP stats it works on one DB and doesn't on another.
In any case, this code is kind of frightening.
(hence I posted Tom's rant again, must be the 102389560242634th time... hopefully it enlightens OP )