Re: dbms_sql question

From: Ethan Post <epost1_at_my-deja.com>
Date: Mon, 05 Feb 2001 17:40:03 GMT
Message-ID: <95mohh$50r$1@nnrp1.deja.com>

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

---

FUNCTION get_val_from_sql (p_sql in varchar2) return number is

  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;

---

In article <t7a9evamknlh4d_at_xo.supernews.co.uk>,   "Jeremy Ovenden" <jovenden_at_hazelweb.co.uk> wrote:
> Suppose we have a piece of SQL which is unknown, e.g. it might be
>
> select ename,dept
> from emp;
>
> or it could be
>
> select col1,col2,col3
> from tab1 a,
> tab2 b
> where a.
> .... etc
>
> What I would like to do is execute this piece of SQL using dbms_sql
 but
> would like to be able to get the data returned by the select into
 pl/sql
> variables.
>
> My question is: (how) can I identify the columns that are being
 selected?
>
> BTW looking for a solution on 8.0.5+
>
> --
>
> Jeremy
>
>

Sent via Deja.com
http://www.deja.com/ Received on Mon Feb 05 2001 - 11:40:03 CST