Home » Other » Client Tools » sqlplus report headers and titles (Oracle 11.2.0.3)
sqlplus report headers and titles [message #617339] Fri, 27 June 2014 10:07 Go to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Perhaps I just need another cup of coffee. Certainly another set of eyes.

Calling sqlplus in a shell script with this command:

sqlplus -s /nolog @doit.sql $rptfile $rptprd


Using REPHEADER and REPFOOTER in the sql script, the header/footer is coming out on stdout, but not in the spool file.

doit.sql looks like this:

conn / as sysdba
define spofle = &1
define rptprd = &2
alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';
set trimspool on 
set tab off 
set pages 50 
set lines 512
set echo off
set feedback off
set verify off
set head on
--
break on action_name skip 1
spo &spofle
--
repheader center "---------- Report of Audited Actions -------"
repfooter center "---------- End of Report ------------"
select  
    <snip lengthy statement...>
;
--
exit

Re: sqlplus report headers and titles [message #617345 is a reply to message #617339] Fri, 27 June 2014 10:44 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hello Ed,

Finally a question from you...

Let's try two options, one stupid, another is untested.
1. Try PROMPT and your required header. Silly, but should work.
2. I see some suggestions over internet about embedded on with pagesize 0, not sure as I never tested it.

Please feedback.
Re: sqlplus report headers and titles [message #617347 is a reply to message #617345] Fri, 27 June 2014 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I have nothing useful to post but I post it all the same because my goal is to post.

[Updated on: Fri, 27 June 2014 10:53]

Report message to a moderator

Re: sqlplus report headers and titles [message #617350 is a reply to message #617345] Fri, 27 June 2014 10:56 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
The "prompt" value came out on the spool file, not the repheader value
Setting pages 0 suppressed the repheader from stdout, and it still didn't show in the spool file.

Re: sqlplus report headers and titles [message #617352 is a reply to message #617339] Fri, 27 June 2014 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It works for me:
SQL> host type t.sql
define spofle = &1
define rptprd = &2
alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';
set trimspool on
set tab off
set pages 10
set lines 512
set echo off
set feedback off
set verify off
set head on
--
break on action_name skip 1
spo t.lst
--
repheader center "---------- Report of Audited Actions -------"
repfooter center "---------- End of Report ------------"
select level from dual connect by level <= 15;
spool off

SQL> @t 1 1

--------- Report of Audited Actions -------
     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
     LEVEL
----------
         8
         9
        10
        11
        12
        13
        14
        15
     LEVEL
----------

   ---------- End of Report ------------
SQL> host type t.lst

--------- Report of Audited Actions -------
     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
     LEVEL
----------
         8
         9
        10
        11
        12
        13
        14
        15
     LEVEL
----------

   ---------- End of Report ------------

SQL>

The differences are:
1/ I have spool off
2/ I am connected as MICHEL
3/ It is not the same query
4/ It is Windows
Version is also 11.2.0.3.

[Edit: English]

[Updated on: Fri, 27 June 2014 11:00]

Report message to a moderator

Re: sqlplus report headers and titles [message #617358 is a reply to message #617352] Fri, 27 June 2014 11:34 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Michel,
I missing your point. Of course your key difference was spool off. And that is my whole issue. The repheader shows up in stdout, but not in the spool file.
Re: sqlplus report headers and titles [message #617361 is a reply to message #617358] Fri, 27 June 2014 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I have no specific point, just test what you posted with some modifications for my environment and on which I pointed.
As you can see, I have the report header and footer in my spool file.

Re: sqlplus report headers and titles [message #617661 is a reply to message #617339] Wed, 02 July 2014 10:23 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Well, just for closure, it did come down to needing another cup of coffee and fresh eyes.

REPHEADER was set for CENTER. I typically set linesize for something big, like 128 or 256, then use trimspool to control actual line size. But of course the centered repheader was centered on the full line size. So when I opened the spool file in notepad, it was way off past the right edge of the window.

DOH!

Embarassed
Re: sqlplus report headers and titles [message #617664 is a reply to message #617661] Wed, 02 July 2014 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Re: sqlplus report headers and titles [message #617665 is a reply to message #617661] Wed, 02 July 2014 10:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
So a bigger pagesize wouldn't have helped? Not exactly a solution but would have indicated that the alignment went way off.
Re: sqlplus report headers and titles [message #617669 is a reply to message #617665] Wed, 02 July 2014 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You seem do not understand what this parameter is for.
Please refer to the documentation.

Re: sqlplus report headers and titles [message #617670 is a reply to message #617669] Wed, 02 July 2014 10:52 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Sure, will refer docs and test what I suggested. But honestly I thought it would work as workaround.

Thanks for your suggestion, always good to get back to docs.
Re: sqlplus report headers and titles [message #617678 is a reply to message #617665] Wed, 02 July 2014 12:04 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Lalit -

pagesize has no effect at all. That controls the number of lines output before sqlplus throws in a newpage control. Has no bearing on width, spacing, centering, justification.

Increasing linesize has an effect, but in reality it would make my observation even more obscure. Since the centered header is centered over linesize, increasing linesize just throws the centered header further to the right ... right off the edge of the visible (but yes, scrollable) window. And if I were to send it to a printer, it would increase the undesirable line wrap.

Re: sqlplus report headers and titles [message #617688 is a reply to message #617678] Wed, 02 July 2014 12:28 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks Ed. Will explore more into it.

I will make some test cases of my own to understand further per documentation.
Previous Topic: Issue in connecting from SQL Developer 3.2
Next Topic: What is the best way to convert SQL DB to Oracle DB?
Goto Forum:
  


Current Time: Thu Mar 28 13:03:13 CDT 2024