Home » SQL & PL/SQL » SQL & PL/SQL » SQLLDR - How to import and decode my fields
SQLLDR - How to import and decode my fields [message #182597] Mon, 17 July 2006 04:12 Go to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

Hi!
I'm italian, sorry for my english Smile

I need to load various records in a table.

This is my table:

Quote:


PROVA
-------
login varchar2(50)
status varchar2(50)



and this is my example input file for SQLLDR:

Quote:


UAB|pippo2|sad|251392|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;
UAB|pippo3|sad|693696|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;
UAB|pippo4|adsds|406397|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;
UAB|pippo5|asdd|1147129|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;
UAB|pippo6|tilaaslon|186751|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;
UAB|pippo7|sadaa|921966|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;



I need to do this:

1. Import bold data in login and status fields
2. The second bold data must be decoded in this way: 'Active' = 'ATTIVO'

Can anyone help me to build my control file ? Sad

Thanks

[Updated on: Mon, 17 July 2006 04:15]

Report message to a moderator

Re: SQLLDR - How to import and decode my fields [message #182618 is a reply to message #182597] Mon, 17 July 2006 06:44 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Instead of using SQL*Loader, you might do that using external tables:
M:\>sqlplus SYSTEM/PASSWORD@ora10

CREATE OR REPLACE DIRECTORY ext_dir AS 'c:\temp';

CONNECT sys/PASSWORD@ora10 AS sysdba

GRANT READ, WRITE ON DIRECTORY ext_dir TO scott;

CONNECT scott/tiger@ora10

CREATE TABLE prova (login VARCHAR2(50), status VARCHAR2(50));

CREATE TABLE EXT_TABLE_CSV 
(  col1   VARCHAR2(50),
   login  VARCHAR2(50),
   col3   VARCHAR2(50),
   col4   NUMBER,
   col5   VARCHAR2(50),
   col6   NUMBER,
   col7   NUMBER,
   col8   VARCHAR2(1), 
   col9   VARCHAR2(50),
   col10  NUMBER,
   status VARCHAR2(50),
   col12  VARCHAR2(50),
   col13  NUMBER,
   col14  VARCHAR2(50)
)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS 
  ( RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY '|'
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('test.txt')
)
REJECT LIMIT UNLIMITED
/

INSERT INTO prova 
( login, status )
( SELECT login, DECODE(status, 'Active', 'ATTIVO', status)  
  FROM ext_table_csv
);

Re: SQLLDR - How to import and decode my fields [message #182637 is a reply to message #182597] Mon, 17 July 2006 07:51 Go to previous messageGo to next message
dsemen
Messages: 8
Registered: July 2006
Junior Member
You can put DECODE in the SQL*Loader control file. Here is the example:

LOAD DATA
INFILE file.dat
...
(
...
...
, status varchar2(50) "DECODE(:status, 'Active', 'ATTIVO', status)"
...
)

[Updated on: Mon, 17 July 2006 07:52]

Report message to a moderator

Re: SQLLDR - How to import and decode my fields [message #182641 is a reply to message #182637] Mon, 17 July 2006 07:57 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

dsemen wrote on Mon, 17 July 2006 14:51

You can put DECODE in the SQL*Loader control file. Here is the example:

LOAD DATA
INFILE file.dat
...
(
...
...
, status varchar2(50) "DECODE(:status, 'Active', 'ATTIVO', status)"
...
)



Ok...thanks very much.
but can I import just the bold data of the input file? ( see my firts post)

[Updated on: Mon, 17 July 2006 07:57]

Report message to a moderator

Re: SQLLDR - How to import and decode my fields [message #182644 is a reply to message #182597] Mon, 17 July 2006 08:03 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

I think below code should work fine...
load data
          infile 'c:\mydata.csv'
		  into table PROVA
          fields terminated by "|"  
          (  field1 FILLER,
	     login ,
	     field3 FILLER,
	     field4 FILLER,
	     field5 FILLER,
	     field6 FILLER,
	     field7 FILLER,
	     field8 FILLER,
	     field9 FILLER,
	     status 	"decode(:status,'Active', 'ATTIVO', :status)",
	     field11 FILLER,
	     field12 FILLER,
	     field13 FILLER
          )



Naveen
Re: SQLLDR - How to import and decode my fields [message #182652 is a reply to message #182597] Mon, 17 July 2006 08:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

To be more precise ,

LOAD DATA 
INFILE 'dat1.dat'  "str ';'"
INTO TABLE  PROVA
Fields terminated by '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(field1 FILLER,
 login CHAR ,
field3 FILLER,
field4 FILLER,
field5 FILLER,
field6 FILLER,
field7 FILLER,
field8 FILLER,
field9 FILLER,
field10 FILLER,
status CHAR "DECODE(:status,'Active','ATTIVO')" ,
field11 FILLER,
field12 FILLER,
field13 FILLER )


Thumbs Up
Rajuvan.

[Updated on: Mon, 17 July 2006 08:19]

Report message to a moderator

Re: SQLLDR - How to import and decode my fields [message #182653 is a reply to message #182644] Mon, 17 July 2006 08:24 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

navkrish wrote on Mon, 17 July 2006 15:03

I think below code should work fine...
load data
          infile 'c:\mydata.csv'
		  into table PROVA
          fields terminated by "|"  
          (  field1 FILLER,
	     login ,
	     field3 FILLER,
	     field4 FILLER,
	     field5 FILLER,
	     field6 FILLER,
	     field7 FILLER,
	     field8 FILLER,
	     field9 FILLER,
	     status 	"decode(:status,'Active', 'ATTIVO', :status)",
	     field11 FILLER,
	     field12 FILLER,
	     field13 FILLER
          )



Naveen



Thanks...it works fine Wink
Re: SQLLDR - How to import and decode my fields [message #182655 is a reply to message #182653] Mon, 17 July 2006 08:26 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

Just another little question...

If I have...

Quote:

UAB|pippo2|sad|251392|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;
UAB|pippo3|sad|693696|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;
UAB|pippo4|adsds|406397|Gold|20040229000000|20060125005228|N||0|Closed|Active|1|UAB;
UAB|pippo5|asdd|1147129|Gold|20040229000000|20060125005228|N||0|Closed|Active|1|UAB;
UAB|pippo6|tilaaslon|186751|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;
UAB|pippo7|sadaa|921966|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;



And I want import only rows with string "Closed"...there's a way to do this ?

Thanks Smile
Re: SQLLDR - How to import and decode my fields [message #182657 is a reply to message #182597] Mon, 17 July 2006 08:35 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

yes it is possible...

you have to enforce a WHEN condition after
fields terminated by "|" 


WHEN (65:70) = 'Closed'


But this will work only if the string 'Closed' appears at position 65.

May be there could be some other way ...

Naveen
Re: SQLLDR - How to import and decode my fields [message #182660 is a reply to message #182657] Mon, 17 July 2006 08:39 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

I've read this solution, but I can't use this method because 'Closed' string not appear always in that position.

Alternatively, I can insert all records and after delete all records where in that fields there is not 'Closed' string. Smile
Re: SQLLDR - How to import and decode my fields [message #182718 is a reply to message #182660] Mon, 17 July 2006 15:13 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
According to the documentation, there is a way to do it: don't use column position, but its name:

WHEN (status = 'Closed')
Re: SQLLDR - How to import and decode my fields [message #182744 is a reply to message #182597] Mon, 17 July 2006 23:56 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes , Littlefoot is correct .

There is an option with WHEN CLAUSE ..

data file ..

UAB|pippo2|sad|251392|Gold|20040229000000|20060125005228|N||0|Closed|Active|1|UAB;
UAB|pippo3|sad|693696|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;
UAB|pippo4|adsds|406397|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;
UAB|pippo5|asdd|1147129|Gold|20040229000000|20060125005228|N||0|Closed|Active|1|UAB;
UAB|pippo6|tilaaslon|186751|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;
UAB|pippo7|sadaa|921966|Gold|20040229000000|20060125005228|N||0|Active|Active|1|UAB;


Control file ...

LOAD DATA 
INFILE 'dat1.dat'  "str ';'"
INTO TABLE  PROVA
WHEN status = 'Closed'
Fields terminated by '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(field1 FILLER,
 login CHAR ,
field3 FILLER,
field4 FILLER,
field5 FILLER,
field6 FILLER,
field7 FILLER,
field8 FILLER,
field9 FILLER,
field10 FILLER,
status CHAR "DECODE(:status,'Active','ATTIVO',:status)" ,
field11 FILLER,
field12 FILLER,
field13 FILLER )


Prova table stucture :

CREATE TABLE PROVA
(
  LOGIN   VARCHAR2(50),
  STATUS  VARCHAR2(50)                          
)


How it works ..

SQL> column login format a10
SQL> column status format a10
SQL> select* from prova;

no rows selected

SQL> host sqlldr scott/tiger@cdmabill data=dat1.dat control=ctl1.ctl bad=bad1.ba
d

SQL*Loader: Release 9.0.1.1.1 - Production on Tue Jul 18 10:25:14 2006

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 6

SQL> select* from prova;

LOGIN      STATUS
---------- ----------
pippo2     Closed
pippo5     Closed

SQL>


Hope your problem is solved ...

Thumbs Up
Rajuvan.


[Updated on: Mon, 17 July 2006 23:58]

Report message to a moderator

Re: SQLLDR - How to import and decode my fields [message #182750 is a reply to message #182744] Tue, 18 July 2006 01:04 Go to previous messageGo to next message
**Snake**
Messages: 47
Registered: December 2005
Location: Italy
Member

Wink Thanks...
With your guide the problem is solved Wink
Re: SQLLDR - How to import and decode my fields [message #201083 is a reply to message #182597] Thu, 02 November 2006 07:59 Go to previous messageGo to next message
pieuvre
Messages: 2
Registered: November 2006
Junior Member
hi !

I've got a problem related to sqlloader too :

my control file :

OPTIONS( SKIP=1 )
LOAD DATA
TRUNCATE
INTO TABLE ES_R03
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
  TEST_UID SEQUENCE(2,1),
  TEST_EXTERNALID,
  TEST_CODE "decode(:TEST_EXTERNALID, 'ONE', '001', 'TWO', '002', :TEST_CODE)",
  TEST_MODIF,
  TEST_CODE_LAST ":TEST_CODE"
)


I have a data file that looks like this :

ONE;000;Y
TWO;004;N
THR;004;N

When I import something with ...;ONE;... or ...;TWO;... it works fine (TEST_CODE_LAST isn't filled but I don't care)

but when it's something else (...;THR;...) there's no value for TEST_CODE imported (in my example : 004)

In on Solaris and it really only works (TEST_CODE_LAST and all) when I have this as data file :

THR;004;N;^M
(that means comes from windows)
I'm lost, please help !

PS : sqlldr release 9.2.0.4.0

[Updated on: Thu, 02 November 2006 10:25]

Report message to a moderator

Re: SQLLDR - How to import and decode my fields [message #201109 is a reply to message #182597] Thu, 02 November 2006 10:24 Go to previous message
pieuvre
Messages: 2
Registered: November 2006
Junior Member
ok after testing it'is just that I need something after the last ;

like that

THR;004;Y;something

What is the TRAILING NULLCOLS though ?
Previous Topic: trying to call but got error
Next Topic: adding quotes
Goto Forum:
  


Current Time: Wed Dec 07 04:40:04 CST 2016

Total time taken to generate the page: 0.07291 seconds