Members   Search   Help    Register    Login   Home
Home » SQL & PL/SQL » SQL & PL/SQL » Generate multiple file names with fixed format (SQL Nav 4.5)
Generate multiple file names with fixed format [message #410772] Tue, 30 June 2009 03:31 Go to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Can someone advice how can I get to have multiple files generated by 1 stored procedure?

File Name Format : ShipFrom_Shipto_YYYYMMDD_Invoice.txt

EX : CAS_UNM_20090629_CC12345678.txt
CAS_ANS_20090629_CC87654321.txt

Appreciate your quick response.

Report message to a moderator
Re: Generate multiple file names with fixed format [message #410780 is a reply to message #410772] Tue, 30 June 2009 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 28991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Open several files.

Regards
Michel
Report message to a moderator
Re: Generate multiple file names with fixed format [message #410784 is a reply to message #410780] Tue, 30 June 2009 04:06 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
I dont understand Sad How?
Report message to a moderator
Re: Generate multiple file names with fixed format [message #410785 is a reply to message #410784] Tue, 30 June 2009 04:18 Go to previous messageGo to next message
JRowbottom
Messages: 5254
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Using UTL_FILE.FOPEN
Report message to a moderator
Re: Generate multiple file names with fixed format [message #410786 is a reply to message #410785] Tue, 30 June 2009 04:38 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
I tried but it does not work.Please help
Report message to a moderator
Re: Generate multiple file names with fixed format [message #410789 is a reply to message #410786] Tue, 30 June 2009 05:07 Go to previous messageGo to next message
JRowbottom
Messages: 5254
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
'It does not work' isn't an oracle error message.

Show us what you've tried, and tell us what the error you get is.

How are me meant to help if you dont give us any information.
Report message to a moderator
Re: Generate multiple file names with fixed format [message #411261 is a reply to message #410772] Thu, 02 July 2009 21:47 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
I manage to generate multiple files but apparently the data gets replaced.

For example, my codes generated 2 files as below

PD062317.txt --> Reported 1 record
PD062329.txy --> Reported 1 record which is incorrect,it should be 7 records
Dept      Invoice     Customerlotnumber  
DieBank   PD062317    2ILF13069.1A
DieBank   PD062329    DTW6A
DieBank   PD062329    DTW6C
DieBank   PD062329    DWP8CE
DieBank   PD062329    W8M3A
DieBank   PD062329    W8M3C
DieBank   PD062329    WAE5W
DieBank   PD062329    WAL7J

My For Loop
For Lotcur in Diebank_RRTC_Lot  Loop

       v_sysdate    := to_char(sysdate-1,'YYYYMMDD');

        select CS_UTILS.GET_UTL_PATH into vpath from dual;
        edi_hat_data := utl_file.fopen(vpath,filename,'W');

        var1 := to_char(sysdate,'YYYYMMDD HH24MISS');
        dbms_output.put_line('Start      '||var1);

        Hdr_Sql := 'H'||to_char(sysdate-1,'YYMMDD');

        utl_file.put_line(edi_hat_data,Hdr_sql);
        nRecord := '0';

        Filename:= Lotcur.From_Org_Code||'_'||Lotcur.To_Org_Code||'_'||substr(v_sysdate,3,6)||'_'||Lotcur.invoiceno||'.txt';
        
        vLotCode := ' ';
      
        If    instr(vLotCode,'RR') > 0 or instr(vLotCode,'CR') > 0 or
              instr(vLotCode,'RT') > 0 or instr(vLotCode,'RE') > 0 or
              instr(vLotCode,'CE') > 0 or instr(vLotCode,'RW') > 0 Then
              vLotCode := 'Rework';
        Elsif (instr(vLotCode,'E') > 0 and instr(vLotCode,'IE') = 0 and instr(vLotCode,'PPE') = 0) or instr(vLotCode,'Q') > 0 Then
              vLotCode := 'Engineering';
        Elsif instr(vLotCode,'P') > 0 or instr(vLotCode,'IE') > 0 Then
              vLotCode := 'Production';
        Else
              vLotCode := 'Production';
        End if;

 

        Unix_Sql := rpad(LotCur.Record_Id,1,' ')||rpad(Lotcur.InvoiceNo,20,' ')||rpad(Lotcur.MAWB,35,' ')||rpad(Lotcur.HAWB,35,' ')||
                  rpad(Lotcur.Ship_Out_Date,6,' ')||rpad(Lotcur.Ship_Out_Time,6,' ')||
                  rpad(Lotcur.From_Org_Code,3,' ')||
                  rpad(Lotcur.To_Org_Code,3,' ')||
                  rpad(Lotcur.Intersil_Lot_No,20,' ')||rpad(Lotcur.Dept,8,' ')||rpad(Lotcur.WaferLotNumber,20,' ')||
                  rpad(Lotcur.Part_Name,25,' ')||
                  rpad(Lotcur.PD_Part_Name,25,' ')||rpad(Lotcur.Die_Qty,10,' ')||
                  rpad(Lotcur.Wafer_Quantity,2,' ')||
                  rpad(Lotcur.Tracecode,10,' ')||rpad(Lotcur.Datecode,4,' ')||rpad(vLotCode,20,' ')||
                  rpad(' ',30,' ');

        utl_file.put_line(edi_hat_data,Unix_sql);
        nRecord := nRecord + 1;

        Hdr_Sql := 'T'||lpad(nRecord,5,'0');
        utl_file.put_line(edi_hat_data,Hdr_sql);
        
        v_msg := 'Close Unix File';
        utl_file.fclose(edi_hat_data);
        
End loop;

Please help
[EDITED by LF: applied [code] and [pre] tags]

[Updated on: Fri, 03 July 2009 00:12] by Moderator

Report message to a moderator
Re: Generate multiple file names with fixed format [message #411262 is a reply to message #410772] Thu, 02 July 2009 22:04 Go to previous messageGo to next message
BlackSwan
Messages: 3227
Registered: January 2009
Senior Member
We can't debug code that is not formatted & fails to compile.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Report message to a moderator
Re: Generate multiple file names with fixed format [message #411265 is a reply to message #411262] Thu, 02 July 2009 22:37 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Black Swan,Thanks!

Ive attached the DLL of my procedure.No errors encountered.Only that this is not working according to what I want. Embarassed

Report message to a moderator
Re: Generate multiple file names with fixed format [message #411268 is a reply to message #410772] Thu, 02 July 2009 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 3227
Registered: January 2009
Senior Member
>Only that this is not working according to what I want.
So you claim. (Presently we have no way to confirm or deny.)

Regardless, we can not test or debug without
DDL for tables involved or
DML for actual test data
to produce what ever it is you desire to be results.

Perhaps I missed it, but where do you clearly state EXACTLY what the desired/expected output/results should be?
Report message to a moderator
Re: Generate multiple file names with fixed format [message #411277 is a reply to message #411268] Thu, 02 July 2009 23:15 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Agreed that you are unable to test.
Im unable to provide the dlls or dmls of the tables involved as the data is too huge.I was looking at the perspective where you could probably guide me based on your expertise.

I stated my issue as below in the previous msg

For your reference :

I manage to generate multiple files but apparently the data gets replaced.

For example, my codes generated 2 files as below

PD062317.txt --> Reported 1 record
PD062329.txy --> Reported 1 record which is incorrect,it should be 7 records

Dept Invoice Customerlotnumber
DieBank PD062317 2ILF13069.1A
DieBank PD062329 DTW6A
DieBank PD062329 DTW6C
DieBank PD062329 DWP8CE
DieBank PD062329 W8M3A
DieBank PD062329 W8M3C
DieBank PD062329 WAE5W
DieBank PD062329 WAL7J
Report message to a moderator
Re: Generate multiple file names with fixed format [message #411286 is a reply to message #410772] Thu, 02 July 2009 23:49 Go to previous messageGo to next message
BlackSwan
Messages: 3227
Registered: January 2009
Senior Member
>I stated my issue as below in the previous msg
Enjoy your mystery.
How does repeating post improve reality?
Report message to a moderator
Re: Generate multiple file names with fixed format [message #411309 is a reply to message #411277] Fri, 03 July 2009 01:28 Go to previous messageGo to next message
_jum
Messages: 117
Registered: February 2008
Senior Member
You open the File in the LOOP with 
edi_hat_data := utl_file.fopen(vpath,filename,'W');

So You overwrite the filecontent each loop. Try to open for APPEND.
edi_hat_data := utl_file.fopen(vpath,filename,'A');

Or open the file only once outside the loop.

[Updated on: Fri, 03 July 2009 01:29]

Report message to a moderator
Re: Generate multiple file names with fixed format [message #411329 is a reply to message #411309] Fri, 03 July 2009 02:36 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Yay! Thanks jum. This worked.

I cannot open the file outside the loop as my file requires some variable to be passed into.I still need it to be within the loop.

Therefore I tried the 'A' method.Now,Im faced with the below issue

Current Code Output
H090702
DPD062439 117 1975 873 090701093000CASANSM9K20000VF DieBank
T00001
H090702
DPD062439 117 1975 873 090701093000CASANSM9K20001VF DieBank
T00001

It should be
H090702
DPD062439 117 1975 873 090701093000CASANSM9K20000VF DieBank
DPD062439 117 1975 873 090701093000CASANSM9K20001VF DieBank
T00002 -> Count to indicate 2 records

How is it possible for me to get the above output?
Report message to a moderator
Re: Generate multiple file names with fixed format [message #411340 is a reply to message #411329] Fri, 03 July 2009 02:47 Go to previous messageGo to next message
JRowbottom
Messages: 5254
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You will need a second loop.

For each file that you open, output a header record, and then loop through the data to be output, then output the trailing record, and then close the file and move on to the next one.

Yoe seem to be continually re-opening the same file here., which means that you will keep appending Header-Data-trailer records into the same file.
Report message to a moderator
Re: Generate multiple file names with fixed format [message #411352 is a reply to message #411340] Fri, 03 July 2009 03:48 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Apparently im not an expert of for loops.I tried the below method and it gave me ORA06511- Cursor already open error.I know something must be wrong with my for loops.Im trying to maintain one cursor that is Diebank_RRTC_Loop and use this cursor to do my other loops like below.Im not sure if this is doable.
For Lotcur in Diebank_RRTC_Lot Loop

v_sysdate := to_char(sysdate-1,'YYYYMMDD');
Filename:= Lotcur.From_Org_Code||'_'||Lotcur.To_Org_Code||'_'||substr(v_sysdate,3,6)||'_'||Lotcur.invoiceno||'.txt';

select CS_UTILS.GET_UTL_PATH into vpath from dual;
--edi_hat_data := utl_file.fopen(vpath,filename,'W');
edi_hat_data := utl_file.fopen(vpath,filename,'A'); --> append

Hdr_Sql := 'H'||to_char(sysdate-1,'YYMMDD');

utl_file.put_line(edi_hat_data,Hdr_sql);
nRecord := '0';


For A in Diebank_RRTC_Lot Loop
vLotCode := ' ';

If instr(vLotCode,'RR') > 0 or instr(vLotCode,'CR') > 0 or
instr(vLotCode,'RT') > 0 or instr(vLotCode,'RE') > 0 or
instr(vLotCode,'CE') > 0 or instr(vLotCode,'RW') > 0 Then
vLotCode := 'Rework';
Elsif (instr(vLotCode,'E') > 0 and instr(vLotCode,'IE') = 0 and instr(vLotCode,'PPE') = 0) or instr(vLotCode,'Q') > 0 Then
vLotCode := 'Engineering';
Elsif instr(vLotCode,'P') > 0 or instr(vLotCode,'IE') > 0 Then
vLotCode := 'Production';
Else
vLotCode := 'Production';
End if;

Unix_Sql := rpad(LotCur.Record_Id,1,' ')||rpad(Lotcur.InvoiceNo,20,' ')||rpad(Lotcur.MAWB,35,' ')||rpad(Lotcur.HAWB,35,' ')||
rpad(Lotcur.Ship_Out_Date,6,' ')||rpad(Lotcur.Ship_Out_Time,6,' ')||
rpad(Lotcur.From_Org_Code,3,' ')||
rpad(Lotcur.To_Org_Code,3,' ')||
rpad(Lotcur.Intersil_Lot_No,20,' ')||rpad(Lotcur.Dept,8,' ')||rpad(Lotcur.WaferLotNumber,20,' ')||
rpad(Lotcur.Part_Name,25,' ')||
rpad(Lotcur.PD_Part_Name,25,' ')||rpad(Lotcur.Die_Qty,10,' ')||
rpad(Lotcur.Wafer_Quantity,2,' ')||
rpad(Lotcur.Tracecode,10,' ')||rpad(Lotcur.Datecode,4,' ')||rpad(vLotCode,20,' ')||
rpad(' ',30,' ');

utl_file.put_line(edi_hat_data,Unix_sql);
nRecord := nRecord + 1;

End Loop;

var1 := to_char(sysdate,'YYYYMMDD HH24MISS');
dbms_output.put_line('Start '||var1);

Hdr_Sql := 'T'||lpad(nRecord,5,'0');
utl_file.put_line(edi_hat_data,Hdr_sql);

v_msg := 'Close Unix File';
utl_file.fclose(edi_hat_data);

End loop;

[Mod-edit: Frank fixed code tags]

[Updated on: Fri, 03 July 2009 04:44] by Moderator

Report message to a moderator
Re: Generate multiple file names with fixed format [message #411362 is a reply to message #411352] Fri, 03 July 2009 04:27 Go to previous messageGo to next message
JRowbottom
Messages: 5254
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't use the same cursoe for two nested loops - you're using the Diebank_RRTC_Lot cursor for both loops.

Your outer cursor needs to return 1 row per file that you want to create, with that row containing all the details you need to create a filename.

Your inner cursor needs to fetch all the data that you want to put into a given file.
Report message to a moderator
Re: Generate multiple file names with fixed format [message #412170 is a reply to message #411362] Wed, 08 July 2009 01:57 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
This is what I had tried multiple times but to no success.Whats wrong?

Encountered Error
ORA-29288 invalid file name
Cause : A NULL or zero length file name was specified
Action : Correct the file name to be a nonzero length string

For Lotcur in Diebank_RRTC_Lot Loop
For Rec in GetFilename (Lotcur.Invoiceno) Loop

select CS_UTILS.GET_UTL_PATH into vpath from dual;
edi_hat_data := utl_file.fopen(vpath,filename,'A');

var1 := to_char(sysdate,'YYYYMMDD HH24MISS');
dbms_output.put_line('Start '||var1);

Hdr_Sql := 'H'||to_char(sysdate-1,'YYMMDD');

utl_file.put_line(edi_hat_data,Hdr_sql);
nRecord := '0';

v_sysdate := to_char(sysdate-1,'YYYYMMDD');

vShipFrom := Rec.ShipFrom;
vShipTo := Rec.ShipTo;
vInvoiceNo := Rec.Invoiceno;

Filename:= vShipFrom||'_'|| vShipTo||'_'||substr(v_sysdate,3,6)||'_'||vInvoiceNo||'.txt';

Hdr_Sql := 'T'||lpad(nRecord,5,'0');
utl_file.put_line(edi_hat_data,Hdr_sql);

v_msg := 'Close Unix File';
utl_file.fclose(edi_hat_data);

vLotCode := ' ';

If instr(vLotCode,'RR') > 0 or instr(vLotCode,'CR') > 0 or
instr(vLotCode,'RT') > 0 or instr(vLotCode,'RE') > 0 or
instr(vLotCode,'CE') > 0 or instr(vLotCode,'RW') > 0 Then
vLotCode := 'Rework';
Elsif (instr(vLotCode,'E') > 0 and instr(vLotCode,'IE') = 0 and instr(vLotCode,'PPE') = 0) or instr(vLotCode,'Q') > 0 Then
vLotCode := 'Engineering';
Elsif instr(vLotCode,'P') > 0 or instr(vLotCode,'IE') > 0 Then
vLotCode := 'Production';
Else
vLotCode := 'Production';
End if;


Unix_Sql := rpad(Lotcur.Record_Id,1,' ')||rpad(Lotcur.InvoiceNo,20,' ')||rpad(Lotcur.MAWB,35,' ')||rpad(Lotcur.HAWB,35,' ')||
rpad(Lotcur.Ship_Out_Date,6,' ')||rpad(Lotcur.Ship_Out_Time,6,' ')||
rpad(Lotcur.From_Org_Code,3,' ')||
rpad(Lotcur.To_Org_Code,3,' ')||
rpad(Lotcur.Intersil_Lot_No,20,' ')||rpad(Lotcur.Dept,8,' ')||rpad(Lotcur.WaferLotNumber,20,' ')||
rpad(Lotcur.Part_Name,25,' ')||
rpad(Lotcur.PD_Part_Name,25,' ')||rpad(Lotcur.Die_Qty,10,' ')||
rpad(Lotcur.Wafer_Quantity,2,' ')||
rpad(Lotcur.Tracecode,10,' ')||rpad(Lotcur.Datecode,4,' ')||rpad(vLotCode,20,' ')||
rpad(' ',30,' ');
utl_file.put_line(edi_hat_data,Unix_sql);
nRecord := nRecord + 1;

End Loop;
End Loop;
Report message to a moderator
Re: Generate multiple file names with fixed format [message #412175 is a reply to message #412170] Wed, 08 July 2009 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 28991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Report message to a moderator
Re: Generate multiple file names with fixed format [message #412189 is a reply to message #412175] Wed, 08 July 2009 03:19 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
This is what I had tried multiple times but to no success.Whats wrong?

Encountered Error
ORA-29288 invalid file name
Cause : A NULL or zero length file name was specified
Action : Correct the file name to be a nonzero length string

For Lotcur in Diebank_RRTC_Lot Loop
For Rec in GetFilename (Lotcur.Invoiceno) Loop

select CS_UTILS.GET_UTL_PATH into vpath from dual;
edi_hat_data := utl_file.fopen(vpath,filename,'A'); 

var1 := to_char(sysdate,'YYYYMMDD HH24MISS');
dbms_output.put_line('Start '||var1);

Hdr_Sql := 'H'||to_char(sysdate-1,'YYMMDD');

utl_file.put_line(edi_hat_data,Hdr_sql);
nRecord := '0';

v_sysdate := to_char(sysdate-1,'YYYYMMDD');

vShipFrom := Rec.ShipFrom;
vShipTo := Rec.ShipTo;
vInvoiceNo := Rec.Invoiceno;

Filename:= vShipFrom||'_'|| vShipTo||'_'||substr(v_sysdate,3,6)||'_'||vInvoiceNo||'.txt';

Hdr_Sql := 'T'||lpad(nRecord,5,'0');
utl_file.put_line(edi_hat_data,Hdr_sql);

v_msg := 'Close Unix File';
utl_file.fclose(edi_hat_data);

vLotCode := ' ';

If instr(vLotCode,'RR') > 0 or instr(vLotCode,'CR') > 0 or
instr(vLotCode,'RT') > 0 or instr(vLotCode,'RE') > 0 or
instr(vLotCode,'CE') > 0 or instr(vLotCode,'RW') > 0 Then
vLotCode := 'Rework';
Elsif (instr(vLotCode,'E') > 0 and instr(vLotCode,'IE') = 0 and instr(vLotCode,'PPE') = 0) or instr(vLotCode,'Q') > 0 Then
vLotCode := 'Engineering';
Elsif instr(vLotCode,'P') > 0 or instr(vLotCode,'IE') > 0 Then
vLotCode := 'Production';
Else
vLotCode := 'Production';
End if;


Unix_Sql := rpad(Lotcur.Record_Id,1,' ')||rpad(Lotcur.InvoiceNo,20,' ')||rpad(Lotcur.MAWB,35,' ')||rpad(Lotcur.HAWB,35,' ')||
rpad(Lotcur.Ship_Out_Date,6,' ')||rpad(Lotcur.Ship_Out_Time,6,' ')||
rpad(Lotcur.From_Org_Code,3,' ')||
rpad(Lotcur.To_Org_Code,3,' ')||
rpad(Lotcur.Intersil_Lot_No,20,' ')||rpad(Lotcur.Dept,8,' ')||rpad(Lotcur.WaferLotNumber,20,' ')||
rpad(Lotcur.Part_Name,25,' ')||
rpad(Lotcur.PD_Part_Name,25,' ')||rpad(Lotcur.Die_Qty,10,' ')||
rpad(Lotcur.Wafer_Quantity,2,' ')||
rpad(Lotcur.Tracecode,10,' ')||rpad(Lotcur.Datecode,4,' ')||rpad(vLotCode,20,' ')||
rpad(' ',30,' ');
utl_file.put_line(edi_hat_data,Unix_sql);
nRecord := nRecord + 1;

End Loop;
End Loop;
Report message to a moderator
Re: Generate multiple file names with fixed format [message #412243 is a reply to message #412189] Wed, 08 July 2009 06:11 Go to previous messageGo to next message
JRowbottom
Messages: 5254
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The error that you're getting is because you never specify the value of the variable 'filename' before using it.

You've got the logic for this code completely round your neck I'm afraid - the code you've written will step through every record in the Diebank_RRTC_Lot cursor, and for each record, it will open a file, write a header record to it, close the file, and then attempt to write a line of text to the file, which will error.

You want to:
Loop through the file names, and for each file:
open a file
write a header record to the file
loop through the records that belong in this file, anf for each record:
write the record out
maintain any running totals
end of record loop
write a footer record to the file
close the file
end of file names loop.
Report message to a moderator
Re: Generate multiple file names with fixed format [message #412379 is a reply to message #412243] Thu, 09 July 2009 01:08 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Thank you JRowbottom! Below is my final code and most importantly,it WORKS!

For Rec in GetFilename Loop

       v_sysdate    := to_char(sysdate-1,'YYYYMMDD');
       Filename:= Rec.shipfrom||'_'||Rec.shipto||'_'||substr(v_sysdate,1,8)||'_'||Rec.invoiceno||'.txt';


        select CS_UTILS.GET_UTL_PATH into vpath from dual;
        edi_hat_data := utl_file.fopen(vpath,filename,'W');

        var1 := to_char(sysdate,'YYYYMMDD HH24MISS');
        dbms_output.put_line('Start      '||var1);

        Hdr_Sql := 'H'||to_char(sysdate-1,'YYMMDD');

        utl_file.put_line(edi_hat_data,Hdr_sql);
        nRecord := '0';

For Lotcur in Diebank_RRTC_Lot (Rec.InvoiceNo) Loop


        vLotCode := ' ';

        If    instr(vLotCode,'RR') > 0 or instr(vLotCode,'CR') > 0 or
              instr(vLotCode,'RT') > 0 or instr(vLotCode,'RE') > 0 or
              instr(vLotCode,'CE') > 0 or instr(vLotCode,'RW') > 0 Then
              vLotCode := 'Rework';
        Elsif (instr(vLotCode,'E') > 0 and instr(vLotCode,'IE') = 0 and instr(vLotCode,'PPE') = 0) or instr(vLotCode,'Q') > 0 Then
              vLotCode := 'Engineering';
        Elsif instr(vLotCode,'P') > 0 or instr(vLotCode,'IE') > 0 Then
              vLotCode := 'Production';
        Else
              vLotCode := 'Production';
        End if;



        Unix_Sql := rpad(LotCur.Record_Id,1,' ')||rpad(Rec.InvoiceNo,20,' ')||rpad(Lotcur.MAWB,35,' ')||rpad(NVL(Lotcur.HAWB,' '),35,' ')||
                  rpad(Lotcur.Ship_Out_Date,6,' ')||rpad(Lotcur.Ship_Out_Time,6,' ')||
                  rpad(Lotcur.From_Org_Code,3,' ')||
                  rpad(Lotcur.To_Org_Code,3,' ')||
                  rpad(Lotcur.Intersil_Lot_No,20,' ')||rpad(Lotcur.Dept,8,' ')||rpad(Lotcur.WaferLotNumber,20,' ')||
                  rpad(Lotcur.Part_Name,25,' ')||
                  rpad(NVL(Lotcur.PD_Part_Name,' '),25,' ')||lpad(Lotcur.Die_Qty,10,'0')||rpad(Lotcur.Wafer_Quantity,2,'0')||
                  rpad(NVL(Lotcur.Tracecode,' '),10,' ')||rpad(NVL(Lotcur.Datecode,' '),4,' ')||rpad(vLotCode,20,' ')||
                  rpad(' ',30,' ');

        utl_file.put_line(edi_hat_data,Unix_sql);
        nRecord := nRecord + 1;
End Loop;

        Hdr_Sql := 'T'||lpad(nRecord,5,'0');
        utl_file.put_line(edi_hat_data,Hdr_sql);

        v_msg := 'Close Unix File';
        utl_file.fclose(edi_hat_data);

End loop;
Report message to a moderator
Re: Generate multiple file names with fixed format [message #412384 is a reply to message #412379] Thu, 09 July 2009 01:53 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Why does this code keep looping and doesnt stop? Results are as desired but the program does not stop.What should I do?
Report message to a moderator
Re: Generate multiple file names with fixed format [message #412432 is a reply to message #410772] Thu, 09 July 2009 05:45 Go to previous messageGo to next message
cookiemonster
Messages: 1340
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since you're using FOR LOOPS and I can't think of any way they can infinite loop I assume it will end eventually and it's just taking a very long time.

What do your cursors GetFilename and Diebank_RRTC_Lot look like and how many rows will they return?

Also this:
vLotCode := ' ';

        If    instr(vLotCode,'RR') > 0 or instr(vLotCode,'CR') > 0 or
              instr(vLotCode,'RT') > 0 or instr(vLotCode,'RE') > 0 or
              instr(vLotCode,'CE') > 0 or instr(vLotCode,'RW') > 0 Then
              vLotCode := 'Rework';
        Elsif (instr(vLotCode,'E') > 0 and instr(vLotCode,'IE') = 0 and instr(vLotCode,'PPE') = 0) or instr(vLotCode,'Q') > 0 Then
              vLotCode := 'Engineering';
        Elsif instr(vLotCode,'P') > 0 or instr(vLotCode,'IE') > 0 Then
              vLotCode := 'Production';
        Else
              vLotCode := 'Production';
        End if;


Is logically equivalent to this:
vLotCode := 'Production';


Shouldn't that big IF be checking the value of something from the cursor rather than vLotCode?

Also this:
select CS_UTILS.GET_UTL_PATH into vpath from dual;

Should be done before the first loop since I assume the result doesn't change.
Report message to a moderator
Re: Generate multiple file names with fixed format [message #412467 is a reply to message #412432] Thu, 09 July 2009 10:21 Go to previous messageGo to next message
JRowbottom
Messages: 5254
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also, 
select CS_UTILS.GET_UTL_PATH into vpath from dual;
should be written as
vpath := CS_UTILS.GET_UTL_PATH ;
Report message to a moderator
Re: Generate multiple file names with fixed format [message #412540 is a reply to message #412467] Thu, 09 July 2009 22:23 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Cursors as requested.Both returns 10 rows each.Is there anyway I can improve the processing time? It is taking about 15 mins to complete.

Cursor Diebank_RRTC_Lot (pInvoiceNo varchar2) Is
/* Formatted on 2009/06/29 14:37 (Formatter Plus v4.8.8) */
SELECT /*+leading(w)*/
'D' record_id, 'DieBank' dept, invoiceno,
DECODE (INSTR (max(d.device), '/'),
'0', max(d.device),
SUBSTR (max(d.device),
INSTR (max(d.device), '/') + 1,
LENGTH (max(d.device))
)
) part_name,
' ' pd_part_name,
DECODE (INSTR (max(f.appid), '-'),
0, max(d.customerlotnumber),
DECODE (INSTR (max(f.appid), '-C'),
0, max(f.appid),
d.customerlotnumber
)
) intersil_lot_no,
max(ex.waferlotnumber) waferlotnumber,
NVL (max(wip.lotquantity), max(f.componentqty)) die_qty,
NVL (max(d.waferquantity), 0)wafer_quantity,max(mawb) mawb, max(hawb) hawb,
max(ShipFrom) from_org_code, max(ShipTo) to_org_code,
SUBSTR (max(invoicedate), 3, 6) ship_out_date,
SUBSTR (max(invoicedate), 10, 6) ship_out_time,
DECODE ((max(d.customercode) || max(d.PACKAGE)),
'HUSTBA', 'E',
max(f.lottype)
) owner_code,
max(labeldatecode) datecode, max(datecode) tracecode, max(d.lotobject) Lotobject
FROM fwcatns_diesinventory d,
fwcatns_extenddiesinventory ex,
fwlot f,
fwwiphistory wip,
fwwiptransaction w,
proman_invoice_info pr
WHERE d.lotobject = w.lotobject
AND d.lotobject = ex.lotobject
and d.lotobjectline = ex.lotobjectline
AND d.asyinvoiceno = pr.invoiceno
AND activity = 'Terminated'
AND wip.wiptxn = w.sysid
AND INSTR (f.appid, '-') = 0
AND LTRIM (d.customercode) IN ('HAT', 'HUS', 'ISL', 'ELS', 'ELX')
AND f.sysid = d.lotobject
AND wip.lotquantity > 0
AND d.state IN ('RDUMMY', 'RRTC', 'RDSCRAP')
and invoiceno = pInvoiceNo
AND (w.txntimestamp > '20090708 235959'
AND w.txntimestamp <= '20090709 235959')
group by invoiceno,customerlotnumber;




Cursor GetFilename Is
select shipfrom,shipto,invoiceno from
fwcatns_diesinventory d, proman_invoice_info p
where d.asyinvoiceno = p.invoiceno
AND d.state IN ('RDUMMY', 'RRTC', 'RDSCRAP')
AND LTRIM (d.customercode) IN ('HAT', 'HUS', 'ISL', 'ELS', 'ELX')
AND (Invoicedate > '20090708 235959'
AND invoicedate <= '20090709 235959');
Report message to a moderator
Re: Generate multiple file names with fixed format [message #412542 is a reply to message #410772] Thu, 09 July 2009 22:41 Go to previous messageGo to next message
BlackSwan
Messages: 3227
Registered: January 2009
Senior Member
>/* Formatted on 2009/06/29 14:37 (Formatter Plus v4.8.Cool */
What is wrong with this picture?


ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the problem SQL here
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Report message to a moderator
Re: Generate multiple file names with fixed format [message #412543 is a reply to message #410772] Thu, 09 July 2009 22:44 Go to previous message
BlackSwan
Messages: 3227
Registered: January 2009
Senior Member
 
SELECT shipfrom,
       shipto,
       invoiceno
FROM   fwcatns_diesinventory d,
       proman_invoice_info p
WHERE  d.asyinvoiceno = p.invoiceno
       AND d.state IN ('RDUMMY','RRTC','RDSCRAP')
       AND Ltrim(d.customercode) IN ('HAT','HUS','ISL','ELS',
                                     'ELX')
       AND (invoicedate > '20090708 235959'
            AND invoicedate <= '20090709 235959');

Post DDL for fwcatns_diesinventory d, proman_invoice_info p
Report message to a moderator
Previous Topic:Composite Object
Next Topic:Variable Pivot, Possible?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 8 23:03:43 CST 2009

Total time taken to generate the page: 0.27736 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.