1 / 112

Karen Cannell kcannell@thtechnology.com

How Do I Load Data … Let Me Count The Ways. APEX Data Loading Options. Karen Cannell kcannell@thtechnology.com. http://www.thtechnology.com. How Do I Load Data? : Agenda. Oracle Data Loading Options DBAs - Developers - End Users APEX Data Loading Options File Upload

dale
Download Presentation

Karen Cannell kcannell@thtechnology.com

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. How Do I Load Data … Let Me Count The Ways APEX Data Loading Options Karen Cannell kcannell@thtechnology.com http://www.thtechnology.com

  2. 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

  3. 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

  4. About You … (Audience Background) • New to APEX? • APEX Experience? • Previous Tools? • APEX Training? • Version 2.0? 3.1 ? 3.2 ? 4.1?

  5. 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

  6. 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

  7. How DO I Load Data into Oracle? Load XLS Data Into APEX 1,830,000 hits Yikes! • Import Excel File … • APEX Data Loader • Load Excel Data …

  8. 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

  9. 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

  10. Let’s Focus …

  11. Load Data into APEX • Import Excel Data into Oracle … • APEX Data Loader • “Load Excel Data” The Apex Data Loader is a java based application that can bulk process the insert, update and delete on all object data into and build queries to extract data out of salesforce.com using the Apex Web Services (SOAP) API. 95% Say XLS but Really are CSV

  12. 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

  13. Which to Choose? It Depends • Application • Source • Target ( Table?) • Amount of Data • How Often ? 1 or Many • Audience End Users • Resources $$ • Time $$

  14. 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

  15. 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

  16. 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

  17. 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

  18. Oracle Database – Database Link • Don’t Forget the Database Link! SELECT … FROM table@db_link • Simple, Direct • Requires Communication • Ask!

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

  20. SQL Developer – Data Load Option • Import Data … Wizard

  21. SQL Developer – Import Data … Import File Formats • .XLS / XLSX • .CSV • .TSV (tab) • .DSV ( SQL Server)

  22. SQL Developer – Import Data … Import Wiz Detects • File type • Delimiter • Skip Row

  23. SQL Developer – Import Data … INSERT or Generate INSERT Script)

  24. SQL Developer – Import Data … Select Columns to Import

  25. SQL Developer – Import Data … Map Source to Table Columns

  26. SQL Developer – Import Data … Verify

  27. SQL Developer – Import Data … Finished! XLS and XLSX* File Load in Very Few Clicks! *XLSX in SQL Developer 3.1

  28. 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!

  29. SQL Developer – Import Data … Pros Cons Table Must Be Defined Cannot Integrate into App Tedious to Repeat IDE Specific One Shot Data Load Not for End Users • One Shot Data Load • Delimiter (, TAB, others) • Flexible Columns • Flexible Mapping • XLS File • Can be Faster than APEX Data Load Wizard

  30. 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

  31. APEX Data Load/Unload Utility

  32. APEX Data Load/Unload Utility 1st Row Column Heading

  33. APEX Data Load/Unload Utility

  34. APEX Data Load/Unload Utility Specify PK and Trigger Finish

  35. APEX Data Load Utility Pros Cons Many Steps Single Table No XLS Upload (Must Copy/Paste or Save as CSV) One-Time Not for End Users • Simple: Point-Click-Done • Flexible Format • Spreadsheet Convenience • Creates Table • Copy/Paste Option

  36. 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

  37. 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

  38. APEX File Browse + Custom Code • File Browse Item • WWV_FLOW_FILES – Original Way • BLOB column – New Option w APEX 4+

  39. 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

  40. 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

  41. APEX File Browse + Custom Code BLOB Column – New w/ APEX 4

  42. APEX File Browse + Custom Code WWV_FLOW_FILES

  43. APEX File Browse + Custom Code LOCAL_UPLOAD_FILES

  44. APEX File Browse + Custom Code Parse / Load Process • Read Blob into local BLOB variable • Read Records from Blob • Load Records into Destination Objects

  45. 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

  46. 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

  47. 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

  48. 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);

  49. PROCEDURE get_records(p_blob IN blob,p_records OUT varchar2_t) IS l_sepVARCHAR2(2) := chr(13)||chr(10); l_lastINTEGER; l_currentINTEGER; 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;

  50. 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)

More Related