Pauline,
The things are working fine with your SQL Loader , True because you
don't have a External table at that time and you are straight dumping
the data from your Feed into The required table which you say is similar
to the Source table. Why don't you go this way, Create the External
table with the same definition as your Source and destination.
The definition of the External table can be something like this.
Create table pauline.xternal_tester
(use the columns, data_types and their length as they are in your
Source table and dest tables)
organization external
( Type Oracle_loader
default directory xternal_data_dir
access parameters
( records delimited by newline
fields terminated by ','
)
location ('tester.csv')
)
reject limit unlimited;
NO Harm giving a try , isnt it?
Regards,
Viswa
-----Original Message-----
From: pauline
[mailto:oracledba-ezmlmshield-x1964267.[Email address protected]
Sent: Tuesday, January 08, 2008 4:28 PM
To: LazyDBA Discussion
Subject: RE: export schema
Thank you Viswa, Amit
I have created the external table, now the problem comes when I have to
select data from it. I get an ORA-01401.
It looks like the datatypes are different from those I specified on the
external table. DO you have any idea why this could be the case?
This is the structure of my external table:
create table pauline.xternal_tester
( JAN VARCHAR2(4 BYTE),
FEB VARCHAR2(10 BYTE),
MAR VARCHAR2(8 BYTE)
)
organization external
( default directory xternal_data_dir
access parameters
( records delimited by newline
fields terminated by ','
)
location ('tester.csv')
)
reject limit unlimited;
This is the output of the LOG:
Field Definitions for table XTERNAL_TESTER
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:
JAN CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
FEB CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
MAR CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader .
.
error processing column JAN in row 1 for datafile c:\temp\tester.csv
ORA-01401: inserted value too large for column
oracledba-ezmlmsh
ield-x80803713.x1
[Email Address Removed]
.com [Email address protected]
cc
2008/01/08 11:36
AM
Subject
RE: export schema
Hi Pauline,
Viswa is right. If you need more help, plz have a look at the below
link. i think it will help you a lot.
http://blog.econtentpark.com/2006/08/07/Import-of-a-Excel-or-any-other-f
ile-into-Oracle/
Regards,
Amit
-----Original Message-----
From: VISWANADHA RUDRARAJU
[mailto:oracledba-ezmlmshield-x9884046.[Email address protected]
Sent: Tuesday, January 08, 2008 6:00 PM
To: LazyDBA Discussion
Subject: RE: export schema
Pauline,
You can use external Tables to read the data from the Falt files after
they are FTpd to the required directory. Design a procedure that reads
the data from the external table and loads the data into required
tables similat 'insert into select from' and your validations. You can
schedule the execution of SP based on what time you have your feed
coming into.
Regards,
Viswa
-----Original Message-----
From: pauline
[mailto:oracledba-ezmlmshield-x98117779.[Email address protected]
Sent: Tuesday, January 08, 2008 1:58 PM
To: LazyDBA Discussion
Subject: Re: export schema
Hi All,
Please assist if you can, or point me to the right direction. Any
documents will be appreciated. We are currently running Oracle 9i on a
Windows server 2003. I need to upload data from an excel spreadsheet (a
flat file that is being ftp'd onto the server from a remote SQL server
database) onto the oracle db table. A process has been put in place and
is working fine. I do get all the data into my database (oracle). Now
the challenge is automating the process such that once the FTP is
complete, our process is triggered and the upload done.
Your assistance will be appreciated as I have run out of ideas.
Regards,
Pauline (A junior dba)
If you are not the addressee, please inform us immediately that you have
received this e-mail by mistake, and delete it. We thank you for your
support.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
========================================================================
====================================================
Disclaimer:
This message and the information contained herein is proprietary and
confidential and subject to the Tech Mahindra policy statement, you may
review the policy at <a
href="http://www.techmahindra.com/Disclaimer.html">http://www.techmahind
ra.com/Disclaimer.html</a> externally and <a
href="http://tim.techmahindra.com/Disclaimer.html">http://tim.techmahind
ra.com/Disclaimer.html</a> internally within Tech Mahindra.
========================================================================
====================================================
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
This e-mail is confidential and may be legally privileged. If you are
not the intended recipient, please notify the sender immediately and
delete this e-mail.
????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
If you are not the addressee, please inform us immediately that you have
received this e-mail by mistake, and delete it. We thank you for your
support.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
============================================================================================================================
Disclaimer:
This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at <a href="http://www.techmahindra.com/Disclaimer.html">http://www.techmahindra.com/Disclaimer.html</a> externally and <a href="http://tim.techmahindra.com/Disclaimer.html">http://tim.techmahindra.com/Disclaimer.html</a> internally within Tech Mahindra.
============================================================================================================================
Oracle LazyDBA home page