We help IT Professionals succeed at work.

dbms output issue

bkreynolds48
bkreynolds48 asked
on
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
___________________________________________________________________________________
Comment
Watch Question

SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
BRONZE EXPERT

Commented:
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..??
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
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

SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>> 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.

Author

Commented:
Thanks that worked

Explore More ContentExplore courses, solutions, and other research materials related to this topic.