Home » RDBMS Server » Server Utilities » Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 (split from hijacked thread by bb)
Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 (split from hijacked thread by bb) [message #452056] Mon, 19 April 2010 14:16 Go to next message
burasami
Messages: 20
Registered: April 2010
Junior Member
Hi Guys,
i have created the external table as follows



CREATE TABLE "COMPIERE"."EXT_EMPLOYEE" 
   (	"EMPLOYEE_ID" CHAR(30 BYTE), 
	"FIRST_NAME" CHAR(300 BYTE), 
	"LAST_NAME" CHAR(300 BYTE), 
	"SALARY" CHAR(300 BYTE)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "Ext_folder"
      ACCESS PARAMETERS
      ( records delimited by newline
badfile "Ext_folder":'censo.bad'
logfile "Ext_folder":'censo.log'
fields delimited by ',' OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM
  (EMPLOYEE_ID CHAR(30) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM NULLIF EMPLOYEE_ID=BLANKS,
   FIRST_NAME CHAR(300) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM NULLIF FIRST_NAME=BLANKS,
   LAST_NAME CHAR(300) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM NULLIF LAST_NAME=BLANKS,
   SALARY CHAR(300) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM NULLIF SALARY=BLANKS
   
  )
    )
      LOCATION
       ( "Ext_folder":'Employee_Data.csv'
       )
    )
   REJECT LIMIT UNLIMITED;




after table has been created successfully . i have ran the following code.
column EMPLOYEE_ID format A15
column FIRST_NAME format A15
column LAST_NAME format A15
column SALARY format A15


after running all above code
while i trying to execute the following code.
select * from ext_employee;

i am getting error as below. please help me out to fix this problem. i don't know where i have missed some thing. Sad thanks in advance.

Employee_Data file contains following data.

'1004','Sami','Tamil','2000'
'1001','Muthu','Raj','10000'
'1002','Suresh','Vijay','2000'
'1003','Ramesh','Vijay','3000'

Error starting at line 1 in command:
select * from ext_employee
Error report:
SQL Error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "delimited": expecting one of: "column, enclosed, exit, (, ltrim, lrtrim, ldrtrim, missing, notrim, optionally, rtrim, reject, terminated"
KUP-01007: at line 4 column 8
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: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 (split from hijacked thread by bb) [message #452071 is a reply to message #452056] Mon, 19 April 2010 22:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
You need to use TERMINATED not DELIMITED.

Your fields are enclosed in single quotes, not double quotes, so you need to use "'" not '"'.

Although you can create a table with CHAR, it is generally better to use VARCHAR2. Your column formats will not work with CHAR.

If your directory name is created within double quotes, like "Ext_folder", then that is fine. Otherwise it needs to be referenced in upper case like EXT_FOLDER.

Your directory and file must be on your server, not your client.

Please see the corrected code below, in which I have used a different schema.

-- c:\oracle11g\employee_data.csv:
'1004','Sami','Tamil','2000'
'1001','Muthu','Raj','10000'
'1002','Suresh','Vijay','2000'
'1003','Ramesh','Vijay','3000'


-- directory, table and select:
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY "Ext_folder" AS 'C:\oracle11g'
  2  /

Directory created.

SCOTT@orcl_11g> CREATE TABLE "SCOTT"."EXT_EMPLOYEE"
  2  	(    "EMPLOYEE_ID" VARCHAR2( 30 BYTE),
  3  	     "FIRST_NAME"  VARCHAR2(300 BYTE),
  4  	     "LAST_NAME"   VARCHAR2(300 BYTE),
  5  	     "SALARY"	   VARCHAR2(300 BYTE)
  6  	)
  7  	ORGANIZATION EXTERNAL
  8  	 ( TYPE ORACLE_LOADER
  9  	   DEFAULT DIRECTORY "Ext_folder"
 10  	   ACCESS PARAMETERS
 11  	   ( records delimited by newline
 12  	     badfile "Ext_folder":'censo.bad'
 13  	     logfile "Ext_folder":'censo.log'
 14  	     fields TERMINATED by ',' OPTIONALLY ENCLOSED BY "'" and "'" LDRTRIM
 15  	       ( EMPLOYEE_ID CHAR( 30) TERMINATED BY ","
 16  				       OPTIONALLY ENCLOSED BY "'" and "'"
 17  				       LDRTRIM NULLIF EMPLOYEE_ID=BLANKS,
 18  		 FIRST_NAME  CHAR(300) TERMINATED BY ","
 19  				       OPTIONALLY ENCLOSED BY "'" and "'" LDRTRIM
 20  				       NULLIF FIRST_NAME=BLANKS,
 21  		 LAST_NAME   CHAR(300) TERMINATED BY ","
 22  				       OPTIONALLY ENCLOSED BY "'" and "'" LDRTRIM
 23  				       NULLIF LAST_NAME=BLANKS,
 24  		 SALARY      CHAR(300) TERMINATED BY ","
 25  				       OPTIONALLY ENCLOSED BY "'" and "'" LDRTRIM
 26  				       NULLIF SALARY=BLANKS
 27  	       )
 28  	   )
 29  	   LOCATION ( "Ext_folder":'Employee_Data.csv' )
 30  	 )
 31  	REJECT LIMIT UNLIMITED;

Table created.

SCOTT@orcl_11g> column EMPLOYEE_ID format A15
SCOTT@orcl_11g> column FIRST_NAME  format A15
SCOTT@orcl_11g> column LAST_NAME   format A15
SCOTT@orcl_11g> column SALARY	   format A15
SCOTT@orcl_11g> select * from ext_employee;

EMPLOYEE_ID     FIRST_NAME      LAST_NAME       SALARY
--------------- --------------- --------------- ---------------
1004            Sami            Tamil           2000
1001            Muthu           Raj             10000
1002            Suresh          Vijay           2000
1003            Ramesh          Vijay           3000

SCOTT@orcl_11g> 


Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 (split from hijacked thread by bb) [message #452242 is a reply to message #452071] Tue, 20 April 2010 21:36 Go to previous message
burasami
Messages: 20
Registered: April 2010
Junior Member
Hi,
Thanks for your valuable time and work.
its working fine now.
Previous Topic: DB Import with same schema but duplicate keys
Next Topic: Import Performance using sql *loader
Goto Forum:
  


Current Time: Mon Jan 25 02:11:17 CST 2021