| HELP: SQL*Plus and report formatting/scripting [message #97070] |
Tue, 13 November 2001 19:44  |
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 #97524 is a reply to message #97522] |
Tue, 09 July 2002 07:32  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
