Create a User

connect system/system

CREATE USER user3          -- define user name
IDENTIFIED BY pass3        -- assign password
DEFAULT TABLESPACE users   -- assign tablespace for their work (select tablespace_name from dba_data_files;)
TEMPORARY TABLESPACE temp  -- assign temp space for 'order bys'
QUOTA 0 ON SYSTEM          -- allow no usage of this area
QUOTA UNLIMITED ON users   -- allow as much as they need here
PROFILE clerk              -- attach a profile
ACCOUNT LOCK
;

CREATE USER user3
IDENTIFIED BY pass3
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA UNLIMITED ON users
PROFILE clerk
ACCOUNT LOCK
;

Conditional Spool with SQL+

There's a way to jerry-rig an if/then using the decode or case statement in SQL+.

The query shown above is the one we want to have a conditional spool option in.

• Line 1 shows the spool file name being defined. It will be used in the query that is called.
• Line 2 calls the query that sets up the spooling (or not)
• Line 3-5 is the actual query.
• Line 6 Will run a query that was generated that is either blank or says 'spool off'

Condtional_Spool.SQL:

--+------------------------------------------------------------------
--| Store all the settings so we don't loose them. To write a file to
--| be executed, the following settings need to be in place.
--+------------------------------------------------------------------
Store Set InitSets Replace
Set Echo Off Verify Off Feedback Off Newpage None Pagesize 0

Accept Spooler Prompt "Spool (N/Y): "

--+------------------------------------------------------------------
--| The New Value will set up an amper variable.  Based on how they
--| answered the above question, set the spool on statement.
--+------------------------------------------------------------------
Column Spool_On New_Value  Spool_On

Spool Spool_On.Sql Replace
Select Case Upper('&Spooler')
            When 'Y' Then 'Spool &Output_File Replace'
                     Else ' '
      End  As  Spool_On
 From Dual;
Spool Off

--+------------------------------------------------------------------
--| Set up the spool off statement based on the user's response.
--| This is called from the calling program.
--+------------------------------------------------------------------
Column Spool_Off New_Value Spool_Off

Spool Spool_Off.Sql Replace
Select Case Upper('&Spooler')
            When 'Y' Then 'Spool Off'
                     Else ' '
       End As   Spool_Off
  From Dual;
Spool Off

--+------------------------------------------------------------------
--| Start the spooling before going back to the calling program
--+------------------------------------------------------------------
@Spool_On

--+------------------------------------------------------------------
--| Clean up: Undefine amper variables and reset settings.
--+------------------------------------------------------------------
Undefine Spooler Spool_On Spool_Off Output_File
@InitSets

Create a Status File

Someone requested a separate file that showed whether a process was successful or not, and the date that it ran. Since Oracle error messages (ORA-xxx) are written to your spool file, I create an external table that would read each line of output looking for 'ORA' in the first 3 positions.

-------------------------------------------------------------------------------------
-- Create an external table that will be used to read the spooled output.
-------------------------------------------------------------------------------------
Drop Table Ext_SpoolStatus;
Create Table Ext_SpoolStatus
(  SpoolLine   Varchar2(2000) )
Organization External
(  Type Oracle_Loader
  Default Directory Ext
  Location (Ext:'Test.Txt') )
Reject Limit Unlimited;
-------------------------------------------------------------------------------------
-- Turn off everything and dump the data to a file
-------------------------------------------------------------------------------------
Set Echo Off Term Off Pagesize 0 Feedback Off Trimspool On

Spool Test.txt
Select '1', 'A'
 From Dual;
Spool Off
-------------------------------------------------------------------------------------
-- Turn the screen back on. Also set up the counter for one space in the message.
-------------------------------------------------------------------------------------
Set Term On
Column ErrorCnt Format 9
-------------------------------------------------------------------------------------
-- Spool your status file
-------------------------------------------------------------------------------------
Spool TestStatus.txt
-------------------------------------------------------------------------------------
-- Put a message on the front of the count from your previous output file based on the
-- number of 'ORA' messages, and then put in the date and time.
-------------------------------------------------------------------------------------
Select 'Your code ran with ' ||
                (Select Count(*) As ErrorCnt
                  From Ext_SpoolStatus
                 Where Substr(SpoolLine,1,3) = 'ORA')
       || ' Error(s) On ' || To_Char(Sysdate, 'Mm/Dd/Yy Hh24:Mi')
  From Dual;
-------------------------------------------------------------------------------------
-- Add the actual error messages to the status file if you like
-------------------------------------------------------------------------------------
Select SpoolLine
 From Ext_SpoolStatus
Where Substr(SpoolLine,1,3) = 'ORA';

Spool Off

Show Begin and End of a Series

This came from an IT-Toolbox question. The question was how to find the beginning and ending values of a set of series. Joe Celko answered the question with a non-Oracle version. His answer was so cool, I knew I had to keep it.

What's shown in the screen shot is the result from the subquery. By subtracting the row number from the integer, you can display a group number that will be the same until an integer is skipped. I added 'RowNum As Nbr,' to the subquery just so you could see what was going on. It's totally unnecessary.

Drop Table Foobar;
Create Table Foobar
(I Number(2) );

Insert into Foobar Values (3);
Insert into Foobar Values (4);
Insert into Foobar Values (5);
Insert into Foobar Values (9);
Insert into Foobar Values (10);
Insert into Foobar Values (16);
Insert into Foobar Values (17);
Insert into Foobar Values (18);

Set Echo On
Select Min(I) Begin_Series,
       Max(I) End_Series
  From (
        Select I,
               RowNum     As Nbr,
               I - RowNum As Grp_Nbr
          From Foobar
       )
Group By Grp_Nbr;

BEGIN_SERIES END_SERIES
------------ ----------
         3          5
         9         10
        16         18

3 rows selected.

Generating SQL Code

The SQL code (not in the screen shot) will generate the code in the screen shot below for each user table in All_Tables. Basically, the table name, comment, row count, table description and sample data is shown for each.

That code in turn when run, will give you the report shown at the right. This is done this way because table names are constantly changing in the data dictionary.

Concatenating the ASCII character 10 will cause a line feed. Otherwise, all code would display on one line as SQL reports normally do.

--+--------------------------------------------------------------
--| When generating code, turn off the column headings and page breaks.
--+--------------------------------------------------------------

Column RowCnt Format 999,999,999
Set Heading &OnOff Newpage None Echo &OnOff Term &OnOff SqlBlankLines On

--+--------------------------------------------------------------
--| Spool to a file with a SQL extension
--+--------------------------------------------------------------
Spool Dumptbls.Sql

--+--------------------------------------------------------------
--| Draw a dividing line between entries and show the table name.
--+--------------------------------------------------------------

Select 'Set Heading Off Newpage None Pagesize 9999 Linesize 2000'
       || Chr(10) || 'Prompt'
       || Chr(10) || 'Prompt ======================================================================'
       || Chr(10) || 'Prompt'
       || Chr(10) || 'Prompt Table_Name: '||Table_Name 

--+--------------------------------------------------------------
--| Display a comment if there is one
--+--------------------------------------------------------------

       || Chr(10) || Case When Comments Is Not Null 
                          Then 'Prompt Comments:   '||Comments 
                          Else ' ' 
                     End
       || Chr(10) || 'Prompt'

--+--------------------------------------------------------------
--| Write the select that will generate the row count.
--+--------------------------------------------------------------

       || Chr(10) || 'Select ''Row Count:  ''||Count(*) RowCnt
                        From '||Table_Name|| ';'
       || Chr(10) || 'Prompt'

--+--------------------------------------------------------------
--| Show the file description
--+--------------------------------------------------------------

       || Chr(10) || 'Set Linesize 70'
       || Chr(10) || 'Desc '||Table_name

--+--------------------------------------------------------------
--| Turn on the column headings
--+--------------------------------------------------------------

       || Chr(10) || 'Set Linesize 200 Heading On Newpage None'

--+--------------------------------------------------------------
--| Print a few sample records from the table
--+--------------------------------------------------------------

       || Chr(10) || 'Select * 
                        From '||Table_Name|| ' 
                       Where Rownum < 5;'

  From All_Tables 
  Join All_Tab_Comments 
 Using (Table_Name, Owner)
 Where Owner = '&_User'
and table_name in ('SALGRADE','DEPT')
  Order by Table_Name;

Spool Off

--+--------------------------------------------------------------
--| Set to display the final report
--+--------------------------------------------------------------
Set Term On
--+--------------------------------------------------------------
--| Spool file and run the code just spooled above.
--+--------------------------------------------------------------

Spool Dumptbls.Txt
@Dumptbls
Spool Off
Prompt 
Prompt Note: Your table data is in DumpTbls.Txt.
Prompt ====

Insert Date and/or Time Into Spool Name

To generate a report with a date in the filename:

Example: Tblinfo-20080703-1044.Txt

Define Spool name as

1. Some identifying report name
   
2. &variable date name can be smashed up next to it with no concatenation symbols
3. to put something behind it (like a file extension), use a period. I have '..txt'. (The first is a concatenation symbol; the second is the one normally seen between the filename and extension.)
   

------------------------------------------------------------------
-- Set Terminal off so the first query doesn't display.
-- Select to get the date/time from the system, and
-- then set Terminal back on.  Use New_Value to create a variable.
------------------------------------------------------------------

Set Term Off

Column ReportDate  New_Value xRptDate

Select To_Char(Sysdate,'yyyymmdd-hh24mi')  ReportDate
From Dual;

Set Term On

------------------------------------------------------------------
-- Run the report
------------------------------------------------------------------

Spool Tblinfo-&xRptDate..Txt

Select EmpNo, EName, Sal, Deptno, Mgr, Job
From Emp;

Spool Off

------------------------------------------------------------------
-- Optionally, tell them the new name of the report.
------------------------------------------------------------------

Prompt
Prompt Your report has been spooled to Tblinfo-&xRptDate..Txt

Partition By Null

I saw 'Partition by Null' in a snippet of code on the web, but with no explanation. Well, I'm so glad I tried this out, because as shown in the code below, this will give a calculation on a row basis.

With out the 'Partition By' , attempting to sum by row would display 'not a single-group group function.' 'Over (Partition By Null)' can also be written as simply 'Over()'.

Break On Report
Compute Sum Of        -
               Sal    -
               PctSal    On Report

Column Pctsal Heading '% Of Total|Salary'  Format 999

Select Ename,
       Sal,
       -----------------------------------------
             100*Sal/Sum(Sal)
                     Over (Partition By Null)
             As
       Pctsal
       -----------------------------------------
  From Emp
 Order By Sal Desc;

See Oracle documentation for further detail:
Partition By

Elapsed Time 2

If you need to make a decision based on the number of minutes, remember that a day is '1'. Hence a minute is 1/24/60 or .000694.

In this example, I want records where the number of minutes is greater than 120.

To set up the tables and data, run this:

Drop Table Elapsed;

Create Table Elapsed 
      (Starttime Date, 
       Endtime   Date);

Insert Into Elapsed 
   Values         -- A Day
      (To_Date('01-Jul-0801:01', 'dd-Mon-Yyhh:Mi'),
       To_Date('02-Jul-0801:01', 'dd-Mon-Yyhh:Mi')
      );  
Insert Into Elapsed 
   Values       -- An Hour
      (To_Date('02-Jul-0801:00', 'dd-Mon-Yyhh:Mi'),
       To_Date('02-Jul-0802:00', 'dd-Mon-Yyhh:Mi')
       );

This is the query that evaluates which records to display:

Column StartTime      Heading Start|Time
Column EndTime        Heading End|Time
Column ElapsedMinutes Heading Elapsed|Minutes

Select         To_Char(Starttime, 'mm/Dd/Yy Hh:Mi')
               As
       StartTime,
               To_Char(Endtime  , 'mm/Dd/Yy Hh:Mi')
               As
       EndTime,
               (Endtime-Starttime)/(1/24/60) 
               As
       ElapsedMinutes
  From Elapsed
 Where (Endtime-Starttime)/(1/24/60)>120;

See Oracle documentation for further detail:
Dates