Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_SQL parsing error
A copy of this was sent to Greg Postlewait <greg_at_gpconsulting.com>
(if that email address didn't require changing)
On Sat, 28 Aug 1999 13:41:36 -0700, you wrote:
>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.
>
>* the problem was that I started using new style comments (e.g. -- at
>the front of a line), with DBMS_SQL apparently doesn't like yet. Once I
>cleared those out, things began working fine again.
>
>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.
>
[snip]
it is almost the same as the 'regular' dbms_sql.parse. Load up and array (plsql table) of type dbms_sql.varchar2s, and parse the array. Here is an example:
declare
l_stmt dbms_sql.varchar2s; l_cursor integer default dbms_sql.open_cursor; l_rows number default 0;
l_stmt(1) := 'create'; l_stmt(2) := 'table'; l_stmt(3) := 'foo ( x int primary key )'; dbms_sql.parse( c => l_cursor, statement => l_stmt, lb => l_stmt.first, ub => l_stmt.last, lfflg => TRUE, language_flag => dbms_sql.native );l_rows := dbms_sql.execute(l_cursor);
dbms_sql.close_cursor( l_cursor );
exception
when others then
if dbms_sql.is_open(l_cursor) then dbms_sql.close_cursor(l_cursor); end if; raise;
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 30 1999 - 07:55:43 CDT