Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> DBMS_SQL parsing error
Found the problem I was having...
two things:
* first, my files were less than 32k, so the whole thing about VARCHAR2S
is a moot point. I still have yet to find a decent example of how to
implement this.
Also, I found that the DBMS_SQL.LAST_ERROR_POSITION property very useful in tracking these problems handy, as I started removing things at random the 'position' is actually the byte offset where the error is occuring.
Hope this saves someone a headache or two!
Greg
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 */ /* */
script := '';
loop
begin
utl_file.get_line(script_fileHandle,aline); script := script || aline; exception when NO_DATA_FOUND then exit;
/* */ /* run it */ /* */
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 Sat Aug 28 1999 - 15:41:36 CDT