Discussion in 'SQL PL/SQL' started by bnramesh8, May 12, 2015.
Can we call a procedure inside a function? If that procedure having dml operations?
Yes, if procedure has pragma autonomous_transaction
if you are going to cause the function changing data from sql, such approach isn't absolutely correct.
there can be problems with logical integrity of data
No. If you call such a function from a SQL statement, there will be absolutely no problems with data integrity.
That is the case because the query will fail with an error. Test case below:
First -- create the table to be updated as well as a simple procedure to perform DML and a function to call it:
CREATE TABLE tmp_tab (
CREATE PROCEDURE tmp_tab_proc
INSERT INTO tmp_tab VALUES (1);
CREATE FUNCTION tmp_tab_func
RETURN 'It works!';
If the function is called from SQL, it will fail:
SELECT tmp_tab_func FROM dual;
Error starting at line : 18 in command -
SELECT tmp_tab_func FROM dual
Error report -
SQL Error: ORA-14551: cannot perform a DML operation inside a query
However, the function can be called from PL/SQL without an issue:
v_retval := tmp_tab_func;
DBMS_OUTPUT.PUT_LINE('The result of tmp_tab_func is: ' || v_retval);
The result of tmp_tab_func is: It works!
the technical problem isn't present to use autonomous transaction and to cause function in sql.
if function to change data on customer accounts in autonomous transaction - not difficultly to provide what there will be a result....
...after all use of autonomous transaction are specific tasks....