Whenever I run into the 'paste' limited in sqlplus, I just create a x.sql
file and paste into that file. Executing a .sql file doesn't have the
length limitation.
Alternatively, you can paste in chunks in sqlplus and that will work fine
too. So pasting 10 lines of 1000 characters each will work, whereas one
line 10000 will not.
--
Terry Dykstra
Canadian Forest Oil Ltd.
<fitzjarrell_at_cox.net> wrote in message
news:1127928753.778911.206690_at_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 - 13:50:45 CDT
