Re: DBMS_SQL question ?

In article <4t8jm9$7sm_at_gateway.marconi.ca>, TLa_at_mtl.marconi.ca (Tan La) writes:
>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;

                        ^^^^^^^  NEED (cur_name)


>     return(rows_count);

This didn't work.

SELECT XXX('SELECT COUNT(*) FROM DUAL') FROM DUAL; ORA-06571: Function XXX does not guarantee not to update database

--
David Bratton
University of Houston System
DBratton_at_uh.edu