Home -> Usenet -> comp.databases.oracle -> what's wrong with this DYNAMIC SQL ???

what's wrong with this DYNAMIC SQL ???

From: Ronald <_ronr__at__wxs.nl_>
Date: Mon, 04 May 1998 18:14:29 +0200
Message-ID: <B173B60596689423_at_ut0212.wxs.nl> 





This should work I suppose. 


Can anyone tell me what's wrong, or how I can get the exact text that's
been parsed and contains an error ?



Any hint/tip is very much apreciated.




create or replace procedure coa is


  cursor_coalesce integer;


  cursor_files    integer;

  fid             number;
  file_id          number;
  ignore          integer;




BEGIN


  cursor_files    := dbms_sql.open_cursor;
  dbms_sql.parse ( cursor_files, 'select file_id from
sys.dba_data_files', dbms_sql.v7 );




  dbms_sql.define_column (cursor_files, 1, vyl_id);
  ignore := dbms_sql.execute (cursor_files);



  cursor_coalesce := dbms_sql.open_cursor;
  dbms_sql.parse (cursor_coalesce, 


        'alter session set events ''immediate trace name coalesce level
'|| FID|| '''', dbms_sql.v7 );


  LOOP


    if dbms_sql.fetch_rows     (cursor_files) > 0 then
      -- get column value of the row
      dbms_sql.column_value    (cursor_files, 1, file_id);
      dbms_sql.bind_variable   (cursor_coalesce, 'FID', file_id);
      ignore := dbms_sql.execute (cursor_coalesce);
    else
      exit;   -- loop




    end if;


  END LOOP;


  dbms_sql.close_cursor(cursor_coalesce);
  dbms_sql.close_cursor(cursor_files);


EXCEPTION



  when others then

    if    dbms_sql.is_open     (cursor_coalesce) then
          dbms_sql.close_cursor(cursor_coalesce);
    end if;
    if    dbms_sql.is_open     (cursor_files) then
          dbms_sql.close_cursor(cursor_files);


    end if;


    raise;


END;



/




Ronald





mailto:ronr_at_wxs.nl


http://home.wxs.nl/~ronr/RonR.html (last update: dec 31, 1997)
Received on Mon May 04 1998 - 18:14:29 CEST












Original text of this message