Re: Help Requested on Pragma

you could try writing a function that returns an SQL statement as a VARCHAR2, which could then be dynamically parsed and executed.

HTH "Peter Karl Lichtenwagner" <peter_at_monochrom.at> wrote in message news:Z02N4.40719$6X3.1004883_at_news.chello.at...
> 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