fuctions and DBMS_SQL package

From: gocham <gocham_at_mci2000.com>
Date: Sun, 31 Jan 1999 13:41:05 GMT
Message-ID: <R3Zs2.5$C8.870@news.cwix.com>

Hi,

Whenever I try to use DBMS_SQL package within a function I'm getting error on functon execution
ORA-06571:
Finction doesn't guarantee not to update database

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;

Received on Sun Jan 31 1999 - 07:41:05 CST