Home » SQL & PL/SQL » SQL & PL/SQL » spooling choice of columns as log file
spooling choice of columns as log file [message #260610] |
Mon, 20 August 2007 08:02  |
gautamvv
Messages: 254 Registered: June 2005
|
Senior Member |
|
|
create table phldrtmpdata
(file_id number(16),lineno number(16),
linedata varchar2(4000));
insert into phldrtmpdata values
(21,1,'"1","1","21178","ipay col","ipaycol1","acme1",,,,,');
insert into phldrtmpdata values
(21,2,'"2","1","ipaycol","ipaycol","ipaycol","John","m","Smith","acme1","jsmith","John","Smith"');
insert into phldrtmpdata values
(21,3,'"1","2","21178","ipay co2","ipayco2","mouseco",,,,,');
insert into phldrtmpdata values
(21,4,'"2","2","ipayco2","ipayco2","minmouse","minnie","mouse","mouseco","mmmouse","minnie","mouse"');
insert into phldrtmpdata values
(21,1,'"2","2","ipayco2","ipayco2","mikmouse","mickey",,"mouse","mouseco","mkmouse","mickey","mouse"');
The rows where linedata start with 1 are company information
The rows where linedata start with 2 are user information
i have prepared scripts with batch file calling sql scripts, where in sqlldr is used
and then the output is loaded to phldrtmpdata table.
now, I want to spool to an output log file, the following data
note that i should get this data from phldrtmpdata table linedata column, by tokenizing
using Enrollment_Loader.Tokenizer,
"1","1","ipaycol","ipaycol","acme1","successfully added",,
"2","1","ipaycol","ipaycol","acme1","jsmith","successfully added",
"1","2","ipay co2","ipay co2","mouseco","successfully added",,
"2","2","ipay co2","minmouse","mouseco","mmouse","failed"," User ID does not exist"
"2","2","ipay co2","mikmouse","mouseco","mkmouse","successfully added"
so if u take the 1st row its literally dbms_output.put_line(l_Tab(1)||','||l_Tab(2)||','||l_Tab(4)||','||l_Tab(5)||','||l_Tab(6));
where l_tab(1)...are the out parameters of tokenizer proc
Enrollment_Loader.Tokenizer is a proc which when given a list, delemiter, and ""
will give output like this
"1","1","ipay col","ipaycol1","acme1"
I have 2 issues
1)when i run the below script, a log file has to be generated giving the above output file result
2)In this output file, not all the details from input file is picked...
For Eg: 21178 should not be there in output file, (for this reason i did not include l_Tab(3))
and for user information, "John","m" ,"John","Smith"' should not be included
the script i used is
spool logs/outputfile.def
SELECT 'define filelog=IPAYCONVERSION'||TO_CHAR(SYSDATE,'MMDDYYYYHH24MISS')||'.log' FROM dual;
spool off
@logs/outputfile.def
DECLARE
cursor c1 is select Linedata from PHLdrTmpData where file_id ='&L_FILE_ID';
l_TabLen BINARY_INTEGER;
l_Tab Enrollment_Loader.vc_arr_10000;
BEGIN
for r1 in c1 loop
Enrollment_Loader.Tokenizer(r1.linedata,',','',l_TabLen,l_Tab);
dbms_output.put_line(l_Tab(1)||','||l_Tab(2)||','||l_Tab(4)||','||l_Tab(5)||','||l_Tab(6));
end loop;
--SPOOL data/&filelog;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
END;
tbe output i get is
"1","1","ipay col","ipaycol1","acme1"
"2","1","ipaycol","ipaycol","John"
"1","2","ipay co2","ipayco2","mouseco"
"2","2","ipayco2","minmouse","minnie"
"2","2","ipayco2","mikmouse","mickey"
as u see the difference between the desired output file,
and the output that i got
there is no "jsmith"...
also can anyone explain how to add "successfully added"
or "failed" based on success or failure
[Updated on: Mon, 20 August 2007 08:23] by Moderator Report message to a moderator
|
|
|
Re: spooling choice of columns as log file [message #260992 is a reply to message #260610] |
Tue, 21 August 2007 08:11   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
On what circumstances you want these value to be concatenated with your output.
Quote: | successfully added,
User ID does not exist
|
Is this what you want ?
Quote: |
Enrollment_Loader.Tokenizer(r1.linedata,',','',l_TabLen,l_Tab);
if l_Tab(1) = "1" then -- Company Information
dbms_output.put_line(l_Tab(1)||','||l_Tab(2)||','||l_Tab(4)||','||l_Tab(5)||','||l_Tab(6));
elsif l_tab(1) = "2" then -- User Information
dbms_output.put_line(l_Tab(1)||','||l_Tab(2)||','||l_Tab(3)||','||l_Tab(4)||','||l_Tab(8)||','||l_Tab(9));
End If
|
|
|
|
Re: spooling choice of columns as log file [message #261393 is a reply to message #260610] |
Wed, 22 August 2007 08:27   |
gautamvv
Messages: 254 Registered: June 2005
|
Senior Member |
|
|
thanks a lot for your response
actually, before spooling the output file, i have to verify
whether the organization exists in a table called ORG, and
whether the user exists in a table called USERT,
its like , from the input file, l_Tab(6)
is the orgid..
so i have to verify whether the value in l_Tab(6) already
exists in ORG table orgid,
and whether the value in l_Tab(10) from the input file, already
exists in USERT table userid or not,
IF THEY ARE NOT PRESENT IN THE RESPECTIVE TABLES, then
"failed","EB USER ID/ COMPANY does not exist" should be appended
to the company information and user information,
else
"successfully added",, needs to be appended
can you please clarify how to do it?
i am thinking and working on it ...
|
|
|
Re: spooling choice of columns as log file [message #261399 is a reply to message #260610] |
Wed, 22 August 2007 08:56  |
gautamvv
Messages: 254 Registered: June 2005
|
Senior Member |
|
|
I have an idea, but I need to develop on that
my tables are
table org(orgid number primary key,short_name varchar2(30)...)
table usert(user_id number primary key,user_ident varchar2(30),E_Orgid number..foreign key references
org(orgid))
so i have the above 2 tables
in the input file, l_tab(6) is our short_name,
l_tab(10) is our user_ident, (after tokenizing)
so how about this?
select a.short_name,b.user_ident
into l_org,l_user
from ORG a,USERT b
where a.orgid=b.e_orgid
and a.short_name=l_tab(6)
and b.user_ident=l_tab(10);
--here i have to spool the output file with "succesfully added",, appended to the values
when no_data_found then
--here i have to spool the output file with "failed","Customer does not exist" -- for the line
begining with "1"
"failed","User Ident does not exist"-- for the line begining with "2".
note that if it is success, after "succesfully added"..we have an extra comma, showing there is
no error message, the last position is for error message
but no idea how to spool conditionally here ..can u please suggest?
|
|
|
Goto Forum:
Current Time: Sat Feb 15 23:21:50 CST 2025
|