Oracle Database Tools: Data Loading Distinguished Product Manager [email protected] Tweets: @thatjeffsmith Blogs: https://www.thatjeffsmith.com Jeff Smith Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.

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.

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.

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.

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

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.

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.

Are you feeling lucky? Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

Show me the DDL! Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved. sqlcl> load newtable file.csv SHOW_DDL

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)

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

A flick of the switch Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

HTTPS POST, AutoREST TABLE Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

I wanna pump up your data Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

Like writing code?

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

• Product Info & Downloads Basic & Complete • YouTube Overview • Blog Posts • ORDS on Twitter Resources Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.

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.