SQLPLUS Report - SP2-0027: Input is too long (due to static text)

I am executing a SQL script in Oracle SQLPLUS to generate a report which contains some static text at the top of the file, followed by the data.  After adding some additional static text, SQLPLUS is now throwing the error: SP2-0027: Input is too long (> 2499 characters) - line ignored.
All of the web searches I've done refer to the 2500 line length limit, but they all refer to SQL statements that exceed this limit.  The solution is always to split the lines.
However, in this case, the problem is not the SQL statement, it is the static text.  The static text is already broken up into individual lines no longer than LINESIZE=125, but it seems that SQLPLUS considers the entire string of static text as a single line.  Is there any way around this?
 
Example:
TTITLE -
LEFT 'REPORT TITLE'-
SKIP 1 ''-
'STATIC TEXT LINE 1'-
SKIP 1 ''-
'STATIC TEXT LINE 2'-
SKIP 1 ''-
'STATIC TEXT LINE 3'-
SKIP 1 ''-
'STATIC TEXT LINE 4'-
etc....
SQL SELECT statement
arms145Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
My 'guess' is it is the '-' at the end of the line.  This tells sqlplus the line continues so even though the script has the CR in it, sqlplus is likely putting them all back together because of the '-'.
slightwv (䄆 Netminder) Commented:
Guess I could have 'test'ed it so it wasn't a guess.

This reproduces it.

Each line is 100 x's.  With spacing and things, this is over 2499.  Delete one line and it runs fine.

prompt -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 

Open in new window

slightwv (䄆 Netminder) Commented:
forgot to answer the question:

>>Is there any way around this?

Don't use the line continuations?

Do you need them?  I'm not fully understanding what you are doing from what you posted but it doesn't look like you need to continue the lines.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

arms145Author Commented:
Thanks for the clue, I think I understand what the script is doing now.  The entire static string is part of the TTITLE command, that is the reason for the continuing lines.  I suppose if I break it up into 2 separate TTITLE commands, it should work.  This is a very old legacy script, and I'm not familiar with SQL*PLUS, so was just confused by the error after only modifying some of the static text.  I'll give it a try and report back.
slightwv (䄆 Netminder) Commented:
Your report needs a title longer than 2499 characters?

[edit by slightwv]
I see TTITLE will accept a SKIP so if you read what I had posted, I see where I was wrong.

If you have no idea what I'm typing, then you didn't read the previous text, so ignore the edit.
arms145Author Commented:
The report has a section of instruction text at the top of each page.  The section is >2499 chars.  The report does not generate sql loader control file, just a simple select statement with the instruction text at the top of each page.

Doing multiple TTITLE did not work, the second TITTLE overrides the first one.  There is also REPHEADER command to place a header in the report, but pretty sure this will have the same issue.

So the options seem to be (1) using PROMPT, but that will not appear at the top of every page, which may not be acceptable, or (2) see if there is a SQLPLUS command to insert the header from another file, or (3) post-process the reports to insert the headers, which is likely too much effort.

Hopefully option 1 is acceptable, or someone here knows a trick to fool SQL*PLUS into accepting a concatenated TTITLE or REPHEADER string.
slightwv (䄆 Netminder) Commented:
I was hoping you didn't see the sql loader piece...  I edited that but guess I wasn't fast enough.

Generating reports with sqlplus is a long forgotten art form!!!

Sad, it was pretty powerful for when it came out and I still find it useful today!

Let me play a little to see what I can find.
slightwv (䄆 Netminder) Commented:
This was fun!  Haven't done sqlplus reporting in a LONG time!!!

Of course the docs turned out to the place to go:
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_six.htm#sthref734

Entering Long Titles

If you need to enter a title greater than 500 characters in length, you can use the SQL*Plus command DEFINE to place the text of each line of the title in a separate substitution variable:

Of course it works...

define st1 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st2 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st3 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st4 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st5 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st6 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st7 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st8 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st9 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st10 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st11 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st12 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st13 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st14 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st15 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st16 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st16 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st18 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st19 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st20 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st21 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st22 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st23 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st24 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
define st25 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

set pages 1
ttitle LEFT -
	st1 skip 1 st2 skip 1 st3 skip 1 st4 skip 1 st5 skip 1 st6 skip 1 st7 skip 1 st8 skip 1 st9 skip 1 st10 skip 1 -
	st11 skip 1 st12 skip 1 st13 skip 1 st14 skip 1 st15 skip 1 st16 skip 1 st16 skip 1 st18 skip 1 st19 skip 1 st20 skip 1 -
	st21 skip 1 st22 skip 1 st23 skip 1 st24 skip 1 st25 skip 1

set lines 200

select sysdate from dual
union all
select sysdate from dual
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arms145Author Commented:
Looks like I was able to find a workaround.  I tried PROMPT, but the first two lines of the section have substitution variables, which PROMPT does not support.  I tried TTITLE for the first 2 lines and PROMPT for the rest, but it places PROMPT first and TTITLE second in the report, regardless of the order in the sql file.

So the solution was to use both TTITLE and REPHEADER, both support substitution variables.  TTITLE is placed above REPHEADER.  Thanks slightwv for the clues that led me here.
slightwv (䄆 Netminder) Commented:
But repheader is only printed the one time...

Guess you didn't see my other solution posted above?
arms145Author Commented:
I think we both submitted our solutions around the same time.  You're right, looks like repheader is only printed once. I assumed that by general convention a header/footer is printed at top of every page.  Nice solution, thanks!  Hopefully there won't be any issues with doing the first two lines explicitly in order to use substitution variables, and then appending the rest as define variables.  I don't think DEFINE supports substitution variables, just plain text.  I'll let you know if it works shortly.
slightwv (䄆 Netminder) Commented:
>> I don't think DEFINE supports substitution variables, just plain text

I don't think so either.  Do you need them?

I don't think you can use substation variables.  Per the docs (and my testing) they are expanded BEFORE the title is printed so you exceed the same line size.  But, get a different error!!!

I tried substitution variables before I went diffing deeper into the docs...
arms145Author Commented:
The substitution variables used in the first two lines generate short strings (current date, and a dept name from the query), so they won't add much to the line size.  I tried your approach and it worked!  I only needed to use DEFINE for a few lines, not all the lines, just enough to bring the line size down below 2500, sample below.  Thanks again.

TTITLE LEFT -
'REPORT TITLE FOR 'COL1''-
SKIP 1 ''-
'DATA AS OF 'CUR_DATE''-
SKIP 1 ''-
'STATIC TEXT LINE'-
SKIP 1 ''-
'STATIC TEXT LINE'-
SKIP 1 ''-
req11 SKIP 1 req12 SKIP 1 req21 SKIP 1 req22 SKIP 1 req31 SKIP 1 req32 SKIP 1 req33 SKIP 1 req41 SKIP 1 -
'STATIC TEXT LINE'-
SKIP 1 ''-
''
SELECT QUERY
slightwv (䄆 Netminder) Commented:
Glad to help.

Again, this was fun!!!  It isn't often I get to dust off some VERY OLD brain cells!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.