Invoke Generic Scripts on an Autonomous Database Instance

You can invoke generic scripts, of type BASH, C, or Python on your Autonomous Database.

Parent topic: Invoke User Defined Functions

About Running Generic Scripts on Autonomous Database

You can invoke generic scripts, including scripts written in Bash, C, or Python, from an Autonomous Database instance.

You cannot run a generic script directly on an Autonomous Database instance. Instead, the script is hosted remotely on an Oracle Autonomous Database Extproc container image running in an Oracle Cloud Infrastructure Virtual Cloud Network (VCN). You invoke generic scripts from your Autonomous Database using Oracle Scheduler jobs. The Oracle Scheduler job that you create must be an executable job. The executable jobs can execute shell scripts, or other executables.

Note:

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

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

Generic scripts are deployed by using:

  • An Oracle provided Autonomous 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 Database and the EXTPROC agent instance is secured by setting Network Security Group (NSG) rules such that the traffic is allowed from your Autonomous 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 generic scripts.

    See DBMS_SCHEDULER for more information.

Follow these steps to run a generic script from an Autonomous Database instance:

Parent topic: Invoke Generic Scripts on an Autonomous Database Instance

Upload Wallet to Create Secure Connection to the EXTPROC Agent Instance

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

To run generic scripts at the EXTPROC agent instance, the Autonomous Database and the EXTPROC agent connect using Mutual Transport Layer Security (mTLS). When you connect to the EXTPROC agent with mTLS, you use 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 Database Instance for more information.

Note:

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

As a prerequisite, you must export the wallet to Object Storage from the /u01/app/oracle/wallets/extproc_wallet/ directory on the VM where EXTPROC runs. This allows the Autonomous Database to use the wallet to securely access the EXTPROC.

Upload the EXTPROC wallet to your Autonomous Database instance:

  1. Import the wallet, cwallet.sso, containing the certificates for the EXTPROC agent instance from Object Storage in your Autonomous Database. 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 the 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 the Autonomous Database for the wallet file cwallet.sso.
    CREATE DIRECTORY wallet_dir AS 'directory_location';

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

  4. Use DBMS_CLOUD.GET_OBJECT to download the wallet from Object Storage. 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 Database instance.

Parent topic: Invoke Generic Scripts on an Autonomous Database Instance

Steps to Invoke Generic Scripts

Shows the steps to invoke generic scripts on an Autonomous Database.

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

The following are prerequisites to invoke generic scripts with Autonomous Database:

  • The generic scripts must be copied into the EXTPROC agent instance. See GitHub README for more information.

  • To create and manage Scheduler jobs to invoke generic 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 Generic Scripts on an Autonomous Database Instance

Register and Manage Remote Endpoint on Autonomous Database

As the ADMIN user, perform the following steps to register and manage remote endpoints in your Autonomous 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_url parameter in your Autonomous Database. The EXTPROC agent instance is pre-configured to host generic 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 generic scripts from Autonomous 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 Database. See DEREGISTER_REMOTE_EXECUTION_ENV Procedure for more information.

Parent topic: Steps to Invoke Generic Scripts

Create and Manage Scheduler Jobs to Invoke Generic Scripts

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

To run the following steps as a user other than the ADMIN, you must have the required privileges. See Steps to Invoke Generic Scripts for more information.
  1. Use DBMS_SCHEDULER.CREATE_JOB to create a scheduler job with job type as executable.

    Example:

    BEGIN
 DBMS_SCHEDULER.CREATE_JOB (
    job_name             => 'rem_exec_job',
    job_type             => 'executable',
    job_action           => 'script_location_in_remote_docker_image/name',
    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 generic scripts on your Autonomous 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.

    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.

    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    => 'param1');
 END;
/
  3. Use DBMS_SCHEDULER.CREATE_JOB to modify the destination attribute of the rem_exec_job job.

    Example:

    BEGIN
 DBMS_SCHEDULER.SET_ATTRIBUTE (
      name       => 'rem_exec_job',
      attribute  => 'destination',
      value      => 'REMOTE_EXTPROC:remote_endpoint_name');
 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 format, where remote_endpoint_name is the name of the registered remote endpoint.

    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 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 Generic Scripts