Home » RDBMS Server » Server Utilities » SQLLOADER ISSUE
SQLLOADER ISSUE [message #389056] Fri, 27 February 2009 02:22 Go to next message
ORAGENASHOK
Messages: 238
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 #389063 is a reply to message #389056] Fri, 27 February 2009 02:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
At the first glance, this control file line
update_flag     "fn_get_subtype_flag:emp_value_code,:emp_sub_code)"
is invalid.

By the way, saying that "it shows error" means mostly nothing. What error? Does it have a code? If so, which one?
Re: SQLLOADER ISSUE [message #389071 is a reply to message #389056] Fri, 27 February 2009 03:26 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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 session
SQL> $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>
Re: SQLLOADER ISSUE [message #389086 is a reply to message #389056] Fri, 27 February 2009 04:11 Go to previous message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Thanks Littlefoot its working properly...
Previous Topic: EXPDP
Next Topic: sqlldr load error after some
Goto Forum:
  


Current Time: Sat Dec 10 20:52:57 CST 2016

Total time taken to generate the page: 0.03993 seconds