Loading data to Oracle Database Tips & Tricks

Some practical advice and how-to's for loading data to an Oracle Database using our tools, such as SQL Developer, SQL Developer Web, SQLcl, or even via REST APIs.

Video:
https://www.youtube.com/watch?v=JNECXUeOHeo

The Blog:
11 Tips for Loading data from Excel to your Oracle Database
https://www.thatjeffsmith.com/archive/2021/06/tips-on-importing-excel-to-your-database-data-modeling/

Transcript

1. Oracle Database Tools: Data Loading Distinguished Product Manager Jeff.D.Smith@oracle.com Tweets: @thatjeffsmith Blogs: https://www.thatjeffsmith.com Jeff Smith Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.
2. Data: here, there, everywhere We want it where it belongs, in your database • Secure • Performant • Available • SQL! Are you too good for your home? Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
3. Wizard: answer the questions, we’ll do all the work • Desktop New or existing tables Delimited text, csv, Excel files Generate INSERT Scripts or SQL*Loader scenarios • Web New or existing tables Delimited text, csv, Excel, json, xml, avro I want to hold your hand Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
4. New Table? • Bump up the SCAN ROWS value • CSV will be fast, Excel will be…not so fast • Mind the TEMPORALs Old School Tips Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
5. CSV? Millions of rows? • SQLLDR! Old School Tips, Part Deux Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. sqlldr CONTROL=beer_pipe_delimited2.ctl LOG=beer_pipe_delimited2.log BAD=beer_pipe_delimited2.bad skip=1
6. Powered by ORDS • More file formats supported • JSON Arrays => ROWS New School Rulz: SQL Developer Web Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
7. Sometimes the old ways are best. • Load command New or existing tables Delimited text, csv Batchload the tables OR just generated the DDL Perfect for automation Perfect for ad hoc Tons of options I know this! Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
8. Are you feeling lucky? Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
9. Show me the DDL! Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. sqlcl> load newtable file.csv SHOW_DDL
10. Let the computer do the talking Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. GET https://host/ords/hr/emps/1 SELECT * FROM EMPS WHERE ID = 1 HTTP/1.1 200 OK { "id": 1, "name": "Jeff", "job": "PM", "salary": 100 } URI SQL & PLSQL Marshalls to Database HTTP Request HTTP Response Transforms to JSON SQL Result Set / Standard OUT SQL HTTPS {JSON} Oracle REST Data Service (ORDS)
11. • HTTPS is compatible with any: • programming language (even Fortran!) • framework (Angular, React.js, jQuery…) • environment (GUIs like Postman and CLI via cURL) • No Oracle Home to install • No Oracle drivers to configure Easier Oracle Access Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.
12. A flick of the switch Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
13. HTTPS POST, AutoREST TABLE Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
14. I wanna pump up your data Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
15. Like writing code?
16. Loading CSV files import cx_Oracle, csv batch_size = 10000 # Adjust to your memory and performance requirements connection = cx_Oracle.connect(user="scott", password=pw, dsn="localhost/orclpdb1") with connection.cursor() as cursor: sql = "insert into t (k, first_name, last_name, country) values (:1, :2, :3, :4)" cursor.setinputsizes(None, 30, 30, 30) # predefine memory sizes to hold the input records with open("csv/data1.csv", "r") as csv_file: csv_reader = csv.reader(csv_file, delimiter=',') data = [] for line in csv_reader: data.append((line[0], line[1], line[2], line[3])) # e.g [('1', 'Fred', 'Nurke', 'UK')] if len(data) % batch_size == 0: cursor.executemany(sql, data) # call executemany() per batch - don’t call execute() per record! data = [] if data: cursor.executemany(sql, data) connection.commit() cx_Oracle - Python DB API for Oracle Database 3/28/2022 Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted 16
17. • Product Info & Downloads Basic & Complete • YouTube Overview • Blog Posts • ORDS on Twitter Resources Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.
18. Modern Tooling Empowers Users, Increases Productivity Use the right tool for the right job Desktop REST APIs CLI WEB Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.