SQL*Plus report parameter setting 1

[BJCooperIT]

I am generating a fairly simple report in SQL*Plus and having an unusual problem. The line of hyphens (the SET UNDERLINE character) that separates the heading from the data is being truncated to 100 characters.

My login.sql:

set termout off

define my_version='0.0.0'
column my_version new_value my_version
select substr(banner,instr(banner,' ',-1,3),6) my_version
from sys.v_$version v
where rownum = 1;

define new_prompt='nolog'
column new_prompt new_value new_prompt
select username || ':' ||
       substr(substr(global_name,1,30),1,instr(substr(global_name,1,30),'.')-1)
       new_prompt
from   user_users, global_name;

--set sqlprompt "&new_prompt&my_version> "

SET head OFF
SET verify OFF
SET termout ON

SELECT '&new_prompt&my_version' FROM dual;
SET termout OFF
SET head ON
SET verify ON
SET termout ON

SET serveroutput on format wrapped
set linesize 255

Besides setting the heading and ttitle on and off, the script only has these set commands:

set echo off
set linesize 135 
set pagesize 56

A sample of the output with the first 64 characters removed:

ROWS WITH ERRORS                                            

Process               Error                          Program
Date                  Message                        Area
[COLOR=red][b]--------------------- --------------[/b][/color]
06/11/2008 08:29:45am                                DLLS
06/11/2008 08:29:45am                                DLLS
06/11/2008 09:13:49am                                DLLS
06/11/2008 09:13:49am                                DLLS

Am I missing a setting? Any ideas why the line is truncated in the middle of the Error Message after postition 100?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

Barb...Where have you been, young lady?!!! We've missed you.

As far as your hyphen problem is concerned, are you using a GUI version of SQL*Plus? There is a strange idiosyncracy with GUI SQL*PLus's...Check the following (if you are using GUI SQL*Plus): From the top GUI menu...Options -> Environment -> Buffer Width. I'll bet that the number of hyphens (in total) that you are seeing equals the current setting for your Buffer Width, despite the SQL*Plus LINESIZE being set to a higher number.

If this is the case, just increase the "Buffer Width" to some number >= your LINESIZE.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[BJCooperIT]

Oddly enough, I am using the GUI version of SQL*Plus, but my buffer width was set to 255 in Options -> Environment -> Buffer Width.

Hmmm....

Just to test it, I set the environment width to 1000 and ran the script and the underlines magically appeared. I set the width back to 255 in the GUI environment and closed my SQL_plus session. I added the line:

SET buffer   135

to my script and ran it in a new session. The output was correct!

Thanks! I knew if anyone could answer this, it would be you, Dave. I have been working on a contract for a government agency. Most of the questions in the forums have seemed more DBA oriented. So I have stepped back from answering them, as I am not a DBA, just a developer. These government installations won't let me generate so much as a GRANT statement!

Thanks Dave, have a star on me.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com