Transcript Document
How Do I Load Data … Let Me Count The Ways
Karen Cannell [email protected]
TH Technology http://www.thtechnology.com
TH Technology
How Do I Load Data? : Agenda
Oracle Data Loading Options DBAs - Developers - End Users APEX Data Loading Options File Upload Data Load Wizard
2
XLS Upload – APEX Listener WebSheets – Copy/Paste, Add Row
How Do I Load Data …
TH Technology
About Me …
Karen Cannell ~ Consultant, TH Technology SW Engineer 25+ years, Oracle since 1994. Building APEX apps for government, medical, engineering industries.
Leveraging the Oracle 10
g
,11
g
(now 12
c
) suite of tools
Beginning Application Express
, APress, 2011
Agile Application Express
, APress, 2011 Editor, ODTUG Technical Journal
Volunteer to author ODTUG Journal Articles!
Using APEX since the HTMLDB beginning
How Do I Load Data …
TH Technology
About You … ( Audience Background)
New to APEX?
APEX Experience?
Previous Tools?
APEX Training?
Version 2.0? 3.1 ? 3.2 ? 4.1?
How Do I Load Data …
TH Technology
How DO I Load Data into Oracle?
Load Data Into Oracle 7,340,000 hits SQL Loader Oracle Database Utilities Load Excel Data into Oracle How Do I Load Data …
TH Technology
How DO I Load Data into Oracle?
Load Data Into APEX 317,000 hits Import Excel Data … APEX Data Loader Load Text File into APEX How Do I Load Data …
TH Technology
How DO I Load Data into Oracle?
Load XLS Data Into APEX 1,830,000 hits Import Excel File … APEX Data Loader Load Excel Data … How Do I Load Data …
TH Technology
Load Data into Oracle - Options
What Are the Options?
Commercial Solutions - Excel-DB, Quickload Warehouse Builder, ETL Tools Oracle Data Integrator http://www.oracle.com/technetwork/middleware/data integrator/overview/index.html
Oracle Database Utilities Oracle Data Pump SQL Loader External Tables
How Do I Load Data …
Load Data into Oracle, cont’d
TH Technology ODBC Connections IDEs w/built-In Data Loaders Custom Code: Java PL/SQL Using UTL_FILE Scripting from XLS Columns Java and PL/SQL Perl Other
How Do I Load Data …
Let’s Focus …
TH Technology
How Do I Load Data …
TH Technology
Load Data into APEX
Import Excel Data into Oracle … APEX Data Loader “Load Excel Data”
application that can bulk process the insert, update and delete on all object data into and 95% Say XLS but Really are CSV (SOAP) API.
How Do I Load Data …
TH Technology
Our Problem
“Load Data into Oracle” 40% Fixed-Format Text Files 60% Excel Spreadsheets Several XLS Formats 200+ Files Monthly Locations All Over the World Some Networked, Some Not Varying Volume of Data
How Do I Load Data …
TH Technology
Which to Choose?
It Depends
Application Source Target ( Table?) Amount of Data How Often ?
1 or Many
Audience
End Users
Resources
$$
Time
$$
How Do I Load Data …
TH Technology
Which Make Sense for APEX ?
Oracle Database Utilities PL/SQL IDE No Additional Licenses PL/SQL Code No Complex Setup Developer-Friendly End User Friendly
How Do I Load Data …
TH Technology
Oracle Database Utilities
Oracle Data Pump Oracle DB to Oracle DB Faster than EXP and IMP EXPDP 2x faster than EXP IMPDP 15-45x faster than IMP Jobs can be Restarted Supports Network Import and Export Load one instance form another Remote export
How Do I Load Data …
TH Technology
Oracle Database Utilities, cont’d
SQL Loader External files into Tables Variety of formats Filtering Multiple Table Load in One Load Session Conventional Direct Path External Table Load
How Do I Load Data …
TH Technology
Oracle Database Utilities, cont’d
External Tables External files into Tables Variety of Formats Preprocessing Capability
External Tables to Pull RPT Files CSV Files - That We Can Get How Do I Load Data …
TH Technology
Oracle Database – Database Link
Don’t Forget the Database Link!
SELECT … FROM table@db_link
Simple, Direct Requires Communication Ask!
How Do I Load Data …
Use your IDE
SQL Developer PL/SQL Developer Toad Others … Best for One-Time Load TH Technology
How Do I Load Data …
TH Technology
SQL Developer – Data Load Option Import Data …
Wizard
How Do I Load Data …
TH Technology
SQL Developer – Import Data …
Import File Formats .XLS / XLSX .CSV
.TSV (tab) .DSV ( SQL Server)
How Do I Load Data …
TH Technology
SQL Developer – Import Data …
• • Import Wiz • Detects File type Delimiter Skip Row
How Do I Load Data …
TH Technology
SQL Developer – Import Data …
INSERT or Generate INSERT Script)
How Do I Load Data …
TH Technology
SQL Developer – Import Data …
Select Columns to Import
How Do I Load Data …
TH Technology
SQL Developer – Import Data …
Map Source to Table Columns
How Do I Load Data …
TH Technology
SQL Developer – Import Data …
Verify
How Do I Load Data …
TH Technology
SQL Developer – Import Data …
Finished!
XLS and XLSX* File Load in Very Few Clicks!
*XLSX in SQL Developer 3.1
How Do I Load Data …
TH Technology
SQL Developer – Import Data …
• • What About XLSX?
SQL Dev 3.0 – No SQL Developer 3.1 – Yes
XLS/XLSX File Load in Very Few Clicks!
How Do I Load Data …
TH Technology
SQL Developer – Import Data … Pros
One Shot Data Load Delimiter (, TAB, others) Flexible Columns Flexible Mapping XLS File Can be Faster than APEX Data Load Wizard
Cons
Table Must Be Defined Cannot Integrate into App Tedious to Repeat IDE Specific One Shot Data Load Not for End Users
How Do I Load Data …
TH Technology
APEX Data Load/Unload Utility
APEX From the Beginning Easy Load of Spreadsheet or XML Data New or Existing Table Upload File Or Copy/Paste Delimiter Option Column by Column Selection
How Do I Load Data …
TH Technology
APEX Data Load/Unload Utility How Do I Load Data …
TH Technology
APEX Data Load/Unload Utility 1 st Row Column Heading How Do I Load Data …
TH Technology
APEX Data Load/Unload Utility How Do I Load Data …
TH Technology
APEX Data Load/Unload Utility Specify PK and Trigger Finish How Do I Load Data …
TH Technology
APEX Data Load Utility Pros
Simple: Point-Click Done Flexible Format Spreadsheet Convenience Creates Table Copy/Paste Option
Cons
Many Steps Single Table No XLS Upload (Must Copy/Paste or Save as CSV) One-Time Not for End Users
How Do I Load Data …
TH Technology
What About the End User?
APEX Standard File Browse + Custom Code Data Load Wizard Wizard APEX Manual Data Load Wizard End User-ized APEX Web Sheet Data Grid Copy/Paste, Data Entry APEX Listener XLS Upload File Browse + XLS2COLLECTION
How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
Most Common (Still) File Browse Item Process (to Staging) Process To Destination Table(s) MANY MANY OPTIONS …
All Require Custom Code How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
File Browse Item WWV_FLOW_FILES – Original Way BLOB column – New Option w APEX 4+
How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
WWV_FLOW_FILES – Traditional Way File uploads to APEX Table WWV_FLOW_FILES Move Records to a Custom Table (i.e. Staging Table) Clean up!
Files accumulate in APEX_FILES Tablespace
How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
BLOB Column – New w/ APEX 4 File Stored in BLOB Column of Table Specified in
Automatic Row Processing
(DML) Process Column Specified in
Item Source
Table Must Have BLOB Filename MIME Type CHARSET
Saves an Archiving Step How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
BLOB Column – New w/ APEX 4
How Do I Load Data …
TH Technology
APEX File Browse + Custom Code WWV_FLOW_FILES How Do I Load Data …
TH Technology
APEX File Browse + Custom Code LOCAL_UPLOAD_FILES How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
Parse / Load Process Read Blob into local BLOB variable Read Records from Blob Load Records into Destination Objects
How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
Reusable Parse/ Load Process File Browse Item Load File to WWV_FLOW_FILE Generic Parse File Package Generic File Upload Utility Generic Store to APEX Collection Process Code per Use/Table/Format
Custom Code, But Less of It How Do I Load Data …
TH Technology
Generic Parse File Utility Package
Parse File to Collection Load Collection to Specified Table Columns Must Match, or Adjust Code Reusable Code, Pages for Any Upload File/Table Saves One Part of Custom Code Custom Processing from Stage Table Required
Custom Code, But Less of It How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
Parse File Process Read Blob into local BLOB variable BEGIN SELECT blob_content
Or BLOB
INTO l_blob
column
FROM wwv_flow_files
from your
WHERE name=p_file_name;
BLOB
EXCEPTION
table
WHEN NO_DATA_FOUND THEN raise_application_error(-20000, END; 'File not found, id='||p_file_name);
How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
Parse File Process Read Records from Blob Variable TYPE varchar2_t IS TABLE OF VARCHAR2(32767) INDEX BY binary_integer; l_records VARCHAR2_T; … get_records(l_blob,l_records); … -- Initialize the APEX collection apex_collection.create_or_truncate_collection( p_collection_name);
How Do I Load Data …
IS l_sep l_last VARCHAR2(2) := chr(13)||chr(10); INTEGER; l_current INTEGER; BEGIN IF (NVL(DBMS_LOB.INSTR(p_blob, utl_raw.cast_to_raw(l_sep),1,1),0)=0) THEN l_sep := chr(10); END IF; l_last := 1; LOOP l_current := dbms_lob.INSTR( p_blob, utl_raw.cast_to_raw(l_record_separator), l_last, 1 ); EXIT WHEN (nvl(l_current,0) = 0); p_records(p_records.count+1) := utl_raw.cast_to_varchar2( DBMS_LOB.SUBSTR(p_blob,l_current-l_last,l_last)); l_last := l_current+length(l_record_separator); END LOOP; END get_records; How Do I Load Data …
TH Technology
LOOP l_current := dbms_lob.INSTR( p_blob, utl_raw.cast_to_raw(l_record_separator), l_last, 1 ); EXIT WHEN (nvl(l_current,0) = 0); p_records(p_records.count+1) := utl_raw.cast_to_varchar2( DBMS_LOB.SUBSTR(p_blob,l_current-l_last,l_last)); l_last := l_current+length(l_record_separator); END LOOP; Loop to read records from the BLOB, line by line (separator to separator) How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
Parse File Process: Read from Blob , Load Collection FOR i IN 2..l_records.count LOOP csv_to_array(l_records(i),l_record); l_seq_id := apex_collection.add_member(p_collection_name,'dummy'); FOR i IN 1..l_record.count LOOP apex_collection.update_member_attribute( p_collection_name=> p_collection_name, p_seq => l_seq_id, p_attr_number => i, p_attr_value => l_record(i)); END LOOP; END LOOP ;
How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
Parse File Process: Read Blob , Load Collection Insert from Collection to Table … l_ddl := ‘INSERT INTO '||p_table_name||' '|| ‘SELECT '||v(p_columns_item)||' '|| ‘FROM apex_collections '|| ‘WHERE seq_id >= 1 and collection_name='''|| p_collection_name||''''; EXECUTE IMMEDIATE l_ddl; …
How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
Parse File Process: Read Blob , Load Collection, Insert, Remove File -- Cleanup file!
DELETE FROM wwv_flow_files WHERE name=p_file_name;
How Do I Load Data …
TH Technology
APEX File Browse + Custom Code
Process to Destination Table(s) INSERT /* +append */ … SELECT … FROM staging MERGE statement Whatever is Required for Your Business
Custom Code How Do I Load Data …
File Browse & Custom Code Pros
Custom Code Custom Solution
Cons
Custom Code Resources Maintenance One-of Solution TH Technology
How Do I Load Data …
TH Technology
Data Load Wizard Wizard
The SQL Workshop Data Load Utility End-User-ized Page Type Creates a Shared Component
How Do I Load Data …
Data Load Wizard Wizard
Wizard to Create A Data Load Wizard Create Page Data Loading TH Technology
How Do I Load Data …
Data Load Wizard Wizard
TH Technology
How Do I Load Data …
Data Load Wizard Wizard
Define Table Lookups TH Technology
How Do I Load Data …
TH Technology
Data Load Wizard Wizard
Add Transforms To Upper/Lower, R/L/Trim, Single Whitespace
How Do I Load Data …
Data Load Wizard Wizard
Page/Region/Breadcrumb Names TH Technology
How Do I Load Data …
Data Load Wizard Wizard
Define Tabs (if any) TH Technology
How Do I Load Data …
Data Load Wizard Wizard
Define Branches TH Technology
How Do I Load Data …
Data Load Wizard Wizard
Confirmation TH Technology
How Do I Load Data …
Data Load Wizard - Result
Working Upload for our USERS table TH Technology
How Do I Load Data …
Data Load Wizard
End User Sees: Data Load Progression Chance to Select Row to Include/Ignore Failed Rows at the End Chance to Back Out Load?
Chance to Reload Clean?
TH Technology
How Do I Load Data …
TH Technology
Data Load Wizard
Upload OR Copy/Paste Data Limit == Specify Delimiter Specify Enclosed By Specify Currency, Group Separator, Decimal Characters
How Do I Load Data …
Data Load Wizard - Options
TH Technology
How Do I Load Data …
TH Technology
Data Load Wizard – Pro’s/Con’s Pros
Flexible Form Delimiter Option 1 st Row Heading Opt Currency – Group Separator – Decimal Mapping Built In Repeatable for End User Reusable for Developer
Cons
Too Much for End User??
No XLS Copy/Paste Size Limit Upload Size Limit ??
Single Transform per Column Home
How Do I Load Data …
TH Technology
Data Load Wizard Wizard
Is It Foolproof?
No:
Ex: RPT file …. What does this means?
Too Many Columns?
Too Few?
How Do I Load Data …
Data Load Wizard Wizard
Can I trick it into taking an XLS?
No: TH Technology
How Do I Load Data …
TH Technology
Data Load Wizard
Can I Make it “Better”?
Try: Default/Eliminate All Options Eliminate Load/Unload Column Mapping Catch/Warn of File Type, # Columns Errors Load the Collection Behind the Schemes
How Do I Load Data …
TH Technology
Data Load Wizard – Behind the Scenes
Usual Expected Items FILE_BROWSE
How Do I Load Data …
TH Technology
Data Load Wizard – P 2
Parse Uploaded Data Single Process of Type
Parse Uploaded Data
List of Parameters Fires upon
Next How Do I Load Data …
TH Technology
How Do I Load Data …
TH Technology
Data Load Wizard – P 3
Process Uploaded Data Single Process of Type
Process Uploaded Data
List of Parameters Fires upon
Next How Do I Load Data …
Data Load Wizard – P 3
Session Info Collections Character Columns Numeric Columns Date Columns TH Technology
How Do I Load Data …
TH Technology
How Do I Load Data …
TH Technology
Data Load Wizard Wizard Pros
In-App Straightforward Reusable No Custom Code for Data Load into DB
Cons
Too Many End User Steps Still Need Custom Processing Code for Staging to Tables
IF get P1
Finish by Loading Collection, have a Two-Step File Upload for End Users!
How Do I Load Data …
APEX Listener XLS Upload
File Browse Item Load File to WWV_FLOW_FILE Parsing File Custom Code per Use/Table/Format Generic File Upload Utility Custom Process to Destination Tables TH Technology
How Do I Load Data …
TH Technology
APEX Listener XLS Upload
Parse File to Collection Loads Collection to Specified Table Table Exist or Not Options Reusable for Any Upload File/Table Saves One Part of Custom Code Custom Processing from Stage Table Required
How Do I Load Data …
APEX Listener XLS Upload
Demo TH Technology
How Do I Load Data …
TH Technology
XLS Upload via APEX Listener
Must Deploy via APEX Listener Current Release 1.1.3… Allows Upload of XLS File into a Collection Best Reference: (from RMOUG 2010) http://krisrice.blogspot.com/2010/02/another apex-listener-ea-more-knobs-to.html
How Do I Load Data …
TH Technology
XLS Upload via APEX Listener
Install APEX Listener Deploy Edit apex_config.xml
Add Configuration Choices:
How Do I Load Data …
TH Technology
XLS Upload via APEX Listener
Configuration Options: Turn Feature On:
How Do I Load Data …
TH Technology
XLS Upload via APEX Listener
Configuration Options: Specify Name for All Collections:
How Do I Load Data …
TH Technology
XLS Upload via APEX Listener
File Browse Item WWV_FLOW_FILES option Page Item Button Name or REQUEST = XLS2COLLECTION Report Region:
SELECT * FROM apex_collections WHERE collection_name = ‘MY_COLLECTION’ How Do I Load Data …
TH Technology
XLS Upload via APEX Listener How Do I Load Data …
TH Technology
XLS Upload via APEX Listener How Do I Load Data …
TH Technology
XLS Upload via APEX Listener
“Files ending with .xls or .xlsx will be processed.” BUT .xls process Macro-Enabled .xls process
.
XLSX do NOT Process
as of APEX Listener 1.1.3.243.11.40
.XLSX will Process as of v2 Coming Soon!
How Do I Load Data …
TH Technology
XLSX Upload via APEX Listener How Do I Load Data …
TH Technology
XLSX Upload via APEX Listener
SEVERE: service exception: org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
How Do I Load Data …
TH Technology
XLS Upload via APEX Listener
Formulas Translate Literally Ex: IF(E2="","",VLOOKUP(E2,$AE$4:$AF$218,2,FALSE))
How Do I Load Data …
TH Technology
XLS Upload via APEX Listener
STILL Need Custom Code to Process Data from Collection to Database Table(s)
How Do I Load Data …
TH Technology
APEX Listener XLS Upload Pros
XLS File – No Need to Save As CSV Straightforward Reusable Less Custom Code
Cons
APEX Listener Deployment Embedded Formulas No XLSX - May Need a Save As Step
Will Have XLSX in V2 How Do I Load Data …
TH Technology
APEX Websheets
Data Grid Allows Copy/Paste Spreadsheet-Like Edits Data Grid w/in Page Custom Code Required to Transfer Data to Main Schema Tables
How Do I Load Data …
Editable Data in Websheet
TH Technology
How Do I Load Data …
APEX Websheets
Demo TH Technology
How Do I Load Data …
TH Technology
Pull Data From Websheet into App
Look at APEX$_WS Tables: APEX$_WS_ROW – the data APEX$_WS_HISTORY – iff you care about who changed what when Need to process from SQL Dev table to “real” tables
How Do I Load Data …
TH Technology
APEX WebSheets Pros
Copy/Paste Spreadsheet-Like Entry, Edits End User Can Build
Cons
Custom Code to Create Websheet, Data Grid?
Custom Code Required to Pull Data into Schema Objects Custom Code to Maintain Worksheet / Data Grid
More Code Than Other Solutions ?
How Do I Load Data …
TH Technology
Our Solution Problem
RPT Files XLS files Different Formats 200+, Monthly Users Cannot “Save as CSV” Must Support Edits Varying Volume Limited Network
Solution
Tabular Forms - 4 Macro to Save as RPT External Tables Generated RPT LIS Output RPT Files Upload File Process (to be retired) Custom Processing RPT – LI Systems RPT – Spreadsheet Templates
How Do I Load Data …
APEX Data Load Options
TH Technology
How Do I Load Data …
TH Technology
Nice to Have … Future?
1-Step Data Load Wizard Copy/Paste OR Upload XLSX Easiest for Non-Networked, Low(er) Volume End Users
How Do I Load Data …
TH Technology
Some Last Thoughts …
Forms Create/Edit One Row Tabular Forms Enter Many Rows Java Solution .JSP to load XLS directly Presume this means XLSX too!
How Do I Load Data …
TH Technology
How Do I Load Data ?
APEX-based Solution Weigh Requirements Source Data End User Capabilities Resources Time
No One-Size-Fits-All Apply Common Sense How Do I Load Data …
TH Technology
How Do I Load Data …
TH Technology
Common Sense Test …
Two men play 5 games of checkers. Each wins the same # of games. There are no ties.
How can this be?
They are not playing each other.
How Do I Load Data …
TH Technology
References
Upload File into APEX oraexplorer.com – Ittichai http://oraexplorer.com/2007/11/apex-to-upload text-file-and-write-into/ More at avdeo.com In Oracle Milieu http://avdeo.com/2008/05/21/uploading-excel sheet-using-oracle-application-express-apex/
How Do I Load Data …
TH Technology
References
Oracle Data Integrator http://www.oracle.com/technetwork/middleware/data integrator/overview/index.html
Oracle Database Utilities SQL Loader – External Tables http://www.oracle.com/technetwork/database/enterprise edition/index-093639.htm
l SQL Developer http://www.oracle.com/technetwork/developer tools/sql-developer/overview/index.html
How Do I Load Data …
TH Technology
References
APEX Listener XLS Upload – Kris Rice Blog http://krisrice.blogspot.com/2010/02/another apex-listener-ea-more-knobs-to.html
A Java-and-PL/SQL based XLS to CSV Option http://www.dynamicpsp.com/!go?ln=nfaq&faq=FAQ9
How Do I Load Data …
Questions ?
How Do I Load Data? Let Me Count The Ways
TH Technology [email protected]
Thank You
How Do I Load Data? Let Me Count The Ways
TH Technology [email protected]