PL/SQL Difference Between Oracle 8 and Oracle 9 1

[erwan]

I have PL/SQL procedures which are running well on Oracle 8 /Unix Aix4.
It is now impossible to create/compile them on Oralce 9/Aix 5 because some
lines (list of columns)od code are too long.

Have you ever had this trouble?
Is there parameters to change to increase the line length?
Thanks for your help!

 

[SantaMufasa]

Erwan,

I've been running Oracle 9.2.0.4 since it came out and have not noticed any PL/SQL problems. Could you please copy and paste the offending code plus error message(s)?

Mufasa
(aka Dave of Sandy, Utah, USA @ 10:55 (22Jan04) GMT, 03:55 (22Jan04) Mountain Time)

 

[erwan]

Hello,
I don't have the right to copy some codes and there is no special error message .

For exemple, i have :
update TB35M set A00201=nd.A00201,A6470=nd.A6470, ....... ,A7390U=nd.A7390U,A7390V=nd.A7390V,A7390W=nd.A7390W,A7390X=nd.A7390X,A7390Y=nd.A7390Y,A7390Z=nd.A7390Z,A7390A=nd.A7390A,A7390B=nd.A7390B,A7390C=nd.A7390C,A7390D=nd.A7
A7795=nd.A7795,A7805=nd.A7805,A7785=nd.A7785,A7735=nd.A7735,230S=nd.A1230S,B6260=nd.B6260,A7740=nd.A7740,A6290=nd.A6290,A6110=nd.A6110,A6100=nd.A6100,A6180=nd.A6180,G100ZZ=nd.G100ZZ,G180ZZ=nd.G180ZZ,A6520=nd.A6520,A6435=nd.A6435,A0870=nd.A0870,A0890=nd.A0890,A64403=nd.A64403,A6420=nd.A6420,A7900=nd.A7900,B8100=nd.B8100,A1230M=nd.A1230M,A1230N=nd.A1230N,A1230O=nd.A1230O

After 987 characters, it makes a carriage return and unfortunatly is it in a field name ..
Then the error can be different depending the carriage return.
Do you think it is possible to increase the length size of the line?

 

[SantaMufasa]

Erwan,

First of all, there is nothing about Oracle SQL that requires your code lines to be soooooooo loooooooong. (In fact, your long lines (above) are what are unnecessarily causing the strange, super-wide display of your post, above.) You can place a carriage return, without risk, at any natural token boundary (that is, wherever a blank space or syntactical punctuation [such as a comma] appears). It is not good coding form to place a carriage-return in the middle of a literal or token name (such as a SQL reserved word or a user-supplied name).

Make these carriage-return changes, then post your results.

Mufasa
(aka Dave of Sandy, Utah, USA @ 11:18 (22Jan04) GMT, 04:18 (22Jan04) Mountain Time)

 

[erwan]

HEllo,
We have a process which create the procedure.
Our tables have lot of colunms that why the lines are so long.

When the procedure is created, the list of column name is created on 1 line. And it's oracle which make a carriage return after about 1000 characters. And moste of the time the colums name is cut.

For exemple, in the script i have :
A00201=nd.A00201,A6470=nd.A6470, ....... ,A7390U=nd.A7390U,A7390V=nd.A7390V in 1 line

And if i see the procedure with Schema manager, i have
update TB35M set A00201=nd.A00201,A6470=nd.A6470, ....... ,A7390U=nd.A73
90U,A7390V=nd.A7390V... on 2 lines and the field A7390U is cut as A73 on the first line and 90U on the 2nd line
Then the procedure cannot be compiled.

I didn't have this error.

 

[SantaMufasa]

Erwan,

Oracle does not have a "hard" boundary near the 1000-character location to which you refer. I just successfully ran a SELECT that contains 2200 characters on one line without SQL or SQL*Plus imposing a line break. If you are GENERATING this code from SQL to a screen or a file, then you are subject to the generating software's native or user-modified LINESIZE. For example, if you are using SQL*Plus to generate the SQL, and if SQL*Plus's current LINESIZE setting is 1000, then you will see the behaviour of which you speak. SQL*Plus, for example, allows a 32767-maximum LINESIZE setting. If you are using some type of SQL-writing-SQL code-generation method, then I would look at your user-modifiable output-line-size setting.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:05 (22Jan04) GMT, 11:05 (22Jan04) Mountain Time)