| ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML [message #590972] |
Wed, 24 July 2013 07:02  |
na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore
|
Member |
 
|
|
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
Thanks
Dharmendran
|
|
|
|
|
|
| Re: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML [message #590976 is a reply to message #590972] |
Wed, 24 July 2013 07:08  |
 |
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
Hi,
According to the documentation:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#CHDJJCEC
Quote:
PL/SQL Functions that SQL Statements Can Invoke
To be invocable from SQL statements, a stored function (and any subprograms that it invokes) must obey these purity rules, which are meant to control side effects:
When invoked from a SELECT statement or a parallelized INSERT, UPDATE, DELETE, or MERGE statement, the subprogram cannot modify any database tables
. . .
Regards,
Dariyoosh
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
