pump up the volume - the apex dataloader inside out
DESCRIPTION
Since APEX 4.1 we've got the built-in feature of the Data Loader. This feature isn't widely used yet and that is a shame. In this session you will learn all the nifty features of this particular piece of APEX functionality. Afterwards you will be able to use it yourself, making your life a lot easier and your users a lot happier.TRANSCRIPT
![Page 1: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/1.jpg)
Copyright © 2012 Apex Evangelists http://apex-evangelists.com
Pump up the Volume!The APEX Data Loader Inside Out
Roel Hartman
1Thursday, November 29, 12
![Page 2: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/2.jpg)
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
About Roel
• Director of APEX Evangelists NL
• 20+ years of Oracle Experience
• “RoelH” in the APEX Forum
• Presenter at KSCOPE, OOW, Collaborate, DOAG, UKOUG, ...
•Oracle ACE Director
• Blog @ http://roelhartman.blogspot.com
• Twitter : @RoelH
22Thursday, November 29, 12
![Page 3: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/3.jpg)
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
A Small Ad...
33Thursday, November 29, 12
![Page 4: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/4.jpg)
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
How To Get Data Into The Database?
4
DBA Developer End UserETL Tools
Data PumpSQL Loader
External TablesUTL_FILE
XLS UploadFile Upload
Data Load WizardWebsheets
4Thursday, November 29, 12
![Page 5: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/5.jpg)
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
Data Load Wizard - Wizard
• Plain Insert
• Tweak User Experience
• Add Lookups
• Add Transformations
• Add Concurrency
• Under the Covers
55Thursday, November 29, 12
![Page 6: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/6.jpg)
DEMONSTRATION
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
Plain Insert
66Thursday, November 29, 12
![Page 7: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/7.jpg)
77Thursday, November 29, 12
![Page 8: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/8.jpg)
88Thursday, November 29, 12
![Page 9: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/9.jpg)
99Thursday, November 29, 12
![Page 10: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/10.jpg)
1010Thursday, November 29, 12
![Page 11: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/11.jpg)
1111Thursday, November 29, 12
![Page 12: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/12.jpg)
1212Thursday, November 29, 12
![Page 13: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/13.jpg)
1313Thursday, November 29, 12
![Page 14: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/14.jpg)
1414Thursday, November 29, 12
![Page 15: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/15.jpg)
1515Thursday, November 29, 12
![Page 16: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/16.jpg)
1616Thursday, November 29, 12
![Page 17: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/17.jpg)
1717Thursday, November 29, 12
![Page 18: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/18.jpg)
1818Thursday, November 29, 12
![Page 19: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/19.jpg)
1919Thursday, November 29, 12
![Page 20: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/20.jpg)
2020Thursday, November 29, 12
![Page 21: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/21.jpg)
2121Thursday, November 29, 12
![Page 22: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/22.jpg)
DEMONSTRATION
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
Tweak user experience
2222Thursday, November 29, 12
![Page 23: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/23.jpg)
2323Thursday, November 29, 12
![Page 24: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/24.jpg)
2424Thursday, November 29, 12
![Page 25: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/25.jpg)
2525Thursday, November 29, 12
![Page 26: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/26.jpg)
DEMONSTRATION
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
Add lookups
2626Thursday, November 29, 12
![Page 27: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/27.jpg)
2727Thursday, November 29, 12
![Page 28: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/28.jpg)
2828Thursday, November 29, 12
![Page 29: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/29.jpg)
2929Thursday, November 29, 12
![Page 30: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/30.jpg)
30
So “Insert New Value” only works with auto generated keys!(it does insert a record with ST = NULL...)
So you have to manage that using triggers...
30Thursday, November 29, 12
![Page 31: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/31.jpg)
DEMONSTRATION
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
Add transformations
3131Thursday, November 29, 12
![Page 32: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/32.jpg)
3232Thursday, November 29, 12
![Page 33: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/33.jpg)
33
Only columns that are uploaded can be transformed...
33Thursday, November 29, 12
![Page 34: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/34.jpg)
DEMONSTRATION
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
Add concurrency
3434Thursday, November 29, 12
![Page 35: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/35.jpg)
3535Thursday, November 29, 12
![Page 36: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/36.jpg)
36
Does NOT work when you update the UK (First/Last Name) -> will do an insert
(or when you delete the record -> will do an insert)
36Thursday, November 29, 12
![Page 37: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/37.jpg)
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
Under the covers
3737Thursday, November 29, 12
![Page 38: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/38.jpg)
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
Collections Used
38
• After “Data Load Source”
• CLOB_CONTENT
• PARSE_COL_HEAD
• SPREADSHEET_CONTENT
• After “Data / Table Mapping”
• LOAD_COL_HEAD
• LOAD_CONTENT
• NOT_LOAD_CONTENT
• After “Data Validation”
• FIN_LOAD_CONTENT
38Thursday, November 29, 12
![Page 39: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/39.jpg)
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
One-to-many Mapping
• Sales sheet
• Customers
• Products
• Orders
• Order Items
• Use view + Instead of Triggers
3939Thursday, November 29, 12
![Page 40: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/40.jpg)
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
Conclusions
4040Thursday, November 29, 12
![Page 41: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/41.jpg)
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
Conclusions
• Data Load Wizard - Wizard is easy to use for an APEX Developer
• Data Load Wizard is easy to use for an End User
• Data Load Wizard is for 1:1 mappings to Tables
• Table Lookups + Manage Concurrency are nice features
• Easy Transformation Rules are ... easy
• Complex Transformation Rules are ... complex & limited
• Better use View with Instead Of Triggers
• Supports one-to-many mapping
4141Thursday, November 29, 12
![Page 42: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/42.jpg)
Copyright © 2012 Apex Evangelistshttp://apex-evangelists.com
Q & A 42
42Thursday, November 29, 12
![Page 43: Pump up the volume - The APEX DataLoader inside out](https://reader031.vdocuments.us/reader031/viewer/2022020115/54be4bcc4a79599d5e8b458a/html5/thumbnails/43.jpg)
[email protected]://apex-evangelists.com
Copyright © 2012 Apex Evangelists4343Thursday, November 29, 12