The Scratchpad provides you convenient one-click access to a notebook for running SQL statements, PL/SQL, R, and Python scripts that can be renamed. The Scratchpad is available on the Oracle Machine Learning User Interface (UI) home page.

1. To open and use the scratchpad, click Scratchpad on the Oracle Machine Learning UI home page under Quick Actions. The Scratchpad opens. The Scratchpad has three paragraphs each with the following directives:
   • %sql — Allows you to run SQL statements.
   • %script — Allows you to run PL/SQL scripts.
   • %python — Allows you to run Python scripts.
   • %r — Allows you to run R scripts.

   Figure 4-64 Scratchpad

   
2. To run SQL script:
   1. Go to the paragraph with the %sql directive.
   2. Type the following command and click the Run icon. Alternatively, you can press Shift+Enter keys to run the paragraph.

      SELECT * FROM SH.SALES;

   In this example, the SQL statement fetches all of the data about product sales from the table SALES. Here, SH is the schema name, and SALES is the table name. Oracle Machine Learning UI fetches the relevant data from the database and displays it in a tabular format.

   Figure 4-65 SQL Statement in Scratchpad

   
3. To run PL/SQL script:
   1. Go to the paragraph with the %script directive.
   2. Enter the following PL/SQL script and click the Run icon. Alternatively, you can press Shift+Enter keys to run the paragraph.

      CREATE TABLE small_table
      	(
      	 NAME VARCHAR(200),
      	 ID1 INTEGER,
      	 ID2 VARCHAR(200),
      	 ID3 VARCHAR(200),
      	 ID4 VARCHAR(200),
      	 TEXT VARCHAR(200)
      	);
      
      	BEGIN 
      		FOR i IN 1..100 LOOP
      				INSERT INTO small_table VALUES ('Name_'||i, i,'ID2_'||i,'ID3_'||i,'ID4_'||i,'TEXT_'||i);
      		END LOOP;
      		COMMIT;
      	END;

      The PL/SQL script successfully creates the table SMALL_TABLE. The PL/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.

      Figure 4-66 PL/SQL Script in Scratchpad

      
4. To run python script:
   1. To use OML4Py, you must first import the oml module. oml is the OML4Py module that allows you to manipulate Oracle Database objects such as tables and views, call user-defined Python functions using embedded execution, and use the database machine learning algorithms. Go to the paragraph with %python directive. To import the oml module, type the following command and click the Run icon. Alternatively, you can press Shift+Enter keys to run the paragraph.

      import oml

   2. To check if the oml module is connected to Oracle Database, type oml.isconnected() and click the Run icon. Alternatively, you can press Shift+Enter keys to run the paragraph.

      oml.isconnected()

   3. You are now ready to run your Python script. Type the following Python code and click the run icon. Alternatively, you can press Shift+Enter keys to run the paragraph.

      import matplotlib.pyplot as plt
      import numpy as np
      
      list1 = np.random.rand(10)*2.1
      list2 = np.random.rand(10)*3.0
      
      plt.subplot(1,2,1) # 1 line, 2 rows, index nr 1 (first position in subplot)
      plt.hist(list1)
      plt.subplot(1, 2, 2) # 1 line, 2 rows, index nr 2 (second position in subplot)
      plt.hist(list2)
      plt.show()

      In this example, the commands import two python packages to compute and render the data in two histograms for list1 and list2. The Python packages are:

      • Matplotlib — Python package to render graphs.
      • Numpy — Python package for computations.

      Figure 4-67 Python Script in Scratchpad

      

      The two graphs for list1 and list 2 are generated by the python engine, as shown in the screenshot here.

5. After you have created and run your scripts in the Scratchpad, the Scratchpad is automatically saved as a notebook by the name default name Scratchpad on the Notebooks page. You can edit the name of the notebook and save it with the new name by clicking Edit.