Re: Dynamically spescifying a select clause for a cursor

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