functions, pragmas, and a mystery...

From: David Fetter <dfetter_at_shell4.ba.best.com>
Date: 05 Nov 1999 09:48:57 GMT
Message-ID: <3822a809$0$210@nntp1.ba.best.com>

Kind people,

I'm trying to create a function that will count rows (for reporting porpoises :)

So I whipped out a tall stack of Oracle books, and have written the following package, which contains just that function. A error message ensues, and I just don't know what I'm doing wrong here.

What am I missing?

Thanks in advance for any tips, hints, or thoughts on the subject.

Cheers,
David.

  1 CREATE OR REPLACE PACKAGE analyse_this AS   2 FUNCTION foo(table_name_in VARCHAR2)   3 RETURN NUMBER;
  4 PRAGMA RESTRICT_REFERENCES(foo, WNDS);   5 END analyse_this;
  6
  7 CREATE OR REPLACE PACKAGE BODY analyse_this AS   8 FUNCTION foo(table_name_in VARCHAR2)

  9      RETURN NUMBER IS
 10      v_cursor     NUMBER;
 11      v_getcount   VARCHAR2(255);
 12      v_numrows    NUMBER;
 13    BEGIN
 14      v_cursor   := DBMS_SQL.OPEN_CURSOR;
 15      v_getcount := 'SELECT COUNT(*) FROM ' || table_name_in;
 16      BEGIN
 17        DBMS_SQL.PARSE(v_cursor,v_getcount,DBMS_SQL.V7);
 18        v_numrows := DBMS_SQL.EXECUTE(v_cursor);
 19      END;
 20      RETURN v_numrows;

 21 END foo;
 22* END analyse_this;
SQL> show errors
Errors for PACKAGE ANALYSE_THIS:

LINE/COL ERROR

---

---

7/1 PLS-00103: Encountered the symbol "CREATE"

--

          David Fetter               888 O'Farrell Street Apt E1205
       david_at_fetter.org              San Francisco, CA 94109-9046

http://fetter.org/~shackle phone: +1 415 567 2690 fax: +1 415 567 2340

I don't do drugs anymore 'cause I find I get the same effect just by standing up really fast.

                                       Johnathan Katz

Received on Fri Nov 05 1999 - 03:48:57 CST