السبت، 11 يناير 2020

How to Store Blob File Outside Oracle Database In Oracle APEX

1: add privilege to use UTL_FILE  to You User

grant execute on utl_file to STOCK;

2 : Create Directory to save File and add privilege to write on Directory to You User

CREATE OR REPLACE DIRECTORY STOCK_DIR AS 'F:\STOCK_IMAGES';

GRANT read, write ON DIRECTORY STOCK_DIR TO STOCK;

3: Use this Procedure to write BLOB file to your disk

create or replace PROCEDURE blob_to_file (p_blob      IN OUT NOCOPY BLOB,
                                          p_dir       IN  VARCHAR2,
                                          p_filename  IN  VARCHAR2)
AS
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  l_blob_len := DBMS_LOB.getlength(p_blob);
  
  -- Open the destination file.
  l_file := UTL_FILE.fopen(p_dir, p_filename,'wb', 32767);

  -- Read chunks of the BLOB and write them to the file until complete.
  WHILE l_pos <= l_blob_len LOOP
    DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.put_raw(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
  END LOOP;
  
  -- Close the file.
  UTL_FILE.fclose(l_file);
  
EXCEPTION
  WHEN OTHERS THEN
    -- Close the file if something goes wrong.
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;

END blob_to_file;

4- use this process to upload file 

DECLARE
  l_blob  BLOB;
BEGIN
  -- Ge SELECT filename,
select
                      BLOB_CONTENT INTO   l_blob
                   FROM apex_application_temp_files
               where NAME= :P5_PRODUCT_IMAGE ;
  
  

  blob_to_file(p_blob     => l_blob,
               p_dir      => 'STOCK_DIR',
               p_filename => :P5_PRODUCT_IMAGE);
END;




هناك 9 تعليقات:

How to Solve Ords Issue : The request could not be mapped to any database.

 The request could not be mapped to any database.  Check the request URL is correct, and that URL to database mappings have been correctly c...