Solved

How to zip the file and the ftp it

Posted on 2014-01-24
14
820 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 73

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
 

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 73

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 73

Accepted Solution

by:
sdstuber earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now