Solved

How to zip the file and the ftp it

Posted on 2014-01-24
14
826 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cannot open form error 6 66
sql query 5 68
Oracle RAC 12c 8 55
Oracle SQL Select unique values from two columns 4 26
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
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.
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

911 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

20 Experts available now in Live!

Get 1:1 Help Now