Re: string > 255 bytes through dynamic pl/sql

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 24 May 1999 13:18:05 GMT
Message-ID: <37494f0c.2257135@inet16.us.oracle.com>

On Sun, 23 May 1999 20:26:21 +0100, "tim moger" <tmoger_at_proweb.co.uk> wrote:

>does anyone know how to parse and execute a string more than 255 characters
>long through dynamic pl/sql
>
>I am trying to insert into a large table (about 50 columns) and the string
>is becoming rather long.
>

dbms_sql will parse strings up to 32K. If you go beyond that limit then you can break your statement up using

  procedure parse(c in integer, statement in varchar2s,

                  lb in integer, ub in integer,
                  lfflg in boolean, language_flag in integer);

  --  Parse the given statement in the given cursor.  The statement is not in
  --  one piece but resides in little pieces in the PL/SQL table "statement".
  --  Conceptually what happens is that the SQL string is put together as
  --  follows:
  --  String := statement(lb) || statement(lb + 1) || ... || statement(ub);
  --  Then a regular parse follows.
  --  If "lfflg" is TRUE then a newline is inserted after each piece.

• For further information and for documentation on the rest of the
• arguments see the regular parse procedure below.

eg.

SQL> desc t

 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                                       NUMBER
 VAL                                      VARCHAR2(100)

SQL> declare

  2    l_stmt dbms_sql.varchar2s;
  3    l_cursor number;
  4    l_status number;

  5 begin

  6    l_stmt(1) := 'insert into t ( ';
  7    l_stmt(2) := 'id,';
  8    l_stmt(3) := 'val ) ';
  9    l_stmt(4) := 'values ';
 10    l_stmt(5) := '( 1, ''chris'' )';

 11
 11 l_cursor := dbms_sql.open_cursor;  12 dbms_sql.parse( l_cursor, l_stmt, 1, 5, false, dbms_sql.native );  13 l_status := dbms_sql.execute( l_cursor );  14 dbms_sql.close_cursor( l_cursor );  15 end;
 16 /
PL/SQL procedure successfully completed.

SQL> select * from t;

        ID VAL

---------- --------------------
         1 chris

hope this helps.

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon May 24 1999 - 08:18:05 CDT