Home » SQL & PL/SQL » SQL & PL/SQL » Zip a File From PL/SQL (Oracle 10g)
Zip a File From PL/SQL [message #356597] Fri, 31 October 2008 07:25 Go to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Hi All,

Happy Halloween!

I need to convert a .CSV file into a Zip file from an PL/SQL Program.

I am currently writing out the records in the file properly, but I need to Zip it after I close it. I have a separate automated process that will FTP the file to an outside site but it has to be zipped. So I have to do it in my PL/SQL program.

How can I execute a Linux command (gzip) from my PL/SQL Program?

Any help would be appreciated.

Thanks,
Lou
Re: Zip a File From PL/SQL [message #356600 is a reply to message #356597] Fri, 31 October 2008 07:51 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
If you are running a unix shell script already to ftp the file, just gzip in that same script.
Re: Zip a File From PL/SQL [message #356601 is a reply to message #356597] Fri, 31 October 2008 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can I execute a Linux command (gzip) from my PL/SQL Program?

Search for "execute an OS command from PL/SQL".

Regards
Michel
Re: Zip a File From PL/SQL [message #356608 is a reply to message #356601] Fri, 31 October 2008 08:33 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Michael,

I did the search that you suggested....

I got a message that said to use the "HOST" command.

How is this Command Started and/or used?

Do I have to start a Begin? Do you have a sample of code that uses this Host Command?

Thanks,
Lou
Re: Zip a File From PL/SQL [message #356609 is a reply to message #356600] Fri, 31 October 2008 08:40 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Joy,

I don't have access to that Shell Program. This network is rather tight.

I only have access to PL/SQL programming.

Any suggestions?

Thanks,
Lou
Re: Zip a File From PL/SQL [message #356619 is a reply to message #356608] Fri, 31 October 2008 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pcd_lou wrote on Fri, 31 October 2008 14:33
Michael,

I did the search that you suggested....

I got a message that said to use the "HOST" command.

How is this Command Started and/or used?

Do I have to start a Begin? Do you have a sample of code that uses this Host Command?

Thanks,
Lou

Search more.
Host is not a PL/SQL statement, it is a SQL*Plus command.

Regards
Michel

Re: Zip a File From PL/SQL [message #356620 is a reply to message #356619] Fri, 31 October 2008 09:33 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Michel,

I am running SQL*Plus. I can't find any referances to "Host" Command.

Does anyone have someplace I can get syntax, format, parameters, etc for the HOST Command?

Sample code would be helpful.

Thanks,
Lou
Re: Zip a File From PL/SQL [message #356625 is a reply to message #356620] Fri, 31 October 2008 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant you are searching for a SQL*Plus command (I think, as far I understand your question) but a PL/SQL procedure.
If a simple SQL*Plus command fit your requirement then HOST is the good.
I'm surprised you can't find the command in SQL*Plus manual, I just open it, use Ctl-F and find:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12026.htm#sthref2314

Regards
Michel
Re: Zip a File From PL/SQL [message #357266 is a reply to message #356625] Tue, 04 November 2008 09:02 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Michel,

I tried a simple program just to test the ability to do a list and got this error message:

Errors for PROCEDURE TEST_COMMAND:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/7 PLS-00103: Encountered the symbol "LS" when expecting one of the
following:
:= . ( @ % ;
The symbol "; was inserted before "LS" to continue.

I have attached the Program code.
I have tried this command several ways.... with single quotes and without and in a variable.... no luck.

What am I doing wrong?

I'd like to get this working because I need to gzip a file and then send an E-Mail, too.

Any help would be appreciated.

Thanks,
Lou
Re: Zip a File From PL/SQL [message #357271 is a reply to message #356597] Tue, 04 November 2008 09:22 Go to previous messageGo to next message
pmnegrete
Messages: 3
Registered: October 2008
Location: Spain
Junior Member
Use external procedure. It should be written in C or Pro*C and access gzip from UNIX. Then define it in an Oracle Library and use it as a common procedure.

pbc
Re: Zip a File From PL/SQL [message #357275 is a reply to message #357266] Tue, 04 November 2008 09:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you look up a few posts, you'll see the following text:
Host is not a PL/SQL statement, it is a SQL*Plus command.


You can use the HOST command from Sql*Plus, but not within a Pl/Sql block.
Re: Zip a File From PL/SQL [message #357277 is a reply to message #357275] Tue, 04 November 2008 09:52 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
JRowbottom,

I am using PL/SQLPlus.

The Properties from the Icon are:

C:\oracle\product\10.2.0\client_1\BIN\sqlplusw.exe

Is this SQL*Plus???

Is executing a Linux Command from this possiable?

Please let me know.....

Thanks,
Lou
Re: Zip a File From PL/SQL [message #357279 is a reply to message #357271] Tue, 04 November 2008 09:55 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
PBC,

Is there a way to have PL/SQL Execute a Linux Command from within the program?

I need to gzip a file and also I need to send out an E-Mail.

Can I make some kind of Shell Program that I can Call from PL/SQL+ to execute a Linux commmand?

Any help would be appreciated.

Thanks,
Lou
Re: Zip a File From PL/SQL [message #357281 is a reply to message #357279] Tue, 04 November 2008 10:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Use external procedure. It should be written in C or Pro*C and access gzip from UNIX. Then define it in an Oracle Library and use it as a common procedure.

Read the documentation here.

This is non-trivial, and will at least require a few days of learning. So there is no point in asking again and again how to do it, when you are not willing to learn it from the documentation.
Re: Zip a File From PL/SQL [message #357284 is a reply to message #357281] Tue, 04 November 2008 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also how to execute an OS command from PL/SQL has been asked and answered many times, so search for it.

Regards
Michel
Re: Zip a File From PL/SQL [message #357390 is a reply to message #357281] Wed, 05 November 2008 03:05 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's much easier to do it from Java within the database.

Tom Kyte has a thread about it [url=http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:952229840241]here[/url
Previous Topic: Interesting SQL problem
Next Topic: Sybase To Oracle migration
Goto Forum:
  


Current Time: Fri Dec 09 23:20:27 CST 2016

Total time taken to generate the page: 0.06450 seconds