Home » SQL & PL/SQL » SQL & PL/SQL » Copying Several Seq. Flat Files into ONE (Oracle 10g)
Copying Several Seq. Flat Files into ONE [message #284077] Wed, 28 November 2007 15:33 Go to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member

I have a need to read several sequential files and produce one combined file. It is as simple as copying each file together.

I use the 'No_Data_Found' exception to mark the end of the first file, but I don't know how to reset this execption so I can open the next file and read through it and write the records to the open output file.

I'm using the UTL_File functions like FOpen, FClose, Get_Line and Put_Line.

How do I read the first file sequentially, write the records to a sequential output file and then after 'No_Data_Found' exception, do I Open and read the next file?

Thanks,
Lou
Re: Copying Several Seq. Flat Files into ONE [message #284081 is a reply to message #284077] Wed, 28 November 2007 16:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Why are you using Oracle to do what is essentially an Operating System (OS) function?

Why don't you just copy/append the files together from the OS command line?
Re: Copying Several Seq. Flat Files into ONE [message #284082 is a reply to message #284081] Wed, 28 November 2007 16:02 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member

I don't have access to the Operating system.

We are secluded to using PL/SQL for all of our programming.
Re: Copying Several Seq. Flat Files into ONE [message #284084 is a reply to message #284077] Wed, 28 November 2007 16:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
When your only tool is a hammer, all problems are viewed as nails.

A hammer is a great tool for building a house; except when the task at hand is to make two boards out of 1 board.

I am very glad I am not prohibited from using the best tool for any requirement I am asked to satisfy.

This sounds as though it came straight from a Dilbert cartoon.

Good Luck with find a new job where no Pointy Headed Boss exists.
Re: Copying Several Seq. Flat Files into ONE [message #284143 is a reply to message #284077] Thu, 29 November 2007 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I don't know how to reset this execption

Just close the file.

Regards
Michel
Re: Copying Several Seq. Flat Files into ONE [message #284355 is a reply to message #284143] Thu, 29 November 2007 07:45 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
I am trying to end my Exception routine with an "End;", but I get a compile error.

Here's my code:
End Loop; -- Main Loop

-- File I/O Exceptions
Exception
-- End of File
When No_Data_Found Then
UTL_File.FFlush (W_OutFile);
UTL_File.FClose (W_InFile);
End;

W_InFile_Name := 'VVIP';
dbms_output.put_line (' Now Processing:');
dbms_output.put_line ('In File Name: ' || 'UTIL_' || W_Drive || W_InFile_Name || '.dta');
W_InFile := UTL_FILE.Fopen( 'UTIL_' || W_Drive, W_InFile_Name || '.dta', 'R', 32767);


UTL_FILE.get_line (W_InFile, W_InRec);


Here's the Compile ERROR:
SQL> show err
Errors for PROCEDURE CVTCTM_ALL:

LINE/COL ERROR
-------- -------------------------------------------------------------
87/2 PLS-00103: Encountered the symbol "W_INFILE_NAME"


How do I END the Exception?

Thanks,
Lou
Re: Copying Several Seq. Flat Files into ONE [message #284359 is a reply to message #284355] Thu, 29 November 2007 07:52 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Invalid code.

1. You can't start code with an END LOOP if there is no start of the loop.
2. You can't have an EXCEPTION without and BEGIN
3. There is no line 87
Re: Copying Several Seq. Flat Files into ONE [message #284362 is a reply to message #284359] Thu, 29 November 2007 07:56 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Joy,

I just gave you the section of code from the program in question.

Do you want the whole program?

Line 87 is the:
W_InFile_Name := 'VVIP';
Line. The line of code after the "END;" statement.

Thanks,
Lou
Re: Copying Several Seq. Flat Files into ONE [message #284365 is a reply to message #284362] Thu, 29 November 2007 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Post the code with line numbers.
You use the variable outside the scope of its definition.

Regards
Michel
Re: Copying Several Seq. Flat Files into ONE [message #284370 is a reply to message #284362] Thu, 29 November 2007 08:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This will be my best guess.

  1  declare
  2  v number;
  3  begin
  4     declare
  5       a number;
  6     begin
  7        a := 1;
  8     end;
  9     a := 2;
 10  v := 3;
 11* end;
SQL> /
   a := 2;
   *
ERROR at line 9:
ORA-06550: line 9, column 4:
PLS-00201: identifier 'A' must be declared
ORA-06550: line 9, column 4:
PL/SQL: Statement ignored


Elapsed: 00:00:00.07
SQL> ed
Wrote file afiedt.buf

  1  declare
  2  v number;
  3  begin
  4     declare
  5       a number;
  6     begin
  7        a := 1;
  8     end;
  9     a := 2;
 10  v := 3;
 11  end;
 12* v := 2
SQL> /
v := 2
*
ERROR at line 12:
ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "V"


Elapsed: 00:00:00.07


cheers

Raj
Re: Copying Several Seq. Flat Files into ONE [message #284380 is a reply to message #284365] Thu, 29 November 2007 08:20 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Thanks. I will.

Lou
Re: Copying Several Seq. Flat Files into ONE [message #284383 is a reply to message #284362] Thu, 29 November 2007 08:31 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Here's the whole file of code.

There are 7 flat sequential files that I need to combine into one. They all have the same format, but come from other sources. I need to combine these files into one big one and then load the DB table with this.

Any Ideas????

Thanks,
Lou
  • Attachment: Test.pls
    (Size: 4.26KB, Downloaded 127 times)
Re: Copying Several Seq. Flat Files into ONE [message #284402 is a reply to message #284077] Thu, 29 November 2007 09:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I need to combine these files into one big one and then load the DB table with this.

Why do you need to combine them rather than just load them individually?

Why can't you treat them as external tables?
Re: Copying Several Seq. Flat Files into ONE [message #284403 is a reply to message #284402] Thu, 29 November 2007 09:37 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
The input is separate Excel spread sheets. I save it as CSV file and read them in different programs because each XLS file has different columns and conditions that need to be handled differently. My programs produce a formatted file that is all in the same format. I then run a program in our proprietary software to load a table with this file in their format. I have 3 steps to perform in order to get this data loaded. So I have 7 files and 3 steps. I would like to have 1 file and 3 steps. I have to produce the 7 files because there are different account exec's that get the error reports.

So, How do I read in a flat sequential file, write out a flat sequential file, close the input, open the next input and contiue creating the big flat sequential file?

Any insight would be appreciated.

Thanks,
Lou
Re: Copying Several Seq. Flat Files into ONE [message #284405 is a reply to message #284402] Thu, 29 November 2007 09:46 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
I'm looking into the "Begin" and "End" statements.

I'm trying to have a block like:
Begin
Open Output File

Begin
Open Input File

Loop
Read records
Write Records
End Loop

Exception
When No_Data_Found
Close Input File
End
End

Begin
Open Input File

Loop
Read records
Write Records
End Loop

Exception
When No_Data_Found
Close Input File
End
End

Close Output File
End


I get a Compile Error at the second "Begin" line:
PLS-00103: Encountered the symbol "BEGIN"

How can I Tie up these loose ends and open a new file?

Thanks,
Lou
Re: Copying Several Seq. Flat Files into ONE [message #284406 is a reply to message #284405] Thu, 29 November 2007 09:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Begin
   Open Output File

   Begin
     Open Input File

     Loop
       Read records
       Write Records
     End Loop

   Exception
   When No_Data_Found
     Close Input File
   End
End 

Begin
   Open Input File

   Loop
     Read records
     Write Records
   End Loop

Exception
When No_Data_Found
  Close Input File
End

End   

Close Output File
End



This is the sole reason why we always insist use code tags and indent the code. You can easily identify where you are going wrong. Next time I won't do it for you.

Regards

Raj
Re: Copying Several Seq. Flat Files into ONE [message #284408 is a reply to message #284406] Thu, 29 November 2007 09:59 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
I tried to indent in the message. It took out my spaces.

Here goes again.....

Begin
Open Output File

Begin -- Sub-Begin
Open Input File

Loop
Read records
Write Records
End Loop

Exception
When No_Data_Found
Close Input File
End -- Ends the When
End -- Ends the Sub-Begin

Begin -- New Sub-Begin
Open Input File

Loop
Read records
Write Records
End Loop

Exception
When No_Data_Found
Close Input File
End -- Ends the When

End -- Ends the Sub-Begin

Close Output File

End -- Ends the First Begin

This Text Editor seems to FLATTEN my code.

Is this more clear?

I get the compile error on the second Sub-Begin.

What do you think?

Thanks,
Lou
Re: Copying Several Seq. Flat Files into ONE [message #284409 is a reply to message #284077] Thu, 29 November 2007 10:14 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
You need to use code tags round the code (when you post use the formatting tool with the curly brackets symbol on it).

The begin and end statements should be used like this

begin
 .
 .
exception
   when no_data_found then
      .
      .
end


you don't need a specific end for the when and you need a then on the when
Re: Copying Several Seq. Flat Files into ONE [message #284410 is a reply to message #284408] Thu, 29 November 2007 10:23 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Also format your sql statement through SQL FORMATTER
http://www.orafaq.com/utilities/sqlformatter.htm

Re: Copying Several Seq. Flat Files into ONE [message #284421 is a reply to message #284409] Thu, 29 November 2007 10:57 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member


scorpio_biker,

That was it. I've got it working with 3 files. I just have to add the other 4 files to the program.

Thanks again.

And Thanks All!

Lou
Re: Copying Several Seq. Flat Files into ONE [message #284502 is a reply to message #284077] Thu, 29 November 2007 22:37 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

@pcd_lou

The file test.pls opens with real player! And file saying that it is an audio play list!?
Now i worried to download anything from here(Orafaq) as it may contain virus.

Regards,
Kiran.
Re: Copying Several Seq. Flat Files into ONE [message #284503 is a reply to message #284077] Thu, 29 November 2007 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>The file test.pls opens with real player! And file saying that it is an audio play list!?
Why is this a problem?
Of course, this begs the question, "What are you talking about & from where did you actually get the file test.pls"?

>Now i worried to download anything from here(Orafaq) as it may contain virus.
You should always worry about downloading ANY file.
Re: Copying Several Seq. Flat Files into ONE [message #284510 is a reply to message #284502] Thu, 29 November 2007 22:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
kir_ait wrote on Fri, 30 November 2007 05:37

@pcd_lou

The file test.pls opens with real player! And file saying that it is an audio play list!?
Now i worried to download anything from here(Orafaq) as it may contain virus.

Regards,
Kiran.


You can't hold the poster responsible for your computer's settings. Ever realized that YOUR Windows defines what program to use to open specific files?
Re: Copying Several Seq. Flat Files into ONE [message #284647 is a reply to message #284503] Fri, 30 November 2007 07:11 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member


Ok, .PLS might be related to play list on your computer, but at my office, it stands for Procedural Language Statements(PLS) or PL/SQL Program.

I'll strip off the ext for you guys next time.

Thanks for your help in this matter.

Lou
Re: Copying Several Seq. Flat Files into ONE [message #284653 is a reply to message #284077] Fri, 30 November 2007 07:23 Go to previous message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

It is ok, not a problem.

Kiran.
Previous Topic: Creating HTML page in Procedure using htp tags.
Next Topic: Problem with FULL OUTER JOIN
Goto Forum:
  


Current Time: Sun Dec 11 02:30:49 CST 2016

Total time taken to generate the page: 0.08988 seconds