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  |
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 #378644 is a reply to message #378620] |
Wed, 31 December 2008 03:42  |
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 1519 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 #378656 is a reply to message #378650] |
Wed, 31 December 2008 04:25 |
 |
Littlefoot
Messages: 21823
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 |
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 #378713 is a reply to message #378667] |
Wed, 31 December 2008 12:30 |
 |
Barbara Boehmer
Messages: 9102
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>
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jan 11 09:11:56 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
