Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to zip the file and the ftp it

Posted on 2014-01-24
14
843 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
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: 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 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
 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Use of Exception to end a Loop 3 45
PL SQL Developer 7 54
dbms_crypto.decrypt   errors out 6 34
Oracle Query - Convert letters to numbers and display the difference 3 30
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

837 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