Delete Based on Another Table

I recently had to delete records in one table based on the data in another table. This isn't particularly tricky, but the only examples I could find online ended up deleting all the records in the table.

The code at the beginning is just to set up a table I can use as an example.

Conn Scott/Tiger
Set Feedback On Echo On

Select *
From Dept;

Drop Table Dept2 Purge;

Create Table Dept2 As
Select * From Dept;

Insert Into Dept2 Values(50,'HR','PITTSBURGH');
Insert Into Dept2 Values(60,'HOUSEKEEPING','LA');
Insert Into Dept2 Values(70,'MANAGEMENT','ATLANTA');

Select * From Dept2;

This code deletes from the newly created table where a record exists in the original table that has a matching key.

Delete From Dept2
 Where Exists
          (Select 1
             From Dept
            Where Dept.Deptno = Dept2.Deptno);

Select * From Dept2;

Timestamp in an External Table

Since this wasn't particularly easy to find, and it took several tries, I decided to post it here. As most things, once you know how to write it, it's not particularly tricky.

Input Data from External Table:
1,Jan 8 1998 12:43:27:012AM
2,Feb 19 1998 2:43:27:111PM
3,Jan 8 2008 3:49:27:693PM

Create Table Ext_Dt
(  Item       Number
 , Needed_By  Timestamp
)
Organization External
(
 Type Oracle_Loader
 Default Directory Ext
 Access Parameters
 (
    Records Delimited By Newline
    Badfile 'Date.Bad'
    Logfile 'Date.Log'
    Fields Terminated By ','
    Missing Field Values Are Null
       (
          Item,
          Needed_By Char Date_Format Timestamp Mask 'Mon Dd Yyyy Hh:Mi:Ss:Ff3am'
       )
 )
Location (Ext:'Dt.Txt')
)
Reject Limit Unlimited;

Select Item
     , Needed_By
     , To_Char(Needed_By,'Mon' ) Mth
     , To_Char(Needed_By,'Hh24') Hr
  From Ext_Dt;

Database Terminology

Physical	Logical/ Relational	Logical/ Obj.Orient	Definition	Examples
Table	Entity	Class	Something you want to store data for	Department, Employees
Column	Attribute	Attribute	An item that helps describes the entity	Department Location, Employee Birthdate
Row	Instance	Object	A specific example of the entity	Pittsburgh department, Lynn's birthdate: 01/01/1970

Selecting from a Variable TableName

A question was posted on ITToolBox asking how to select from a table based on a column. You can't use a CASE clause in the FROM statement, but the following -- though convoluted -- does work.

'New_Value' in the 'Column' statement will create a new variable named 'Table' that can then be used in the final query.

Set Verify Off Echo Off Linesize 200

Accept Key Prompt 'Enter the key for the table you wish to view (EmpNo, DeptNo): '

/*----------------------------------------------------------*/
/* Turn off the terminal so this query doesn't display      */
/*----------------------------------------------------------*/
Set Term Off
Column Table_From_Case New_Value Table

Select        Case Upper('&Key') When 'EMPNO'  Then 'EMP'
                                When 'DEPTNO' Then 'DEPT'
             End
             As
     Table_From_Case
From Dual;

Set Term On

/*----------------------------------------------------------*/
/* Select from the tablename set up with COLUMN/NEW_VALUE   */
/*----------------------------------------------------------*/

Select *
 From &Table
Where Rownum < 3;

How to Code Single Quotes with Q-Quote

Starting in 10g, use a 'q', a pair of single quotes, and a pair of brackets to code a literal that uses a single quote. With this method, you can code it exactly as it is.

Select q'!He's ok!'        As Single_Quote_In_Middle
     , q'['I like him.']'  As Single_Quote_At_Ends
     , q'(')'              As Single_Quote_Alone
     , q'<''>'             As Two_Single_Quotes
  From Dual;

To delete or insert a name with a single quote:

Delete From Emp Where EName = q'\O'MALLEY\';

Insert Into Emp ( EmpNo , EName         , DeptNo )
         Values ( 7474  , q'{O'MALLEY}' , 10     );
Commit;

To search for a name with a single quote in it:

Select EmpNo, Ename, DeptNo
 From Emp
Where Ename Like q'[%'%]';

Prior to 10g:

Select 'He''s ok'         As Single_Quote_In_Middle
     , '''I like him.'''  As Single_Quote_At_Ends
     , ''''               As Single_Quote_Alone
     , ''''''             As Two_Single_Quotes
  From Dual;

Oracle Exceptions found in DBA_Source

rem +--------------------------------------------------------------------------+
rem | Script ID:     Exceptions.sql
rem |
rem | Purpose:       Display a list of exception numbers assigned by Oracle.
rem |               
rem | Developer:     Lynn Tobias
rem | Script Date:   3/12/2009
rem | Oracle Ver:    10g
rem |
rem | Table(s) Used: DBA_Source
rem |               
rem | Output:        Exceptions.Txt
rem |
rem | Revisions:     Dvl Date     Ver Comment/Change
rem |                --- -------- --- -----------------------------------------+
rem +--------------------------------------------------------------------------+
Clear Screen
Column Exception Format A30     Heading 'Exception Name'
Column Name      Format A30     Heading 'DBA_Source Name'
Column Errnum    Format 9999999 Heading 'Exception'
Spool Exceptions.Txt

Select
       --- 1 ---------------------------------------------------------------------------------------
       -- 1.1 Replace:       There were so many variations that to make the regexp simpler, I 
       --                    removed all the spaces.
       -- 1.2 Replace(2):    I also removed the quotes from the few that used those on the numbers.
       -- 1.3 Regexp_Substr: Look for a string starting with a comma, then one or more of anything
       --                    and ending with a closed parenthesis.
       -- 1.4 Substr:        To remove the leading comma and the trailing parenthesis, start the 
       --                    substring at position two, and go for a length of the string minus 2.
       -- 1.5 To_Number:     Convert it to a number so it will sort correctly.
       ---------------------------------------------------------------------------------------------
               To_Number(
                         Substr(
                                Regexp_Substr(
                                              Replace(
                                                      Replace(Text,' ',Null)
                                                     ,'''',Null
                                                     )
                                              ,',.{1,}\)'
                                             ) 
                               ,2
                               ,Length(Regexp_Substr(Replace
                                        (Replace(Text,' ',Null)
                                        ,'''',Null),',.{1,}\)')
                                      )
                                      -2
                               )
                        ) 
               As
       Errnum
       --- 2 ---------------------------------------------------------------------------------------
       -- This is the name field from DBA_Source
       ---------------------------------------------------------------------------------------------
     ,         Initcap(Name)
               As
       Name      
       --- 3 ---------------------------------------------------------------------------------------
       -- 3.1 RegExp_Substr: Find the text starting with a open parenthesis for one or more letters 
       --                    and ending with a comma.
       -- 3.2 Substr:        Drop '(' and ',' from string by substringing starting at position 2, 
       --                    and going for a length of the original string minus 2.
       -- 3.3 InitCap:       Make it consistent and more readable
       ---------------------------------------------------------------------------------------------
     ,         InitCap(
                       Substr(
                              Regexp_Substr(
                                            Text
                                           ,'\(.{1,}\,'
                                           )
                             ,2
                             ,Length(Regexp_Substr(Text,'\(.{1,}\,'))
                              -2
                                    )
                      ) 
               As
       Exception
       ---------------------------------------------------------------------      
  From Dba_Source 
 Where Upper(Text) Like '%EXCEPTION_INIT%'
   And Text Not Like '--%'
 Order By Errnum Desc;

Prompt
Prompt Note: This list has been spooled to Exceptions.Txt

Line Breaks

To get a line break in your output data, use 'Chr(10).' It must be concatenated to the columns it's between

  1* Select 'Hi' || Chr(10) || 'There' From Dual
SQL> /

Hi
There

Regular Expressions

The create table statement and all the code to produce this report can be found here. Scroll down to the end to find the attachment: RegExp.Sql

This is the data the queries run against:

Select * From Fam;

NAME  PHONE           DSCR
----- --------------- --------------------------------------------------------------------------------
Me    555-123-0987    Fun-loving aunt with a lot of nieces and nephews. 111-123-4567
Don   555-234-2323    alternate phone (555)234-2222. Father Father of mostly-grown kids.
Craig 555-888-8888    at-home {nuts} {nuts} +45 8 676 49 86
Glenn 555-232-8888    (123) 242-2424
Dee   232-388-3333    -farmer 787.222.9834

Description to Match:  3 of anything, a dash, then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'...-....')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   234-2222
Glenn 242-2424

Description to Match:  3 of anything, a dash, then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'.{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   234-2222
Glenn 242-2424

BRACKET EXPRESSIONS

Description to Match: 3#s, -, 4

NAME  REGEXP_SUBSTR(DSCR,'[12345]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424

Description to Match: 3 numbers(1-5), -, 4

NAME  REGEXP_SUBSTR(DSCR,'[1-5]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424

Description to Match: 3 numbers,-,4

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424

Description to Match: Any 3 characters except for numbers,-,4

NAME  REGEXP_SUBSTR(DSCR,'[^[:digit:]]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   tly-grow

Description to Match: 3 numbers, A-G or X-z or 'n' or 'u' ,a dash then 4 more characters. 
Don's data doesn't show because he has an earlier digit value

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]A-GX-Znu]{3}-.{4}')
----- ------------------------------------------------------------------------------------------------
Me    Fun-lovi
Don   234-2222
Glenn 242-2424

Description to Match: 3 numbers, a literal period

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}\.')
----- ------------------------------------------------------------------------------------------------
Don   222.
Dee   787.

SUBEXPRESSIONS

(find a "+" then 1-4 sections of 1-3 digits followed by a space. 
The last grouping will be 1 or more numbers) 
i.e., ([0-9]{1,3} ) means 1-3 numbers followed by a space

Description to Match: International phone #

NAME  REGEXP_SUBSTR(DSCR,'\+([0-9]{1,3}){1,4}([0-9]+)')
----- ------------------------------------------------------------------------------------------------
Craig +45 8 676 49 86

ALTERNATION

(use '|')  i.e., (-|\.)

Description to Match: 3 numbers, - or ., then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}(-|\.).{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424
Dee   787.222.

Description to Match: 3 numbers, - or ., then 4 of anything

NAME  REGEXP_SUBSTR(DSCR,'[[:digit:]]{3}[.-].{4}')
----- ------------------------------------------------------------------------------------------------
Me    111-123-
Don   234-2222
Glenn 242-2424
Dee   787.222.

Description to Match: Phone numbers with dashes, periods or parens

NAME  REGEXP_SUBSTR(DSCR,'([0-9]{3}[.-]|\([0-9]{3}\)?)'||'[0-9]{3}[.-][0-9]{4}')
----- ------------------------------------------------------------------------------------------------
Me     111-123-4567
Don   (555)234-2222
Glenn (123) 242-2424
Dee    787.222.9834

GREEDINESS

Description to Match: Get a word ending in a space (gets all it can find)

.+[[:space:]]
-------------
In the

Description to Match: To get 1st word only

[^[:space:]]
------------
In

BACKREFERENCES

Description to Match: Find double-words

                                     REGEXP_SUBSTR(DSCR,'(^|[[:space:][:punct:]]+)([[:alpha:]]+)'||
NAME  DSCR                                                     '[[:space:][:punct:]]+\2')
----- ------------------------------ -----------------------------------------------------------------
Don   alternate phone (555)234-2222. Father . Father Father
      Father Father of mostly-grown 
      kids.
Craig at-home {nuts} {nuts} +45 8    {nuts} {nuts
      676 49 86

Fixed-Width Fonts for Blogging

Update: It should have been obvious, but it wasn't. You shouldn't use a font that is not commonly installed on everyone's machine (for example, Consolas). I've gone to fixedsys, which I think comes with Windows.

I got a little bored looking at the same fixed-width font, and started testing for a new look. The first example is what you'll see if you use the <pre> tag. In the standard font, the zero and capital 'O' are virtually undistiguishable, as is the one and lower-case 'l'.

         1         2         3         4         5         6         7         8         9         0         1
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
Select 0Oo, 1ilI ,
     , name
  from Emp;

You can modify the pre tag to include a font, and in some cases a size: <pre STYLE="font-size: 11pt; font-family:'Consolas'">

Courier:

Only one size. Characters look different side-by-side, but I like a slashed zero.

         1         2         3         4         5         6         7         8         9         0         1    
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
Select 0Oo, 1ilI ,
     , name
  from Emp;

Courier New 10pt:

You can select a size. Again, I'd like a slashed zero.

         1         2         3         4         5         6         7         8         9         0         1    
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
Select 0Oo, 1ilI ,
     , name
  from Emp;

Fixedsys:

A little dark but the zero and capital "O" look different. Also, one and lower-case "L" look different. (Only one size)

         1         2         3         4         5         6         7         8         9         0         1    
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
Select 0Oo, 1ilI ,
     , name
  from Emp;

Consolas 11pt:

This has a bit of a fuzzy look to me. Also, the one and lower-case 'l' look too similar.

         1         2         3         4         5         6         7         8         9         0         1    
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
Select 0Oo, 1ilI ,
     , name
  from Emp;

Bitstream Vera Sans Mono 10pt:

You can select a size. The zero has a dot inside, but it's hard to see unless you go with a larger size, or you bold it.

         1         2         3         4         5         6         7         8         9         0         1    
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
Select 0Oo, 1ilI ,
     , name
  from Emp;

Bitstream Vera Sans Mono 10pt - Bold:

         1         2         3         4         5         6         7         8         9         0         1    
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
Select 0Oo, 1ilI ,
     , name
  from Emp;

Sequence Basics

This shows how to create a basic sequence. CurrVal will show what number was last used. Whenever NextVal is referenced, the sequence gets incremented.

Set Feedback Off Heading Off
Drop Table TestTbl;
Create Table TestTbl
      ( Id   Number(4),
        Note Varchar2(20) );

Drop Sequence Test_Seq;
Create Sequence Test_Seq
         Increment By 1 Start With 1
         NoMaxValue NoCache NoCycle;

Insert Into TestTbl Values(Test_Seq.Nextval, 'Apple');
Select 'CurrVal: '||Test_Seq.CurrVal From Dual;

CurrVal: 1

Insert Into TestTbl Values(Test_Seq.Nextval, 'Nuts' );
Select 'CurrVal: '||Test_Seq.CurrVal From Dual;

CurrVal: 2

Select 'ID: '  ||ID         ||' '||
       'Note: '||Note
 from TestTbl;

ID: 1 Note: Apple
ID: 2 Note: Nuts

Select 'CurrVal: '||Test_Seq.CurrVal ||' '||
       'NextVal: '||Test_Seq.NextVal
  From Dual;

CurrVal: 3 NextVal: 3

/

CurrVal: 4 NextVal: 4

Trigger - Check for Positive Values

Create Or Replace Trigger PositiveOnly
 Before Insert Or Update
 Of DeptNo On Dept
 For Each Row
Begin
 If :new.DeptNo -20100, 'Please insert a positive value');
 End If;
End PositiveOnly;
/

Range & List-Partitioned Tables

A range partition allows Oracle to place data in a partition based on the input value being between two values. 'MaxValue' is used to hold everything higher than the previous value listed.

A list partition allows Oracle to place data in a partition based on the input value matching a value in the list. The 'Default' is used to hold everything not mentioned in the list.

For instance, in the range-partitioned table below, data is grouped based on the year

partition Part_2004 - any year less than 2005

partition Part_2005 - any year between 2005 and 2006

partition Part_Max - any year greater than 2006

SQL> Create Table Test_Range
  2   ( Id    Number(3)
  3   , Year  Number(4)
  4   )
  5    Partition By Range(Year)
  6    (
  7      Partition Part_2004 Values Less Than (2005)
  8    , Partition Part_2005 Values Less Than (2006)
  9    , Partition Part_Max  Values Less Than (MaxValue)
 10    );
 
SQL> Insert Into Test_Range Values (1,2003);
SQL> Insert Into Test_Range Values (2,2004);
SQL> Insert Into Test_Range Values (3,2005);
SQL> Insert Into Test_Range Values (4,2006);
SQL> Insert Into Test_Range Values (5,2007);

SQL> Select * From Test_Range;

ID       YEAR
-- ----------
 1       2003
 2       2004
 3       2005
 4       2006
 5       2007

SQL> Select * from Test_Range PARTITION (Part_2004);

ID       YEAR
-- ----------
 1       2003
 2       2004

SQL> Select * from Test_Range PARTITION (Part_2005);

ID       YEAR
-- ----------
 3       2005

SQL> Select * from Test_Range PARTITION (Part_Max);

ID       YEAR
-- ----------
 4       2006
 5       2007

---

In the List-partitioned table below, data is grouped based on continents:

partition Part_Europe - France or Italy

partition Part_America - Canada or United States

partition Part_Others - any country not listed in the other two partitions.

SQL> Create Table Test_List
  2   ( Id      Number(3)
  3   , Country Varchar2(30)
  4   )
  5    Partition By List(Country)
  6     (
  7       Partition Part_Europe  Values ('France', 'Italy')
  8     , Partition Part_America Values ('Canada', 'United States')
  9     , Partition Part_Others  Values (Default)
 10     );

SQL> Insert Into Test_List Values (1,'France');
SQL> Insert Into Test_List Values (2,'Italy');
SQL> Insert Into Test_List Values (3,'Canada');
SQL> Insert Into Test_List Values (4,'United States');
SQL> Insert Into Test_List Values (5,'Australia');
SQL> Insert Into Test_List Values (6,'China');

SQL> Select * from Test_List;

ID COUNTRY
-- -------------
 1 France
 2 Italy
 3 Canada
 4 United States
 5 Australia
 6 China
 
SQL> Select * from Test_List PARTITION (Part_Europe );

ID COUNTRY
-- -------------
 1 France
 2 Italy

SQL> Select * from Test_List PARTITION (Part_America);

ID COUNTRY
-- -------------
 3 Canada
 4 United States
 
SQL> Select * from Test_List PARTITION (Part_Others );

ID COUNTRY
-- -------------
 5 Australia
 6 China

For more information see the Oracle documentation on Partitioned Tables and Indexes

How to Find a Linking Table

What do you do if you need to select data from two tables, but discover they have no common column? You look for the table that links them.

As an example, I'm using the Oracle-supplied HR schema. This query will find the table (if there is one) that has a field in Locations, and also one in Regions. (This is based on column names so if a column was named 'RegID', it would not match to 'Region_ID' even if the data was the same.)

SQL> desc locations
 Name              Null?    Type
 ----------------- -------- ------------
 LOCATION_ID       NOT NULL NUMBER(4)
 STREET_ADDRESS             VARCHAR2(40)
 POSTAL_CODE                VARCHAR2(12)
 CITY              NOT NULL VARCHAR2(30)
 STATE_PROVINCE             VARCHAR2(25)
 COUNTRY_ID                 CHAR(2)

SQL> desc regions
 Name              Null?    Type
 ----------------- -------- ------------
 REGION_ID         NOT NULL NUMBER
 REGION_NAME                VARCHAR2(25)

When the query is run, the user supplies two table names. The query then gets a list of available column names, and searches User_Tab_Columns to find other tables with columns in that list.

Set Newpage 1 Pagesize 50 Linesize 200 Echo &Onoff Feedback &Onoff Verify &Onoff

Accept Table1 Prompt '1st Table: '
Accept Table2 Prompt '2nd Table: '

Column Table_Name       Heading 'Tables From|Which Data|Is Needed'
Column Connecting_Table Heading 'Table Through|Which Connection|Needs To Be Made'
Column Column_Name      Heading 'Connecting|Column_Name'

--+-----------------------------------------------------------------------------
--| Get a list of columns in either table
--+-----------------------------------------------------------------------------
With Available_Columns As
      (
       Select Table_Name
            , Column_Name
         From User_Tab_Columns
       Where Table_Name In ( Upper('&Table1')
                           , Upper('&Table2')
                           )
      )
--+-----------------------------------------------------------------------------
--| Get the final list of original table names, connecting tables, and their
--|  columns.
--+-----------------------------------------------------------------------------
Select Table_Name
     , Connecting_Table
     , Column_Name
  From
        --+---------------------------------------------------------------------
        --| We need to count them up because if it doesn't have at least 2 
        --| columns (Col_Count > 1),  or they're both in the same table 
        --| (Tbl_Count = 1), it wouldn't be a usable set.
        --| 
        --| Table_Name Column_Name Connecting_Table Col_Count Tbl_Count
        --| ---------- ----------- ---------------- --------- ---------
        --| LOCATIONS  COUNTRY_ID  COUNTRIES                2         1
        --| REGIONS    REGION_ID   COUNTRIES                2         1
        --| LOCATIONS  LOCATION_ID DEPARTMENTS              1         1
        --+---------------------------------------------------------------------
       (
        Select Table_Name
             , ac.Column_Name
             , Connecting_Table
             ,                  Count(*) Over (Partition By Connecting_Table) 
                                As
               Col_Count
             ,                  Count(*) Over (Partition By Connecting_Table, 
                                                            Table_Name) 
                                As
               Tbl_Count
          From Available_Columns ac,
              --+---------------------------------------------------------------
              --| Get a list of columns from other tables that match those found
              --| in the two tables requested.
              --| 
              --| Connecting_Table   Column_Name
              --| ------------------ -----------
              --| COUNTRIES          COUNTRY_ID
              --| COUNTRIES          REGION_ID
              --| DEPARTMENTS        LOCATION_ID
              --+---------------------------------------------------------------
              (
              Select                 Table_Name 
                                     As 
                     Connecting_Table
                   , Column_Name
                From User_Tab_Columns
               Where Table_Name Not In ( Upper('&Table1')
                                       , Upper('&Table2')
                                       )
                 And Table_Name Not Like 'BIN$%'    /* not in Recycle Bin */
                 And Column_Name In
                            (
                            Select Column_Name
                            From Available_Columns
                            )
              )T2
              --+---------------------------------------------------------------
        Where ac.Column_Name = T2.Column_Name
       )
Where Col_Count > 1
  And Tbl_Count = 1;
  
Undefine OnOff Table1 Table2

Printing Labels from SQL through HTML

I recently needed to print 3-up address labels with the same information on each. I used a combination of padding, and adjusting the margins in the browser 'Page Setup' to get these to print in the perfs.

• This title shows in the browser title, and tab: Head "<Title>Test Labels</Title>
  

• Where there are four options, they are in this order: top right bottom left.

• Styles are Dotted Dashed Solid Double Groove Ridge Inset Outset.

This section is for the entire table:

<!-- Body {Border-Width: 5px 5px 5px 5px;             -
           Border-Style: outset outset outset outset; -
           Border-Color: Gray Gray Gray Gray;         -
           Background-Color: White;                   -
          }

This section is for each cell:

      Td {Border-Width: 5px 10px 5px 10px;           -
          Padding: 0px 50px 0px 0px;                 -
          Border-Style: Inset Inset Inset Inset;     -
          Border-Color: Gray Gray Gray Gray;         -
          Background-Color: White;                   -
          -Moz-Border-Radius: 10px 0px 0px 0px;       (Rounds edges of cells)

---

The code:

Set Term Off Echo Off Heading Off Linesize 2000 Feedback Onoff

Set Markup Html On Entmap Off Spool On Preformat Off   -
Head "<Title>Test Labels</Title>                       -
<Style Type='Text/Css'>                                -
<!-- Body {Border-Width: 0px 0px 0px 0px;              -
           Border-Style: Outset Outset Outset Outset;  -
           Border-Color: Gray Gray Gray Gray;          -
           Background-Color: White;                    -
          }                                            -
       Td {Border-Width: 0px 0px 0px 0px;              -
           Padding: 8px 11px 8px 5px;                  -
           Border-Style: Inset Inset Inset Inset;      -
           Border-Color: Gray Gray Gray Gray;          -
           Background-Color: White;                    -
           -Moz-Border-Radius: 0px 0px 0px 0px;        -
          }                                            -
</Style>" -
Table "Width='100%' Border='0'"

Column Title Entmap Off

Spool Labels.Html

Select --+----------------------------------------------------------------------
       --| Left Side Label
       --+----------------------------------------------------------------------
         '<Font Size=5 Face="Comic Sans" Color="Magenta"><B><Center>'
       || First || '</B> <Br> <Font Size = 3 Color = "Black">'
       || Last || ', ' || Mom_First || '<Br> Age:<B>'|| Age
       || '</B> Birthdate:' || To_Char(Birthdate,'Mm/Dd/Yy') || ' </Font>'
       --+----------------------------------------------------------------------
       --| Middle Label (Same Code As Above)
       --+----------------------------------------------------------------------
     ,    '<Font Size=5 Face="Comic Sans" Color="Magenta"><B><Center>'
       || First || '</B> <Br> <Font Size = 3 Color = "Black">'
       || Last || ', ' || Mom_First || '<Br> Age:<B>'|| Age
       || '</B> Birthdate:' || To_Char(Birthdate,'Mm/Dd/Yy') || ' </Font>'
       --+----------------------------------------------------------------------
       --| Right side Label (Same Code As Above)
       --+----------------------------------------------------------------------
     ,    '<Font Size=5 Face="Comic Sans" Color="Magenta"><B><Center>'
       || First || '</B> <Br> <Font Size = 3 Color = "Black">'
       || Last || ', ' || Mom_First || '<Br> Age:<B>'|| Age
       || '</B> Birthdate:' || To_Char(Birthdate,'Mm/Dd/Yy') || ' </Font>'
  From Moms_And_Kids
 Order By Last, Birthdate Desc;

Spool Off
Set Markup Html Off Entmap Off Spool Off Preformat Off
Set Echo On Term On

Using Colors in HTML

This chart shows the names of colors that can be used in HTML. In this example, I'm using the actual name to select a color. Realize that the background color will make a difference in how the font color looks. (Note the difference between the 'DarkRed' on black in the chart, and the 'DarkRed' on white in the example).

Set Markup Html On Entmap Off Spool On Preformat Off  
Set Term Off
Spool ColorTest.Html

Select 
       '<Font Color="LightSeaGreen">'|| EmpNo    ||'</Font>' As Empno
     , '<Font Color="DarkRed">'      || EName    ||'</Font>' As EName
     , '<Font Color="Magenta">'      || Hiredate ||'</Font>' As Hiredate
  From Emp
 Where Deptno = 20;

Spool Off
Set Markup Html Off Entmap Off Spool Off Preformat Off 
Set Term On

It will generate the HTML report shown on the right.