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
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:
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.
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.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?