Re: DBMS_SQL and large files

That would work if it were not for the persistance of local variables that make this work very well. By breaking each file down into stand-alone executes, which has a value onto itself, you would loose the ability to have any variable manupulation that was not resolved within a simple statement.

wasim_ahmed_at_my-deja.com wrote:

> Try breaking ur SQL Statement when the symbol ';' appears in
> script file, execute that statement and then continue again like
>
> a := instr(aline, ';');
> if a <> 0
> then
> script := script || substr( aline, 1, a);
> -- excute the script using DBMS_SQL here
> script := substr(aline, a);
> end if
> -- also execute it for the last statement
>
> regards,
> Wasim.
>
> In article <37C07AD2.157E6900_at_gpconsulting.com>,
> Greg Postlewait <greg_at_gpconsulting.com> wrote:
> > 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;
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Mon Aug 23 1999 - 12:27:48 CDT