Re: DBMS_SQL question ?

In article <4t6p09$fho_at_dailyplanet.wam.umd.edu>, albewong_at_wam.umd.edu says...
>
>How to return a value using dbms_sql ?
>
>I parse a sql statement using dbms_sql.parse for selecting a count(*)
>on a table, now I want to return the count on the table to a variable
>how can I do this ?
>
>
>Thanks ..

Here is how:

   CREATE OR REPLACE FUNCTION XXX(sql_str VARCHAR2) RETURN NUMBER AS

     cur_name   INTEGER;
     dummy      INTEGER;
     rows_count NUMBER;
   BEGIN
     cur_name := dbms_sql.open_cursor;
     dbms_sql.parse(cur_name, sql_str, dbms_sql.v7);
     dbms_sql.define_column(cur_name, 1, rows_count);
     dummy := dbms_sql.execute_and_fetch(cur_name);       
     dbms_sql.column_value(cur_name, 1, rows_count);
     dbms_sql.close_cursor;
     return(rows_count);