Improve company productivity with a Business Account.Sign Up

x
?
Solved

dbms output issue

Posted on 2012-03-14
5
Medium Priority
?
410 Views
Last Modified: 2012-03-15
I want to include the date and the database name in this report but don't know much about formatting dbms_output

here is what I have - hash marks are not in the sql script

##############
set serverout on feed off pause off timing off pagesize 56 linesize 180 echo off

column today noprint new_value xdate
select to_char(sysdate,'MM-DD-YYYY')  today from dual;

col dbname new_value dbnm
select name dbname from v$database;


spool /home/sql/reports/&dbnm..reports.doc

begin
dbms_output.put_line('___________________________________________________________________________________');
dbms_output.put_line('                      Assessment Report');
dbms_output.put_line('___________________________________________________________________________________');
end;
/

prompt
prompt
prompt
#################

here is what I get


DBNAME
---------
DEVDATABASE
___________________________________________________________________________________
Assessment Report
___________________________________________________________________________________
0
Comment
Question by:bkreynolds48
  • 3
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37721836
what do you want the output to look like?
you are printing the dbname, but you have defined your "today" column as noprint, so it's hidden
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37721932
as sdstuber said, you have to use your statement like this

column today print new_value xdate
select to_char(sysdate,'MM-DD-YYYY')  today from dual;


if you are using plsql, then its better to display everything in plsql itself,. why to go in sql prompt again and using spool for printing the output to file..??
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37721959
if you want to do everything in pl/sql try something like this


set serverout on feed off pause off timing off pagesize 56 linesize 180 echo off
spool /home/sql/reports/&dbnm..reports.doc

begin
dbms_output.put_line('___________________________________________________________________________________');
dbms_output.put_line('                      Assessment Report');
dbms_output.put_line('Report date: ' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
dbms_output.put_line('DB Name: ' || sys_context('userenv','db_name'));
dbms_output.put_line('___________________________________________________________________________________');
end;
/

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37721969
>> as sdstuber said, you have to use your statement like this

actually, I would simply remove the noprint, because print is the default

or, as I did above, remove the sql and corresponding column commands completely.
0
 
LVL 1

Author Closing Comment

by:bkreynolds48
ID: 37724640
Thanks that worked
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question