Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: dbms_sql question
The following procedure is used in my Gnumetrics package to return a value from a SQL, it assumes column 1. You can probably figure how to get other columns from this. Many thanks to Sybrand who actually supplied me with most of this.
Thanks,
Ethan www.freeocp.com, www.gnumetrics.com
cursor1 integer; lv_val metrics.met_sql_val%type;rows_processed number;
BEGIN lv_val := 0;
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(cursor1, p_sql, dbms_sql.native);
dbms_sql.define_column (cursor1, 1, lv_val);
rows_processed := dbms_sql.execute (cursor1);
loop
dbms_sql.column_value (cursor1, 1, lv_val); lv_val := nvl(lv_val, 0); rows_processed := dbms_sql.fetch_rows(cursor1); exit when rows_processed = 0;
end loop;
dbms_sql.close_cursor(cursor1);
return lv_val;
EXCEPTION
when others then
if cursor1 > 0 then dbms_sql.close_cursor(cursor1); end if; return -999888777;
END get_val_from_sql;
Sent via Deja.com
http://www.deja.com/
Received on Mon Feb 05 2001 - 11:40:03 CST