Home » SQL & PL/SQL » SQL & PL/SQL » External Tables for a fixed width file
External Tables for a fixed width file [message #364919] Wed, 10 December 2008 08:18 Go to next message
bahubcd
Messages: 36
Registered: July 2007
Location: Bangalore
Member
I am trying to create an external table from a file which is attached. It is a fixed width file. The colours denote the delimiters which has to be dealt using the width of the numbers.

./fa/5421/0/

I used the below query to create the same but it dint work. Can somone suggest the correct usage of external table creation in this regards?


--------------------------------------------------------------
prompt Creating external table DC_RPLDELFRCST
CREATE TABLE DC_RPLDELFRCST
(
eow_date DATE NOT NULL,
bracket NUMBER(4) NOT NULL,
store NUMBER(10) NOT NULL,
app NUMBER(13) NOT NULL,
cum NUMBER(13) NOT NULL
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY &&dc_data_dir
ACCESS PARAMETERS
(
RECORDS FIXED 62 FIELDS (eow_date CHAR(8),
bracket INTEGER(4),
store INTEGER(10),
app INTEGER(13),
cum INTEGER(13))
LOAD WHEN (
eow_date != BLANKS and
bracket != BLANKS and
store != BLANKS and
app != BLANKS and
cum != BLANKS
)
BADFILE &&dc_log_dir:'dc_rpldelfrcst.bad'
DISCARDFILE &&dc_log_dir: 'dc_rpldelfrcst.dsc'
LOGFILE &&dc_log_dir:'dc_rpldelfrcst.log'
(
eow_date,
bracket,
store,
app
cum
)
)
location
(
'rpldelfrcst.out '
)
)
reject limit unlimited
/
prompt DC_RPLDELFRCST created successfully
--------------------------------------------------------------


[mod-edit: disabled smilies]
  • Attachment: data.JPG
    (Size: 11.64KB, Downloaded 3210 times)

[Updated on: Wed, 10 December 2008 18:14] by Moderator

Report message to a moderator

Re: External Tables for a fixed width file [message #364928 is a reply to message #364919] Wed, 10 December 2008 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I used the below query to create the same but it dint work.

What does this mean?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: External Tables for a fixed width file [message #365073 is a reply to message #364919] Wed, 10 December 2008 19:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You have a whole bunch of errors. You cannot use NOT NULL with external tables. You have failed to specify column positions and newline. You have duplicated your field declarations. You haven't provided a date format mask. You are missing parentheses around each when condition. When you post your data as jpg, it makes it impossible to copy and paste and has to be retyped. In the future please copy and paste directly into the topic or as txt. Please see the demonstration using corrected code below.

-- contents of c:\oracle11g\rpldelfrcst.out:
200812105879000026473156947416324265744393861862
200812110009536106473100000006834265760373938618
200812125899536826633156947416914260000000061837

SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY dc_data_dir AS 'c:\oracle11g'
  2  /

Directory created.

SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY dc_log_dir AS 'c:\oracle11g'
  2  /

Directory created.

SCOTT@orcl_11g> CREATE TABLE dc_rpldelfrcst
  2    (eow_date  DATE,
  3  	bracket   NUMBER(4),
  4  	store	  NUMBER(10),
  5  	app	  NUMBER(13),
  6  	cum	  NUMBER(13))
  7  ORGANIZATION EXTERNAL
  8    (TYPE ORACLE_LOADER
  9  	DEFAULT DIRECTORY dc_data_dir
 10  	ACCESS PARAMETERS
 11  	  (RECORDS DELIMITED BY NEWLINE
 12  	   LOAD WHEN
 13  	     (eow_date != BLANKS) AND
 14  	     (bracket  != BLANKS) AND
 15  	     (store    != BLANKS) AND
 16  	     (app      != BLANKS) AND
 17  	     (cum      != BLANKS)
 18  	 BADFILE     'DC_LOG_DIR':'dc_rpldelfrcst.bad'
 19  	 DISCARDFILE 'DC_LOG_DIR':'dc_rpldelfrcst.dsc'
 20  	 LOGFILE     'DC_LOG_DIR':'dc_rpldelfrcst.log'
 21  	 FIELDS
 22  	 (eow_date ( 1: 8) CHAR( 8) DATE_FORMAT DATE MASK "YYYYMMDD",
 23  	  bracket  ( 9:12) CHAR( 4),
 24  	  store    (13:22) CHAR(10),
 25  	  app	   (23:35) CHAR(13),
 26  	  cum	   (36:48) CHAR(13)))
 27    LOCATION ('rpldelfrcst.out'))
 28  REJECT LIMIT UNLIMITED
 29  /

Table created.

SCOTT@orcl_11g> SET NUMWIDTH 15
SCOTT@orcl_11g> SELECT * FROM dc_rpldelfrcst
  2  /

EOW_DATE          BRACKET           STORE             APP             CUM
--------- --------------- --------------- --------------- ---------------
10-DEC-08            5879          264731   5694741632426   5744393861862
11-DEC-08               9      5361064731          683426   5760373938618
12-DEC-08            5899      5368266331   5694741691426           61837

SCOTT@orcl_11g> 



Re: External Tables for a fixed width file [message #365660 is a reply to message #365073] Thu, 11 December 2008 20:29 Go to previous message
bahubcd
Messages: 36
Registered: July 2007
Location: Bangalore
Member
Thanks a lot Barbara. Your approach worked perferctly fine. Sorry for not providing the data in text format.

Sincere apologies to Micheal for not indenting the code.
Previous Topic: Query to find out weekly average of job execution time
Next Topic: Passing CHAR literal containing more than 4000 char in function parameter
Goto Forum:
  


Current Time: Sun Dec 04 00:48:05 CST 2016

Total time taken to generate the page: 0.09985 seconds