Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to zip the file and the ftp it

Posted on 2014-01-24
14
Medium Priority
?
881 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
[X]
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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

609 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