Re: problem with DBMS_SQL.Parse function

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 16 Dec 1998 21:47:55 GMT
Message-ID: <367d29dd.20667888@192.86.155.100>

A copy of this was sent to ravuri_raj_at_emc.com (if that email address didn't require changing) On Wed, 16 Dec 1998 15:54:49 GMT, you wrote:

>Hi
>I am using VB 6.0 with ActiveX Data Objects (ADO 2.0) and Oracle 7.3.2.3.1.
>
>I am using DBMS_SQL.PARSE function to parse a SQL statement before execution.
>Whenever the SQL string that I am parsing is beyond a certain length above 250
>characters I get the following message :
>
>
>ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
>ORA-06512: at "SYS.DBMS_OUTPUT", line 99
>ORA-06512: at "SYS.DBMS_OUTPUT", line 65
>ORA-06512: at "CPSDBO.SQLPROG655351120", line 205
>ORA-06512: at line 2
>
>A small snippet of my code is given below:
>

the code below is NOT causing the error tho. the procedure SQLPROG655351120 is. dbms_sql handles stuff much larger then 255 bytes (an example of 2026 bytes done in 7.3.4 follows).

You need to look at line number 2055 of SQLPROG655351120. eg: execute:

select line, text from user_source where name = 'SQLPROG655351120' and line between 200 and 210 order by line;

the following demonstrates that dbms_sql.parse works with at least 2,026 bytes.... I believe it goes to 32k and if the statement is larger then use dbms_sql.parse with a pl/sql table...

SQL> create or replace procedure execute_immediate( sql_stmt in varchar2 )   2 as

  3      exec_cursor     integer default dbms_sql.open_cursor;
  4      rows_processed  number  default 0;
  5  begin
  6          dbms_output.put_line( 'statement length = ' || length(sql_stmt));
  7          dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
  8          rows_processed := dbms_sql.execute(exec_cursor);
  9          dbms_sql.close_cursor( exec_cursor );
 10  exception
 11      when others then
 12        if dbms_sql.is_open(exec_cursor) then
 13          dbms_sql.close_cursor(exec_cursor);
 14        end if;
 15        raise;

 16 end;
 17 /

Procedure created.

SQL>
SQL> drop table t;

Table dropped.

SQL> create table t ( x varchar2(2000) );

Table created.

SQL>
SQL> begin
  2 execute_immediate( 'insert into t values ( ''' || rpad( '*', 2000, '*' ) || ''')' );
  3 end;
  4 /
statement length = 2026

PL/SQL procedure successfully completed.

>----------------------------------------
>PROCEDURE InsertSystemDefaults(sInputValues IN VARCHAR2,
>lSystemDefaultId OUT NUMBER, sStatus OUT VARCHAR2)
>IS
> sInsertValues VARCHAR2(1000); -- The Insert string
> iCur INTEGER;
> iRetCd INTEGER;
>begin
> iCur := DBMS_SQL.OPEN_CURSOR;
>
> sInsertValues := 'INSERT INTO CPSDBO.SYSTEM_DEFAULTS ' ||
> 'VALUES(' || sInputValues;
> DBMS_SQL.PARSE(iCur, sInsertValues, DBMS_SQL.V7);
>
> iRetCd := DBMS_SQL.EXECUTE(iCur);
>...........
>..........
>..........
>exception
>........
>.......
>.......
> when others then
>
> declare
> nErrorCd NUMBER := sqlcode;
> sErrorMsg VARCHAR2(200) := sqlerrm;
>
>
> begin
>......
>.......
>.......
>
> DBMS_SQL.CLOSE_CURSOR(iCur);
>DBMS_OUTPUT.PUT_LINE('The Error Number :' || nErrorCd );
> DBMS_OUTPUT.PUT_LINE('The Error Msg :' || sErrorMsg); end;
>-----------------------------------------------------------------------------
>---
>
>Does any one know if there is a limit on the size of the STATEMENT variable in
>the call to DBMS_SQL.PARSE( cursor IN INTEGER, statement IN VARCHAR2,
>language_flag); Is there any way to specify the values in the string variable
>on multiple lines to get around the "255 bytes per line overflow" error.
>
>Please send me your response to ravuri_raj_at_emc.com
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Dec 16 1998 - 15:47:55 CST