-
I am trying to create some external tables and I get strange results.
I have a flat file as follows
Code:
20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0;
20020821 00:00:00;Sop Ges Arpa Rech;0;3;1;0;0;
20020821 00:00:00;Sop Ges Arpa Res;0;20;3;0;7;
20020821 00:00:00;Sop Ges Arpa Tra;5;0;5;0;0;
20020821 00:00:00;Sop Ges Portabilidad;9;1;9;0;0;
created external table as follows
Code:
create table
colas_caup
(fecha date,
cola VARCHAR2(45),
ABIERTAS number,
CERRADAS number,
PENDIENTES number,
RECHAZADAS number,
RESUELTAS number)
organization external
(type oracle_loader
default directory tablas_externas
access parameters
(records delimited by newline
fields terminated by ';'
missing field values are null
(fecha char date_format date mask 'YYYYMMDD HH24:MI:SS',
cola,
ABIERTAS,
CERRADAS,
PENDIENTES,
RECHAZADAS,
RESUELTAS))
location('colas.dat'))
reject limit unlimited;
the problem is when I query I get only the first row back with no errors at all in the log!
if then I change the flat file into
Code:
20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0
20020821 00:00:00;Sop Ges Arpa Rech;0;3;1;0;0
20020821 00:00:00;Sop Ges Arpa Res;0;20;3;0;7
20020821 00:00:00;Sop Ges Arpa Tra;5;0;5;0;0
20020821 00:00:00;Sop Ges Portabilidad;9;1;9;0;0
I get 0 rows and error in log stating:
error processing column RESUELTAS in row 1 for datafile /tmp/colas.dat
ORA-01722: invalid number
I then again edit the flat file so it shows now
Code:
20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0
query the table and get the row back with no errors
I cant see what the heck is going on
Anyone tried external tables :?
-
Just check if there are any invisible characters like 'TAB' at the end of last character in flat file.
20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0
Sameer
-
nop, there is no tabs
in fact this flat file is one of many that feed datamart loading processes with sql loader which loads with no problems at all
-
Just a suggestion..
How about taking partial file for loading..
If the file has only 1 rec and then it shows properly, then we can give a try taking records in batches... Might be one of the one of the record in the flat file has a junk character.
may be initially 100 then next 100
I understand file may have millions of records.. You must have shown us a sample records.. but we can give a try....
Sameer
-
well the funny thing is querying following flat file
Code:
20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0;
20020821 00:00:00;Sop Ges Arpa Rech;0;3;1;0;0;
20020821 00:00:00;Sop Ges Arpa Res;0;20;3;0;7;
20020821 00:00:00;Sop Ges Arpa Tra;5;0;5;0;0;
20020821 00:00:00;Sop Ges Portabilidad;9;1;9;0;0;
I get 20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0; back as result
if i change the file to
Code:
20020821 00:00:00;Sop Ges Arpa Rech;0;3;1;0;0;
20020821 00:00:00;Sop Ges Arpa Res;0;20;3;0;7;
20020821 00:00:00;Sop Ges Arpa Tra;5;0;5;0;0;
20020821 00:00:00;Sop Ges Portabilidad;9;1;9;0;0;
20020821 00:00:00;Sop Ges Arpa;1;4;1;0;0;
I get 20020821 00:00:00;Sop Ges Arpa Rech;0;3;1;0;0; back as result so the data are not bad, the problem is it only reads the first row
and if I have 1 million rows obviously I am not gonna split it into 1 million files
-
Originally posted by pando
well the funny thing is querying following flat file
if I have 1 million rows obviously I am not gonna split it into 1 million files
Ofcourse not.. But I have tried the same code at my home, I just copy-pasted everything and it worked without a single error
I am using WinXP, O9i R2. Heres the output.. There must be something wrong with the flatfile only.. No syntax error.. nothing.. Following is the spooled output..
Code:
SQL> CREATE OR REPLACE DIRECTORY tablas_externas AS 'c:\oracle\etl_file';
Directory created.
SQL> CREATE TABLE
2 colas_caup
3 (fecha DATE,
4 cola VARCHAR2(45),
5 ABIERTAS NUMBER,
6 CERRADAS NUMBER,
7 PENDIENTES NUMBER,
8 RECHAZADAS NUMBER,
9 RESUELTAS NUMBER)
10 ORGANIZATION EXTERNAL
11 (TYPE oracle_loader
12 DEFAULT DIRECTORY tablas_externas
13 ACCESS PARAMETERS
14 (RECORDS DELIMITED BY NEWLINE
15 FIELDS TERMINATED BY ';'
16 MISSING FIELD VALUES ARE NULL
17 (fecha CHAR date_format DATE MASK 'YYYYMMDD HH24:MI:SS',
18 cola,
19 ABIERTAS,
20 CERRADAS,
21 PENDIENTES,
22 RECHAZADAS,
23 RESUELTAS))
24 LOCATION('colas.dat'))
25 REJECT LIMIT UNLIMITED;
Table created.
SQL>
SQL> SELECT * FROM colas_caup;
FECHA COLA ABIERTAS CERRADAS PENDIENTES RECHAZADAS RESUELTAS
--------- --------------------------------------------- ---------- ---------- ---------- ---------- ----------
21-AUG-02 Sop Ges Arpa 1 4 1 0 0
21-AUG-02 Sop Ges Arpa Rech 0 3 1 0 0
21-AUG-02 Sop Ges Arpa Res 0 20 3 0 7
21-AUG-02 Sop Ges Arpa Tra 5 0 5 0 0
21-AUG-02 Sop Ges Portabilidad 9 1 9 0 0
5 rows selected.
I will be glad if u reply back after solving the problem
Regards
Sameer
-
hmmm this is gettng my nerves, I just cut and pasted in another server and it works
bummer :-(
-
You probably have problems with 'newline'. It can be CHR(13) or CHR(10)||CHR(13) together. On of the above is giving you problems.
-
I am not sure, the dat is generated by sql*plus spool
-
I think that sqlplus spool adds blanks en every result row until linesize.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|