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!