| HELP! SQL*Plus and report formatting/scripting [message #36273] |
Wed, 14 November 2001 05:49  |
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 <
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 <
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
---------------------------------------------------------------------
-----------
----------------------------------------------------------------------
|
|
|
|
|
|
| Re: HELP! SQL*Plus and report formatting/scripting [message #36282 is a reply to message #36277] |
Wed, 14 November 2001 07:58  |
phil corchary
Messages: 3
Registered: November 2001
|
Junior Member |
|
|
This still isn't really what I'm looking for.
I don't want Oracle to output anything directly from my SELECTS... I want to put the values into variables and PRINT them myself at the end ... like the T-SQL example ...
Am I really trying to do something that's SO unusual? I've been writing simple reports like this on-and-off for 10 years in T-SQL under Sybase and more recently MSSQL ...
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
