Re: Cursor with dynamic query

"Yann CAUCHARD" <yann.cauchard_at_spcconsultants.com> wrote in message news:9ltbmu$a4n$1_at_news6.isdnet.net...
> Something like :
>
> temp_cur integer;
> temp_count number;
> Status integer;
> num_recs integer;
>
> begin
> temp_cur := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(temp_cur,'select count(distinct '||col_name||') from
> '||tab_name
> ||' where '||where_qry,
> DBMS_SQL.NATIVE);
> DBMS_SQL.DEFINE_COLUMN (Temp_Cur, 1, Temp_Count);
> Status := DBMS_SQL.EXECUTE (Temp_Cur);
> IF (DBMS_SQL.FETCH_ROWS (Temp_Cur) > 0) THEN
> DBMS_SQL.COLUMN_VALUE (Temp_Cur, 1, Temp_Count);
> num_recs := Temp_Count;
> END IF;
> DBMS_SQL.CLOSE_CURSOR (Temp_Cur);
> .....
>
> Yann

There's an easier way (with 8i and up though). Use EXECUTE IMMEDIATE and you don't need to do all that DBMS_SQL stuff.

-Matt Received on Sun Aug 26 2001 - 10:45:50 CDT