Re: 8i SQL Plus Edit Question

From: <fitzjarrell_at_cox.net>
Date: 28 Sep 2005 10:32:33 -0700
Message-ID: <1127928753.778911.206690@g47g2000cwa.googlegroups.com>

Comments embedded.
Tim Marshall wrote:
> Server 8.1.7.4.0, SQL Plus 8.1.7.0.0 I'm trying to see if SQL PLus can
> accept more characters in an SQL statement than it does.
>
> I am finished working on a pretty hideous SQL statement that was more
> than 10,000 characters*. I use MS Access as a front end tool against
> our Oracle database, using Oracle SQL (bypassing the Jet database engine
> which is integral to Access). This works fine, but when I come up
> against errors in my SQL that don't jump out at me immediately, I like
> to copy the SQL statement to SQL plus which gives me a line location and
> often a specific pointer to where in a line my error is.
>
> However, SQL Plus won't let me paste the entirity of the long SQL into
> it. When I go to edit mode and paste the full SQL into Notepad and then
> close to return to SQL Plus, it only accepts up to a specific amount of
> characters and drops the rest, giving me a message that line whatever
> has been truncated.
>
> I tried going into the environment menu and setting the screen buffer,
> thinking that might do it, but this only allows me to increase from the
> install default (I think) of 1000 to a max of 2000.
>
> I eventually got the SQL running just fine but I'm wondering if and how
> one can adjust SQL Plus to accept more characters.
>

I cannot believe SQL*Plus is limiting you on the total number of characters in your query. I CAN believe SQL*Plus is limiting the length of a single line. Many of the wrapped package bodies supplied by Oracle greatly exceed this 10,000 character 'limitation' you've apparently stumbled across; character counts for some of the .plb files are listed below:

24419 dbmsaq.plb
61318 dbmsaqds.plb
26699 dbmsaw.plb
11199 dbmsrmin.plb
773060 owmadmb.plb
68939 owmadms.plb
38484 owmaqb.plb
453277 owmcddlb.plb

16501 owmctab.plb
27913 owmctrg.plb
99872 owmctxb.plb
21109 owmctxs.plb
44880 owmcvws.plb
16058 owmd901.plb

345784 owmddlb.plb
577611 owmdtrgb.plb
817109 owmdutlb.plb
39329 owmdutls.plb

37224 owmerrb.plb
13133 owmexpb.plb
813416 owmltb.plb
52440 owmltrb.plb
27070 owmltrs.plb
156276 owmlts.plb
70421 owmmigb.plb
49043 owmprvb.plb

13095 owmr9013.plb
257985 owmreplb.plb
350389 owmricb.plb
51407 owmu901.plb
14373 owmuinst.plb
308891 owmutlb.plb
32226 owmutls.plb
374443 owmutrgb.plb

18057 owmv9013.plb
12065 owmv9014.plb
48673 prvtalrt.plb
153293 prvtany.plb

36350 prvtaq.plb
52741 prvtaq8x.plb
94178 prvtaqad.plb
152685 prvtaqal.plb
30788 prvtaqdi.plb
708875 prvtaqds.plb
12734 prvtaqem.plb

257964 prvtaqim.plb
217124 prvtaqin.plb
325122 prvtaqip.plb
391222 prvtaqis.plb
35734 prvtaqji.plb
36680 prvtaqjm.plb
18311 prvtaqme.plb
35962 prvtaqmi.plb
35076 prvtaqxe.plb

70239 prvtaqxi.plb
84228 prvtarpp.plb
80382 prvtaw.plb
315536 prvtbadd.plb

99273 prvtbapp.plb
25582 prvtbath.plb
88327 prvtbcap.plb

272036 prvtbcnf.plb
81841 prvtbcut.plb
146135 prvtbdmn.plb
146943 prvtbfla.plb
72011 prvtbfma.plb
101271 prvtbfut.plb
77873 prvtbiai.plb
65891 prvtbiat.plb
223608 prvtbiau.plb

24176 prvtbint.plb
14397 prvtbipk.plb
15624 prvtbirp.plb

357163 prvtbkrs.plb
62183 prvtblcr.plb
301135 prvtblin.plb
41538 prvtblrp.plb
57152 prvtblut.plb
477156 prvtbmas.plb
407093 prvtbobg.plb
95044 prvtboft.plb
150857 prvtbog2.plb
35567 prvtbog3.plb
19256 prvtbord.plb
254505 prvtbout.plb
13671 prvtbowp.plb

176031 prvtbrep.plb
846334 prvtbrgt.plb
168063 prvtbrmg.plb
367710 prvtbrpc.plb
100338 prvtbrrq.plb
15777 prvtbrut.plb

20331 prvtbsna.plb
40795 prvtbsqu.plb
276670 prvtbstr.plb
779007 prvtbsut.plb
15599 prvtbtop.plb

106858 prvtbut2.plb
512319 prvtbut3.plb
520471 prvtbut4.plb

1143495 prvtbutl.plb

45013 prvtbval.plb
22236 prvtcdcp.plb
32163 prvtcdcu.plb
15147 prvtcxml.plb

640252 prvtdefr.plb
27982 prvtdesc.plb
10414 prvtdfrd.plb
123516 prvtdfri.plb
84170 prvtdrep.plb
22754 prvtdrs.plb
10905 prvtenc.plb
106168 prvtepgc.plb
79573 prvtfile.plb
1301167 prvtgen.plb
10505 prvthapp.plb
36515 prvthcnf.plb
125389 prvthdcl.plb

11942 prvthfla.plb
37564 prvthfut.plb
10131 prvthjob.plb
28509 prvthlin.plb
12255 prvthlrp.plb
14108 prvthlut.plb
30465 prvthmas.plb
14891 prvthobg.plb
13036 prvthoft.plb
25951 prvthout.plb
41102 prvthrgt.plb
85913 prvthrpc.plb

234865 prvths.plb
113500 prvthssq.plb
26128 prvthstr.plb
34040 prvthsut.plb
133004 prvthttp.plb

51322 prvthut2.plb
26869 prvthut3.plb
50214 prvthut4.plb

116406 prvthutl.plb

10088 prvtindx.plb
31625 prvtiotc.plb
37546 prvtitrg.plb

86612 prvtjob.plb
318052 prvtldap.plb

156872 prvtlmc.plb
375231 prvtlmd.plb
87999 prvtlmrd.plb

51932 prvtlms.plb
80045 prvtlob.plb
29266 prvtlock.plb
253743 prvtlsby.plb
46308 prvtmap.plb
86561 prvtmeta.plb
445964 prvtmeti.plb
67426 prvtmetu.plb
39619 prvtobtk.plb
177747 prvtoctk.plb
85235 prvtofln.plb
59440 prvtofsn.plb
33102 prvtol.plb
26167 prvtotpt.plb

372664 prvtpb.plb
22776 prvtpbp.plb
10131 prvtpbt.plb

90493 prvtpckl.plb
54081 prvtpexp.plb
16701 prvtpgb.plb
26867 prvtpipe.plb
112920 prvtpitr.plb
315935 prvtplts.plb

24369 prvtpool.plb
153057 prvtpsp.plb
27848 prvtrawb.plb

192068 prvtrctf.plb
51598 prvtread.plb
204421 prvtreie.plb
31536 prvtres.plb
104374 prvtreut.plb

17148 prvtrlsa.plb
38453 prvtrmad.plb
16686 prvtrmge.plb
29715 prvtrmie.plb
84243 prvtrmin.plb

1114468 prvtrmns.plb

28563 prvtrmpa.plb
19300 prvtrmpe.plb
11091 prvtrpch.plb

28324 prvtrpr.plb

11623 prvtsath.plb
190285 prvtsms.plb
42764 prvtsmtp.plb
135138 prvtsmv.plb

762051 prvtsnap.plb
20634 prvtspad.plb
140981 prvtsql.plb
263618 prvtssql.plb
185326 prvtstas.plb
1544384 prvtstat.plb
66530 prvtsum.plb

10819 prvtsupp.plb
230449 prvtsxu.plb
17261 prvtsyer.plb

32750 prvttcp.plb
38919 prvttxfm.plb
81202 prvttypu.plb
70998 prvturi.plb
397246 prvtutil.plb
80112 prvtxdb.plb
28889 prvtxdbr.plb
66243 prvtxdbz.plb
23664 prvtxml.plb
352285 prvtxmld.plb

35854 prvtxmlp.plb
48603 prvtxmlt.plb
11858 prvtxpsw.plb
60292 prvtxsch.plb
65668 prvtxslp.plb
15678 prvtzexp.plb
36210 prvtzhlp.plb
1782203 xumuts.plb

All of these run in SQL*Plus; your file, per se, wasn't truncted, just the lines exeeding 255 characters (I believe that is the line length limit).

> Thanks very much in advance for any replies on this.
>
> * Note to file - the extreme (for me) length of this statement was
> because the Oracle app vendor on whose app I am working to write reports
> has really lost their way and dealt with the same data entities in
> completely different ways, depending on whether a single or multiple
> entities is tied to a specific record on a main table. Basically, the
> intended function is to be able to write a single work order against
> either one location (in which case the location PK is written to a PK
> field in the work order table) or multiple locations (you would think
> they would have just used a many to many relationship, but they created
> another entity altogether to represent set "groups" of locations and
> write the group identifier to the work order table in a separate column)
> While the intended functioning of their approach works on screen, it
> makes it virtually impossible for any of their clients to do any
> straighforward analysis of costs.
> --
> Tim http://www.ucs.mun.ca/~tmarshal/
> ^o<
> /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
> /^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

David Fitzjarrell Received on Wed Sep 28 2005 - 12:32:33 CDT