Re: DBMS_SQL: Dynamic where clause.....

Hsin-tang Pai wrote:
>
> Hi! Folks:
>
> I got a problem when I tried to create a storage procedure in Procedure
> Builder. I am using Form 4.5 and Oracle Server 7.2. And I need to use
> dynamic where clause in a select statement. So I create a DBMS_SQL
> procedure at "Procedure Builder".
>
> However,after defined the columns value, how could I store those rows
> value? I couldn't assign the :block.item equal to those value. Because
> Procedure Builder will not take this parament. So what shall I do?
> Thank you!
>
> Borren
> bjeng_at_jjma.com
>
>
Not sure if this is what you want, but if you need to store something in PL/SQL across program units, you can use package variables. For example,
create this package spec:

PACKAGE glo_var IS
  storedNum NUMBER(7);
END glo_var;

Now, you can do this:

PROCEDURE hi_there IS
  retrievedNum NUMBER(7);
  cursNo INTEGER;
  rowsAffected INTEGER;
BEGIN
  cursNo := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cursNo, 'select 1234 from DUAL');   rowsAffected := DBMS_SQL.EXECUTE(cursNo);   DBMS_SQL.DEFINE_COLUMN(cursNo, 1, retrievedNum);   rowsAffected := DBMS_SQL.FETCH_ROWS(cursNo);   DBMS_SQL.COLUMN_VALUE(cursNo, 1, retrievedNum);

• Now, here's where you store the value for future
• reference. glo_var.storedNum := retrievedNum; DBMS_SQL.CLOSE_CURSOR(cursNo);

END; And finally...
PROCEDURE show_num IS
BEGIN
  text_io.put_line(to_char(glo_var.storedNum)); END; If you execute the following:

hi_there;
show_num;

then
1234
should be printed in the interpreter.

Of course, I haven't tested any of this, but hopefully you get the idea (the theory is sound, the syntax might be botched).

Hope that's helpful. You can use this form of global variables (IE, a PL/SQL
package as an program unit) in all Developer/2000 applications.

• Art Clarke