Re: DBMS_SQL line length over 255 chars

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 01 Nov 1999 08:58:06 -0500
Message-ID: <XpsdOI05OgB7RJLAp9NYQUhKEV89@4ax.com>

A copy of this was sent to CC <kkons_at_intrasoft.gr> (if that email address didn't require changing) On Mon, 01 Nov 1999 13:46:04 +0200, you wrote:

>
>hi all,
>
>I 'm using the DBMS_SQL package and the problem is that the
>DBMS_SQL.PARSE can't parse a line that is longer than 255 characters.
>Is there a workaround for that problem?
>
>The line I 'm trying to parse is an "insert into ... select ..."
>statement
>
>
>thanks in advance

dbms_sql can handle upto 32k. I'll guess that you are using DBMS_OUTPUT to print the sql statement before actually parsing it and the error is actually that dbms_output has a max string length of 255. You should either:

• dbms_output.put_line( substr( the_string, 1, 250 ) || '...' );
• procedure p( p_str in varchar2 ) is l_str long := p_str; begin while ( l_str is not null ) loop dbms_output.put_line( substr( l_str, 1, 250 ) ); l_str := substr( l_str, 251 ); end loop; end;

  and then use procedure p instead of dbms_output.put_line.

there is a variant of dbms_sql.parse (overloaded procedure) of dbms_sql that takes an array of string for things over 32k. for example:

declare

    l_stmt          dbms_sql.varchar2s;
    l_cursor        integer default dbms_sql.open_cursor;
    l_rows          number  default 0;

begin

    l_stmt(1) := 'insert';
    l_stmt(2) := 'into foo';
    l_stmt(3) := 'values';
    l_stmt(4) := '( 1 )';

    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;
      for i in l_stmt.first .. l_stmt.last loop
        dbms_output.put_line( l_stmt(i) );
      end loop;
      raise;

end;
/

--
See http://osi.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 Nov 01 1999 - 07:58:06 CST