Re: Dynamically spescifying a select clause for a cursor

In article <3516f5df.18026407_at_news.okay.net>,   peter.schneider_at_okay.net (Peter Schneider) wrote:
>
> On Mon, 23 Mar 1998 16:19:55 -0600, fkgrass_at_hotmail.com wrote:
>
> >How do I dynamically specify the select for a cursor. Here is what I want
 to
> >do. This is just an example of what i want to do.
> >declare
> >c_cursor cusor;
> >l_sql long:= 'select * from dual';
> >begin
> > open c_cursor for l_sql;
> > for em in c_cursor loop
> > ....
> > end loop;
> >end;
> >
> >Any ideas?
> >
> >Thanks,
> >Fred Grass
>
> Hi Fred,
>
> you can do it with dynamic SQL using the DBMS_SQL package. A simple
> example which retrieves all empnos and enames from Scott's well known
> emp table could look like this:
>
> DECLARE
> hndl INTEGER; -- the cursor handle
> exec INTEGER; -- dummy return value for DBMS_SQL.EXECUTE
> stmt VARCHAR2(32767); -- holds our statement
>
> -- some sample data from the emp table
> v_empno NUMBER;
> v_ename VARCHAR2(30);
>
> BEGIN
>
> -- build a dynamic statement
> stmt := 'SELECT empno, ename FROM emp';
>
> -- obtain a cursor handle
> hndl := DBMS_SQL.OPEN_CURSOR;
> -- parse the statement
> DBMS_SQL.PARSE(hndl, stmt, DBMS_SQL.V7);
>
> -- define columns for the data we want to retrieve
> DBMS_SQL.DEFINE_COLUMN(hndl, 1, v_empno);
> DBMS_SQL.DEFINE_COLUMN(hndl, 2, v_ename);
>
> -- now execute our statement
> exec := DBMS_SQL.EXECUTE(hndl);
>
> -- as long as there are rows in the result set...
> WHILE (DBMS_SQL.FETCH_ROWS(hndl) <> 0) -- fetch them...
> LOOP
>
> -- and get the data
> DBMS_SQL.COLUMN_VALUE(hndl, 1, v_empno);
> DBMS_SQL.COLUMN_VALUE(hndl, 2, v_ename);
>
> END LOOP;
>
> -- close the cursor when we're done
> DBMS_SQL.CLOSE_CURSOR(hndl);
>
> EXCEPTION WHEN others THEN
> IF DBMS_SQL.IS_OPEN(hndl) THEN
> -- don't forget to close the cursor in case of error
> DBMS_SQL.CLOSE_CURSOR(hndl);
> END IF;
> RAISE;
>
> END;
>
> You can find all details about this powerful package in the Oracle
> developer docs; a short but quite good overview is given in the
> package spec of DBMS_SQL itself.
>
> HTH
> Peter
>
> --
> Peter Schneider
> peter.schneider_at_okay.net
>

After I run the command

   hndl := DBMS_SQL.OPEN_CURSOR;
hndl is null and any other dbms_sql commands error out.

Any Suggestions?

Thanks,
Fred Grass

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Mar 30 1998 - 00:00:00 CST