| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Pragma Violation
A copy of this was sent to markag_at_my-dejanews.com
(if that email address didn't require changing)
On Mon, 08 Mar 1999 18:17:25 GMT, you wrote:
>I'm baffled. What's wrong?
>The function works fine when taken out of the package and tested. The problem
>is when I try to compile the package body, I get this error:
>
the function compiles fine standalone cause you are not specifying the pragma (when you compile standalone, it is not testing for WNDS)
the problem is you are using dbms_sql and dbms_sql does NOT guarantee to not update the database. you could parse 'update t set ...' just as easily as 'select nvl(....'
you cannot call a function from SQL that uses dbms_sql.
>PLS-00452: SUBPROGRAM 'GET_PROVIDERHASH' violates it's associated pragma
>
>heres the code:
>
>PACKAGE SPEC:
>
>CREATE OR REPLACE PACKAGE JOURNALS AS
>FUNCTION GET_PROVIDERHASH(IN_PROVIDER_ID IN NUMBER, IN_BATCH_IDS IN VARCHAR2)
>RETURN NUMBER;
>PRAGMA RESTRICT_REFERENCES (GET_PROVIDERHASH, WNDS);
>END JOURNALS;
>
>PACKAGE BODY:
>CREATE OR REPLACE PACKAGE BODY JOURNALS
>AS
>
>FUNCTION GET_PROVIDERHASH(IN_PROVIDER_ID IN NUMBER DEFAULT NULL,
> IN_BATCH_IDS IN VARCHAR2 DEFAULT NULL)
>
>RETURN NUMBER
>
>IS
>-- This function returns a summation
> cur INTEGER;
> status INTEGER;
> rows_processed INTEGER;
> v_HASH NUMBER;
> sSQL VARCHAR2(1000);
> HASH NUMBER;
>BEGIN
> sSQL := 'SELECT NVL(SUM(MAINT_PROCEDURE_CPT_CD), 0) AS HASH' ;
> sSQL := sSQL || ' FROM PAT_PROCEDURE PP, MAINT_PROCEDURE MP,
>PAT_PROCEDURE_BALANCE PPB,';
> sSQL := sSQL || ' PAT_FINANCIAL_TRANSACTION PFT,
>MAINT_FINANCIAL_TRANSACTION MFT';
> sSQL := sSQL || ' WHERE PP.PAT_PROCEDURE_ID = PPB.PAT_PROCEDURE_ID';
> sSQL := sSQL || ' AND PP.PROVIDER_ID = ' || IN_PROVIDER_ID;
> sSQL := sSQL || ' AND MFT.FINANCIAL_TRANSACTION_ID =
>PFT.FINANCIAL_TRANSACTION_ID';
> sSQL := sSQL || ' AND PFT.PAT_PROCEDURE_BALANCE_ID =
>PPB.PAT_PROCEDURE_BALANCE_ID';
> IF IN_BATCH_IDS IS NOT NULL THEN
> sSQL := sSQL || ' AND PFT.BATCH_ID IN (' || IN_BATCH_IDS || ')';
> END IF;
> sSQL := sSQL || ' AND PP.PROCEDURE_ID = MP.PROCEDURE_ID';
> cur := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cur, sSQL, DBMS_SQL.v7);
> DBMS_SQL.DEFINE_COLUMN(cur, 1, HASH);
> rows_processed := DBMS_SQL.EXECUTE(cur);
> status := DBMS_SQL.FETCH_ROWS(cur);
> DBMS_SQL.COLUMN_VALUE(cur, 1, HASH);
> v_HASH := HASH;
> RETURN v_HASH;
>
>END GET_PROVIDERHASH;
>END JOURNALS;
>
>
>
>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |