DBMS_SQL and large files

From: Greg Postlewait <greg_at_gpconsulting.com>
Date: Sun, 22 Aug 1999 15:33:54 -0700
Message-ID: <37C07AD2.157E6900@gpconsulting.com>

Here is a very handy procedure that works great with small files. It is a process that reads in a SQL script and processes it--- very handy for making very flexable processing systems.

Problem is that I'm now using it against much larger script files. The docs refer to the using of the VARCHAR2S data type when working with scripts larger than 32K, but really gives no examples or detailed information on how to do this.

Has anyone used this before and can lend a hand on this code.

Thanks!

Greg

---

create or replace procedure sp_execute_script(a_dir_name varchar2, ascript_name varchar2) as
  script_FileHandle UTL_FILE.FILE_TYPE;   aline varchar2(255);
  script varchar2(4096); -- this should be varchar2S, but it then causes a lot of other weird errors
  v_cursor_id integer;
  v_dummy integer;
  stage integer;
begin
/* */
/* get the script */
/* */

  stage := 1;
  script_FileHandle := UTL_FILE.FOPEN( a_dir_name, ascript_name, 'r');

  script := '';
  loop
    begin

      utl_file.get_line(script_fileHandle,aline);
      script := script || aline;
    exception
      when NO_DATA_FOUND then
        exit;

    end;
  end loop;
  UTL_FILE.FClose_All;
/* */
/* run it */
/* */

  stage := 2;
  v_cursor_id := dbms_sql.open_cursor;
  stage := 3;
  DBMS_SQL.parse(v_cursor_id, script,dbms_sql.native);   stage := 4;
  v_dummy := DBMS_SQL.execute(v_cursor_id);   stage := 5;
  DBMS_SQL.close_cursor(v_cursor_id);
EXCEPTION
   when UTL_FILE.INVALID_OPERATION then     UTL_FILE.FClose_All;
     raise_application_error (-20051, 'Invalid Operation in stage ' || to_char(stage));

   when UTL_FILE.INVALID_FILEHANDLE then     UTL_FILE.FClose_All;
     raise_application_error (-20052, 'Invalid File Handle in stage ' || to_char(stage));

   when UTL_FILE.INVALID_PATH then
    UTL_FILE.FClose_All;
     raise_application_error (-20053, 'Invalid File Path: ' || a_dir_name);

   when others then
    UTL_FILE.FClose_All;
     raise_application_error (-20054, 'General Error stage ' || to_char(stage) || script);

     raise;
end; Received on Sun Aug 22 1999 - 17:33:54 CDT