Can a function execute dml stmts.
Below is the message when tried to execute a function with an update/insert stmts
ORA-06571: Function TESTUPD does not guarantee not to update database
Printable View
Can a function execute dml stmts.
Below is the message when tried to execute a function with an update/insert stmts
ORA-06571: Function TESTUPD does not guarantee not to update database
You are trying to execute this function within a SQL statement. A function executing DML cannot be part of a SQL statement:
To prevent side effects, the following rules apply
to stored functions called from SQL statements:
- The function cannot execute an INSERT, UPDATE, or DELETE statement.
- Remote or parallelized functions cannot read or write the
values of package variables.
- Only functions called from a SELECT, VALUES, or SET clause can write the
values of packaged variables.
- The function cannot call another subprogram that does not follow the
previous rules, and it cannot reference a view that does not follow the
previous rules.
You didn't provide much information on how/when you want this function to execute. If you don't need it to be in the SQL statement, just call it within the trigger body:
declare
x number;
begin
x :=myfunc;
end;
You may also need to add a PRAGMA RESTRICT_REFERENCES(function name, WNDS,RNDS,WNPS,RNPS) or some combination to make the dtabase let you use the function. I think you can get away without in 8i.
the pragma is only needed to allow a function/procedure within a package to be executed within a sql statement. This is related to a concept called "Purity", which essentially means "how wide ranging are the effects of this function/procedure?". Since Oracle won't allow you to execute DML within a sql statement, it looks at the code in functions/procedures before executing them, to determine the level of purity. Oracle does not look at functions/procedures within packages. Thus if you call a procedure/function within a package in a sql statement, Oracle assumes the function is not "pure"; the assumption is that the function does have side effects on other data object and thus Oracle will not execute it. To get around this, you can more or less promise to Oracle that your intentions (and your function/procedure) is pure by including the pragma described by bmycroft.
Hope this helps!
Gunas,
In order to use user defined function in SQL stmt u need to define the Pragma Restrict referrence in u r function.
--Sriram.
In 8i, you do not need to use the pragma in a function. I just tested it.
Also, as kmesser has allready mentioned, you don't need pragma in pre-8i releases if the function is a standalon function. Pragma was required only for packaged functions if they ought to be called from SQL statements.