HELP: SQL/Plus, PL/SQL, formatting, variable assignments

From: PhilC <news_at_macostech.com>
Date: 14 Nov 2001 09:59:23 -0800
Message-ID: <2c42efef.0111140959.4a0221c1_at_posting.google.com>

[Quoted] [Quoted] Is there a way in Oracle to set a variable to the results of a SELECT, and then PRINT or otherwise manipulate that variable seperately? This is SO, SO simple with Sybase T-SQL ... but I can't for the life of me figure a way in Oracle ... see below for examples, plus a TSQL fragment of what I'm trying to do...

---

 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  

---

 #!/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).

---

 #!/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 ...

---

 DECLARE _at_TDATE SMALLDATETIME 
 DECLARE _at_CALLSOPENED INT 
 DECLARE _at_MSG VARCHAR(60) 

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

---

Received on Wed Nov 14 2001 - 18:59:23 CET