- #Oracle apex sql statement separator manual#
- #Oracle apex sql statement separator software#
- #Oracle apex sql statement separator code#
The rest of this post is focussed primarily on Parsing and Mapping Text and Excel files.
#Oracle apex sql statement separator manual#
SQL*Loader Express Mode) Manual (allow the user to choose which column maps to which target) or A.I.
#Oracle apex sql statement separator code#
Parse – External tables / SQL*Loader APEX 3rd-party code.Load – External tables / SQL*Loader APEX file select.Process – make the relevant changes in the database based on the dataĮach of these components have multiple solution options, some are listed here:.Validate – check that the data satisfies all schema and business rule constraints.Map – identify how the text data relates to your schema.Parse – extract the text data from the file.Solution ComponentsĪny solution for processing uploaded files must incorporate each of the following components: I’ve received spreadsheets where some data rows were not “real” data rows, but merely explanatory text or notes entered by the users – since they coloured the background on those rows in grey, they expected my program to automatically filter those rows out. is it a simple “header line, data line, data line” structure or are there bits and pieces dotted around the spreadsheet)? If it’s an Excel file, is all the data in one sheet, or is it spread across multiple sheets? Should all the sheets be processed, or should some of them be ignored? Can the columns vary depending on requirement – might there be some columns in one file that don’t exist in other files, and vice versa?įinally, is all the data to be loaded actually encoded in text form? This is an issue where a spreadsheet is provided where the user has, trying to be helpful, highlighted rows with different colours to indicate different statuses or other categorising information. Will the columns in the file remain the same, or might they change all the time? If the structure is not amenable to automated data matching, can the file structure be changed to accommodate our program? Is the structure even in tabular form (e.g. if they are stored on the database server, do we know what the files will be called? is this a ad-hoc, rare situation something they need to do a few times per month or something that is frequent and needs to be painless and automatic? How much automation is required, how often – i.e.are they stored on the database server, or is the user going to upload them via an online APEX application Knowing these will help you choose the right approach and solution. When your user wants you to provide a facility for uploading data, there are some basic questions you’ll need to ask. Perhaps in some odd instances the user will have received the data from some system in fixed width, XML or JSON format – but this is rare as this is typically part of the build of a system integration solution and users expect these to be “harder”. If they have copied the data from a table in Excel, it will be in tab-delimited format in the clipboard. Typically the data will be provided in some kind of text format (CSV, tab delimited, fixed width) or binary file (XLS or XLSX). Depending on what exactly they mean by “upload this data” it may be reasonably easy to build or it could get quite complex.
Of course, you know and I know that this is not necessarily a simple thing it is certainly not just an option to be enabled. Once they’ve finished with the data, odds are they’ll come back to you and ask “how do I upload this data back into APEX?” and expect that the answer is merely a flip of a switch to enable a built-in APEX feature.
#Oracle apex sql statement separator software#
You can show them the excellent Interactive Report and Interactive Grid features of APEX, and train some of your users to use some of their capabilities, but at the end of the day, your users will still download the data into their spreadsheet software to do their own stuff with it. Face it: your users are in love with Microsoft Excel, and you can’t do anything about it.