Invoke Python Scripts with WITH_CONTEXT on Autonomous AI Database

Shows the steps to invoke Python scripts using WITH_CONTEXT on your database.

Topics

Parent topic: Invoke User Defined Functions

About Invoking Python Scripts with WITH_CONTEXT on Autonomous AI Database

You can run Python scripts on an Autonomous AI Database instance using Oracle Scheduler jobs with the WITH_CONTEXT attribute.

When you invoke Python scripts with WITH_CONTEXT on Autonomous AI Database, a context pointer is passed to the script that enables the script to call back to the database. The context refers to the database session, connection, and any associated state or data that the script needs to access or manipulate.

You cannot run a Python script directly on an Autonomous AI Database instance. Instead, you host the script remotely on an Oracle Autonomous AI Database Extproc container image running in an Oracle Cloud Infrastructure Virtual Cloud Network (VCN). The container image is preconfigured with the EXTPROC agent and includes all necessary libraries, such as utils, onnx, and python-oracledb, for running the script.

You invoke Python scripts from your Autonomous AI Database using Oracle Scheduler jobs. The Scheduler job that you create must be an executable job and is run with the WITH_CONTEXT attribute. The executable jobs can execute shell scripts, or other executables and the WITH_CONTEXT Oracle Scheduler attribute enables a script to inherit the current session privileges when calling an external procedure, program or script. The WITH_CONTEXT attribute enables external routines to access session-specific information such as schema, privileges, and other context variables.

Python scripts from your Autonomous AI Database are only supported when your database is on a private endpoint. To run Python scripts, you must obtain, install, and configure Oracle Autonomous AI Database EXTPROC container image with the EXTPROC agent installed. The Autonomous AI Database EXTPROC container image enables you to call external procedures and scripts written in BASH, C, or Python from your Autonomous AI Database. The EXTPROC agent instance is hosted on a private subnet, and the Autonomous AI Database accesses the EXTPROC agent through a Reverse Connection Endpoint (RCE).

Note:

This feature is only supported for Oracle database release 19c.

You deploy Python scripts by using:

  • An Oracle provided Autonomous AI Database container image with the EXTPROC agent installed. Oracle provides the container image on GitHub packages.

    See GitHub README for instructions to obtain and configure the EXTPROC container image:

    The EXTPROC agent instance is hosted remotely on a container image running in an Oracle Cloud Infrastructure Virtual Cloud Network (VCN). The secure communication between your Autonomous AI Database and the EXTPROC agent instance is secured by setting Network Security Group (NSG) rules such that the traffic is allowed from your Autonomous AI Database instance running on a private endpoint to the EXTPROC agent instance. The EXTPROC agent image is pre-configured to host and run external procedures on port 16000.

  • PL/SQL procedures to register endpoint environments and manage privileges on the registered endpoints. See DBMS_CLOUD_FUNCTION_ADMIN Package for more information.

  • PL/SQL procedures to create and manage scheduler jobs and programs to invoke Python scripts.

    See DBMS_SCHEDULER for more information.

Follow these steps to run a Python script with WITH_CONTEXT on an Autonomous AI Database instance:

Parent topic: Invoke Python Scripts with WITH_CONTEXT on Autonomous AI Database

Create a Python Script

Shows an example of creating a Python script

  • Example: Python script to create a table in your database.

    #!/usr/bin/env python1

import oracledb
import utils
 def gsf_main(argc, argv):
    table_name = argv[0]
    table_pk = argv[1]
    print(f"Total number of args: {argc}")
    print(f"Arg1: {table_name}")
    print(f"Arg2: {table_pk}")
    print(f"Arg3: {argv[2]}")
    print(f"Arg4: {argv[3]}")
    print(f"Arg5: {argv[4]}")
     
    # Step 1: Get connection object
    con = utils.getconnection()
    if con is None:
        print("Failed to establish database connection.")
        return -1
      try:
        # Step 2: Create a table
        cur = con.cursor()
        create_table_query = f"""
            CREATE TABLE {table_name} (
                employee_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
                employee_name VARCHAR2(4000),
                employee_age NUMBER,
                CONSTRAINT {table_pk} PRIMARY KEY (employee_id)
            )
            """
        cur.execute(create_table_query)
    except Exception as e:
        print(f"Error performing operations: {e}")
        return -1
    finally:
        if cur:
            cur.close()

    This example creates the python1.py script. The script defines a Python function gsf_main(argc, argv) that accepts arguments from a Scheduler job, creates a table in the database with the provided attributes, and handles exceptions.

    The oracledb driver enables the script to connect to the database.

    The utils package provides a helper function getconnection() for obtaining a database connection.

See Python documentation for more information.

Parent topic: Invoke Python Scripts with WITH_CONTEXT on Autonomous AI Database

Configure Oracle Autonomous AI Database EXTPROC Container Image

Describes the steps to obtain and configure Oracle Autonomous AI Database EXTPROC container image.

An Oracle provided Autonomous AI Database container image with the EXTPROC agent installed is hosted on GitHub packages. See GitHub README for instructions to obtain and configure the EXTPROC container image.

Parent topic: Invoke Python Scripts with WITH_CONTEXT on Autonomous AI Database

Upload Wallet to Create Secure Connection to the EXTPROC Agent Instance

A self-signed wallet is created as part of the Autonomous AI Database EXTPROC agent application creation. This wallet allows you to access the Extrpoc agent instance.

To execute Python scripts at the EXTPROC agent instance, the Autonomous AI Database and the EXTPROC agent connect using Mutual Transport Layer Security (mTLS). When using Mutual Transport Layer Security (mTLS), clients connect through a TCPS (Secure TCP) database connection using standard TLS 1.2 with a trusted client certificate authority (CA) certificate. See About Connecting to an Autonomous AI Database Instance for more information.

Note:

You can also obtain and use a public certificate issued by a Certificate Authority (CA).

You must first export the wallet to Object Storage from the /u01/app/oracle/extproc_wallet directory on the VM where EXTPROC runs.

Follow these steps to upload the wallet to your Autonomous AI Database:

  1. Import the wallet, cwallet.sso, containing the certificates for the EXTPROC agent instance into your Autonomous AI Database from Object Storage. Note the following for the wallet file:

    • The wallet file, along with the Database user ID and password provide access to the EXTPROC agent instance. Store wallet files in a secure location and share them only with authorized users.

    • Do not rename the wallet file. The wallet file in Object Storage must be named cwallet.sso.

  2. Create credentials to access your Object Storage where you store the wallet file cwallet.sso. See CREATE_CREDENTIAL Procedure for information about the username and password parameters for different object storage services.
    Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See About Using Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.
  3. Create a directory on Autonomous AI Database for the wallet file cwallet.sso.
    CREATE DIRECTORY wallet_dir AS 'directory_location';

    See Create Directory in Autonomous AI Database for more information creating directories.

  4. Use DBMS_CLOUD.GET_OBJECT to upload the wallet. For example:
    BEGIN
  DBMS_CLOUD.GET_OBJECT (
    credential_name     => 'DEF_CRED_NAME',
    object_uri          => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
    directory_name      => 'WALLET_DIR'
);
END;
/

    In this example, namespace-string is the Oracle Cloud Infrastructure Object Storage namespace and bucketname is the bucket name. See Object Storage Namespaces for more information.

    The wallet is copied to the directory created in the previous step, WALLET_DIR. The wallet that allows you to connect to the EXTPROC agent instance is now available on your Autonomous AI Database instance.

Parent topic: Invoke Python Scripts with WITH_CONTEXT on Autonomous AI Database

Steps to Invoke Python Scripts

Shows the steps to invoke Python scripts on an Autonomous AI Database.

After you configure the EXTPROC agent instance to run Python scripts, you register a remote endpoint and create Scheduler jobs to call the scripts.

The following are prerequisites to invoke Python scripts on Autonomous AI Database:

  • The Python scripts must be copied into the EXTPROC agent instance.

  • To create and manage Scheduler jobs to invoke Python scripts with a user other than ADMIN, you must have the following privileges:

    • MANAGE SCHEDULER

    • CREATE JOB

    • Privilege on the registered remote endpoint

Topics

Parent topic: Invoke Python Scripts with WITH_CONTEXT on Autonomous AI Database

Register and Manage Remote Endpoint on Autonomous AI Database

As the ADMIN user, perform the following steps to register and manage remote endpoints in your Autonomous AI Database.

Register a Remote Endpoint

Use DBMS_CLOUD_FUNCTION_ADMIN.REGISTER_REMOTE_EXECUTION_ENV to register a remote endpoint.

Example:

BEGIN
 DBMS_CLOUD_FUNCTION_ADMIN.REGISTER_REMOTE_EXECUTION_ENV (
        remote_endpoint_name => 'rem_executable',
        remote_endpoint_url  => 'remote_extproc_hostname:16000',
        wallet_dir           => 'WALLET_DIR',
        remote_cert_dn       => 'CN=MACHINENAME'
);
END;
/

This example creates the rem_executable library and registers the EXTPROC agent instance specified in the remote_endpoint_url parameter in your Autonomous AI Database. The EXTPROC agent instance is pre-configured to host Python scripts on port 16000.

See REGISTER_REMOTE_EXECUTION_ENV Procedure for more information.

Manage Privileges on a Registered Endpoint

This step is optional and is only required when a user other than the ADMIN needs to invoke Python scripts from Autonomous AI Database.

Use DBMS_CLOUD_FUNCTION_ADMIN.GRANT_REMOTE_EXECUTION_ENV to grant privilege on the registered endpoint to a user other than the ADMIN.

Example:

BEGIN
 DBMS_CLOUD_FUNCTION_ADMIN.GRANT_REMOTE_EXECUTION_ENV (   
    remote_endpoint_name => 'REM_EXECUTABLE',
    user_name            => 'username');
END;
/

This example grants privilege on REM_EXECUTABLE to the specified user. See GRANT_REMOTE_EXECUTION_ENV Procedure for more information.

After you grant privilege on the registered endpoint, you can use DBMS_CLOUD_FUNCTION_ADMIN.REVOKE_REMOTE_EXECUTION_ENV to revoke privilege on the registered endpoint form a user.

Example:

BEGIN
 DBMS_CLOUD_FUNCTION_ADMIN.REVOKE_REMOTE_EXECUTION_ENV (   
    remote_endpoint_name => 'REM_EXECUTABLE',
    user_name            => 'username');
END;
/

This example revokes privilege on REM_EXECUTABLE from the specified user. See REVOKE_REMOTE_EXECUTION_ENV Procedure for more information.

You can query the DBA_CLOUD_FUNCTION_REMOTE_EXECUTION_GRANT to list the permissions granted for all remote endpoints. See DBA_CLOUD_FUNCTION_REMOTE_EXECUTION_GRANT View for more information.

Remove a Registered Endpoint

Use DBMS_CLOUD_FUNCTION_ADMIN.DEREGISTER_REMOTE_EXECUTION_ENV to remove a registered remote endpoint.

Example:

BEGIN
 DBMS_CLOUD_FUNCTION_ADMIN.DEREGISTER_REMOTE_EXECUTION_ENV (   
    remote_endpoint_name => 'REM_EXECUTABLE');
END;
/

This removes the rem_executable remote endpoint from your Autonomous AI Database. See DEREGISTER_REMOTE_EXECUTION_ENV Procedure for more information.

Parent topic: Steps to Invoke Python Scripts

Create and Manage Scheduler Jobs to Invoke Python Scripts

Shows the steps to create and manage scheduler jobs to invoke Python scripts from Autonomous AI Database.

To run the following steps as a user other than the ADMIN, you must have the required privileges. See Steps to Invoke Python Scripts for more information.
  1. Use DBMS_SCHEDULER.CREATE_JOB to create a scheduler job with job type as executable For example:
    BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
    job_name             => 'rem_exec_job',
    job_type             => 'executable',
    job_action           => '/script_location_on_extproc_agent_image/python1.py',
    number_of_arguments  => 1,
    enabled              => false,
    auto_drop            => true);
 END;
/

    This example creates the rem_exec_job scheduler job of executable type.

    The job_name parameter specifies the name of the job.

    The job_type parameter specifies the job action type. You must specify the job_type as executable to invoke Python scripts on your Autonomous AI Database.

    The job_action parameter specifies the inline action of the job. This is the location of the script on the remote endpoint that you need to invoke. This example invokes the python1.py script, created in a previous step.

    The number_of_arguments parameter specifies the number of job arguments.

    The enabled parameter indicates whether the job should be enabled immediately after creating it. You cannot enable an Oracle Scheduler job that invokes a Python script at creation, use DBMS_SCHEDULER.ENABLE to enable the job after the job is created.

    The auto_drop parameter indicates whether the job should be dropped once completed.

  2. Use DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE to set the value of the job argument.

    Example:

    BEGIN
 DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
    job_name          => 'rem_exec_job',
    argument_position => 1,
    argument_value    => 'job_param');
 END;
/

    This example sets the first argument of the rem_exec_job job is set to a String value job_param. When the job runs, it passes job_param as the value for its first parameter.

  3. Use DBMS_SCHEDULER.SET_ATTRIBUTE to modify the destination attribute of the rem_exec_job job. The destination attribute specifies the remote endpoint destination.

    Example:

    BEGIN
 DBMS_SCHEDULER.SET_ATTRIBUTE (
      name       => 'rem_exec_job',
      attribute  => 'destination',
      value      => 'REMOTE_EXTPROC:remote_endpoint_name:WITH_CONTEXT');
 END;
/

    This example modifies the destination attribute of the rem_exec_job job to specify the remote library path.

    The job_name parameter specifies the name of the job.

    The attribute parameter specifies the attribute to be modified.

    The value parameter modifies the destination attribute to specify the remote endpoint destination.

    The parameter accepts a String value in REMOTE_EXTPROC:remote_endpoint_name:WITH_CONTEXT format, where remote_endpoint_name is the name of the registered remote endpoint. The WITH_CONTEXT clause enables a script to inherit the current session privileges when calling an external procedure, program or script.

    An error is encountered if you do not have privileges on the specified endpoint.

    See DBMS_SCHEDULER Subprograms for more information.

  4. Run DBMS_SCHEDULER.ENABLE to enable the scheduler job.

    Example:

    BEGIN
 DBMS_SCHEDULER.ENABLE (
    name => 'rem_exec_job');
 END; 
/

    This example enables the rem_exec_job job. See DBMS_SCHEDULER for more information.

    After you enable the job, the Scheduler begins to run the job.

    Query USER_CLOUD_FUNCTION_RUN_DETAILS View and DBA_CLOUD_FUNCTION_RUN_DETAILS View to view the status of your Scheduler jobs.

Parent topic: Steps to Invoke Python Scripts