Home » SQL & PL/SQL » Client Tools » SQL * Plus report output in vertical allignment (Oracle 10G)
SQL * Plus report output in vertical allignment [message #404198] Wed, 20 May 2009 15:15 Go to next message
aaashwini@gmail.com
Messages: 24
Registered: May 2009
Junior Member
Hi

I have a SQL* plus report which displays the changes made to the responsibilities assigned to the user. Basically is a Responsibility is added/modified to the user. Details are printed like :User Name, Responsibility Name, Assignment start date, end date so on. .. In SQL * plus reports the output shown is always tabular. But the user needs the output in vertical format. Something like :



===================================================================================================================================== ===============

The access to the following responsibility has been changed for user: User Name


Responsibility: Responsibility Name
Change Date: change_date


Assign.Start Date: start date
Assign.End Date: End date


===================================================================================================================================== ===============

Please help me in formatting this kind of output for SQL*plus report. Many thanks in advance.
Re: SQL * Plus report output in vertical allignment [message #404199 is a reply to message #404198] Wed, 20 May 2009 15:24 Go to previous messageGo to next message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
SQL> set pagesize 0
SQL> column eol newline
SQL>
SQL> select 'Table_Name    : ' || TABLE_NAME eol,
  2         'Num_Rows      : ' || NUM_ROWS eol,
  3         'Last_Analyzed : ' || LAST_ANALYZED eol
  4  from USER_TABLES
  5  where rownum < 5;
Table_Name    : ABC
Num_Rows      : 0
Last_Analyzed : 08-MAY-09

Table_Name    : TABLE_A
Num_Rows      : 2220
Last_Analyzed : 19-MAY-09

Table_Name    : TABLE_B
Num_Rows      : 12585
Last_Analyzed : 08-MAY-09

Table_Name    : TABLE_C
Num_Rows      : 3795
Last_Analyzed : 19-MAY-09

Re: SQL * Plus report output in vertical allignment [message #404201 is a reply to message #404199] Wed, 20 May 2009 15:50 Go to previous messageGo to next message
aaashwini@gmail.com
Messages: 24
Registered: May 2009
Junior Member
Thanks a lot for the reply. Output is coming as desired. But there lot of other junk characters coming along with the output like below: Please help me eliminating this.

-------------------
EOL
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------
EOL
-------------------------------------------------------------------------------------------------------------------
EOL
------------------------------
EOL
------------------------------------
EOL
----------------------------------
EOL
----------------------------------
EOL
--------------------------------
Re: SQL * Plus report output in vertical allignment [message #404202 is a reply to message #404201] Wed, 20 May 2009 15:52 Go to previous messageGo to next message
aaashwini@gmail.com
Messages: 24
Registered: May 2009
Junior Member
My report code is as follows :

set head on
set pagesize 350
set linesize 350
TTITLE CENTER "XX 100 613 Responsibility Change Report" SKIP 1 -
Center "From Date: " '&&1' SKIP 1 -
Center "To Date: " '&&2' SKIP 1 -
Center "Run Date: " TODAY
column eol newline
SELECT DISTINCT
TO_CHAR(SYSDATE, 'MM/DD/YYYY hh24:mi:ss') "TODAY",
'The access to the following responsibility has been changed for user:'||resp_chng.employee_name eol,
'Responsibility:'||resp_chng.responsibility_name eol,
'Change Date:'||resp_chng.resp_last_update_date eol,
'Assign.Start Date:'||resp_chng.resp_start_date eol,
'Assign.End Date:'||resp_chng.resp_end_date eol,
'User Start Date:'||resp_chng.resp_creation_date eol,
'User End Date:'||resp_chng.resp_last_update_date eol
FROM xx.xx_rs_100_613_emp_resp_chng resp_chng, fnd_user fu_cr, fnd_user fu_upd
WHERE fu_cr.user_id = resp_chng.RESP_CREATED_BY
AND fu_upd.user_id = resp_chng.RESP_LAST_UPDATED_BY
-- AND TRUNC(resp_chng.resp_last_update_date) BETWEEN TO_DATE('&&1','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('&&2','YYYY/MM/DD HH24:MI:SS')
ORDER BY --TRUNC('User End Date:'||resp_chng.resp_last_update_date),
'The access to the following responsibility has been changed for user:'||resp_chng.employee_name ;



Please help me in eliminating the EOL printing at the start of the report.

Many Thanks in Advance...
Re: SQL * Plus report output in vertical allignment [message #404206 is a reply to message #404198] Wed, 20 May 2009 16:54 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
SELECT   DISTINCT To_char(SYSDATE,'MM/DD/YYYY hh24:mi:ss') "TODAY",
                  'The access to the following responsibility has been changed for user:'
                  ||resp_chng.employee_name eol,
                  'Responsibility:'
                  ||resp_chng.responsibility_name eol,
                  'Change Date:'
                  ||resp_chng.resp_last_update_date eol,
                  'Assign.Start Date:'
                  ||resp_chng.resp_start_date eol,
                  'Assign.End Date:'
                  ||resp_chng.resp_end_date eol,
                  'User Start Date:'
                  ||resp_chng.resp_creation_date eol,
                  'User End Date:'
                  ||resp_chng.resp_last_update_date eol
FROM     xx.xx_rs_100_613_emp_resp_chng resp_chng,
         fnd_user fu_cr,
         fnd_user fu_upd
WHERE    fu_cr.user_id = resp_chng.resp_created_by
         AND fu_upd.user_id = resp_chng.resp_last_updated_by
-- AND TRUNC(resp_chng.resp_last_update_date) BETWEEN TO_DATE('&&1','YYYY/MM/DD HH24:MI:SS') 
-- AND TO_DATE('&&2','YYYY/MM/DD HH24:MI:SS')
ORDER BY --TRUNC('User End Date:'||resp_chng.resp_last_update_date),
 'The access to the following responsibility has been changed for user:'
         ||resp_chng.employee_name; 



You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

[Updated on: Thu, 21 May 2009 06:31] by Moderator

Report message to a moderator

Re: SQL * Plus report output in vertical allignment [message #404207 is a reply to message #404206] Wed, 20 May 2009 17:00 Go to previous messageGo to next message
aaashwini@gmail.com
Messages: 24
Registered: May 2009
Junior Member
The junk characters still repeat even after following the code above. I noticed that as I'm printing 7 columns the junk character is printing 7 times at the begining of each page. Please let me know how to avoid.
Re: SQL * Plus report output in vertical allignment [message #404218 is a reply to message #404207] Wed, 20 May 2009 23:08 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
Are you talking about the column headers?
You should make yourself more clear, instead of "junk characters".
If you really are talking about the headers, set heading off
Re: SQL * Plus report output in vertical allignment [message #404242 is a reply to message #404207] Thu, 21 May 2009 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And maybe you should read SQL*PlusĀ® User's Guide and Reference, chapter 6 Formatting SQL*Plus Reports and SQL*Plus Command Summary and no more rely on others to find answer to these SQL*Plus questions.

Regards
Michel
Re: SQL * Plus report output in vertical allignment [message #404318 is a reply to message #404242] Thu, 21 May 2009 05:46 Go to previous message
aaashwini@gmail.com
Messages: 24
Registered: May 2009
Junior Member
Thanks alot for the links..will go through them and implement.
Previous Topic: Script to generate Flat file + assign sysdate to a variable in sql script
Next Topic: output the result to a flat file in local PC
Goto Forum:
  


Current Time: Fri Aug 22 13:34:26 CDT 2014

Total time taken to generate the page: 0.17052 seconds