1

I am creating a file in a directory which i am specifying. I want for each run a different folder name is created in a directory with different file name. And this folder should be zipped.

Currently I am using :

create or replace
PROCEDURE xx_WriteBLOBToFILE (myfilename IN VARCHAR2,L_PERSON_ID IN NUMBER) IS 

  v_blob         BLOB;
  blob_length    INTEGER;
    out_file utl_file.file_type;
  v_buffer       RAW(32767);
  chunk_size     BINARY_INTEGER := 32767;
  blob_position  INTEGER := 1;

BEGIN

  -- Retrieve the BLOB for reading
  Select Image Into V_Blob From Per_Images 
  Where Parent_Id =L_PERSON_ID;

  -- Retrieve the SIZE of the BLOB
  blob_length:=DBMS_LOB.GETLENGTH(v_blob);

  -- Open a handle to the location where you are going to write the BLOB to file
  -- NOTE: The 'wb' parameter means "write in byte mode" and is only availabe
  --       in the UTL_FILE package with Oracle 10g or later
  out_file := UTL_FILE.FOPEN ('INTF_DIR1', myfilename, 'wb', chunk_size);

  -- Write the BLOB to file in chunks 
  WHILE blob_position <= blob_length LOOP
    IF blob_position + chunk_size - 1 > blob_length THEN
      chunk_size := blob_length - blob_position + 1;
    END IF;
    DBMS_LOB.READ(v_blob, chunk_size, blob_position, v_buffer);
    UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);
    blob_position := blob_position + chunk_size;
  END LOOP;

  -- Close the file handle
  UTL_FILE.FCLOSE (out_file);
END;

This piece of code is moving the blob image to a folder in a directory. i want this blob image to be generated in a seperate folder which will be under a zipped folder

sreekem bose
  • 451
  • 3
  • 12
  • 28
  • 1
    Do you mean *zip* as in an OS compressed file which can be read external to the database by e.g. `gzip`, `winzip`, etc? Or just a compressed file which can be decompressed only in the database? – APC Sep 12 '16 at 11:01
  • @APC - I mean zip in a directory in the server – sreekem bose Sep 12 '16 at 11:02
  • So that's my option 1, a database independent file which can be unzipped by an OS utility like `gzip`? You are more likely to get a helpful and timely answer if you explain *exactly* what you need. – APC Sep 12 '16 at 12:55
  • Check this out: https://community.oracle.com/thread/1117748?tstart=0 – daZza Sep 12 '16 at 13:10
  • @daZza - I did go through it but i couldnt understand how to add my file in there – sreekem bose Sep 12 '16 at 14:15

1 Answers1

2

see link to az_zip package at the end of this discussion Zip using Oracle Stored Procedure

https://technology.amis.nl/wp-content/uploads/2010/06/as_zip10.txt

Community
  • 1
  • 1
are
  • 2,535
  • 2
  • 22
  • 27