Help! Dynamic SQL

From: Jen Chien <jenchien_at_seas.gwu.edu>
Date: 1997/05/22
Message-ID: <5m23c1$sg$2_at_cronkite.seas.gwu.edu>#1/1

[Quoted] 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. 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?

  else

    exit;
[Quoted]   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;

---

Received on Thu May 22 1997 - 00:00:00 CEST