Excel File reading in PL-SQL has been tricky to say the least. Reason: Excel has it’s own encoding which is not easy to be interpreted, especially the xlsx file. But thankfully, now we have a tool provided by Oracle in R12 ( All the versions might not have it). Tool you are looking for is “wwv_flow_data_parser” which is accessible in apps schema using the synonym “apex_data_parser”. but first thing first, how to identify if the package is available.
Well, answer is simple. Just run the query and see if the query returns any records.
select *
from DBA_objects
where object_name = ‘WWV_FLOW_DATA_PARSER’
and object_type = ‘PACKAGE’;
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
If the above query returns the data, means your version of the EBS has the capability to read the .xlsx file. All you need to do is get the file content in a BLOB type variable/ column and use the below query.
Query used for Excel File Reading using PL-SQL
SELECT LINE_NUMBER, COL001,COL002 , COL003,COL004 , COL005
from table(
apex_data_parser.parse(p_in_blob_file, null, 1)
) ;
Here p_in_blob_file is the BLOB variable/ column, Second parameter ( having value null here) passed can have the name of the file but not mandatory to pass it. The last parameter ( having the value of 1) is sheet number of the excel.
In output, “LINE_NUMBER” in select column gives the row number of the excel, COL001, COL002 etc are the values of the columns in excel. Per Oracle, this column count can go up to 300. Output is a VARCHAR2(32767) for each of these col001, col002 etc. Once you have the output in a query/ cursor, you know how to handle it…
So now you know how to do the Excel File Reading using PL-SQL.. Go ahead and try your hands..
Reference:
support.oracle.com
