Home » RDBMS Server » Server Utilities » SQL Loader - extra field in data file, but need to use its value for another field (Oracle Database 10g Release 10.2.0.3.0)
SQL Loader - extra field in data file, but need to use its value for another field [message #378620] Wed, 31 December 2008 02:12 Go to next message
smora
Messages: 59
Registered: May 2006
Member
Hi

I have a table with 3 columns defined as:

TableA:
Field1 NUMBER(32)
Field2 NUMBER(32)
Field3 NUMBER(32)

My data file has 4 fields:
Data1,Data2,Data3,Data4

I need to load Data1 and Data2 directly into Field1 and Field2, but the value to be loaded for Field3 is derived using a function Func1(:Data3, :Data4)

If I specify Data4 as INTEGER EXTERNAL in the ctl file, I get this error:
SQL*Loader-466: Column Data4 does not exist in table TableA.

If I specify Data4 as FILLER, I get this error:
SQL*Loader-291: Invalid bind variable Data4 in SQL string for column Field3.

Is there a way to do this? Any leads are appreciated.

Thank you
SM
Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378622 is a reply to message #378620] Wed, 31 December 2008 02:16 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Can you please post a small test case?
Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378629 is a reply to message #378620] Wed, 31 December 2008 02:41 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use external tables feature instead; it will enable you to use the function.
Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378631 is a reply to message #378629] Wed, 31 December 2008 02:49 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Functions can be called from a SQL*Loader control file as well (when using the conventional load path).
Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378632 is a reply to message #378631] Wed, 31 December 2008 02:52 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Certainly; I just meant to say that something like
INSERT INTO table (col1, col2, col3) 
SELECT fil1, fil2, my_func(fil3, fil4)
FROM external_table
is a relatively simple way to do the job.
Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378644 is a reply to message #378620] Wed, 31 December 2008 03:42 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
Hi Frank

Create table and create function for test case are as follows. Control file for test case is attached, data is inline.

CREATE TABLE TABLEA (
FIELD1 NUMBER(32),
FIELD2 NUMBER(32),
FIELD3 NUMBER(32)
);

create or replace function get_id
(p_id1  IN NUMBER,
p_id2  IN NUMBER)

RETURN NUMBER

AS

v_id NUMBER;

BEGIN

IF (p_id1 = 0) THEN
 v_id := p_id1;
ELSE
 v_id := p_id2;
END IF;

RETURN v_id;

EXCEPTION
 WHEN OTHERS THEN
  RETURN 0;

END get_id;
/




The output from the run is:
SQL>CREATE TABLE TABLEA (
2 FIELD1 NUMBER(32),
3 FIELD2 NUMBER(32),
4 FIELD3 NUMBER(32)
5 );

Table created.

Elapsed: 00:00:00.92
SQL>
SQL>create or replace function get_id
2 (p_id1 IN NUMBER,
3 p_id2 IN NUMBER)
4
5 RETURN NUMBER
6
7 AS
8
9 v_id NUMBER;
10
11 BEGIN
12
13 IF (p_id1 = 0) THEN
14 v_id := p_id1;
15 ELSE
16 v_id := p_id2;
17 END IF;
18
19 RETURN v_id;
20
21 EXCEPTION
22 WHEN OTHERS THEN
23 RETURN 0;
24
25 END get_id;
26 /

Function created.

Elapsed: 00:00:00.95

With the control file the way it is, I get:
SQL*Loader-291: Invalid bind variable FIELD4 in SQL string for column FIELD3.

If I change the FIELD4 from FILLER to INTEGER EXTERNAL, I get:
SQL*Loader-466: Column FIELD4 does not exist in table TABLEA.

  • Attachment: gid.ctl
    (Size: 0.32KB, Downloaded 235 times)

[Updated on: Wed, 31 December 2008 03:45]

Report message to a moderator

Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378647 is a reply to message #378644] Wed, 31 December 2008 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your function is equivalent to:
decode(p_id1,0,0,p_id2)

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Wed, 31 December 2008 03:53]

Report message to a moderator

Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378650 is a reply to message #378620] Wed, 31 December 2008 03:58 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
Thanks for the reply Michel, however, this is not the real function that I am using, just a simple one I cooked up for the test case. The error is not coming from the function, it is coming from the misalignment between fields in the control file and table.
Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378656 is a reply to message #378650] Wed, 31 December 2008 04:25 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's the external table solution.

First, create the environment (I have pre-created the table and the function, using your CREATE TABLE/FUNCTION statements):
M:\>sqlplus sys/pwd@ora10 as sysdba

SQL> create or replace directory ext_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory ext_dir to scott;

Grant succeeded.

SQL>

I have taken your sample data and created the 'GID.TXT' file, saved it into server's c:\temp directory.

Now, SQL part of the job: create an external table:
SQL> connect scott/tiger@ora10
Connected.
SQL> create table ext_table_csv
  2  (field1 number,
  3   field2 number,
  4   field3 number,
  5   field4 number
  6  )
  7  organization external
  8  (type oracle_loader
  9   default directory ext_dir
 10   access parameters
 11   (records delimited by newline
 12    fields terminated by ','
 13    missing field values are null
 14   )
 15   location ('gid.txt')
 16  )
 17  reject limit unlimited;

Table created.

Let's see what's in there (this is, actually, contents of the 'GID.TXT' file):
SQL> select * from ext_table_csv;

    FIELD1     FIELD2     FIELD3     FIELD4
---------- ---------- ---------- ----------
         1          1          1          0
         1          1          1          1
         2          2          2          0
         2          2          2          1

Finally, a simple insert:
SQL> insert into tablea
  2    (field1, field2, field3)
  3    (select field1, field2, get_id(field3, field4)
  4     from ext_table_csv
  5    );

4 rows created.

SQL> select * from tablea;

    FIELD1     FIELD2     FIELD3
---------- ---------- ----------
         1          1          0
         1          1          1
         2          2          0
         2          2          1

SQL>        


See? Not that complicated, is it?
Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378667 is a reply to message #378620] Wed, 31 December 2008 04:38 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
Thanks Littlefoot. Couple of questions:

The data will actually be generated afresh every day, so I want to be able to automate the whole process. The final data will not be inline, will use the DATA parameter on the command line, but thats a minor point. I can probably automate the final insert statement that selects from the external table, but i was hoping to avoid using an intermediate table at all, since I would have to clear its data every day after the daily run.

Also, I dont see what benefit the external table offers, I could just as easily use a regular staging table that had the FIELD4, use SQL Loader to write to the staging table, and then select only the required fields into the final table.

Either way, looks like I cant avoid the intermediate table, whether external or not.

Thanks
SM
Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378671 is a reply to message #378667] Wed, 31 December 2008 04:45 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What "intermediate" table? External table is an object which points to a file system file ('GID.TXT' in your case). You don't "clear" it - when there's no 'GID.TXT' file, the external table is empty (actually, selecting from external table when there's no file there will result in an error ("there's no file I expect at the location EXT_DIR directory points to")).

smora
I dont see what benefit the external table offers
Well, perhaps you should not rely on this isolated example but read some more about this useful feature as it appears that you have no idea what it, actually, is.
Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378713 is a reply to message #378667] Wed, 31 December 2008 12:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
You can do this using SQL*Loader in one step, without any intermediate table. You need to declare your third and fourth columns as BOUNDFILLER, using different names than your actual columns, then put any calculated fields last, so you can put your field3 last, passing the two boundfiller columns as parameters to your get_id function. Please see the demonstration below.

-- revised contents of test.ctl:
OPTIONS (ERRORS=10000000)
LOAD DATA
INFILE *
APPEND
INTO TABLE TABLEA
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
FIELD1 INTEGER EXTERNAL,
FIELD2 INTEGER EXTERNAL,
filler3 BOUNDFILLER,
filler4 BOUNDFILLER,
FIELD3  "get_id (:filler3, :filler4)"
)
BEGINDATA
1,1,1,0
1,1,1,1
2,2,2,0
2,2,2,1


-- table and function you provided:
SCOTT@orcl_11g> CREATE TABLE TABLEA
  2    (FIELD1	NUMBER (32),
  3  	FIELD2	NUMBER (32),
  4  	FIELD3	NUMBER (32))
  5  /

Table created.

SCOTT@orcl_11g> create or replace function get_id
  2    (p_id1  IN NUMBER,
  3  	p_id2  IN NUMBER)
  4    RETURN NUMBER
  5  AS
  6    v_id NUMBER;
  7  BEGIN
  8    IF (p_id1 = 0) THEN
  9  	 v_id := p_id1;
 10    ELSE
 11  	 v_id := p_id2;
 12    END IF;
 13    RETURN v_id;
 14  EXCEPTION
 15    WHEN OTHERS THEN
 16  	 RETURN 0;
 17  END get_id;
 18  /

Function created.


-- load:
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log


-- results:
SCOTT@orcl_11g> SELECT * FROM tablea
  2  /

    FIELD1     FIELD2     FIELD3
---------- ---------- ----------
         1          1          0
         1          1          1
         2          2          0
         2          2          1

SCOTT@orcl_11g> 


Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378755 is a reply to message #378713] Thu, 01 January 2009 04:25 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Boundfiller! ./fa/3958/0/
Re: SQL Loader - extra field in data file, but need to use its value for another field [message #378852 is a reply to message #378620] Fri, 02 January 2009 02:17 Go to previous message
smora
Messages: 59
Registered: May 2006
Member
Thanks Barbara, this is exactly what I was looking for! And thank you Littlefoot, I tried the external tables before Barbara posted this solution, and it looked like that would have worked as well.

Regards,
SM
Previous Topic: changing tablespace during import
Next Topic: How to write set define off statement in control file of sqldr
Goto Forum:
  


Current Time: Tue Dec 06 02:22:47 CST 2016

Total time taken to generate the page: 0.09892 seconds