Re: Help! Dynamic SQL

Jen Chien wrote:
>
> Hi! Folks my question is:
>
> I'm using Oracle server 7.2 and Oracle Form 4.5. I want to use DBMS_SQL to create a procedure for the dynamic "Where Clause". However, after created the procedure, I also do the "DBMS_OUTPUT" to store the query result. I run the procedure at SQLPLUS. It works fine. By yhe way, Oraclr Form v4.5 wouldn't take the DBMS_SQL package. So all the actions I did above were created at Procedure Builder. [Quoted]
> However, I got the problem now. If I want to use this procedure at the Oacle Form. How shall I output those query results to :field.item. Becuase I want people to be able read those query result through :field.item. Can anyone just give me a hand here. Please!
>
> Borren
>
> ***************************************************
> PROCEDURE get_rows(where_clause varchar2) IS
> cursor1 integer;
> rows_processed integer;
> d_id number;
> d_title varchar2(225);
>
> BEGIN
>
> cursor1 := dbms_sql.open_cursor;
> dbms_sql.parse (cursor1, 'select drs_id, documenttitle from document where '||
> where_clause ||'Order by 1', dbms_sql.v7);
> dbms_sql.define_column(cursor1, 1, d_id);
> dbms_sql.define_column(cursor1, 2, d_title, 225);
>
> rows_processed :=dbms_sql.execute(cursor1);
>
> loop
> if dbms_sql.fetch_rows (cursor1) >0 then
> dbms_sql.column_value (cursor1, 1, d_id);
> dbms_sql.column_value (cursor1, 2, d_title);
> dbms_output.put_line(to_char(d_id)); --------------->How could I output this to :field.item?

How about :field.item := to_char(d_id);? Should work fine.

If you are outputting only the two items: id and title, why not make them OUT parameters then:

procedure declare:

     PROCEDURE get_rows(where_clause varchar2,
                        pm_id OUT NUMBER,
                        pm_title OUT VARCHAR2) IS

and call would look like:

     get_rows ('my_where_clause', :b_my_block.ti_id, :b_my_block.ti_title);

and finally, the internals would be

     dbms_sql.column_value(cursor1,1,pm_id);
     dbms_sql.column_value(cursor1,2,pm_title);

That way you would interface with the procedure which would be responsible how it gets the data without overly tying the procedure to one set of rules.

> else
> exit;
> end if;
> end loop;
>
> dbms_sql.close_cursor(cursor1);
> exception
> when others then
> dbms_output.put_line(sqlerrm);
> if dbms_sql.is_open(cursor1) then
> dbms_sql.close_cursor(cursor1);
> end if;
>
> END;
> ***************************************************

Loyal Barber
Edison Group Received on Fri May 23 1997 - 00:00:00 CEST