Home » SQL & PL/SQL » SQL & PL/SQL » HELP! SQL*Plus and report formatting/scripting
HELP! SQL*Plus and report formatting/scripting [message #36273] Wed, 14 November 2001 05:49 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 <
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 #36277 is a reply to message #36273] Wed, 14 November 2001 06:51 Go to previous messageGo to next message
Rob Baillie
Messages: 33
Registered: November 2001
Member
Well, you're looking in the right place:

http://otn.oracle.com/doc/server.804/a53717/ch4.htm#top

gives you a good introduction to SQL Plus formatting (though to tell you the truth I've never really used SQLPlus to output reports this way)

Personally, instead of trying to set the heading right I've always used the supplied package

DBMS_OUTPUT

See:
http://otn.oracle.com/doc/server.804/a58241/ch8.htm#1724

Remember to
SQL> SET SERVER OUTPUT ON SIZE 1000000
(the maximum output is 1 million characters)

before your block

or

DBMS.OUTPUT.ENABLE;

within your block, or you simply won't get any output.

Oh, and if you want it writing to a flat file... just SPOOL abc.txt before and SPOOL OFF afterwards.

Hope this helps

Rob

----------------------------------------------------------------------
Re: HELP! SQL*Plus and report formatting/scripting [message #36282 is a reply to message #36277] Wed, 14 November 2001 07:58 Go to previous message
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 ...

----------------------------------------------------------------------
Previous Topic: Help on select needed
Next Topic: Embedded PL/SQL: Cursor Loops
Goto Forum:
  


Current Time: Thu Mar 28 13:15:14 CDT 2024