Re: forms5: dynamic where in procedure?

It sounds like you need to use dynamic sql. The DBMS_SQL package provided by Oracle should provide the functionality that you need. It can provide a lot of flexibility too. You could create a function that accepts a table name and a where clause and returns the number of rows that match the criteria. Below is an example of something you might try.

Hope this helps.
Patrick Flahan
flahan_at_earthlink.net

--This will return the number of rows in a table for a given where clause --NOTE: This could be modified to pass in the several tables as FROM clause CREATE OR REPLACE FUNCTION CountRows(pcTableName IN VARCHAR2, pcWhereClause IN VARCHAR2)
 RETURN NUMBER
IS
  curAction INTEGER := DBMS_SQL.OPEN_CURSOR; --variable holding pointer to dynamic sql cursor
  nReturn INTEGER :=0; --dummy variable   nRowCount NUMBER :=0; --row count, will return this to the calling process
BEGIN
  --parse the sql statement
  DBMS_SQL.PARSE(curAction,'SELECT count(*) FROM '||pcTableName|| ' '||pcWhereClause,DBMS_SQL.NATIVE);

  --associate the column to be returned with a variable of type number. ie tell compiler the selected column is of number datatype   DBMS_SQL.DEFINE_COLUMN(curAction,1,nRowCount);

  --execute the sql statement and fetch the result set   nReturn := DBMS_SQL.EXECUTE_AND_FETCH(curAction);

  --retrieve the value from the result set in column one   DBMS_SQL.COLUMN_VALUE(curAction,1,nRowCount);

  --perform clean up by closing cursor and freeing up memory   DBMS_SQL.CLOSE_CURSOR(curAction);

  RETURN nRowCount;
END;
/

Patrick Flahan
flahan_at_earthlink.net

Gudrun Janssen <janssen_at_itu.fzk.de> wrote in message news:36F7A76A.4C2EA8E7_at_itu.fzk.de...
> Hello,
>
> Im trying to do something like that in procedure program unit:
>
> :misc.my_where_clause is a varchar2 that contains a valid
> where-clause. That where-clause is created dynamically at
> runtime.
>
> BEGIN
> DECLARE
> select count(*)
> into :misc.my_counter
> from my_table
> where :misc.my_where_clause;
> END;
>
> This doesn't work ( I didn't expect it to). I could create
> a dummy-block and set its default-where programmatically.
> But I need this in 9 cases for nine large tables and it would
> lead to nine large dummy-blocks.
>
> Annyone has a better idea?
> Thanks
> Gudrun
>
>
Received on Tue Mar 23 1999 - 19:27:46 CET