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  |
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.  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  |
 |
Barbara Boehmer
Messages: 9101
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>
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 14:03:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
