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

[Quoted] I would try putting the SQL commands into a file and then on the line where you invoke SQL you add '_at_filename' so that it will run. Add an 'exit' -- I think that is it -- to the end of the file with the SQL commands in it to leave SQL after it is done.

"PhilC" <news_at_macostech.com> wrote in message news:2c42efef.0111140959.4a0221c1_at_posting.google.com...
> 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 - 20:33:40 CET