Re: Problem with execute immediately ....( clob variable)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 17 Jan 2000 08:14:46 -0500
Message-ID: <8b568sko3bln3amh35c45liqdhmv0rl3sg@4ax.com>

A copy of this was sent to "George D. Benetos" <gbene_at_intrasoft.gr> (if that email address didn't require changing) On Mon, 17 Jan 2000 11:06:25 +0200, you wrote:

>Hello ,
>Here is the problem.
>There is an command I used "EXECUTE IMMEDIATETELY MAINSQL"
>where mainsql is a string which can not be represented neither as an
>varchar2 nor as long variable , due to the size of the string (over 32000 ,
>which is the limit for a long variable).
>I tried to solve the problem using the "clob " datatype for
>mainsql,but there is an error message about "inconsistent datatypes" .
>Is there any idea ?
>Thank you .....
>

You'll need to use DBMS_SQL to execute a statement >32k. You need to chunk the statement up into 'lines' and execute an 'array' that contains the statement.

here is a small 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 the dbms_sql documentation in the 'supplied packages guide' for more details.

--
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 Jan 17 2000 - 07:14:46 CST