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.
   

   View Slide

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.
   

   View Slide

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.
   

   View Slide

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.
   

   View Slide

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
   

   View Slide

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.
   

   View Slide

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.
   

   View Slide

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

   View Slide

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

   View Slide

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)
    

    View Slide

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.
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

15. Like writing code?
    

    View Slide

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
    

    View Slide

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

    View Slide

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.
    

    View Slide