Oracle Database Gateway

November 25, 2009 PGS Team

PGS in cooperation with one of our customers has started a new project in BI area using a range of Oracle tools. There are a few core components in a system. Just to give you a brief overview in what we are doing. There is an ETL process realized with ODI that transfers data from a regular OLTP system into a new schema that is used in a reporting platform. The reporting system has been implemented as an interactive dashboard of Oracle Business Intelligence Enterprise Edition. There are of course multiple technical problems that has been solved. An example of such challenge was providing users access to historical reports saved as files in a directory system. It is necessary to mention that OBIEE doesn’t support such operations out of box as its main function is to present charts, tables and other informational units. To sum up – there is no possibility to provide a downloadable file list. And here we’ve made up a solution for that. One of the components of an Oracle Database is an Oracle Database Gateway. The module allows you to call stored procedures with an HTTP request via web browser. Of course there are a few limitations of the solution, mainly security issues, however the first tests seemed to be very promising. Our procedure should meet following requirements:

  • takes a report identifier as an argument
  • verifies internally that the report really exists
  • reads the content of the report from a file system
  • sends the content of the file in an HTTP response

Seems to be trivial but we need to remember that we’ve got some security limitation, eg. DBMS_LOB package is inaccessible for us. On the other hand we’ve got an access to Oracle Web Toolkit package. I won’t present in here the entire body of the procedure, however I will give you some tips how to solve certain problems.

Check properties of the file

utl_file.fgetattr(fileDirectory, fileName, ifExists, fileLength, bufferSize);

We can use UTL_FILE package and a procedure fgetattr. It takes to parameters: file directory and a file name. The rest of the paramers are output. We get information whether the file exists and what’s the size of the particular file.

Read the file in a binary mode

fileHandler := utl_file.fopen(fileDirectory, fileName, 'RB');
utl_file.get_raw(fileHandler, fileRawContent, fileLength);

Although the documentation says that only text files are supported in UTL_FILE package there is a way to open a binary file. First of all it must be opened with ‘RB’ argument that stands for ‘Read Binary’. After that you may read the file using get_raw procedure. The second argument is an output one and its type is RAW. This type causes a lot of problems as its conversion is not very easy.

RAW to BLOB conversion

INSERT INTO long_table(long_col) VALUES(conversionKey, fileRawContent);
INSERT INTO lob_table SELECT TO_LOB(long_col) FROM cnv_long_table;

The problem with RAW to BLOB conversion is that TO_LOB function can be used only in SELECT statements. Additionally you need to use physical tables rather than temporary ones or even a PL/SQL anonymous block. Anyway, double insert solves the problem of a time conversion however it takes some type to discover the proper way of doing it.

Send a response to a browser

wpg_docload.download_file( fileBlobContent );

The last step is well documented in the network and many examples can be found. The most important is the fact that download_file procedure requires BLOB data type.

Last posts