Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_SQL parsing error

Re: DBMS_SQL parsing error

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 30 Aug 1999 12:55:43 GMT
Message-ID: <37ca7f15.1647268@newshost.us.oracle.com>


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;

begin
    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;

end;
/

--
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US