Re: fuctions and DBMS_SQL package

Stand-alone functions can be scanned by the engine to make sure that it doesn't have any side-effects, like inserting/updating/deleting to the database (such side-effects are NOT allowed in functions called by SQL!). Packaged functions, on the other hand, have their implementation hidden in the package body. In order to call such functions from SQL, you must assert their purity by coding PRAGMA RESTRICT_REFERENCES for that function in the package header (right after the function declaration). Furthermore, if your function calls other packaged procedures/functions, those must also assert their purity; and dbms_sql does not. Therefore, your function cannot be called from SQL (Function doesn't guarantee not to update database) because of its dependency on other routines whose purity have not been asserted. In Oracle8, many more routines do have purity asserted; but not all. In some cases, you can edit their package headers and add the pragma restrict_references to assert their purity. I haven't tried this on dbms_sql routines. But there you have the special problem because you may dynamically (at runtime) issue a DML statement (causing a side-effect), but how will the engine know? For this reason, you probably cannot use dynamic SQL in a function called from SQL. One workaround might be to store the statement text in a PL/SQL table for later execution. I'm not sure if you can use dbms_pipe to send it to another session; depends on whether those routines also have purity asserted.

• Dan Clamage For free, cool PL/SQL utilities, go to http://www.telerama.com/~dclamage

In article <R3Zs2.5$C8.870_at_news.cwix.com>,   "gocham" <gocham_at_mci2000.com> wrote:
> Hi,
>
> Whenever I try to use DBMS_SQL package within a function I'm getting error
> Finction doesn't guarantee not to update database
on functon execution
> ORA-06571:
> >
> Am I doing something wrong?
>
> Thanks
>
> Gocha
>
> CREATE OR REPLACE FUNCTION RECCNT
> ( sTableName IN VARCHAR2) RETURN NUMBER
> IS
> BEGIN
> DECLARE
> iCr INTEGER;
> sCmd VARCHAR2(100);
> iDummy INTEGER;
> iRecCnt NUMBER(20);
> BEGIN
> /*
> This function returns number of records
> in the table.
> param is a table
> */
> iCr:=DBMS_SQL.OPEN_CURSOR;
> sCmd:='SELECT COUNT(*) AS RCnt FROM ' || sTableName ;
> DBMS_SQL.PArse(iCr, sCmd, DBMS_SQL.NATIVE);
> DBMS_SQL.DEFINE_COLUMN(iCr, 1, iRecCnt);
> iDummy:=DBMS_SQL.EXECUTE(iCr);
> IF DBMS_SQL.FETCH_ROWS(iCr) =0 THEN
> DBMS_OUTPUT.PUT_LINE(sCmd);
> DBMS_OUTPUT.PUT_LINE('NO ROWS');
> END IF;
> DBMS_SQL.COLUMN_VALUE(iCr,1,iRecCnt);
> DBMS_SQL.CLOSE_CURSOR(iCr);
> RETURN(iRecCnt);
> -- DBMS_OUTPUT.PUT_LINE(TO_CHAR(iRecCnt));
> END;
> END;
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Feb 02 1999 - 14:41:06 CST