?
Solved

Store Files Outside Oracle XE from APEX

Posted on 2009-12-23
7
Medium Priority
?
1,569 Views
Last Modified: 2012-05-08
Hi,

How can I store files (PDF, GIF, ...) outside Oracle XE database?

I found this link -> <b>Writing BLOB to OS-file</b>:
http://monkeyonoracle.blogspot.com/2009/10/storing-images-outside-oracle-xe-with.html

when trying to run the following code:
<i>
declare
l_file         blob;
l_content      clob := 'This is soon to be a blob';
l_src_offset   integer := 1;
l_dest_offset  integer := 1;
l_lang_ctx     integer := dbms_lob.default_lang_ctx;
l_warn         integer;
begin
dbms_lob.createtemporary(l_file, false);
dbms_lob.converttoblob(l_file, l_content, dbms_lob.getlength(l_content), l_dest_offset, l_src_offset, 1, l_lang_ctx, l_warn);
write_to_file('testfile.txt', 'IMAGES', l_file);
dbms_lob.freetemporary(l_file);
exception
when others
then
 dbms_lob.freetemporary(l_file);
 raise;
end;
</i>

I got these errors:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475

Regards,

0
Comment
Question by:HHG
  • 4
  • 3
7 Comments
 
LVL 7

Expert Comment

by:ppinon
ID: 26111047
It seems to be a problem of rights on the directory or files you're trying to replace. Check your OS rights on the directory you created (IMAGES) and be sure you're not trying to replace an existing file testfile.txt (if it is the case, remove it)
0
 

Author Comment

by:HHG
ID: 26111083
OS: Linux Ubuntu 9.1
DBMS: Oracle XE 10g
Application: APEX 3.2.1

in the example found in the above link, I executed ..
create or replace directory IMAGES as '/home/abc'
/

and 'abc' can be modified ( I can add files and folders). The file testfile.txt does not exist.
0
 
LVL 7

Expert Comment

by:ppinon
ID: 26111109
You can add files in abc using which OS user ?
You have to know which OS user is used by the database, because this user needs the right rights on the directory. Normally you installed the database using the user 'oracle', so you can do:

chown oracle /home/abc --> oracle will have all access rights on the folder

or can can test with:

chmod 777 /home/abc --> All users have full access on the folder
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:HHG
ID: 26111224

Yes, that was because of the rights. Thanks.

Now, I am able to let APEX create text files outside Oracle DB.

How can I upload a binary file (with the same example) to "/home/abc" directly after pressing a submit button from APEX using file browser?
0
 
LVL 7

Accepted Solution

by:
ppinon earned 2000 total points
ID: 26111266
With APEX you can manage files from an application using the table wwv_flow_files (see http://avdeo.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/)

and to create binary files from BLOB you can use a procedure like this one:
CREATE OR REPLACE PROCEDURE Write_BLOB_To_File
AS
    v_lob_loc      BLOB;
    v_buffer       RAW(32767);
    v_buffer_size  BINARY_INTEGER;
    v_amount       BINARY_INTEGER;
    v_offset       NUMBER(38) := 1;
    v_chunksize    INTEGER;
    v_out_file     UTL_FILE.FILE_TYPE;

BEGIN

    -- +-------------------------------------------------------------+
    -- | SELECT THE LOB LOCATOR                                      |
    -- +-------------------------------------------------------------+
    SELECT  image
    INTO    v_lob_loc
    FROM    test_blob
    WHERE   id = 1001;

    -- +-------------------------------------------------------------+
    -- | FIND OUT THE CHUNKSIZE FOR THIS LOB COLUMN                  |
    -- +-------------------------------------------------------------+
    v_chunksize := DBMS_LOB.GETCHUNKSIZE(v_lob_loc);

    IF (v_chunksize < 32767) THEN
        v_buffer_size := v_chunksize;
    ELSE
        v_buffer_size := 32767;
    END IF;

    v_amount := v_buffer_size;

    -- +-------------------------------------------------------------+
    -- | OPENING THE LOB IS OPTIONAL                                 |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);

    -- +-------------------------------------------------------------+
    -- | WRITE CONTENTS OF THE LOB TO A FILE                         |
    -- +-------------------------------------------------------------+
    v_out_file := UTL_FILE.FOPEN(
        location      => 'EXAMPLE_LOB_DIR', 
        filename      => 'oracle9i_logo2.gif', 
        open_mode     => 'w',
        max_linesize  => 32767);

    WHILE v_amount >= v_buffer_size
    LOOP

      DBMS_LOB.READ(
          lob_loc    => v_lob_loc,
          amount     => v_amount,
          offset     => v_offset,
          buffer     => v_buffer);

      v_offset := v_offset + v_amount;

      UTL_FILE.PUT_RAW (
          file      => v_out_file,
          buffer    => v_buffer,
          autoflush => true);

      UTL_FILE.FFLUSH(file => v_out_file);

      -- +-------------------------------------------------------------+
      -- | HEY WAIT, THIS IS A BINARY FILE! WHAT IS THIS NEW_LINE      |
      -- | PROCEDURE DOING HERE? THIS WAS A TEST I WAS PERFORMING TO   |
      -- | CONFIRM A BUG (bug#: 2883782). IN 9i THERE IS CURRENTLY A   |
      -- | RESTRICTION OF A MAXIMUM OF 32K THAT CAN BE WRITTEN WITH    |
      -- | PUT_RAW UNLESS YOU INSERT NEW LINE CHARACTERS IN BETWEEN    |
      -- | THE DATA. IN 10i THERE IS A NEW BINARY MODE. WHEN FILES ARE |
      -- | OPENED WITH THIS MODE ANY AMOUNT OF RAW DATA CAN BE WRITTEN |
      -- | WITHOUT THE NEED FOR NEW LINES. IN SHORT, THIS IS A BUG     |
      -- | THAT, IF IT CREEPS UP IN ORACLE9i, THERE IS NO SOLUTION!    |
      -- +-------------------------------------------------------------+
      -- UTL_FILE.NEW_LINE(file => v_out_file);

    END LOOP;

    UTL_FILE.FFLUSH(file => v_out_file);

    UTL_FILE.FCLOSE(v_out_file);

    -- +-------------------------------------------------------------+
    -- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT          |
    -- +-------------------------------------------------------------+
    DBMS_LOB.CLOSE(v_lob_loc);

END;
/

Open in new window

0
 

Author Comment

by:HHG
ID: 26123739

Thanks ppinon for your fast responses. The attachment is working fine now.
0
 

Author Closing Comment

by:HHG
ID: 31669357
fast solution response
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month9 days, 16 hours left to enroll

571 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