Re: DBMS_SQL and large files

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 - 05:23:29 CDT