Database Reference
In-Depth Information
Now we can create an externally organized table. All constraints have
been removed, including the NOT NULL and primary key constraint.
External tables do not appear to allow these constraints. See Chapter 20 for
details on constraints. The default directory is the DATA directory on the
database server machine.
CREATE TABLE SALESEXT(
SALES_ID
NUMBER
, MUSICCD_ID
NUMBER
, CUSTOMER_ID
NUMBER
, RETAILER_ID
NUMBER
, CONTINENT_ID
NUMBER
, COUNTRY_ID
NUMBER
, LIST_PRICE
FLOAT
, DISCOUNT
FLOAT
, SALE_PRICE
FLOAT
, SALE_DATE
DATE
, SALE_QTY
NUMBER
, SHIPPING_COST
FLOAT)
ORGANIZATION EXTERNAL
(
DEFAULT DIRECTORY data
LOCATION ('salesext.txt')
)
REJECT LIMIT UNLIMITED;
To put data into the SALESEXT table, do the following in SQL*Plus:
SET COLSEP, REPSEP OFF LINESIZE 5000 PAGESIZE 5000 HEAD OFF;
SPOOL C:\TMP\SALESEXT.TXT;
SELECT * FROM SALES;
SPOOL OFF;
Then parse-replace all space characters in a text editor to make the text
file look like this:
1,12,1,3,7,24,7.98,0,7.98,03-NOV-03,1,0
2,11,2,3,7,22,19.99,0,19.99,03-NOV-03,1,0
3,8,3,2,7,17,19.99,.05,18.99,03-NOV-03,1,0
996,3,5,1,2,6,14.99,.2,11.99,17-DEC-03,1,0
 
Search WWH ::




Custom Search