problem with DBMS_SQL.Parse function

From: <ravuri_raj_at_emc.com>
Date: Wed, 16 Dec 1998 15:54:49 GMT
Message-ID: <758l48$jp8$1@nnrp1.dejanews.com>

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:

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 Received on Wed Dec 16 1998 - 09:54:49 CST