Transcript Document
How APEX Do Data I Load Loading Data … Let Options 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 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 Simple: Point-ClickDone 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 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 … TH Technology PROCEDURE get_records(p_blob IN blob,p_records OUT varchar2_t) 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: <entry key="apex.excel2collection">true</entry> <entry key="apex.excel2collection.onecollection">true</entry> <entry key="apex.excel2collection.name">EXCEL_COLLECTION</entry> <entry key="apex.excel2collection.useSheetName">true</entry> How Do I Load Data … TH Technology XLS Upload via APEX Listener Configuration Options: Turn Feature On: <entry key="apex.excel2collection">true</entry> One Collection per Worksheet: <entry key= "apex.excel2collection.onecollection">true|false</entry> How Do I Load Data … TH Technology XLS Upload via APEX Listener Configuration Options: Specify Name for All Collections: <entry key= "apex.useSheetName">true|false</entry> false == sequential sheet name true == uses sheet name Specify Name of Collection for All Collections: <entry key= "apex.excel2collection.collection.name">MY_COLLECTION</entry> 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]