Home » RDBMS Server » Server Utilities » Loading variable and fixed data (SQL* LOADER)
Loading variable and fixed data [message #291036] Wed, 02 January 2008 16:19 Go to next message
marydvu
Messages: 4
Registered: January 2008
Junior Member

How can i load these records properly if only the first 2 fields are fixed and the rest are variable? I can't use the TERMINATED BY clause anywhere.



Sample Data:

SANDRA----BULLOCK--MISS CONGENIALITY------ACTRESS
WILL------SMITH----I AM LEGEND--ACTOR

Control File:
.
.
.
INTO TABLE CELEBRITY
(
fname position(1:10) char,
lname position(11:30) char,
movie ??
Profession ? ?
)


THanks,
Mary
Re: Loading variable and fixed data [message #291037 is a reply to message #291036] Wed, 02 January 2008 16:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I can't use the TERMINATED BY clause anywhere.
Then how do you know/determine/differentiate/separate the 3rd & 4th fields.

Simply put, the input file should be (re?)designed.
Re: Loading variable and fixed data [message #291038 is a reply to message #291036] Wed, 02 January 2008 16:39 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Can you describe (using your own words) how to extract movie and profession? If so, it can probably be coded in (PL/)SQL. In order to be able to do that, either load the whole file into a temporary table or use an input file as an external table and write that piece of (PL/)SQL code against it.

Sample data you provided suggests that first and last name are fixed, profession is only one word, so - movie title must be the rest which is left in between.

If you, however, don't know how to extract it, Houston, we have a problem.
Re: Loading variable and fixed data [message #291039 is a reply to message #291037] Wed, 02 January 2008 16:42 Go to previous messageGo to next message
marydvu
Messages: 4
Registered: January 2008
Junior Member
Yes that makes sense..
It's ridiculous because changing the input file is not an option for me.
Re: Loading variable and fixed data [message #291040 is a reply to message #291036] Wed, 02 January 2008 16:49 Go to previous messageGo to next message
marydvu
Messages: 4
Registered: January 2008
Junior Member
Hmm, is there away to say :

..movie...TERMINATED BY '--' OR '------' ??
Re: Loading variable and fixed data [message #291081 is a reply to message #291040] Wed, 02 January 2008 23:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No but you can use an external table and then you have the full power of SQL.

Regards
Michel
Re: Loading variable and fixed data [message #291239 is a reply to message #291081] Thu, 03 January 2008 08:59 Go to previous messageGo to next message
marydvu
Messages: 4
Registered: January 2008
Junior Member
Thanks Michel,

that's a really great idea!
Re: Loading variable and fixed data [message #291265 is a reply to message #291038] Thu, 03 January 2008 09:53 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is it?

Littlefoot wrote on Wed, 02 January 2008 23:39

... or use an input file as an external table and write that piece of (PL/)SQL code against it.

Re: Loading variable and fixed data [message #291269 is a reply to message #291265] Thu, 03 January 2008 10:10 Go to previous message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I didn't see it and it seems OP neither.
Maybe sentences are too long. Laughing

Regards
Michel
Previous Topic: SQL* Loader
Next Topic: Rows missing in the tables during import
Goto Forum:
  


Current Time: Tue Dec 24 23:54:37 CST 2024