Home » Infrastructure » Unix » HELP: SQL*Plus and report formatting/scripting
HELP: SQL*Plus and report formatting/scripting [message #97070] Tue, 13 November 2001 19:44 Go to next message
phil corchary
Messages: 3
Registered: November 2001
Junior Member
All: I'm starting to wonder if I'm trying to do something wierd!

What I want to be able to do is write very simple reports and execute them as unix shell commands. I've done this with Sybase on unix, and even with MSSQL on NT, but Oracle is stumping me.

This works just fine, but I don't want generic, unformatted output

quote:
--------------------------------------------------------------------------------
#!/bin/ksh

echo "headers go here"

/u01/app/oracle/product/8.1.5/bin/sqlplus -s <<EOF
user/pass

SELECT
round(sum(((CLOSETIME - OPENTIME)*24)*60)) "Total Duration in Minutes"
FROM TBL_DTCONFINFO
where to_char(OPENTIME,'mm/dd/yyyy') = '${1}';

--------------------------------------------------------------------------------

What I really want to do is *SOMETHING* like this (I think the syntax is wrong).

quote:
--------------------------------------------------------------------------------
#!/bin/ksh

echo "headers go here"

/u01/app/oracle/product/8.1.5/bin/sqlplus -s <<EOF
user/pass

/* this section computes the total duration */
DECLARE
_duration NUMBER := 0;

_duration := SELECT
round(sum(((CLOSETIME - OPENTIME)*24)*60)) "Total Duration in Minutes"
FROM TBL_DTCONFINFO
where to_char(OPENTIME,'mm/dd/yyyy') = '${1}';

PRINT _duration
--------------------------------------------------------------------------------

But I'm having a devil of a time finding what the proper syntax is. I've bought the OReilly PL/SQL book, but it's no help at all - way more in-depth that what I need for this. I've been looking over the SQL*Plus reference on line, but it's has to LITTLE information...

HELP!!! Please? This is SO easy is T-SQL for Sybase, what is wrong? Am I using the wrong facility? If so, what should I use?

For instance, here is a T-SQL Fragment that is something like what I want to do in PL/SQL or SQL*Plus, whatever ...

quote:
--------------------------------------------------------------------------------
DECLARE @TDATE SMALLDATETIME
DECLARE @CALLSOPENED INT
DECLARE @MSG VARCHAR(60)
SELECT @TDATE = GETDATE() -- set @TDATE to system date
--next line executes select and places results in @CALLSOPENED variable
SELECT @CALLSOPENED = count ("EVENTTYPE")
FROM TblDtSvrLog
WHERE ( EVENTTYPE = 13 )
AND ( LOGTIME between @TDATE and (dateadd(mi, 15, @TDATE)) )
--next line builds an output message
SELECT @MSG = CONVERT(VARCHAR(20),@TDATE, 120) + "t" + CONVERT(VARCHAR(4),@CALLSOPENED)
-next line displays/outputs the message
PRINT @MSG
--------------------------------------------------------------------------------

All of this goes out to STDOUT, so it can be shown on the tty, or directed to a file...

----------------------------------------------------------------------
Re: HELP: SQL*Plus and report formatting/scripting [message #97075 is a reply to message #97070] Thu, 15 November 2001 11:43 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://www.orafaq.net/msgboard/unix/messages/832.htm

----------------------------------------------------------------------
Re: HELP: SQL*Plus and report formatting/scripting [message #97522 is a reply to message #97075] Mon, 08 July 2002 23:23 Go to previous messageGo to next message
anupma singh
Messages: 1
Registered: July 2002
Junior Member
how to generate a report in which column name will be in horizontal. e.g.
for 1 report
*************
col1: a
col2: b
....

**************
col1 :c
col1:d
***************
Re: HELP: SQL*Plus and report formatting/scripting [message #97524 is a reply to message #97522] Tue, 09 July 2002 07:32 Go to previous messageGo to next message
Ben Li
Messages: 3
Registered: May 2002
Junior Member
Anupma,

Try this script. Customize it for your purpose.
=======================================================

set doc off
/*
|| Script name: 1row.sql
|| Created by : Ben Li
|| Description:
|| Given a table name and column names,
|| converts the whole column values into one row.
|| This script is designed for up to 3 columns and
|| a small number of records.
*/

accept vtbl_name prompt 'Enter table name : '
accept vcol1 prompt 'Enter column name(1) : '
accept vcol2 prompt 'Enter column name(2) : '
accept vcol3 prompt 'Enter column name(3) : '

set pagesize 0
set feedback off
set ver off
set term off
col nop noprint

spool 1row.txt
select decode(rownum,1,'Select ''&vcol1: ','||'' ''||''')||&vcol1||'''' from &vtbl_name;
select &vcol1 nop, 'from dual;' from &vtbl_name where rownum=1;
select decode(rownum,1,'Select ''&vcol2: ','||'' ''||''')||&vcol2||'''' from &vtbl_name;
select &vcol2 nop, 'from dual;' from &vtbl_name where rownum=1;
select decode(rownum,1,'Select ''&vcol3: ','||'' ''||''')||&vcol3||'''' from &vtbl_name;
select &vcol3 nop, 'from dual;' from &vtbl_name where rownum=1;
spool off
set term on
set pagesize 20
set hea off
@1row.txt
set term off
set hea on
set term on
=======================================================

Good luck.

Ben
Re: HELP: SQL*Plus and report formatting/scripting [message #97526 is a reply to message #97524] Tue, 09 July 2002 11:00 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://www.akadia.com/services/show_vertical.txt
Previous Topic: Re: backup not being done in tape in sco unix
Next Topic: ORA-00604 & ORA-04031 - pls help (tru64 unix)
Goto Forum:
  


Current Time: Tue Jan 19 21:57:19 CST 2021