How Do I Load Data … Let Me Count The Ways Karen Cannell [email protected]

TH Technology http://www.thtechnology.com

APEX Data Loading Options 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 Wizard2 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 10g,11g (now 12c) 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/dataintegrator/overview/index.html

Oracle Database Utilities Oracle Data Pump SQL Loader External Tables How Do I Load Data …

TH Technology

Load Data into Oracle, cont’d 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 …

TH Technology

Let’s Focus …

How Do I Load Data …

TH Technology

Load Data into APEX Import Excel Data into Oracle … APEX Data Loader TheExcel Apex Data” Data Loader is a java based “Load

application that can bulk process the insert, update and delete on all object data into and build queries to extract data out of 95% Say XLS but Really areServices CSV salesforce.com using the Apex Web (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 …

TH Technology

Use your IDE SQL Developer PL/SQL Developer Toad Others … Best for One-Time Load

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

1st 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 Cons Simple: Point-ClickMany Steps Done Single Table Flexible Format No XLS Upload (Must Spreadsheet Copy/Paste or Save Convenience as CSV) Creates Table One-Time Copy/Paste Option 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 INTO l_blob FROM wwv_flow_files WHERE name=p_file_name; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20000, 'File not found, id='||p_file_name); END;

Or BLOB column from your BLOB table

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 …

PROCEDURE get_records(p_blob IN blob,p_records OUT varchar2_t) TH Technology IS l_sep VARCHAR2(2) := chr(13)||chr(10); l_last 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 …

TH Technology

File Browse & Custom Code Pros Custom Code Custom Solution

Cons Custom Code Resources Maintenance One-of Solution

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 …

TH Technology

Data Load Wizard Wizard Wizard to Create A Data Load Wizard Create Page  Data Loading

How Do I Load Data …

TH Technology

Data Load Wizard Wizard

How Do I Load Data …

TH Technology

Data Load Wizard Wizard Define Table Lookups

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 …

TH Technology

Data Load Wizard Wizard Page/Region/Breadcrumb Names

How Do I Load Data …

TH Technology

Data Load Wizard Wizard Define Tabs (if any)

How Do I Load Data …

TH Technology

Data Load Wizard Wizard Define Branches

How Do I Load Data …

TH Technology

Data Load Wizard Wizard Confirmation

How Do I Load Data …

TH Technology

Data Load Wizard - Result Working Upload for our USERS table

How Do I Load Data …

TH Technology

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?

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 …

TH Technology

Data Load Wizard - Options

How Do I Load Data …

TH Technology

Data Load Wizard – Pro’s/Con’s Pros Flexible Form Delimiter Option 1st 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 …

TH Technology

Data Load Wizard Wizard Can I trick it into taking an XLS? No:

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 …

TH Technology

Data Load Wizard – P 3 Session Info Collections Character Columns Numeric Columns Date Columns

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 …

TH Technology

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

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 …

TH Technology

APEX Listener XLS Upload Demo

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/anotherapex-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: true true EXCEL_COLLECTION true How Do I Load Data …

TH Technology

XLS Upload via APEX Listener Configuration Options: Turn Feature On: true One Collection per Worksheet: true|false

How Do I Load Data …

TH Technology

XLS Upload via APEX Listener Configuration Options: Specify Name for All Collections: true|false false == sequential sheet name true == uses sheet name Specify Name of Collection for All Collections: MY_COLLECTION 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 …

TH Technology

Editable Data in Websheet

How Do I Load Data …

TH Technology

APEX Websheets Demo

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 TemplatesHow Do I Load Data …

TH Technology

APEX Data Load Options

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-uploadtext-file-and-write-into/ More at avdeo.com In Oracle Milieu http://avdeo.com/2008/05/21/uploading-excelsheet-using-oracle-application-express-apex/

How Do I Load Data …

TH Technology

References Oracle Data Integrator http://www.oracle.com/technetwork/middleware/dataintegrator/overview/index.html

Oracle Database Utilities SQL Loader – External Tables http://www.oracle.com/technetwork/database/enterpriseedition/index-093639.html

SQL Developer http://www.oracle.com/technetwork/developertools/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/anotherapex-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]