| SQLLOADER ISSUE [message #389056] |
Fri, 27 February 2009 02:22  |
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
|
Senior Member |
|
|
I have a entire script given below while executing it shows error.Can anyone correct me where i made mistake or tell is any other way to achive my target.
I have a txt file which contain empid,emp_value_code,emp_sub_code
i need to call a function which input parameter as emp_value_code and emp_sub_code that will return the flag Y/N that return value will be update into the update_flag column of the table.
SQL string for column : "fn_get_subtype_flag((LTRIM(RTRIM(:emp_value_code)),LTRIM(RTRIM(:emp_sub_code)))"
create table update_emp
(
empid varchar2(10),
emp_value_code varchar2(5),
emp_sub_code varchar2(5),
update_flag char(1)
)
create table mstr_uemp
(
emp_value_code varchar2(5),
emp_sub_code varchar2(5)
)
insert into mstr_uemp (emp_value_code,emp_sub_code) values ('IN','IN001');
/
insert into mstr_uemp (emp_value_code,emp_sub_code) values ('CS','CS001');
/
insert into mstr_uemp (emp_value_code,emp_sub_code) values ('SI','SI001');
/
commit
/
LOAD DATA
INFILE 'data_file.txt'
APPEND INTO TABLE update_emp
FIELDS TERMINATED BY '§'
(
empid integer external,
emp_value_code char(5),
emp_sub_code char(5),
update_flag "fn_get_subtype_flag:emp_value_code,:emp_sub_code)"
)
My txt file is given below.
1§IN§IN001§
2§CS§CS001§
3§SI§SI001§
CREATE OR REPLACE function fn_get_cr133_uflag(p_type_code in varchar2,p_sub_type_code in varchar2)
return varchar2 is
u_flag varchar2(1);
cnt_flag number;
Begin
select count(*) into cnt_flag
from table mstr_uemp
where emp_value_code = p_type_code and
emp_sub_code = p_sub_type_code;
if (cnt_flag >0 ) then
u_flag := 'Y';
else
u_flag := 'N';
end if;
return u_flag;
End;
/ |
|
|
|
|
|
| Re: SQLLOADER ISSUE [message #389071 is a reply to message #389056] |
Fri, 27 February 2009 03:26  |
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
|
Senior Member |
|
|
I try to take the value from two previous column which is going to insert into that columns is it possible?.
Please take this corrected control file.
LOAD DATA
INFILE 'data_file.txt'
APPEND INTO TABLE update_emp
FIELDS TERMINATED BY '§'
(
empid integer external,
emp_value_code char(5),
emp_sub_code char(5),
update_flag "fn_get_subtype_flag(:emp_value_code,:emp_sub_code)"
)
simple i need if the both emp_value_code and emp_sub_code present in the mstr table then i need to put Y in update_status else N
|
|
|
|
| Re: SQLLOADER ISSUE [message #389080 is a reply to message #389071] |
Fri, 27 February 2009 03:47 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You have created a function whose name is "fn_get_cr133_uflag", but in a control file you use "fn_get_subtype_flag". Also, as there's no fourth column value in the input file, you need to specify TRAILING NULLCOLS. Once you modify it, the result is as follows (tables and function are precreated):
Control file
LOAD DATA
INFILE *
append INTO TABLE update_emp
FIELDS TERMINATED BY '§'
trailing nullcols
(
empid integer external,
emp_value_code char(5),
emp_sub_code char(5),
update_flag "fn_get_subtype_flag(:emp_value_code,:emp_sub_code)"
)
begindata
1§IN§IN001§
2§CS§CS001§
3§SI§SI001§
Loading sessionSQL> $sqlldr scott/tiger@ora10 control=test.ctl log=test.log
Commit point reached - logical record count 2
Commit point reached - logical record count 3
SQL> select * from update_emp;
EMPID EMP_V EMP_S U
---------- ----- ----- -
1 IN IN001 Y
2 CS CS001 Y
3 SI SI001 Y
SQL>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
