?
Solved

How to zip the file and the ftp it

Posted on 2014-01-24
14
Medium Priority
?
909 Views
Last Modified: 2014-01-29
I want to write a code in PL/SQL to zip the .txt file which i am generating and
ftp it the ftp login i have already written
and the flat file creation logic i have already written

the only thing remaining is how to zip the .txt file which i am generating
0
Comment
Question by:pardeshirahul
  • 9
  • 5
14 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39806606
can you use utl_compress ?



DECLARE
    v_clob         CLOB := 'my test text';
    v_temp         BLOB;
    v_src_offset   INTEGER := 1;
    v_dest_offset  INTEGER := 1;
    v_lang_context INTEGER := DBMS_LOB.default_lang_ctx;
    v_warning      INTEGER;
    v_compressed   BLOB;
BEGIN
    DBMS_LOB.createtemporary(lob_loc => v_temp, cache => TRUE, dur => DBMS_LOB.session);

    DBMS_LOB.converttoblob(
        dest_lob       => v_temp,
        src_clob       => v_clob,
        amount         => DBMS_LOB.lobmaxsize,
        dest_offset    => v_dest_offset,
        src_offset     => v_src_offset,
        blob_csid      => DBMS_LOB.default_csid,
        lang_context   => v_lang_context,
        warning        => v_warning
    );

    v_compressed := UTL_COMPRESS.lz_compress(v_temp);
END;
0
 

Author Comment

by:pardeshirahul
ID: 39806790
i am getting this error when i am trying to compress

ORA-21560: argument 3 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 701
ORA-06512: at "APPS.UTL_ZIP", line 49
ORA-06512: at line 12
0
 

Author Comment

by:pardeshirahul
ID: 39806798
i am able to create .txt file in a particular directory on unix
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:pardeshirahul
ID: 39806830
in your code where should i mention the
.txt file which i am creting in a particular path on the unix side

CREATE OR REPLACE DIRECTORY CSV_FILES AS '/cfsdshr/ccld3/tmp';

GRANT ALL ON DIRECTORY  CSV_FILES TO PUBLIC;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39806912
In my example the clob was hard coded text.

Your clob would probably be a BFILE pointing to your file.

Is your data coming out of the database?  If so, why are you creating a file locally?

You can FTP directly from the db without creating a physical file locally.  You only need FTP to write data to the remote system to create a physical file.

You can see examples here...
http://www.experts-exchange.com/Database/Oracle/A_3043-How-to-FTP-with-Oracle-PL-SQL.html
0
 

Author Comment

by:pardeshirahul
ID: 39806926
i am using your code

DECLARE
    v_clob         CLOB  := 'temp.txt';
    v_temp         BLOB;
    v_src_offset   INTEGER := 1;
    v_dest_offset  INTEGER := 1;
    v_lang_context INTEGER := DBMS_LOB.default_lang_ctx;
    v_warning      INTEGER;
    v_compressed   BLOB;
    l_text number;

BEGIN
 l_text := APPS.ccl_dump_csv('select * from all_users where rownum < 1000',',', 'CSV_FILES', 'temp.txt' );
 
 
    DBMS_LOB.createtemporary(lob_loc => v_temp, cache => TRUE, dur => DBMS_LOB.session);

    DBMS_LOB.converttoblob(
        dest_lob       => v_temp,
        src_clob       => v_clob,
        amount         => DBMS_LOB.lobmaxsize,
        dest_offset    => v_dest_offset,
        src_offset     => v_src_offset,
        blob_csid      => DBMS_LOB.default_csid,
        lang_context   => v_lang_context,
        warning        => v_warning
    );

    v_compressed := UTL_COMPRESS.lz_compress(v_temp);
END;

it is creatint temp.txt file in a particular directory but not compressing it
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39806952
yes, you need to read the file, that's why I suggested NOT creating a file.

but, since you already have an api to do so, you need to do something with the file you created.

Note at the end you won't have a compresed file.  You'll have a compressed blob.  You can FTP the blob directly from the database using the article above.

Using the ftp code in the article above it might look like this...

DECLARE
    v_bfile        BFILE := BFILENAME('CSV_FILES', 'temp.txt');
    v_clob         CLOB;
    v_temp         BLOB;
    v_src_offset   INTEGER := 1;
    v_dest_offset  INTEGER := 1;
    v_lang_context INTEGER := DBMS_LOB.default_lang_ctx;
    v_warning      INTEGER;
    v_compressed   BLOB;
    v_conn sdsftp.connection;
BEGIN
    DBMS_LOB.createtemporary(v_clob, TRUE);
    DBMS_LOB.open(v_bfile, DBMS_LOB.lob_readonly);
    DBMS_LOB.loadfromfile(v_clob, v_bfile, DBMS_LOB.lobmaxsize);

    DBMS_LOB.createtemporary(lob_loc => v_temp, cache => TRUE, dur => DBMS_LOB.session);

    DBMS_LOB.converttoblob(
        dest_lob       => v_temp,
        src_clob       => v_clob,
        amount         => DBMS_LOB.lobmaxsize,
        dest_offset    => v_dest_offset,
        src_offset     => v_src_offset,
        blob_csid      => DBMS_LOB.default_csid,
        lang_context   => v_lang_context,
        warning        => v_warning
    );

    v_compressed := UTL_COMPRESS.lz_compress(v_temp);
   
    v_conn := sdsftp.open('your_ftp_target', 'your_ftp_username', 'your_ftp_password');
    sdsftp.put_blob(v_conn, v_compressed, 'temp.txt');
    sdsftp.close(v_conn);
END;
0
 

Author Comment

by:pardeshirahul
ID: 39806971
This code is taking care of everything
creating a file and putting it is the partcular directory on the UNIX side

only thing is it is not converting the temp.txt file to a .zip file
0
 

Author Comment

by:pardeshirahul
ID: 39806972
this code

l_text := APPS.ccl_dump_csv('select * from all_users where rownum < 1000',',', 'CSV_FILES', 'temp.txt' );
0
 

Author Comment

by:pardeshirahul
ID: 39806973
I will try what you have suggested
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39807114
if you want a file zipped, then I suggest using OS level tools to zip the file
0
 

Author Comment

by:pardeshirahul
ID: 39807195
i has to be done in PL/SQL code , i am also doing some research on internet
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39807282
if it all has to be done in pl/sql then look into your apps package,  does it have a clob option instead of generating a file?  You can continue to use the file but it's just an extra step.
But, having said that, try

http:#a39806952

you can  use your own ftp package if you have one, make sure it's sends a blob though because once compressed the data isn't a clob anymore.
0
 

Author Comment

by:pardeshirahul
ID: 39807316
okay
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month6 days, 4 hours left to enroll

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question