sqlplus report headers and titles [message #617339] |
Fri, 27 June 2014 10:07  |
 |
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   |
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 #617352 is a reply to message #617339] |
Fri, 27 June 2014 10:58   |
 |
Michel Cadot
Messages: 68510 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
|
|
|
|
|
|
|
|
|
|
|
|