APEX

Hi INAMIK,

INAMIK wrote:

system: oracle 12c
apex 4.2

We are converting an old oracle "Orbit" application into APEX app.
The old system has about 90K documents(pdf,doc,txt etc) that the new system will still need to access.
These documents live on a network file-share.

Do you know if there is a way apex can access these files right off of the file-share?  Without installing them into the database?
In the old application, the document downloads to the users machine then they open it up.

     I don't think that you can access the files from network file-share in Oracle APEX 4.2.

     Following are the options:

• You have to upload it as BLOB to a table in your application and write an PL/SQL process or use declarative BLOB support in Oracle APEX to download it.

     Refer :

     http://docs.oracle.com/cd/E14373_01/appdev.32/e13363/up_dn_files.htm#HTMAD008

     http://docs.oracle.com/cd/E37097_01/doc.42/e35125/advnc_blob.htm#HTMDB25903

     How to Download BLOB Content as a File Using Oracle Application Express » Eddie Awad's Blog

• You can create an DIRECTORY object in your database, mapping to your database server OS directory and write PL/SQL process to download it using BFILE.

     Refer:

     Create an Application to Upload Files Using Oracle APEX, In Less Than 10 Minutes (Video) » Eddie Awad's Blog - See the procedure regarding writing file to OS directory

     Download BFILE Procedure

• You can upload it to the images directory (/i/) on your APEX web server and access them directly in your application.

     Hope this helps!

Regards,

Kiran

Hi INAMIK,

Oracle can be configured to access files off of a file share. You just have to make sure that the user who runs the database can access the share.

Kiran already listed the most common approaches to this requirement. In addition to his last item, you could host the files on a different path than /i/ to separate APEX images from your own files. Please also note that this option is only feasible if everybody is allowed to see all files.

As a 4th option, you could write a CGI script to download the files. The script would get a parameter that references the file, but is only valid for the lifetime of your current APEX session.

Regards,

Christian

Chris,

So we are going to bulk convert the files to PDF's.  But they will remain on a network fileshare.  We cannot add the to the DB or DB Server.

The application is hosting internally.

So if i want to open these PDFs on a fileshare i can write a CGI script to do it?  Whats the benefit of this?

Or can i just give the DB User access to the fileshare and reference the pdf files by name from within the APEX application?  Like <a href="http://myinternalfileshare/supplies.pdf">My Doc</a>

@"Christian Neumueller-Oracle"

INAMIK,

if the files on the share are directly accessible (e.g. http://myinternalfileshare/supplies.pdf), then anybody who can connect to that machine can see the files. Maybe that's fine in your scenario. If not, you could write a CGI script that serves the files and enforces access privileges. Either way, the DB User does not need access to the file share, unless you want to read the files using PL/SQL. It is the web server/CGI script which need access. The DB just needs to be able to generate the links.

Regards,

Christian

Chris,

Everyone won't have access this file-share directly.  They will have to access the files through the APEX application.

However, if they have access to the application, then they are "authorized" to access the files.

So it appears a CGI script is going to be the way to go.

@"Christian Neumueller-Oracle"

Chris,

Would you happen to have any instructions building the CGI Script?

I'm not that familiar with CGI Scripting.

Also our web-server is an Apache Tomcat using HTTPS

Will CGI Scripts work on it?

Any instructions on this configurations?

I've search on the net, but not quite sure of the best criteria to search by.

@"Christian Neumueller-Oracle"

INAMIK,

with Tomcat you will have to write a Java app. If you want to avoid that and additional CPU cost on the DB server is no problem, consider implementing the download process in APEX (read via BFILEs or UTL_FILE, write via sys.htp or sys.wpg_docload).

Regards,

Christian

Hi,

INAMIK wrote:

Chris,
Everyone won't have access this file-share directly.  They will have to access the files through the APEX application.
However, if they have access to the application, then they are "authorized" to access the files.

So it appears a CGI script is going to be the way to go.

Christian Neumueller-Oracle

In this case, I go with Oracle Directory option.

with this option,

• I can easily secure files by granting read/write access to APEX Parsing schema (this ensures only users who have access to schema or APEX app can access the files)
• No big changes in architecture and also I can leverage my PL/SQL skills

All you need to do is,

• Grant required privileges to network file share to OS user who is running Oracle
• Create Directory in Oracle DB which is pointing to network fileshare
• Grant required access to APEX Parsing Schema

Regards,

Hari

@Hari_639

I have the oracle directory object created which maps to the network share, example named FILE_DIR

I created the following procedure to download the file.  I'm not quite sure where my oracle directory named "FILE_DIR" comes in?

How do i reference the directory in the procedure?

This is what i have so far.

create or replace procedure "DOWNLOAD_BFILE"

(file_in IN VARCHAR2,

directory_in IN VARCHAR2 default 'FILE_DIR')

    AS

      lob_loc    BFILE;

      v_mime     VARCHAR2 (48) DEFAULT 'application/txt';

      v_length   NUMBER;

begin   

      lob_loc := BFILENAME (UPPER (directory_in), file_in);

      v_length := DBMS_LOB.getlength (lob_loc);

      OWA_UTIL.mime_header (NVL (v_mime, 'application/octet'), FALSE);

      HTP.p ('Content-length: ' || v_length);

      HTP.p (   'Content-Disposition:  attachment; filename="'

             || SUBSTR (file_in, INSTR (file_in, '/') + 1)

             || '"'

            );

      OWA_UTIL.http_header_close;

      WPG_DOCLOAD.download_file (lob_loc);

end;

Christian, sorry to keep bugging, but i just can't get this to work.

Here is what i did so far:

1. Granted the os db user access to the existing file-share.

2. Created a Oracle directory object on the oracle DB

CREATE OR REPLACE DIRECTORY FILE_DIR AS '\\fileserver.net\apps\pdffiles'

4. Granted the Parsing schema, ANONYMOUS and APEX_PUBLIC_USER read access to the directory object FILE_DIR

5.  I validated that the directory object exists by doing this:

SELECT *

FROM all_directories

where directory_name = 'FILE_DIR'

It returns the results of the directory object details just fine.

6. I placed a file called test.txt at this location \\fileserver.net\apps\pdffiles so \\fileserver.net\apps\pdffiles\test.txt

7. I'm trying to validate that is has access the file using the directory object like so:

select dbms_xslprocessor.read2clob('FILE_DIR','test.txt')

from dual

Getting error: ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation

8. Created a new apex page and added a PL/SQL region and added the following code:

declare

      file_in VARCHAR2(2000);

      file_path varchar2(2000);

      directory_in VARCHAR2(2000);

      lob_loc    BFILE;

      v_mime     VARCHAR2 (48) :='application/txt';

      v_length   NUMBER;

begin   

      directory_in := 'FILE_DIR';

      file_in := 'text.txt';

      lob_loc := BFILENAME(UPPER (directory_in), file_in);

      v_length := DBMS_LOB.getlength (lob_loc);

      OWA_UTIL.mime_header (NVL (v_mime, 'application/octet'), FALSE);

      HTP.p ('Content-length: ' || v_length);

      HTP.p (   'Content-Disposition:  attachment; filename="'

             || SUBSTR (file_in, INSTR (file_in, '/') + 1)

             || '"'

            );

      OWA_UTIL.http_header_close;

      WPG_DOCLOAD.download_file (lob_loc);

end;

Getting error when i run the page:

ORA-22288: file or LOB operation GETLENGTH failed No such file or directory

• is_internal_error: true
• apex_error_code: APEX.REGION.UNHANDLED_ERROR
• ora_sqlcode: -22288
• ora_sqlerrm: ORA-22288: file or LOB operation GETLENGTH failed No such file or directory
• component.type: APEX_APPLICATION_PAGE_REGIONS
• component.id: 22344132454749454
• component.name: ppp
• error_backtrace: ORA-06512: at "SYS.DBMS_LOB", line 850 ORA-06512: at line 15 ORA-06512: at "SYS.DBMS_SYS_SQL", line 1926 ORA-06512: at "SYS.WWV_DBMS_SQL", line 1065 ORA-06512: at "APEX_040200.WWV_FLOW_DISP_PAGE_PLUGS", line 4658 ORA-06512: at "APEX_040200.WWV_FLOW_DISP_PAGE_PLUGS", line 3264

Any ideas on what i may be doing wrong?

My file share is on a windows server, my db server is on a UNIX server.  UNC(\\fileserver.net\apps\pdffiles) should be sufficient correct?

    @"Christian Neumueller-Oracle"