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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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?
Previous Topic: Run through files with wildcard..?
Next Topic: Changing Character Sets in PL/SQL
Goto Forum:
  


Current Time: Mon Dec 05 20:58:15 CST 2016

Total time taken to generate the page: 0.14746 seconds