I am getting below error, can anyone please suggest how to correct the below error, and in the function I want return 1 or 0, because this function will be called from front end if insert is successfully I have return 1 or if insert failed then I have return 0
CREATE TABLE TEST_CLOB
SQL> create or replace function fn_clob(p_id number, p_file_name clob)
2 return number is
3 pragma autonomous_transaction;
5 insert into test_clob(id,file_name) values(p_id,p_file_name);
6 return 1;
8 when others then
10 return 0;
SQL> select fn_clob(200,'dsfafasdffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffasfdasdfsadf') from dual
select fn_clob(200,'dsfafasdffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffasfdasdfsadf') from dual
ERROR at line 1:
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
ORA-06512: at "CRIS_ADJUSTMENT_USER.FN_CLOB", line 9
ORA-06519: active autonomous transaction detected and rolled back
It is because, you mentioned rollback in case of an exception, but not when there is a successful call. Autonomous transaction means, it creates a separate session for itself when called and the basic purpose it was introduced was to achieve certain tasks that you could not do in a transaction, for eg. logging. Have a commit or rollback before the return statement should solve your issue.
That is a TERRIBLE thing to want to do for many reasons.
1. use of WHEN OTHERS - that completely hides the real problem from everyone. No one will ever know just what went wrong.
2. you are using PL/SQL to do a job that SQL can do
3. you are HARD CODING a query into the function. That eliminates scalability and makes maintenance more difficult.
4. that is NOT a proper use for autonomous transactions.
5. you are using a SELECT query to alter the database. That hides the true purpose of the function and makes it easy to misuse the function.
Abandon that idea and either do the job using pure SQL or use a normal procedure/function to perform the transaction.
Your exception is raised because an autonomous transaction needs to perform either a COMMIT or a ROLLBACK before it returns to the caller.
This works for me:
CREATE OR REPLACE function SCOTT.fn_clob(p_empno number)
return number is
insert into emp_copy (empno) values (p_empno);
commit; -- need a COMMIT or ROLLBACK before the function returns
when others then