Loading a multi column excel/csv into oracle using Apex

This week i was asked about a simple requirement to upload a csv formatted file into Apex (well into a table using Apex as the tool to be precise). The only issue is the csv has 64 'columns' so the default tools you can use for this kind of thing do not work.

The solution?

Well you just use the file upload item with some clever post process plsql - however first you have to get past the confusion where the file upload seems to work fine but no data shows in WWV_FLOW_FILES..... (more on that in a bit)

Lets start at the beginning (in an existing app in my case but you could just as well create a new one)

We start with create page -> blank page-> next -> give it a name ('testpage' in my case) -> whatever tab options you want -> then finish

Now you have a blank page with nothing on it

Now edit that page to add a region

New region -> HTML -> HTML -> title (testregion) -> click create region

Now we still have a blank page, just with a testregion area on it (but empty)

Now to add a file browse item

Add Item -> File Browse -> give it a name (browser for me) ->next->next -> change storage type to WWV_FLOW_FILES -> next ->create item

Now we have a screen that looks like this

When you click browse you get the normal windows explorer interface and you can go and choose a file - when you do the field is populated with that filename.

Now we need to actually save this into the database - to do this is simple we just need to submit the page - the file browse item does the rest.

So lets add a page submit button

Add button -> next switch to among region items -> next -> give the button a name and label (i chose upload for both) -> then click create button as the default action for a button is submit page and there is nothing to change

Now when i run the page i see

Now if i browse again and choose a file and then click upload to submit the page, the page is submitted without error so that means its worked right?

Well lets go and have a look in the WWV_FLOW_FILES table (well view as i found out later......)

So the first thing i try is this

SQL> select count(*) from wwv_flow_files;

  COUNT(*)
----------
         0

Right... - that's working well :-(

Resolving that took me quite a while and a tremendous amount of frustration, i'll cut to the chase though and just tell you what was wrong.

Essentially WWV_FLOW_FILES has a vpd policy on it (it hasn't ts just a view with a where clause but the result is the same) - if you don't set your context to be the workspace you are using in Apex then you don't see anything.

The key clause on the end of the view is

 where security_group_id = wwv_flow.get_sgid

If i run that for my sqlplus login i get

SQL> select wwv_flow.get_sgid from dual;
         0

So how do i set it?

First i need to find my workspace is - i do this with this simple sql

select workspace, to_char(workspace_id)
from apex_workspaces;

The second column returned is the id which i then use to pass into a 'setter' procedure

SQL> exec wwv_flow_api.set_security_group_id(4096721095133068); -- (where the number is the value returned from the previous query)

PL/SQL procedure successfully completed.

Now when i query it

SQL> select FILENAME from wwv_flow_files where CREATED_ON > sysdate-1/24
  2  /

FILENAME
--------------------------------------------------------------------------------
post_install_acf_part1.csv

It shows up fine - the damn thing was there all along i just hadn't set my 'context' up  -aargh

So phase 1 of what i wanted is complete - now i want to take that csv that was uploaded and post process it into my real destination table (the one with loads of columns)

First i need to actually create the table (long i know but this is the pain when the csv has loads of columns)

  CREATE TABLE "APEX_WORKSPACE_ER"."RICH_TEST"

   (    "ID" NUMBER,

        "BUSINESS_DATE" DATE,

        "SOURCE_SYS_NAME" VARCHAR2(1 CHAR),

        "DELTA_CODE" VARCHAR2(1 CHAR),

        "DESTINCLUDEEMIR" VARCHAR2(1 CHAR),

        "DESTSTATUSEMIR" VARCHAR2(1 CHAR),

        "DESTINCLUDEREMIT" VARCHAR2(1 CHAR),

        "DESTSTATUSREMIT" VARCHAR2(30 CHAR),

        "OURENTITYID" VARCHAR2(30 CHAR),

        "OURENTITYTRADERUSERNAME" VARCHAR2(30 CHAR),

        "TRADINGCOUNTERPARTYID" VARCHAR2(30 CHAR),

        "EXCHANGEFLAG" VARCHAR2(1 CHAR),

        "DELEGATED" VARCHAR2(1 CHAR),

        "THIRDPARTY" VARCHAR2(1 CHAR),

        "TRADINGCTRPRTYTRADERUSERNAME" VARCHAR2(1 CHAR),

        "BENEFICIARYID" VARCHAR2(1 CHAR),

        "DELEGATEDBENEFICIARYID" VARCHAR2(1 CHAR),

        "DELEGATEDBENEFICIARYIDTYPE" VARCHAR2(1 CHAR),

        "TRADINGCAPACITY" VARCHAR2(1 CHAR),

        "DELEGATEDTRADINGCAPACITY" VARCHAR2(1 CHAR),

        "BUYSELLFLAG" VARCHAR2(1 CHAR),

        "INITIATORAGGRESSOR" VARCHAR2(1 CHAR),

        "ISBOOKTRANSFER" VARCHAR2(1 CHAR),

        "STANDARDPRODUCTID1" VARCHAR2(30 CHAR),

        "INTERNALPRODUCT" VARCHAR2(1 CHAR),

        "TRADETYPE" VARCHAR2(30 CHAR),

        "UNDERLYING" VARCHAR2(1 CHAR),

        "INDEXVALUE" VARCHAR2(1 CHAR),

        "NOTIONALCURRENCY1" VARCHAR2(30 CHAR),

        "CONTRACTDESCRIPTION" VARCHAR2(30 CHAR),

        "CONTRACTTRADINGHOURS" VARCHAR2(30 CHAR),

        "TRADEID" VARCHAR2(255 CHAR),

        "SOURCESYSTEMTRADEID" VARCHAR2(30 CHAR),

        "VENUEOFEXECUTION" VARCHAR2(128 CHAR),

        "DEALTPRICE" VARCHAR2(40 CHAR),

        "PRICENOTATION" VARCHAR2(30 CHAR),

        "NOTIONALAMOUNT" VARCHAR2(40 CHAR),

        "PRICEMULTIPLIER" VARCHAR2(40 CHAR),

        "QUANTITY" VARCHAR2(40 CHAR),

        "QUANTITYUNIT" VARCHAR2(30 CHAR),

        "DERIVATIVETYPE" VARCHAR2(10 CHAR),

        "EXECUTIONTIMESTAMP" VARCHAR2(40 CHAR),

        "TERMINATIONDATE" VARCHAR2(1 CHAR),

        "LINKEDTRANSACTIONID" VARCHAR2(128 CHAR),

        "LINKEDORDERID" VARCHAR2(128 CHAR),

        "DELEGATEDLINKEDTRANSACTIONID" VARCHAR2(1 CHAR),

        "VOICEFLAG" VARCHAR2(1 CHAR),

        "COMMODITY" VARCHAR2(30 CHAR),

        "DELIVERYPROFILENUMBER" VARCHAR2(40 CHAR),

        "DELIVERYPOINT" VARCHAR2(30 CHAR),

        "LOADTYPE" VARCHAR2(30 CHAR),

        "DURATION" VARCHAR2(1 CHAR),

        "DAYSOFWEEK" VARCHAR2(30 CHAR),

        "DELIVERYSTARTDATE" VARCHAR2(30 CHAR),

        "DELIVERYENDDATE" VARCHAR2(30 CHAR),

        "REMITLASTDATETIME" VARCHAR2(30 CHAR),

        "LOADDELIVERYINTERVALS" VARCHAR2(30 CHAR),

        "QUANTITYOFLEG" VARCHAR2(30 CHAR),

        "QUANTITYUNITOFLEG" VARCHAR2(1 CHAR),

        "PRICEPERTIMEINTERVALQUANTITIES" VARCHAR2(1 CHAR),

        "PUTCALLFLAG" VARCHAR2(1 CHAR),

        "REMITOPTIONEXERCISEDATE" VARCHAR2(1 CHAR),

        "OPTIONSTYLE" VARCHAR2(1 CHAR),

        "STRIKEPRICE" VARCHAR2(1 CHAR),

        "SOURCESYSTEMTRANSTATUS" VARCHAR2(30 CHAR),

         CONSTRAINT "RICH_TEST_PK" PRIMARY KEY ("ID"));

Now a sequence to generate the PK

CREATE SEQUENCE  "APEX_WORKSPACE_ER"."RICH_TEST_SEQ"  

MINVALUE 1 MAXVALUE 9999999999999999999999

999999 INCREMENT BY 1 START WITH 201 CACHE 20 NOORDER  NOCYCLE;

A trigger to create the pk for me (note this is 11g not 12c)

  CREATE OR REPLACE TRIGGER "APEX_WORKSPACE_ER"."BI_RICH_TEST"

  before insert on "RICH_TEST"

  for each row

begin

  if :new."ID" is null then

    select "RICH_TEST_SEQ".nextval into :new."ID" from sys.dual;

  end if;

end;

/

Now i need a function to do hex to decimal conversion as a prereq for some code i need later - i borrowed that from here 

CREATE OR REPLACE FUNCTION hex2dec (hexnum IN CHAR) RETURN NUMBER IS

  i                 NUMBER;

  digits            NUMBER;

  result            NUMBER := 0;

  current_digit     CHAR(1);

  current_digit_dec NUMBER;

BEGIN

  digits := LENGTH(hexnum);

  FOR i IN 1..digits LOOP

     current_digit := SUBSTR(hexnum, i, 1);

     IF current_digit IN ('A','B','C','D','E','F') THEN

        current_digit_dec := ASCII(current_digit) - ASCII('A') + 10;

     ELSE

        current_digit_dec := TO_NUMBER(current_digit);

     END IF;

     result := (result * 16) + current_digit_dec;

  END LOOP;

  RETURN result;

END hex2dec;

/

OK - still with me - now i have everything i need in the database i just need to create a plsql post process to fire after upload to parse the blob that just created in WWV_FLOW_FILES and turn that into rows and columns in my new table - so lets do that.

So i click on

create process -> PL/SQL-> give it a name (dodata in my case) -> then paste in the plsql (see below for that code block) and then click create process

**** note - the framework for this code came from here

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;  

--v_first_line_done boolean := false;  

v_error_cd number :=0;    

BEGIN    

 

select    

 blob_content    

 into v_blob_data    

 from wwv_flow_files    

 where name = :BROWSER;    

v_blob_len := dbms_lob.getlength(v_blob_data);    

v_position := 1;    

WHILE ( v_position <= v_blob_len )    

 LOOP    

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

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

 v_line := v_line || v_char;    

 v_position := v_position + c_chunk_len;    

 IF v_char = CHR(10) THEN    

  v_line := REPLACE (v_line, ',', ':');    

  v_data_array := wwv_flow_utilities.string_to_table (v_line);  

IF v_sr_no = 1 THEN

null;

ELSE

APEX_DEBUG.MESSAGE(v_sr_no);

insert into rich_test (

 BUSINESS_DATE,

 SOURCE_SYS_NAME,

 DELTA_CODE  ,

 DESTINCLUDEEMIR,

 DESTSTATUSEMIR,

 DESTINCLUDEREMIT,

 DESTSTATUSREMIT,

 OURENTITYID   ,

 OURENTITYTRADERUSERNAME,

 TRADINGCOUNTERPARTYID ,

 EXCHANGEFLAG  ,

 DELEGATED  ,

 THIRDPARTY ,

 TRADINGCTRPRTYTRADERUSERNAME,

 BENEFICIARYID        ,

 DELEGATEDBENEFICIARYID         ,

 DELEGATEDBENEFICIARYIDTYPE  ,

 TRADINGCAPACITY         ,

 DELEGATEDTRADINGCAPACITY   ,

 BUYSELLFLAG                              ,

 INITIATORAGGRESSOR                          ,

 ISBOOKTRANSFER                                   ,

 STANDARDPRODUCTID1                                ,

 INTERNALPRODUCT                                   ,

 TRADETYPE                                         ,

 UNDERLYING                                        ,

 INDEXVALUE                                       ,

 NOTIONALCURRENCY1                                ,

 CONTRACTDESCRIPTION                               ,

 CONTRACTTRADINGHOURS                             ,

 TRADEID                                        ,

 SOURCESYSTEMTRADEID                             ,

 VENUEOFEXECUTION                             ,

 DEALTPRICE                                      ,

 PRICENOTATION                                 ,

 NOTIONALAMOUNT                                ,

 PRICEMULTIPLIER                            ,

 QUANTITY                                  ,

 QUANTITYUNIT                             ,

 DERIVATIVETYPE                               ,

 EXECUTIONTIMESTAMP                            ,

 TERMINATIONDATE                                ,

 LINKEDTRANSACTIONID                         ,

 LINKEDORDERID                               ,

 DELEGATEDLINKEDTRANSACTIONID               ,

 VOICEFLAG                                ,

 COMMODITY                                ,

 DELIVERYPROFILENUMBER                     ,

 DELIVERYPOINT                              ,

 LOADTYPE                                      ,

 DURATION                                       ,

 DAYSOFWEEK                                     ,

 DELIVERYSTARTDATE                                  ,

 DELIVERYENDDATE                                    ,

 REMITLASTDATETIME                                ,

 LOADDELIVERYINTERVALS                             ,

 QUANTITYOFLEG                                   ,

 QUANTITYUNITOFLEG                                 ,

 PRICEPERTIMEINTERVALQUANTITIES                 ,

 PUTCALLFLAG                                     ,

 REMITOPTIONEXERCISEDATE                         ,

 OPTIONSTYLE                                  ,

 STRIKEPRICE                                ,

 SOURCESYSTEMTRANSTATUS                    

)

values 

(to_date(v_data_array(1),'DD/MM/YYYY'), v_data_array(2), v_data_array(3), v_data_array(4), v_data_array(5), v_data_array(6), v_data_array(7),v_data_array(8),v_data_array(9),v_data_array(10),

v_data_array(11), v_data_array(12), v_data_array(13), v_data_array(14), v_data_array(15), v_data_array(16), v_data_array(17),v_data_array(18),v_data_array(19),v_data_array(20),

v_data_array(21), v_data_array(22), v_data_array(23), v_data_array(24), v_data_array(25), v_data_array(26), v_data_array(27),v_data_array(28),v_data_array(29),v_data_array(30),

v_data_array(31), v_data_array(32), v_data_array(33), v_data_array(34), v_data_array(35), v_data_array(36), v_data_array(37),v_data_array(38),v_data_array(39),v_data_array(40),

v_data_array(41), v_data_array(42), v_data_array(43), v_data_array(44), v_data_array(45), v_data_array(46), v_data_array(47),v_data_array(48),v_data_array(49),v_data_array(50),

v_data_array(51), v_data_array(52), v_data_array(53), v_data_array(54), v_data_array(55), v_data_array(56), v_data_array(57),v_data_array(58),v_data_array(59),v_data_array(60),

v_data_array(61), v_data_array(62), v_data_array(63), v_data_array(64))

  ; 

END IF;

   

   v_line := NULL;

 v_sr_no := v_sr_no + 1;  

 

 END IF;    

 END LOOP;  

DELETE FROM WWV_FLOW_FILES where name = :BROWSER;  

 

END;  

When you now run the screen and click upload instead of it instantly returning it now fires the post process code , spins around for a while. Once it finishes i query the destination tables and see

SQL>  select count(*) from apex_workspace_er.rich_test;

  COUNT(*)

----------

       200

So it's worked fine. So we have our solution!

Before i finish off a few comments in general about the plsql post process block above

1) You have to use the :BROWSER (or whatever you named it) page item in two places in the code above to identify the file from WWV_FLOW_FILES based on what is on screen and laso to delete it at the end (that's optional).

2) Date columns seem to be a pain - if there is a formatting error you dont get much hep in which column is the problem one mismatch lengths are fine but date conversion is a pain

3) i added in  an APEX_DEBUG.MESSAGE(v_sr_no); - this can be useful in debug mode to see which line is a problem

4) it doesn't seem very quick - a bulk/forall may be required to help with this

Hope that's useful for someone - in particular the first bit with what is visible in WWV_FLOW_FILES!