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
(
ID NUMBER,
FILE_NAME CLOB
);
SQL> create or replace function fn_clob(p_id number, p_file_name clob)
2 return number is
3 pragma autonomous_transaction;
4 begin
5 insert into test_clob(id,file_name) values(p_id,p_file_name);
6 return 1;
7 exception
8 when others then
9 rollback;
10 return 0;
11 end;
12 /
Function created.
SQL> select fn_clob(200,'dsfafasdffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffasfdasdfsadf') from dual
2 /
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