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

Don't forget to put the command

  spool <filename>

at the beginning and

  spool end

at the end. Of course if you do an "exit" it will stop adding to the output file. The spool command will save the results to a file.

Matt.

Scott Mattes wrote:
>
> 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 - 21:21:42 CET