Re: Long insert statement in PCC ireclen exceeded.

From: Olli Mikkonen <omikko_at_cs.joensuu.fi>
Date: Mon, 23 Nov 1992 08:26:04 GMT
Message-ID: <1992Nov23.082604.5467_at_cs.joensuu.fi>

kortikar_at_mipos2.intel.com (Aniruddha Kortikar) writes:

>I have a long insert statement in PRO*C insert into table ( ....) values (...)
>ireclen is exceeded.
ireclen is the maximum length of source line. The PCC option does not work. ireclen is always 80 characters. If you have longer source lines, there will occur some kind of a input buffer overflow in PCC, and the results are quite unpredictable (core dump or erraneous code produced).

>I tried
>char stmt[1000];
>sprintf(stmt,"insert into ... values (:v1,:v2);");
>exec sql execute s using :val1,:val2 ....
1. Remove the semicolon from the end of the SQL-statement. 2. There is a bug in Oracle. If you declare a host variable

   as an array of char, Oracle can't always detect the end of the    string. To get around the bug there are several alternatives:

a) Add some spaces to the end of the string.
b) Use VARCHAR as a type the host variable.
c) Try something like this:

char stmt[1000];
EXEC SQL BEGIN DECLARE SECTION;
char *sqlstatement;
EXEC SQL END DECLARE SECTION;
...
sprintf(stmt,"insert into ... values (:v1,:v2);"); sqlstatement = stmt;
EXEC SQL PREPARE S FROM :sqlstatement;
exec sql execute s using :val1,:val2 ....

>but even then the exec sql execute s using is about 300 chars long.
 

>is there any way out other than using :a1,:a2 as variable names.
 

>Aniruddha Kortikar (kortikar_at_mipos2.intel.com)
>-------------------------------------------------------------------------------
>E-mail : kortikar_at_mipos2.intel.com | A billion here, a billion there ...
>Phone(W): 408 765 5515 | pretty soon it adds upto real money.
>-------------------------------------------------------------------------------

The information above is based on my personal experiences with Oracle versions 6.0.3[1-4] and discussions with Oracle (Finland) tech support. People at Oracle may disagree.

Olli Mikkonen

-- 
Olli Mikkonen		: Any opinions above are purely my personal ones.
Internet: 		: Especially they do not reflect my employers
omikko_at_cs.joensuu.fi	: official opinions.

Received on Mon Nov 23 1992 - 09:26:04 CET