Oracle by Example brandingOracle® Cloud Creating SQL Scripts in Oracle Machine Learning

section 0Before You Begin

This tutorial shows you how to create a SQL script and share it with other users in Oracle Machine Learning. This is the fourth tutorial in the series Working with Oracle Machine Learning. Read the tutorials in sequence

This tutorial takes approximately 10 minutes to complete.

Background

A SQL script is a set of SQL statements. You can save the SQL script that you create in Oracle Machine Learning as a .json file in your system. You can also import a SQL script in to your notebook. In this tutorial, you will learn how to create a SQL script that creates a table, and inserts value in it. You will also learn how to save and share the SQL script with other users.

What Do You Need?

  • Access to your Oracle Machine Learning account
  • A connection to a database interpreter

section 1Create a SQL Script in Oracle Machine Learning

To create a SQL script in Oracle Machine Learning:

  1. Log in to Oracle Machine Learning using your credentials provided by your Oracle Machine Learning administrator.
  2. In the Oracle Machine Learning home page, click Run SQL Scripts. The SQL Query Scratchpad opens. 
    home page
    Description of the illustration oml_homepage_create_sql.png
  3. In the SQL Query Scratchpad, after %script, press enter.
  4. In the next line in the SQL Query Scratchpad, type the SQL script as shown in the screenshot.
    sql script
    Description of the illustration sql_script.png
    The SQL script in this example, contains two parts:
    • The first part of the script contains the SQL statement CREATE TABLE to create a table named small_table. It defines the table name, table column, data types, and size. In this example, the column names are NAME, ID1, ID2, ID3, ID4, and TEXT.
    • The second part of the script begins with the keyword BEGIN. It inserts 100 rows in to the table small_table.

    Note: When using the CREATE statement with a primary key, it fails and displays the error message Insufficient privileges. This error occurs due to lockdown profiles in the database. If you encounter this error, contact your database administrator or the designated security administrator to grant the required privileges.

  5. Once you have finished writing the SQL script, click Run.

section 2Share Your SQL Script

After you have created the SQL script, you can share it with other users:

  • Export SQL script: In the SQL Query Scratchpad, click the export icon to save it as a .json file in your system.
    export
    Description of the illustration export_sql_script.png
  • Import SQL script: In the Notebooks page, click Import to import the SQL file saved as a .json file in to your notebook.
    import
    Description of the illustration import_sql_script.png
    After the SQL script is successfully imported, it is listed in the Notebooks page. Click the SQL script that is listed here to open and edit it in the Notebook editor. You can also save the SQL script as a notebook template in My Library, Shared, or in the Gallery.

next stepNext Tutorial

Running SQL Statements in Oracle Machine Learning