Re: Help Requested on Pragma

DBMS_SQL does not and cannot guarantee any purity level. Your function inherits this problem.

regards, peter

Abhijit Bhattacharya <abhi_mita_at_yahoo.com> wrote in message news:390472D4.74448C96_at_yahoo.com...
> Here is the package I have coded and trying to compile
>
> My intention is to use calcKPI.calculateKPI function in SQL statements.
> But during compilation I encounter the following error -
>
> 0/0 PL/SQL: Compilation unit analysis terminated
> 2/4 PLS-00452: Subprogram 'CALCULATEKPI' violates its associated
> pragma
>
> I tried with various combinations of pragma definition. None of them
> worked.
> I will appreciate any insight.
>
> - Abhijit
>
>
> SQL> !cat test_func1.sql
> CREATE OR REPLACE PACKAGE calcKPI AS
> FUNCTION calculateKPI(v_rowid IN VARCHAR2) RETURN NUMBER;
> PRAGMA RESTRICT_REFERENCES(calculateKPI,RNDS,WNDS) ;
> END calcKPI;
> /
> CREATE OR REPLACE PACKAGE BODY calcKPI AS
> FUNCTION calculateKPI(v_rowid IN VARCHAR2) RETURN NUMBER IS
> CURSOR ods_composite_kpis_cur IS
> select operator,(child_kpi_id) child_kpi
> from ods_composite_kpis
> where child_kpi_id not in
> (select distinct parent_kpi_id from ods_composite_kpis)
> connect by prior child_kpi_id = parent_kpi_id
> start with parent_kpi_id = 195;
> str VARCHAR2(4000) ;
> ods_cursor INT;
> retValue INT;
> loopIdx INT;
> total_sum NUMBER ;
> BEGIN
> str := 'SELECT ';
> loopIdx := 0 ;
> FOR ods_composite_kpis_var IN ods_composite_kpis_cur
> LOOP
> BEGIN
> SELECT str|| ' NVL(' ||
> DECODE(loopIdx,0,'',ods_composite_kpis_var.o
> perator) || '1*' ||fact_column_name || ',0) + '
> INTO str
> FROM ods_kpis
> WHERE kpi_id = ods_composite_kpis_var.child_kpi;
> loopIdx := loopIdx + 1;
> END;
> END LOOP;
> str := str || ' 0 total_sum ' || ' FROM ods_facts WHERE rowid =
> '''||v_row
> id||'''' ;
>
> ods_cursor := DBMS_SQL.OPEN_CURSOR;
> DBMS_OUTPUT.PUT_LINE(substr(str,1,100)) ;
> DBMS_OUTPUT.PUT_LINE(substr(str,101,200)) ;
> DBMS_SQL.PARSE(ods_cursor,str,DBMS_SQL.NATIVE);
> /* define the column to be selected ods_cur */
> DBMS_SQL.DEFINE_COLUMN(ods_cursor,1,total_sum);
> retValue := DBMS_SQL.EXECUTE(ods_cursor) ;
> IF DBMS_SQL.FETCH_ROWS(ods_cursor) > 0 THEN
> /* returns the value of cursor position for a given position in
> cursor */
> DBMS_SQL.COLUMN_VALUE(ods_cursor,1,total_sum);
> END IF;
> DBMS_SQL.CLOSE_CURSOR(ods_cursor) ;
> RETURN total_sum;
> END calculateKPI;
> END calcKPI;
> /
>
Received on Mon Apr 24 2000 - 00:00:00 CDT