Help Requested on Pragma

From: Abhijit Bhattacharya <abhi_mita_at_yahoo.com>
Date: 2000/04/24
Message-ID: <390472D4.74448C96@yahoo.com>#1/1

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