Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Loading data to Oracle Database Tips & Tricks

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/

thatjeffsmith

March 28, 2022
Tweet

More Decks by thatjeffsmith

Other Decks in Technology

Transcript

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

    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