Home » SQL & PL/SQL » SQL & PL/SQL » external table (oracle 10g )
external table [message #602958] Tue, 10 December 2013 10:05 Go to next message
karthik7narla
Messages: 9
Registered: December 2013
Location: hyderabad
Junior Member
hi every one
this is what the external table i have created


CREATE TABLE external_karthik (
DEALER_CODE number(30),
TARGET_OR_COMM VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY usage_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
DEALER_CODE number(30),
TARGET_OR_COMM VARCHAR2(50)
)
)
LOCATION ('Green_Outlet_MNP.txt')
)

table is created successfully but when i am trying to retrive using select statement

select * from external_karthik
it is throwing the following 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 "identifier": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
KUP-01008: the bad identifier was: number
KUP-01007: at line 5 column 24
ORA-06512: at "S


can any one help me out of this ..

Report message to a moderator

Re: external table [message #602967 is a reply to message #602958] Tue, 10 December 2013 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You must use SQL*Loader types in ACCESS PARAMETERS clause.
In your case, just list the column names, no datatype.

Report message to a moderator

Re: external table [message #602993 is a reply to message #602958] Tue, 10 December 2013 12:22 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
What is the OS?
Did you give proper permissions to the data file in the directory?

[Updated on: Tue, 10 December 2013 23:31]

Report message to a moderator

Re: external table [message #603013 is a reply to message #602993] Wed, 11 December 2013 00:16 Go to previous messageGo to next message
karthik7narla
Messages: 9
Registered: December 2013
Location: hyderabad
Junior Member
Sir my OS is windows 7
even i gave the permissions

$ ls -lrt Green_Outlet_MNP.txt
-rwxrwxrwx 1 icomspsg users 162038 Dec 10 20:40 Green_Outlet_MNP.txt

The same script which i mentioned above is working fine for some other text file .
but i am getting problem with this text file .
is there any fault with format of text file ?

but when i viewed the file in VI editor .
the file is appended with ^M . i dont understand how this ^M is comming .

10031742,75^M
10031747,75^M
10031755,75^M
10031757,75^M

Report message to a moderator

Re: external table [message #603017 is a reply to message #603013] Wed, 11 December 2013 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is coming from the difference in end of line character(s) in both OS.
You have to transfer your file in ASCII/TEXT mode and not BINARY one.
You can also use, on Unix, a program like "dos2unix" to convert your Windows file into a Unix one.

Report message to a moderator

Re: external table [message #603045 is a reply to message #603013] Wed, 11 December 2013 03:17 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
karthik7narla wrote on Wed, 11 December 2013 11:46
but when i viewed the file in VI editor .
the file is appended with ^M . i dont understand how this ^M is comming .
10031742,75^M
10031747,75^M
10031755,75^M
10031757,75^M


This is why I asked you to mention the OS.

In UNIX, you can do type this in VI editor to remove all ^M characters :
:%s/^V^M//g


Different OS will interpret the end-of-line differently. That's the reason you got this ^M from Windows to UNIX. Just type the command manually and get rid of all the ^M characters. You should be able to process the file. Even dos2unix is a good option.

Report message to a moderator

Re: external table [message #603089 is a reply to message #603045] Wed, 11 December 2013 06:38 Go to previous messageGo to next message
karthik7narla
Messages: 9
Registered: December 2013
Location: hyderabad
Junior Member
thank u very much sir .

now its working fine

Report message to a moderator

Re: external table [message #603094 is a reply to message #603013] Wed, 11 December 2013 07:27 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
karthik7narla wrote on Wed, 11 December 2013 00:16
Sir my OS is windows 7
even i gave the permissions

$ ls -lrt Green_Outlet_MNP.txt
-rwxrwxrwx 1 icomspsg users 162038 Dec 10 20:40 Green_Outlet_MNP.txt

The same script which i mentioned above is working fine for some other text file .
but i am getting problem with this text file .
is there any fault with format of text file ?

but when i viewed the file in VI editor .
the file is appended with ^M . i dont understand how this ^M is comming .

10031742,75^M
10031747,75^M
10031755,75^M
10031757,75^M




You say your OS is Windows, but then you proceed to show evidence of *nix.

???

Confused

Report message to a moderator

Previous Topic: shortest way to concatenate multiple messages based on column multiple values.
Next Topic: dbms_lob.append raising error.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue May 07 00:27:12 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.