Home » SQL & PL/SQL » SQL & PL/SQL » External tables (.csv) (Oracle 10g, Windows Server 2003)
External tables (.csv) [message #504568] Tue, 26 April 2011 11:18 Go to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
Hi everybody. I was wondering if someone could help me. I'm trying to load a csv file into an external table and when I select the table 0 rows is the result.

The log file has the following errors:

KUP-04021: field formatting error for field DEPTNO
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /usr/tmp\clie.csv
error processing column EMPNO in row 2 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number

This is the script for the table:

create table emp_ext (
EMPNO    NUMBER(4),
ENAME    VARCHAR2(10),
JOB      VARCHAR2(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2))
Organization external
(type oracle_loader
default directory testdir
access parameters (records delimited by newline
fields terminated by ',')
location ('emp_ext.csv'))
reject limit 1000;



And this is csv:

7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20

Thanks for your help.
Re: External tables (.csv) [message #504571 is a reply to message #504568] Tue, 26 April 2011 11:32 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Your first row is one column short.
Re: External tables (.csv) [message #504572 is a reply to message #504571] Tue, 26 April 2011 11:39 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
but now that I test it, it works for me:
SQL> select * from emp_ext;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20



That was done with an exact copy/paste of your code and data file.
Re: External tables (.csv) [message #504574 is a reply to message #504572] Tue, 26 April 2011 11:49 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
Thank you.

I'll show you the complete log file:



LOG file opened at 04/26/11 12:43:12

Field Definitions for table CLIENTE7
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

EMPNO CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
ENAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
JOB CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
MGR CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
HIREDATE CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
SAL CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COMM CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
DEPTNO CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field DEPTNO
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /usr/tmp\clie.csv
error processing column EMPNO in row 2 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 3 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 4 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 5 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 6 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 7 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 8 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 9 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 10 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 11 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
Re: External tables (.csv) [message #504576 is a reply to message #504574] Tue, 26 April 2011 12:01 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Please can you format your log file properly next time? It would be much easier to read. But it's the wrong logfile anyway, it refers to a table called CLENTE7 and a data file called clie.csv. This is my logfile:
[oracle@sbnf ~]$ cat EMP_EXT_14536.log


 LOG file opened at 04/26/11 16:56:08

Field Definitions for table EMP_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    EMPNO                           CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    ENAME                           CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    JOB                             CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    MGR                             CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    HIREDATE                        CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    SAL                             CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    COMM                            CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader
    DEPTNO                          CHAR (255)
      Terminated by ","
      Trim whitespace same as SQL Loader

Date Cache Statistics for table EMP_EXT
  Max Size:      1000
  Entries :        11
  Hits    :         0
  Misses  :         0

Re: External tables (.csv) [message #504580 is a reply to message #504576] Tue, 26 April 2011 12:09 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
Sorry I used a different names for the table and the .csv file. Those names are correct.

Anyway I created a new csv file with the name emp_ext and a new table, and this happened when I
select * from emp_ext


create or replace directory succeeded.
create table succeeded.

Error starting at line 20 in command:
select *
from emp_ext
Error report:
SQL Error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EMP_EXT_3576_1028.log
 OS error The system cannot find the file specified.
ORA-06512: at "SYS.ORACLE_LOADER", line 19
29913. 00000 -  "error in executing %s callout"
*Cause:    The execution of the specified callout caused an error.
*Action:   Examine the error messages take appropriate action.
Re: External tables (.csv) [message #504585 is a reply to message #504580] Tue, 26 April 2011 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
unable to open log file EMP_EXT_3576_1028.log

Fix that: directory exist and is accessible to Oracle, has t the right to write into it?

Regards
Michel
Re: External tables (.csv) [message #504588 is a reply to message #504585] Tue, 26 April 2011 12:23 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
Yes the directory was created by me. And how do I know if it has the rights to write into it?
Re: External tables (.csv) [message #504589 is a reply to message #504588] Tue, 26 April 2011 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
>Yes the directory was created by me.
does Windows knows/understand who "me" is?
I don't know who "me" is.
Windows know about OS users; not about folks who bang on keyboards or click mouse.
Re: External tables (.csv) [message #504590 is a reply to message #504572] Tue, 26 April 2011 12:28 Go to previous messageGo to next message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
John Watson wrote on Tue, 26 April 2011 12:39
but now that I test it, it works for me:


I ran it in SQL*Plus: Release 10.1.0.4.2, and got 10 of the records loaded with an exact cut and paste. The first record rejects with:
KUP-04021: field formatting error for field DEPTNO
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /vol02/opt/app/data/emp_ext.csv

but all the others loaded. Maybe you have control characters or and bad CR/LF combo in each record.
Re: External tables (.csv) [message #504592 is a reply to message #504589] Tue, 26 April 2011 12:29 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
Sorry, it was created by Administrator.

Is that correct, folk?
Re: External tables (.csv) [message #504596 is a reply to message #504592] Tue, 26 April 2011 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Windows does not understand "/vol02/opt/app/data/emp_ext.csv".
Once again something wrong in your post.
Why don't you post the COMPLETE AND CORRECT information?

Post OS and version.
Post Oracle and version.
Copy and paste the WHOLE SQL*Plus session from your connection, directory creation, table creation up to the error.
Post a "type" or "cat" of your data file.
And so on.

Regards
Michel
Re: External tables (.csv) [message #504620 is a reply to message #504592] Tue, 26 April 2011 15:55 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Sorry, but I can't understand what is going on. Your code worked for me, and it worked for Joy (with one problem - probably to do with copy/paste.) But your posts aren't consistent. The title says Windows Server 2003, and you mention the directory as being owned by Administrator (which sounds Windows-ish) but your log file refers to /usr/tmp (which sounds Unix-ish.) I think you need to start again, with a consistent and complete description of the problem.
Re: External tables (.csv) [message #504701 is a reply to message #504620] Wed, 27 April 2011 07:33 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
I'm using Windows Server 2003 and I created the directory /usr/tmp following this example www.orafaq.com/node/1902.

So basically, what I'm trying to do is that example.

I'm sorry if I'm not describing my problem completely, my english is quite limited. That's why. Hope you can understand.



CM: wrapped web address in [url] tags.

[Updated on: Wed, 27 April 2011 07:37] by Moderator

Report message to a moderator

Re: External tables (.csv) [message #504702 is a reply to message #504701] Wed, 27 April 2011 07:35 Go to previous messageGo to next message
ThomasG
Messages: 3205
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Rodrignra wrote on Wed, 27 April 2011 14:33
I'm using Windows Server 2003 and I created the directory /usr/tmp


That is impossible. You can't have a /usr/tmp directory in Windows.
Re: External tables (.csv) [message #504703 is a reply to message #504701] Wed, 27 April 2011 07:37 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's a unix example.
Unix directories have forward slashs '/' in the path.
Windows directories have back slashs '\' in the path.
Re: External tables (.csv) [message #504706 is a reply to message #504703] Wed, 27 April 2011 07:49 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
I'm confused. So that means I can't use that example on windows?
Re: External tables (.csv) [message #504707 is a reply to message #504706] Wed, 27 April 2011 07:50 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you change the directory to one windows will actually recognize it'll work fine.
Re: External tables (.csv) [message #504708 is a reply to message #504707] Wed, 27 April 2011 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use it on Windows, Linux or any OS supported by Oracle, just give the correct name and path for this OS.

Regards
Michel
Re: External tables (.csv) [message #504710 is a reply to message #504708] Wed, 27 April 2011 07:56 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
The correct path could be 'C:\example'?
Re: External tables (.csv) [message #504711 is a reply to message #504710] Wed, 27 April 2011 07:57 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
yes
Re: External tables (.csv) [message #504713 is a reply to message #504710] Wed, 27 April 2011 08:06 Go to previous messageGo to next message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
Rodrignra wrote on Wed, 27 April 2011 08:56
The correct path could be 'C:\example'?


I'm not so sure. Your "faked" code with false filenames, etc. is only hurting you and making it longer for you to get a correct answer. Your csv file needs to reside on the server, not your local PC. It seems the oracle DIRECTORY that was created is defined as /usr/tmp but who knows since you already faked the filename.

I am guessing that you have a unix database server and a local windows PC. You do not put the file on your windows PC. You put it on the database server in /usr/tmp or whatever "testdir" is defined as.
Re: External tables (.csv) [message #504715 is a reply to message #504713] Wed, 27 April 2011 08:10 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
OP does specifically say windows server, but we'll see.
Re: External tables (.csv) [message #504716 is a reply to message #504711] Wed, 27 April 2011 08:11 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
I created the tables...

CREATE OR REPLACE DIRECTORY DAT_DIR AS 'C:\example';

CREATE TABLE SAMPLE_EXT 
( 
ID NUMBER, 
BEGIN_DATE VARCHAR2(10),   
FIRST_NAME VARCHAR2(30), 
LAST_NAME VARCHAR2(30), 
STATUS VARCHAR2(10) 
)                       
 ORGANIZATION EXTERNAL
 (
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY DAT_DIR
   ACCESS PARAMETERS
   (
            records delimited by newline LOAD WHEN (ID != ID)
            badfile BAD_DIR:'SAMPLE_EXT%a_%p.bad'
            logfile LOG_DIR:'SAMPLE_EXT%a_%p.log'
            fields terminated by ',' optionally enclosed by '"' LRTRIM
            MISSING FIELD VALUES ARE NULL 
   )
   LOCATION ('sample.csv')
 )
 PARALLEL 4
 REJECT LIMIT 1000;

CREATE TABLE SAMPLE_TAB 
( 
ID NUMBER, 
BEGIN_DATE VARCHAR2(10),   
FIRST_NAME VARCHAR2(30), 
LAST_NAME VARCHAR2(30), 
STATUS VARCHAR2(10) 
);


...And then the function

CREATE OR REPLACE FUNCTION SAMPLE_ETL_FNC (P_FILE_PATH VARCHAR2 ) RETURN NUMBER AS
    l_filename varchar2(100);
    l_rowcount number;
    l_sqlerrm varchar(2000);
    l_filesize number;
   BEGIN
   
   DBMS_OUTPUT.PUT_LINE(to_char(sysdate, 'YYYY-MON-DD HH:MI:SS') || ' - BEGIN ETL PROCEDURE - Path: ' || p_file_path);
   
   --Flexible to allow for file name or file path
    if instr(p_file_path, '/') > 0 then
    select substr(p_file_path, instr(p_file_path,'/', -1)+1, 
    length(p_file_path)-instr(p_file_path,'/', -1))
    INTO l_filename
    from dual;
   else
    l_filename := p_file_path;
   end if;
   
   --Get file size if required
   --l_filesize := flength ('DAT_DIR',l_filename);
   DBMS_OUTPUT.PUT_LINE('Filename: ' || l_filename);
   
   BEGIN
    --Alter the Log and Bad file names for debugging
    --Can modify the bad and log file names if required
    DBMS_OUTPUT.PUT_LINE('ALTER EXTERNAL TABLE DATA SOURCE');
     execute immediate 'alter table SAMPLE_EXT location('''||l_filename||''')';
    --Prepare Insert SQL
    DBMS_OUTPUT.PUT_LINE('BEGIN INSERT FROM EXTERNAL TO DATA TABLE');
    execute immediate 'Insert into sample_tab select * from sample_ext A';
     l_rowcount := SQL%rowcount;
    DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM') || ': INSERT SUCCESSFUL... IN SERTED ' || l_rowcount || ' ROWS'); 
     COMMIT;
     
     RETURN 0;
    exception      
     when others then
        DBMS_OUTPUT.PUT_LINE('INSERT FAILED');
        l_sqlerrm:=sqlerrm;
        DBMS_OUTPUT.PUT_LINE(l_sqlerrm);
       ROLLBACK; 
      commit;
       RETURN 1;
    END; 
    
   exception      
     when others then
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
       RETURN 1;
   END;


And finally:

select * from sample_tab


set serveroutput on
declare
    l_return number;
    begin
    l_return := SAMPLE_ETL_FNC('july19.csv');
    dbms_output.put_line('RETURN STATUS: ' || l_return);
    end;


These are the scripts output:

CREATE OR REPLACE DIRECTORY succeeded.
CREATE TABLE succeeded.
CREATE TABLE succeeded.
FUNCTION SAMPLE_ETL_FNC Compiled.

ID                     BEGIN_DATE FIRST_NAME                     LAST_NAME                      STATUS     
---------------------- ---------- ------------------------------ ------------------------------ ---------- 

0 rows selected

2011-APR-27 10:03:34 - BEGIN ETL PROCEDURE - Path: july19.csv
Filename: july19.csv
ALTER EXTERNAL TABLE DATA SOURCE
BEGIN INSERT FROM EXTERNAL TO DATA TABLE
INSERT FAILED
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object LOG_DIR not found
RETURN STATUS: 1


What am I doing wrong?

PS. I created the folder too in local disk c.

[Updated on: Wed, 27 April 2011 08:16]

Report message to a moderator

Re: External tables (.csv) [message #504718 is a reply to message #504716] Wed, 27 April 2011 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What I am doing wrong?

You didn't use SQL*Plus and copy and paste your session.

Regards
Michel
Re: External tables (.csv) [message #504719 is a reply to message #504718] Wed, 27 April 2011 08:19 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
I can't use SQL*Plus because when I tried to log on...

ERROR:
ORA-12560: TNS: protocol adapter error

So I'm using the developer, what's the problem with using it? I'm asking because I don't know anything about Oracle.

[Updated on: Wed, 27 April 2011 08:19]

Report message to a moderator

Re: External tables (.csv) [message #504720 is a reply to message #504716] Wed, 27 April 2011 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
KUP-04080: directory object LOG_DIR not found

You need to create an oracle directory called log_dir:
  (
            records delimited by newline LOAD WHEN (ID != ID)
            badfile BAD_DIR:'SAMPLE_EXT%a_%p.bad'
            logfile LOG_DIR:'SAMPLE_EXT%a_%p.log'
            fields terminated by ',' optionally enclosed by '"' LRTRIM
            MISSING FIELD VALUES ARE NULL 
   )

And obviously you'll need one called bad_dir as well. Vjain appears to have forgotten to mention that.
Re: External tables (.csv) [message #504721 is a reply to message #504720] Wed, 27 April 2011 08:22 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
BAD_DIR and LOG_DIR has the exact same path as DAT_DIR?
Re: External tables (.csv) [message #504723 is a reply to message #504721] Wed, 27 April 2011 08:26 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
It worked! But, inserted 0 rows...

create or replace directory BAD_DIR as 'C:\example';
create or replace directory LOG_DIR AS 'C:\example';

declare
   l_return number;
    begin
    l_return := SAMPLE_ETL_FNC('july19.csv');
    dbms_output.put_line('RETURN STATUS: ' || l_return);
   end;


create or replace directory succeeded.
create or replace directory succeeded.
2011-APR-27 10:15:49 - BEGIN ETL PROCEDURE - Path: july19.csv
Filename: july19.csv
ALTER EXTERNAL TABLE DATA SOURCE
BEGIN INSERT FROM EXTERNAL TO DATA TABLE
27-APR-2011 10:15:54 AM: INSERT SUCCESSFUL... IN SERTED 0 ROWS
RETURN STATUS: 0


Re: External tables (.csv) [message #504724 is a reply to message #504715] Wed, 27 April 2011 08:26 Go to previous messageGo to next message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
cookiemonster wrote on Wed, 27 April 2011 09:10
OP does specifically say windows server, but we'll see.


Yes, but for the database or the client?
I just find it too hard to try to decipher what is really happening unless like Michel says, we see the exact input and output in a full SQL*Plus session.
And faking output doesn't help either with what John pointed out with the table names.
Re: External tables (.csv) [message #504732 is a reply to message #504724] Wed, 27 April 2011 08:48 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
These are the log and bad files. Apparently there's a problem with the column ID.

SAMPLE_EXT_3576_1028.log

 LOG file opened at 04/27/11 10:15:49

Field Definitions for table SAMPLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted
  Load when (ID != ID)

  Fields in Data Source: 

    ID                              CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    BEGIN_DATE                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    FIRST_NAME                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    LAST_NAME                       CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    STATUS                          CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right


 LOG file opened at 04/27/11 10:16:11

Field Definitions for table SAMPLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted
  Load when (ID != ID)

  Fields in Data Source: 

    ID                              CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    BEGIN_DATE                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    FIRST_NAME                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    LAST_NAME                       CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    STATUS                          CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right


SAMPLE_EXT000_3576_4548.log

 LOG file opened at 04/27/11 10:15:53

Field Definitions for table SAMPLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted
  Load when (ID != ID)

  Fields in Data Source: 

    ID                              CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    BEGIN_DATE                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    FIRST_NAME                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    LAST_NAME                       CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    STATUS                          CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
error processing column ID in row 1 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 2 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 3 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 4 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 5 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 6 for datafile C:\example\july19.csv
ORA-01722: invalid number


 LOG file opened at 04/27/11 10:16:11

Field Definitions for table SAMPLE_EXT
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted
  Load when (ID != ID)

  Fields in Data Source: 

    ID                              CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    BEGIN_DATE                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    FIRST_NAME                      CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    LAST_NAME                       CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
    STATUS                          CHAR (255)
      Terminated by ","
      Enclosed by """ and """
      Trim whitespace from left and right
error processing column ID in row 1 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 2 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 3 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 4 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 5 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 6 for datafile C:\example\july19.csv
ORA-01722: invalid number


SAMPLE_EXT000_3576_4584.bad
"ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS"
"1, 19-JUL-07, John, Adams, Active"
"2, 19-JUL-07, Tyler, Howell, Active"
"3, 19-JUL-07, Jim, Lopez, Active"
"4, 19-JUL-07, Carlos, White, Inactive"
"5, 19-JUL-07, Scott, Tiger, Active"
Re: External tables (.csv) [message #504738 is a reply to message #504732] Wed, 27 April 2011 09:12 Go to previous messageGo to next message
ThomasG
Messages: 3205
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Are then (") signs in the *.csv file, too? That would be wrong. It should either be (for example the first like)

1, 19-JUL-07, John, Adams, Active


or
"1", "19-JUL-07", "John", "Adams", "Active"
Re: External tables (.csv) [message #504740 is a reply to message #504568] Wed, 27 April 2011 09:32 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
When I opened the .csv in Excel it looked like this:

ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS
1, 19-JUL-07, John, Adams, Active
2, 19-JUL-07, Tyler, Howell, Active
3, 19-JUL-07, Jim, Lopez, Active
4, 19-JUL-07, Carlos, White, Inactive
5, 19-JUL-07, Scott, Tiger, Active


If I open the .csv with the notepad:

"ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS"
"1, 19-JUL-07, John, Adams, Active"
"2, 19-JUL-07, Tyler, Howell, Active"
"3, 19-JUL-07, Jim, Lopez, Active"
"4, 19-JUL-07, Carlos, White, Inactive"
"5, 19-JUL-07, Scott, Tiger, Active"
Re: External tables (.csv) [message #504741 is a reply to message #504740] Wed, 27 April 2011 09:35 Go to previous messageGo to next message
ThomasG
Messages: 3205
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Notepad is better than Excel when displaying how a file really looks. Excel seems to have saved the entire row as one column.

Remove the " in notepad and save the file again.
Re: External tables (.csv) [message #504743 is a reply to message #504740] Wed, 27 April 2011 09:37 Go to previous messageGo to next message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
Rodrignra wrote on Wed, 27 April 2011 10:32
When I opened the .csv in Excel it looked like this:

ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS
1, 19-JUL-07, John, Adams, Active
2, 19-JUL-07, Tyler, Howell, Active
3, 19-JUL-07, Jim, Lopez, Active
4, 19-JUL-07, Carlos, White, Inactive
5, 19-JUL-07, Scott, Tiger, Active


If I open the .csv with the notepad:

"ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS"
"1, 19-JUL-07, John, Adams, Active"
"2, 19-JUL-07, Tyler, Howell, Active"
"3, 19-JUL-07, Jim, Lopez, Active"
"4, 19-JUL-07, Carlos, White, Inactive"
"5, 19-JUL-07, Scott, Tiger, Active"


Jeez, why don't you explain more clearly and fully? Are all of those values in one cell? If yes, then that is why the entries string is surrounded by quotes as a csv file will put quotes around and value that has a comma in it.
You said "it looks like this in Excel" but excel is a spreadsheet, and you did not post a screen shot of a spreadsheet, you only posted a text representation of who knows what (eg. one cell, 2 cells, 3 cells, etc.)
Re: External tables (.csv) [message #504745 is a reply to message #504743] Wed, 27 April 2011 09:42 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
Thank you Thomas, it worked!

Now, what I am doing wrong in excel? Why is it saving with (")?
Re: External tables (.csv) [message #504746 is a reply to message #504745] Wed, 27 April 2011 09:52 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
Presumably you have each line in one cell as already stated.
Re: External tables (.csv) [message #504747 is a reply to message #504746] Wed, 27 April 2011 10:03 Go to previous messageGo to next message
Rodrignra
Messages: 63
Registered: April 2011
Member
cookiemonster wrote on Wed, 27 April 2011 11:52
Presumably you have each line in one cell as already stated.


This was the problem, omg I'm so ashamed right now.

Thanks everybody for the helping hand!
Re: External tables (.csv) [message #658081 is a reply to message #504568] Fri, 02 December 2016 12:46 Go to previous messageGo to next message
rizuane
Messages: 10
Registered: December 2016
Junior Member
hi people,

i need some help in this same example from this article: http://www.orafaq.com/node/1902

im using oracle 10g in localhost.
i Successful create and fill the sample_tab from sample_ext.

My problem is when i want to insert second time in the sample_tab from sample_ext knowing that my datafile in my director i aded more rows.

when run this code in sql developer:

set serveroutput on;    
declare l_return number; 
begin l_return := SAMPLE_ETL_FNC('testt.txt'); 
dbms_output.put_line('RETURN STATUS: ' || l_return); 
end;

i received this error:

2016-DEZ-02 06:57:01 - BEGIN ETL PROCEDURE - Path
: testt.txt
Filename: testt.txt
ALTER EXTERNAL TABLE DATA SOURCE
BEGIN INSERT FROM EXTERNAL TO DATA TABLE
INSERT FAILED
ORA-00001: unique constraint (SYSTEM.PK_WS_T) violated
RETURN STATUS: 1

this condition in external table was Suppose to permite add only new rows (merge):

records delimited by newline 
LOAD WHEN (ID != ID)

why is not working ? please can someone can help me ??

Thanks in advance
regards
Re: External tables (.csv) [message #658082 is a reply to message #658081] Fri, 02 December 2016 13:11 Go to previous messageGo to previous message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

ora-00001 occurs when UNIQUE constraint exists & you try to INSERT row that has the same key value as an existing row.
Previous Topic: Correct order of data in Hierarchical Query
Next Topic: Outer Joins and To_Char
Goto Forum:
  


Current Time: Sat Jan 20 20:02:11 CST 2018

Total time taken to generate the page: 0.20738 seconds