Muhammad Nadeem Chaudhry's Blog


Uploading CSV using Oracle Application Express (APEX)

Posted in Uncategorized by Muhammad Nadeem Chaudhry on July 18, 2011
Sometimes we need the flexibility to upload the data to a table from CSV sheet. The can be implemented very well in JSP and other application. I was having this requirement to implement the same in Oracle Application Express (APEX).

I was going though one of the blog by Ittichai, for the same and he has given a very clean steps for getting this work. I followed the steps and it worked for me. I have documented the similar steps below as per my understanding. Below are the details.

Pre-requisite:

File should be in CSV Format.

First you will need to identify the workspace ID.

SQL> col WORKSPACE_ID format 999999999999999

SQL> select workspace, schemas, workspace_id from apex_workspaces;

You won’t be able to see anything yet.

SQL> select id, flow_id, name, filename from wwv_flow_files;

But after setting the workspace ID, you should be able to see its contents.

SQL> exec wwv_flow_api.set_security_group_id(‘858728211457679’);

PL/SQL procedure successfully completed

SQL> select id, flow_id, name, filename from wwv_flow_files;

Implementing data upload using CSV sheet

Following is the table detail which I want to populate from data in CSV sheet.

SQL> DESC Table_Name;

1) Created a “File Browse” item on a page and having internal name it P35_UPLOAD.

2) Created a button having internal name as “Upload“. This button is used to upload and process the data in the CSV sheet.

3) Created a conditional branch when upload button is pressed, it should branch to the same page (35 in my case)

4) When we select an CSV sheet using browse button and click on submit button, it will branch to same page (indirectly we are saying that its going to refresh the page). In such case even if there is no code written in the backend, APEX is going to load the CSV sheet in a BLOB format into a table wwv_flow_files.

This is the internal table used by APEX. Our task is to read this table and get the required row which got inserted into this table. With each upload 1 row will get inserted into the table. One of the column of the table (BLOB_CONTENT) is BLOB where the actual CSV sheet is uploaded. All other columns are metadata about CSV.

When we upload an CSV sheet, a random name will get generated for the file in the form FXXXXX/<file_name_you_uploaded>. You can use this to get the file details. Also when you refresh the page, it will upload the file to this table wwv_flow_files, but as soon as refresh completed and it displays the page back again, that row will get deleted again. So you have to get that row processed immediately using the code that you will write when submit button is pressed.

5) Following is the code for the same

Before using the below code, make sure you have a function hex_to_decimal  in your database. The code for the same is as given below. 

create or replace function hex_to_decimal 

–this function is based on one by Connor McDonald 

http://www.jlcomp.demon.co.uk/faq/base_convert.html 

( p_hex_str in varchar2 ) return number 

is

v_dec   number; 

v_hex   varchar2(16) := ‘0123456789ABCDEF’; 

begin

v_dec := 0; 

for indx in 1 .. length(p_hex_str) 

loop 

v_dec := v_dec * 16 + instr(v_hex,upper(substr(p_hex_str,indx,1)))-1; 

end loop; 

return v_dec; 

End hex_to_decimal;

Below is the code for uploading the CSV sheet. 

DECLARE

v_blob_data       BLOB; 

v_blob_len        NUMBER; 

v_position        NUMBER; 

v_raw_chunk       RAW(10000); 

v_char      CHAR(1);

c_chunk_len   number       := 1; 

v_line        VARCHAR2 (32767)        := NULL; 

v_data_array      wwv_flow_global.vc_arr2; 

v_rows number; 

v_sr_no number := 1; 

BEGIN

delete from MACS; 

      — Read data from wwv_flow_files</span> 

Select blob_content into v_blob_data 

from wwv_flow_files 

where last_updated = (select max(last_updated) from wwv_flow_files where UPDATED_BY = :APP_USER) 

and id = (select max(id) from wwv_flow_files where updated_by = :APP_USER); 

v_blob_len := dbms_lob.getlength(v_blob_data); 

v_position := 1; 

     — Read and convert binary to char</span> 

WHILE ( v_position <= v_blob_len ) LOOP 

v_raw_chunk := dbms_lob.substr(v_blob_data,c_chunk_len,v_position); 

v_char :=  chr(hex_to_decimal(rawtohex(v_raw_chunk))); 

v_line := v_line || v_char; 

v_position := v_position + c_chunk_len; 

     — When a whole line is retrieved </span> 

IF v_char = CHR(10) THEN

    — Convert comma to : to use wwv_flow_utilities </span> 

v_line := REPLACE (v_line, ‘,’, ‘:’); 

    — Convert each column separated by : into array of data </span> 

v_data_array := wwv_flow_utilities.string_to_table (v_line); 

   — Insert data into target table </span> 

EXECUTE IMMEDIATE ‘insert into MACS (ITEM,MAC) 

values (:1,:2)’

USING 

v_sr_no, 

v_sr_no;

    — Clear out 

v_line := NULL; 

v_sr_no := v_sr_no + 1; 

END IF; 

END LOOP; 

END;

Create a new PLSQL process under Processes section in APEX and put this code under the same. Make the process run when upload button is pressed. Data in CSV sheet will get loaded into table. Hope this helps !!

Reference:

http://oraexplorer.blogspot.com/2007/11/apex-to-upload-text-file-and-write-into.html

Creating Help in Oracle APEX 4.2

Posted in Uncategorized by Muhammad Nadeem Chaudhry on July 15, 2011

Creating Oracle Database Link

Posted in Uncategorized by Muhammad Nadeem Chaudhry on July 11, 2011

create [public] database link db_link_name
connect to      user_name
identified by   password
using              Service_name

service_name must be resolvable on the server, for example by entering it into the tnsnames.ora file. A database link is required for various purposes, such as primary key materialized view replication.  This will create one sided link as specified in clause.

For further inform please visit http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_admin002.htm

RTF Template Based Reports in Oracle APEX – Oracle BI Publisher

Posted in Uncategorized by Muhammad Nadeem Chaudhry on July 8, 2011

–          Set the report printing server as follows:

 

–          Create JDBC Connection.

–          Create report query in Oracle BI Publisher.

–          Generate report template.

–          Download template & format that report. Upload modified template.

Oracle APEX – HTTP Access

Posted in Uncategorized by Muhammad Nadeem Chaudhry on July 7, 2011

By default Apex page of XE, it was set to local access by default.  From Apex, check the link  “Home>Administration>Manage HTTP Access”.  In addition, you can also execute this command below using SYS account:

exec dbms_xdb.setListenerLocalAccess(false);

Change Password (Navigation Bar Entry) In Oracle APEX

Posted in Uncategorized by Muhammad Nadeem Chaudhry on July 4, 2011

Create navigation bar URL as:

javascript:popupURL(‘f?p=4350:58:&SESSION.:::58:F4350_P58_USER_NAME::APP_USER’);