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
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
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 '-'.
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.
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
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.
>>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.
ASKER
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.
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.
[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.
ASKER
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
But repheader is only printed the one time...
Guess you didn't see my other solution posted above?
Guess you didn't see my other solution posted above?
ASKER
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.
>> 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...
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...
ASKER
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
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
Glad to help.
Again, this was fun!!! It isn't often I get to dust off some VERY OLD brain cells!
Again, this was fun!!! It isn't often I get to dust off some VERY OLD brain cells!