External Tables in Oracle

External Tables

Uploading Daily Files.

If you can load a file using SQLLDR you can now load the same file using external tables. Caveat - as long as the file is on the database server.

You will first need to prove that you can load the file to the database using SQLLDR.

Then, as DBA, create a directory in the database and grant read and write to your user. You need write access to the directory, because Oracle will update the logfile each time you 'load' the file. The logfile could in theory be placed in another 'writeable'
directory.

create or replace directory COMET_LOAD as '/app/toolkit/COMET';
grant read on directory COMET_LOAD to cometimport;
grant write on directory COMET_LOAD to cometimport;

Then using SQLLDR you can generate the commands to build your external table

 SQLLDR .....all your commands...... EXTERNAL_TABLE=GENERATE_ONLY.

Opening up the logfile will reveal the create table command necessary to build an external table on the file mentioned in your SQLLDR statement.

CREATE TABLE COMETIMPORT.LINK_DYNAMIC_EXTERNAL
(
 TOOLKIT_LINK_ID     VARCHAR2(255 BYTE),
 CONGESTIONPERCENT   NUMBER,
 CURRENTFLOW         NUMBER,
 AVERAGESPEED        NUMBER,
 LINKTRAVELTIME      NUMBER,
 LASTUPDATED         DATE,
 EXITBLOCKED         NUMBER,
 LINKSTATUS          NUMBER,
 JOURNEYCOUNT        NUMBER,
 TYPE_OF_DAY         VARCHAR2(255 BYTE),
 DAYSLICE            VARCHAR2(255 BYTE),
 TOOLKIT_TYPE_OF_DAY VARCHAR2(255 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
  DEFAULT DIRECTORY COMET_LOAD
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'COMET_LOAD':'comet_bad_xt.bad'
    LOGFILE 'COMET_LOAD':'comet_lg_xt.log'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
    TOOLKIT_LINK_ID CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    CONGESTIONPERCENT CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    CURRENTFLOW CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    AVERAGESPEED CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    LINKTRAVELTIME CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    LASTUPDATED CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    DATE_FORMAT DATE MASK "YYYY-MM-DD HH24:MI:SS",
    EXITBLOCKED CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    LINKSTATUS CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    JOURNEYCOUNT CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    TYPE_OF_DAY CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    DAYSLICE CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
    TOOLKIT_TYPE_OF_DAY CHAR(255)
      TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  LOCATION (COMET_LOAD:'linkdynamic20090121.csv')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

You can create the table without the file being present, querying it without the file present will generate an error.

Extracting the data is then done as an insert into...... select * from .... query

If you need to load another file, with a different name, you can perform an alter table command.

ALTER TABLE link_dynamic_external LOCATION ('linkdynamic20090101.csv');

Generating XML direct from the Database

Generating XML direct from the Database


There are several ways to generate XML directly from the database using SQL.

DBMS_XMLGEN
The first method demands minimal knowledge for the maximum output. It is limited in what it does and you are limited in
what you can can influence.

Firstly create a table to store your result.

CREATE TABLE temp_xml_table(result CLOB);

The procedure then follows the following structure.

DECLARE
   qryctx DBMS_XMLGEN.ctxhandle;
   RESULT CLOB;
   l_sql VARCHAR2 (1000)
         := 'SELECT s.ID, CURSOR (SELECT e_code
                                    FROM site_equipments eq
                                   WHERE eq.sit_id = s.ID) as eqip
               FROM sites s
              WHERE ROWNUM <  11';
BEGIN
   qryctx := DBMS_XMLGEN.newcontext (l_sql);
   -- set the row header to be SITE
   DBMS_XMLGEN.setrowtag (qryctx, 'SITE');
   -- now get the result
   RESULT := DBMS_XMLGEN.getxml (qryctx);
   INSERT INTO temp_xml_table VALUES (RESULT);
   --always close context or you'll blow up.
   DBMS_XMLGEN.closecontext (qryctx);
END;
/ 

You can view the resulting clob by double clicking in TOAD which starts the XML viewer.

When you are finished drop the table.

DROP TABLE temp_xml_table PURGE;

SYS_XMLAGG
The second method demands more knowledge and planning upfront (and patience).

This is a 'more' straightforward way of generating XML content but uses SQL natively and wraps the columns in functions and procedures to produce a more clean output.

Once again start by creating a table for output.

CREATE TABLE temp_xml_table(result CLOB);

The query looks like follows:

INSERT INTO temp_xml_table
SELECT SYS_XMLAGG (XMLELEMENT ("SiteID",xmlattributes (
       LPAD (s.b_borough_no, 2, '0')'/'LPAD (s.site_no, 6, '0') AS "SiteNumber" ),
       s.ID,(SELECT xmlagg(xmlelement("eqi", e_code))
               FROM site_equipments eq
              WHERE eq.sit_id = s.ID and eq.end_date > sysdate)as "Equip"),
       xmlformat ('Sites')
       ) AS lmx
  FROM sites s
 WHERE ROWNUM < 11;

Other methods are more complex, involving object types, object views and casting to multisets and are not mentioned here but are left out for the sake of sanity (mainly mine).

This is just starting to scratch the surface of generating XML, so if you need help or advice let me know.

Navigating XML in Oracle

A few code snippets for navigating and extracting dynamically from XML in PL/SQL.

--The executable section of the traverse_and_display procedure. 
BEGIN
  -- get all elements
  nodes := xmldom.getElementsByTagName (doc, '*');

  -- loop through elements
  FOR node_index IN 0 .. xmldom.getLength (nodes) - 1
  LOOP
     one_node := xmldom.item (nodes, node_index);

     display_element (one_node);

     node_map := xmldom.getAttributes (one_node);

     FOR attr_index IN
        0 .. xmldom.getLength (node_map) - 1
     LOOP
        display_attribute (node_map, attr_index);
     END LOOP;
  END LOOP;
END traverse_and_display;



-- The code required for displaying the name and value of an element. 
PROCEDURE display_element (node IN xmldom.DOMNode)
IS
  one_element   xmldom.DOMElement;
  value_node    xmldom.DOMNode;
BEGIN
  one_element := xmldom.makeElement (node);
  DBMS_OUTPUT.put_line ('Element: ' ||
                       xmldom.getTagName (one_element
                       )
  );
  value_node := xmldom.getFirstChild (node);
  DBMS_OUTPUT.put_line ('Value: ' ||
                       xmldom.getNodeValue (value_node
                       )
  );
END;



--Displaying the value of an attribute. 
PROCEDURE display_attribute (
  node_map     IN   xmldom.DOMNamedNodeMap,
  attr_index   IN   PLS_INTEGER
)
IS
  one_node   xmldom.DOMNode;
  attrname   VARCHAR2 (100);
  attrval    VARCHAR2 (100);
BEGIN
  one_node := xmldom.item (node_map, attr_index);
  attrname := xmldom.getNodeName (one_node);
  attrval := xmldom.getNodeValue (one_node);
  DBMS_OUTPUT.put_line ('   ' || 
        attrname || ' = ' || attrval
  );
END;

Temp Tablespace

Default Temporary Tablespaces

If you CREATE USER and forget to include a TEMPORARY TABLESPACE clause, Oracle uses the SYSTEM tablespace for that user’s sorts. This hurts performance. 9i addresses this by allowing you to specify a system-wide default temporary tablespace. Specify the DEFAULT TEMPORARY TABLESPACE on the CREATE DATABASE statement. Or, define the new temporary tablespace by the CREATE TEMPORARY TABLESPACE statement, and make it the default by running:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE default_temp_ts ;

Automated UNDO Management

Automated Undo Management (AUM)

9i’s new feature Automated Undo Management (AUM) relieves you of the traditional, labor-intensive task of sizing and managing rollbacks. To use AUM, create a tablespace that will be used for rollbacks (the UNDO tablespace). Then start the instance with these two new 9i initialization parameters set to:

UNDO_MANAGEMENT = AUTO

UNDO_TABLESPACE = undo_tablespace_name

Once an instance is started with AUM, you can not and do not create or manage rollback segments manually. You can switch to another UNDO tablespace whenever you want, but you can not drop an UNDO tablespace while it has active transactions.