Re: functions, pragmas, and a mystery...

From: David Fetter <dfetter_at_shell4.ba.best.com>
Date: 05 Nov 1999 22:25:37 GMT
Message-ID: <38235961$0$234@nntp1.ba.best.com>

Michel Cadot <micadot_at_netcourrier.com> wrote:
> There is 2 points:
> First, you missed a / between the "end" of the "create package"
> block and the "create package body" one.

OK, that's fixed.

> Second, you can't use the pragma restricted_references(foo, wrds)
> because you use the package dbms_sql that does not garanties not
> writing in the DB.

How do I make it guarantee this? I thought I was doing the right thing. Code and errors follow...

CREATE OR REPLACE PACKAGE analyse_this
AS

    FUNCTION count_rows(table_name_in IN VARCHAR2)

        RETURN INTEGER;
    PRAGMA RESTRICT_REFERENCES(count_rows, WNDS); END analyse_this;
/

CREATE OR REPLACE PACKAGE BODY analyse_this AS

    v_cursor     INTEGER          := 0;
    v_getcount   VARCHAR2(255)    := NULL;
    v_numrows    INTEGER          := 0;
    FUNCTION count_rows(table_name_in IN VARCHAR2)
        RETURN INTEGER

    IS
    BEGIN

        v_cursor   := DBMS_SQL.OPEN_CURSOR;
        v_getcount := 'SELECT COUNT(*) FROM ' || table_name_in;
        BEGIN
            DBMS_SQL.PARSE(v_cursor,v_getcount,DBMS_SQL.V7);
            v_numrows := DBMS_SQL.EXECUTE(v_cursor);
        END;
        RETURN v_numrows;

    END count_rows;
END analyse_this;
/

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY ANALYSE_THIS:

LINE/COL ERROR

-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
6/5      PLS-00452: Subprogram 'COUNT_ROWS' violates its associated pragma

-- 
            David Fetter         888 O'Farrell Street Apt E1205
       dfetter_at_best.com           San Francisco, CA 94109-7089 USA
  http://www.best.com/~dfetter     +1 415 567 2690 (voice)

print unpack ("u*",q+92G5S="!!;F]T:&5R(%!E<FP_at_2&%C:V5R"@``+)

Today I met with a subliminal advertising executive for just a second.

                                       Steven Wright

Received on Fri Nov 05 1999 - 16:25:37 CST