Invoke Cloud Functions and External Procedures

In an on-premise Oracle Database (Enterprise, Standard, Express, Personal Editions), you can write user-defined functions in PL/SQL, Java, or C to provide functionality that is not available in SQL or SQL built-in functions. You can invoke these user-defined functions in a SQL statement wherever an expression can occur. See About User Defined Functions.

From Oracle Database 19c onwards, you can create, store, and execute Python scripts using the Embedded Python execution feature in SQL. See Embedded Python Execution using OML4Py.

Oracle Autonomous AI Database on Dedicated Exadata Infrastructure extends user defined functions capability to the cloud by enabling you to write SQL Functions that invoke serverless compute services in the cloud, such as OCI Functions and AWS Lambda Functions, or External Procedures - which include C/C++ routines, Shell or Python scripts - executing in an OCI compute VM that is external to the Autonomous AI Database.

Related Topics

About Cloud Functions and External Procedures

User defined functions enable you to invoke externally available functions from PL/SQL or SQL code within your database. You can invoke the following external functions using user defined functions:

  • Oracle Cloud Infrastructure Functions: Oracle Cloud Infrastructure Functions are fully managed, multi-tenant, highly scalable, on-demand, Functions-as-a-Service platform. Oracle Cloud Infrastructure Functions are built on enterprise-grade Oracle Cloud Infrastructure and powered by the Fn Project open-source engine. See Overview of OCI Functions for more information.
  • AWS Lambda Functions: AWS Lambda is a serverless, event-driven compute service that lets you run code for virtually any application or backend service without provisioning or managing servers. See AWS Lambda for more information.
  • External Procedures: External procedures are functions written in a third-generation language (C, for example), or OS shell scripts or Python scripts that can be invoked as SQL functions or PL/SQL procedures or functions. See What is an External Procedure for more information.

Invoke OCI Cloud Functions as SQL Functions

Shows the steps to invoke OCI cloud functions as SQL functions in your Autonomous AI Database on Dedicated Exadata Infrastructure.

Before you proceed with these steps, it is assumed that you have created and deployed OCI Functions in a OCI tenancy and compartment. See OCI Functions for details.

Once you have working OCI Function(s), you will be using the DBMS_CLOUD and DBMS_CLOUD_FUNCTION PL/SQL APIs to create a catalog of SQL wrapper functions in the Autonomous AI Database that reference and call their respective cloud function via their API endpoints. You will be using DBMS_CLOUD_FUNCTION API to manage the functions from your database application.

  1. Create credentials using the DBMS_CLOUD.CREATE_CREDENTIAL Procedure.

    Provide the OCI tenancy user's API Private key (content of the dot-pem file that you downloaded when creating the API key) as the credential object, the key's fingerprint, the tenancy OCID, and the user OCID. See Required Keys and OCIDs. 

    SET DEFINE OFF
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => 'OCI_CRED', -- provide a string as the name
       user_ocid       => 'user_ocid', -- provide the OCID string for the user, obtained from OCI Console User Profile
       tenancy_ocid    => 'tenancy_ocid', -- provide the OCID string for the tenancy, obtained from OCI Console User Profile
       private_key     => 'private_key', -- provide the content of the dot-pem file that you downloaded when you created the API Key
       fingerprint     => 'fingerprint' -- provide the fingerprint string for the API key
  );
END;
/

    This creates the OCI_CRED credential object.

    See CREATE_CREDENTIAL Procedure for more information.

  2. Create a Catalog object using the DBMS_CLOUD_FUNCTION.CREATE_CATALOG Procedure.

    A catalog is a collection of wrapper functions that reference and call their respective cloud functions via their API endpoints. Provide the credential object, the cloud service provider name - in this case OCI, and the OCI Region ID (PHX in this example), and OCI Compartment ID in which the OCI Functions are located.

    BEGIN
DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
    credential_name  => 'OCI_CRED',
    catalog_name     => 'OCI_DEMO_CATALOG',
    service_provider => 'OCI',
    cloud_params     => '{"region_id":"phx", "compartment_id":"compartment_id"}'
);
END;
/

    This creates the OCI_DEMO_CATALOG catalog object.

    See CREATE_CATALOG Procedure for more information. You can query DBA_CLOUD_FUNCTION_CATALOG View and USER_CLOUD_FUNCTION_CATALOG View to retrieve the list of all the cloud function catalogs in your database.

  3. You can list all the cloud functions in the catalog using the DBMS_CLOUD_FUNCTION.LIST_FUNCTIONS Procedure.

    SET PAGESIZE 1000

VAR function_list CLOB;

BEGIN
    DBMS_CLOUD_FUNCTION.LIST_FUNCTIONS (
      credential_name  => 'OCI_CRED', 
      catalog_name     => 'OCI_DEMO_CATALOG',
      function_list    => :function_list
 );
END;
/

PL/SQL procedure successfully completed.

SELECT JSON_QUERY (:function_list, '$' RETURNING VARCHAR2(32676) pretty) AS search_results FROM dual;

SEARCH_RESULTS
------------------------------------------------------------------------------------------------
[
  {
    "functionName"   : "create_par",
    "functionId"     : "ocid.funfc.oc1.phx.aaaa_example", 
    "invokeEndpoint" : "https://dw.us.func.oci.oraclecloud_example.com"
  },
  {
    "functionName"   : "fintech",
    "functionId"     : "ocid.funfc.oc1.phx.bbbb_example"
    "invokeEndpoint" : "https://dw.us.func.oci.oraclecloud.com_example"
  },
  {
    "functionName"   : "jwt_codec",
    "functionId"     : "ocid.funfc.oc1.phx.jwt_code_example", 
    "invokeEndpoint" : "https://dw.us.func.oci.oraclecloud_example.com"
  },
  {
    "functionName"   : "oci-objectstorage-create-par-python",
    "functionId"     : "ocid.funfc.oc1.phx.aaaaaaaas_example", 
    "invokeEndpoint" : "https://dw.us.func.oci.oraclecloud_example.com"
  },
  {
    "functionName"   : "run_dbt",
    "functionId"     : "ocid.funfc.oc1.phx.aaaaaaaav_example",
    "invokeEndpoint" : "https://dw.us.func.oci.oraclecloud_example.com"
  }
]

    See LIST_FUNCTIONS Procedure for more information.

  4. Create the SQL Wrapper Function. You can use one of the following two methods to create the wrapper SQL functions from the catalog, that call their respective cloud functions:

    • You can use DBMS_CLOUD_FUNCTION. SYNC_FUNCTIONS Procedure to generate SQL wrappers from the catalog object.
    • You can manually create individual wrapper functions using DBMS_CLOUD_FUNCTION. CREATE_FUNCTION Procedure.
    1. SYNC_FUNCTIONS: SYNC_FUNCTIONS automatically syncs (creates and/or updates) wrapper functions in the catalog with the complete list of cloud functions defined in the region, compartment, and tenancy with which the catalog was created.
      BEGIN
 DBMS_CLOUD_FUNCTION.SYNC_FUNCTIONS (
        catalog_name => 'OCI_DEMO_CATALOG'
 );
END;
/

      This creates a PL/SQL wrapper for adding new functions to the catalog and removing wrappers for functions that have been deleted from the catalog.

      You can verify the sync results using the following query, for a given current user.

      SHOW user
TEST_USER

SELECT object_name FROM sys.all_objects WHERE owner='TEST_USER' AND object_type='FUNCTION';

OBJECT_NAME
--------------------------------------------------------------------------------
CREATE_PAR
FINTECH
JWT_CODEC
OCI-OBJECTSTORAGE-CREATE-PAR-PYTHON
RUN_DBT

      See SYNC_FUNCTIONS Procedure for more information.

    2. CREATE_FUNCTION: You can manually create a SQL Function in your catalog that calls its respective cloud function using DBMS_CLOUD.CREATE_FUNCTION. 

      VAR function_args CLOB;

EXEC :function_args := TO_CLOB('{"command": "VARCHAR2", "value": "VARCHAR2"}');

BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
        credential_name  => 'OCI_CRED',
        catalog_name     => 'OCI_DEMO_CATALOG',
        function_name    => 'FINTECH_FUNCTION',
        function_id      => 'ocid1.fnfunc.oc1.phx.aaabbbcccc_example',
        input_args       => :function_args
 );
END;
/

      This creates the FINTECH_FUN function in the OCI_DEMO_CATALOG catalog, as a reference to the respective cloud function whose endpoint is referenced by the FUNCTION_ID parameter. Invoking the function in the catalog along with its arguments runs the corresponding cloud function in OCI, and provides the output returned by the cloud function.

      Note:

      The name of the OCI Function can be something entirely different than FINTECH_FUNCTION. Only the OCID of the OCI Function that you provide as input to the function_id parameter is taken into account to create this reference.

    3. CREATE_FUNCTION with custom return types and response handlers: Manual creation of functions allows you to create custom return types and response handlers, and shown in the following example.

      First create a return type and the function's response handler.

      CREATE OR REPLACE TYPE fintech_rt AS OBJECT (
    status VARCHAR2(1000),  
    output CLOB
);
/
Type created.

CREATE OR REPLACE FUNCTION fintech_response_handler(function_response IN CLOB) 
RETURN fintech_rt
IS
      l_comp     fintech_rt;
      l_json_obj JSON_OBJECT_T;
      status     VARCHAR2(1000);
      output     CLOB;
BEGIN
      l_json_obj := JSON_OBJECT_T.parse(function_response); 
      status     := l_json_obj.get('STATUS').to_string;
      output     := l_json_obj.get('RESPONSE_BODY').to_string;
      l_comp     := fintech_rt(status,output);
      RETURN l_comp;
END;
/
Function created.

      Next, use this type and response handler during the manual creation of the SQL Wrapper Function.

      VAR input_param CLOB;
VAR l_return_type VARCHAR2(100);
VAR l_response_handler VARCHAR2(1000);

-- Define function parameters
exec :input_param       := TO_CLOB('{"command": "VARCHAR2", "value": "VARCHAR2"}');

PL/SQL procedure successfully completed.

exec :l_return_type     := 'fintech_rt';

PL/SQL procedure successfully completed.

exec :l_response_handler := 'fintech_response_handler';

PL/SQL procedure successfully completed.

BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
        credential_name  => 'OCI_CRED',
        catalog_name     => 'OCI_DEMO_CATALOG',
        function_name    => 'FINTECH_FUNCTION',
        function_id      => 'ocid1.fnfunc.oc1.phx.aaabbbcccc_example',
        input_args       => :input_param,
        return_type      => :l_return_type,
        response_handler => :l_response_handler
 );
END;
/

      You can query DBA_CLOUD_FUNCTION View and USER_CLOUD_FUNCTION View views to retrieve the list of all the functions in your database.

      See CREATE_FUNCTION Procedure for more information.

      Once the function is created, you can DESCRIBE the function to get its return details.

      DESC fintech_fun
COLUMN STATUS format a30
COLUMN OUTPUT format a30

      You can then invoke the function, providing the values for input parameters.

      SET SERVEROUTPUT ON

DECLARE
    l_comp fintech_rt;
BEGIN
    l_comp := fintech_fun(command=>'tokenize',value => 'PHI_INFORMATION');

    DBMS_OUTPUT.put_line ('Status of the function   =  '|| l_comp.status);
    DBMS_OUTPUT.put_line ('Response of the function =  '|| l_comp.output);
END;
/

      This invokes the fintech_fun cloud function by calling the function reference oocid1.funfn.oci.phx.aaaaaa_example in the OCI_DEMO_CATALOG catalog.

  5. You can drop an existing function using the DROP_FUNCTION procedure.

    BEGIN
    DBMS_CLOUD_FUNCTION.DROP_FUNCTION (
        catalog_name  => 'OCI_DEMO_CATALOG',
        function_name => 'fintech_fun');
END;
/

    This drops the FINTECH_FUN function from the OCI_DEMO_CATALOG catalog.

    See DROP_FUNCTION Procedure for more information.

  6. You can drop an existing catalog using the DROP_CATALOG procedure.

    BEGIN
    DBMS_CLOUD_FUNCTION.DROP_CATALOG (
        catalog_name => 'OCI_DEMO_CATALOG'
  );
END;
/

    This drops the OCI_DEMO_CATALOG from your database.

    See DROP_CATALOG Procedure for more information.

Invoke AWS Lambda Functions as SQL Functions

Shows the steps to invoke AWS remote functions as SQL functions in your Autonomous AI Database on Dedicated Exadata Infrastructure.

Before you proceed with these steps, it is assumed that you have created and deployed AWS Lambda Functions in a AWS tenancy. See AWS Lambda for details.

To access AWS lambda functions you need to configure the necessary policies in Oracle Cloud Infrastructure. See Creating an IAM policy to access AWS Lambda resources and Using resource-based policies for Lambda for more information.

Once you have working AWS Lambda function(s), you will be using the DBMS_CLOUD and DBMS_CLOUD_FUNCTION PL/SQL APIs to create a catalog of SQL wrapper functions in the Autonomous AI Database that reference and call their respective cloud function via their API endpoints. You will be using DBMS_CLOUD_FUNCTION API to manage the functions from your database application.

  1. Create a credential using the procedure DBMS_CLOUD.CREATE_CREDENTIAL using the AWS Secret Key as the credential object.
    SET DEFINE OFF

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'AWS_CRED',
    username        => 'access_key_ID', -- ID of Secret Key
    password        => 'secret_access_key' -- Secret Key password
  );
END;
/

    This creates the AWS_CRED credential object.

    See CREATE_CREDENTIAL Procedure for more information.

  2. Create a Catalog object using the DBMS_CLOUD_FUNCTION.CREATE_CATALOG Procedure.

    A catalog is a collection of wrapper functions that reference and call their respective cloud functions via their API endpoints. Provide the credential object, the cloud service provider name - in this case AWS, and the AWS Region ID (ap-northeast-1 in this example) in which the AWS Lambda Functions are located. 

    BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
        credential_name  => 'AWS_CRED', 
        catalog_name     => 'AWS_DEMO_CATALOG', 
        service_provider => 'AWS',
        cloud_params     => '{"region_id":"ap-northeast-1"}'
 );
END;
/

    This creates the AWS_DEMO_CATALOG catalog object.

    See CREATE_CATALOG Procedure for more information. You can query DBA_CLOUD_FUNCTION_CATALOG View and USER_CLOUD_FUNCTION_CATALOG View to retrieve the list of all the catalogs in your database.

    The rest of the steps/operations - namely, LIST_FUNCTIONS Procedure, SYNC_FUNCTIONS Procedure, CREATE_FUNCTION Procedure, DROP_FUNCTION Procedure, and DROP_CATALOG Procedure - have the same usage as described for OCI cloud functions. See Invoke OCI Cloud Functions as SQL Functions.

External Procedures Overview

Provides an overview of External Procedures and their use in Autonomous AI Database on Dedicated Infrastructure applications.

External procedures are functions written in a third-generation language and invoked as SQL functions or PL/SQL procedures or functions. SQL and PL/SQL are best suited for fast and efficient data and transaction processing in the database. External procedures can complement SQL and PL/SQL by executing compute and memory-intensive tasks in a dedicated, external VM, and providing the results back to the database. Typical examples of such tasks are scientific and engineering problems whose computational libraries exist in the Linux OS (and are not easily portable to a data platform), offline data analysis, control of real-time devices and processes, and so on.

You can invoke and use external procedures in your Autonomous AI Database with user defined functions. Rather than executing these procedures within the database, you will place the executable code in a dedicated, custom OCI Linux VM that is provisioned with a container and runtime to execute C programs, Shell scripts, and Python scripts, along with Oracle SQL*Net libraries to enable remote SQL execution from the database.

Note:

The remote host for external procedures must be an OCI Linux EXTPROC VM provisioned from the OCI Marketplace and configured in a suitable OCI Virtual Cloud Network (VCN). See System Configuration in OCI for External Procedures and Scriptsfor details.

Remote execution on OCI Linux EXTPROC VM is supported only by Autonomous AI Database on Dedicated Exadata Infrastructure on Oracle Public Cloud starting with version 19.30, and in Oracle Database 26ai, starting with version 23.26.1. This feature is not yet supported by Autonomous AI Database on Dedicated Exadata Infrastructure database on Exadata Cloud@Customer (ExaCC).

System Configuration for External Procedures and Scripts

Shows the steps to provision the EXTPROC VM, and the steps to configure OCI network and compute for executing external procedures.

Minimally, the architecture consists of the following resources:

  • OCI Virtual Cloud Network provisioned in an OCI Compartment in the OCI tenancy.
  • An Autonomous AI Database, created as an instance under an Autonomous Container Database (ACD), that is provisioned from an Autonomous Exadata VM Cluster (AVMC), that is created on the Exadata Infrastructure on Oracle Public Cloud.
  • An EXTPROC VM provisioned as part of an EXTPROC Stack from the OCI Marketplace.

As a best practice, the AVM cluster will be placed in a private subnet in a VCN. The Default route table, and the Security Lists of the VCN will be configured as per the Autonomous AI Database requirements in the OCI tenancy. The VCN may have a Default Route table and one Default Security List, where you can define all the routing and security rules. Optionally, the VCN may be configured with a NAT gateway if Autonomous AI Database applications and other resources in the private subnet need Egress access to the Internet.

Follow these minimal OCI Networking configuration steps when you introduce the ExtProc VM into this topology.

  • You must be able to login to your ExtProc VM to build the shared library (dot-so) with the external procedures implemented in C/C++, inspect and configure the VM and container, and perform other such operations.
  • The typical practice is to configure the EXTPROC VM to be in a public subnet in the VCN. In this case, you must create an Internet Gateway in the VCN (if not already configured by the administrator).

    Note:

    The EXTPROC VM must NOT be in a subnet whose CIDR range is 10.x.x.x. If the IP address of the EXPROC VM is 10.x.x.x, it will not be able to communicate with the Autonomous AI Database.

  • You must define an Ingress rule in the VCN’s Security List to allow for SSH login access to the VM via Port 22. You must also define an Egress rule in VCN’s Security List to allow ADBD to access the Destination Port 16000 to be specific, or all destination ports, in general. See OCI Networking Access and Security.
  • As part of provisioning the EXTPROC VM Stack, you will provide all the Client IP addresses of the AVMC. You will obtain these IPv4 Client IP addresses from the OCI Dashboard for the AVM Cluster.
  • Subsequently, the provisioning tool will automatically create a network security group (NSG) - named nsg_acl. This NSG will have an Ingress Rule allowing the Autonomous Database Dedicated Client IP addresses as the only sources that can access the Port 16000 destination.

Advanced Topics

  • If the subnets hosting the ExtProc VM and Autonomous AI Database are in the same VCN, you need not specify any special routing rules in the VCN's routing table. If the subnets hosting the ExtProc VM and ADBD are in different VCNs, then you must implement VCN peering. See VCN Peering.
  • If you choose to configure the ExtProc VM to be in a private subnet, you must be able to login to the VM via a bastion/jump-off server and enable Ingress only into the ExtProc VM, and secure access to the other resources in the private subnet, such as the Autonomous AI Database. See the OCI Networking Guides for details.

Implement User Defined Functions with External Procedures

  1. Provision and Configure the EXTPROC VM from the OCI MarketPlace

    1. Sign in to the OCI Console at http://cloud.oracle.com. See Sign in to the Oracle Cloud Infrastructure Console for more information.
    2. From the Oracle Cloud Infrastructure left navigation menu, drop down to Marketplace and then, under Marketplace, click All Applications.
    3. Enter "EXTPROC" in the Search bar, and click Search. You will see two widgets - one labelled Stack, and another labelled Image.
    4. Choose the widget named Stack (this is important). This will take you to Oracle Autonomous Database EXTPROC Agent details page, providing the stack version, release date, and release notes about the Stack. On this page, click on the Launch Stack button. This takes you to the next Launch Stack page.

    5. On the Launch Stack page:

      • From the Version drop-down list, choose the package version of the stack.
      • From the Compartment drop-down list, select the name of the compartment where you want to provision the EXTPROC VM. Unless you have specific reasons not to, choose the same compartment as that of the ADBD (as a best practice, avoid the root compartment for any resource). Review System Configuration for External Procedures and Scripts to make the appropriate choice for the compartment.
      • Accept the terms and conditions, and click on Launch Stack.

      This takes you to the next page - the Create Stack wizard.

    6. In this page, provide a name, description, Compartmentname, the Terraform version, and any Tag information. The compartment name is mandatory, and most important, input to the wizard in this page. Click Next.
    7. This takes you to the configuration variables page for the EXTPROC VM (also called the EXTPROC Agent). The first part of this page gathers the information on the EXTPROC libraries and a wallet password.
      • For External Libraries, provide a list of libraries, separated by comma (,), that you want to allow to be invoked from your Autonomous AI Database on Dedicated Infrastructure. These shared libraries will contain the external procedures written in C/C++, that are ideally compiled and built in the VM itself (to match the architecture and environment).

        Note:

        Given that your are still in the process of creating the EXTPROC VM, note down the names of the shared libraries that you provide here. When the VM is available, and you develop your external procedures in C/C++, your must remember to compile and build the library/libraries with the exact name(s) provided above.
      • For Wallet Password, provide a password for the wallet file that will be created in the ExtProc VM. The wallet and a self-signed certificate is generated for mutual TLS authentication between the Autonomous AI Database on Dedicated Infrastructure and the EXTPROC Agent VM.
    8. The second part of the configuration variables page involves network configuration for the EXTPROC VM.
      • For Compartment, choose the compartment name for the VCN from the pulldown menu. In the above example, it is shown as adbd. Ideally, the Autonomous AI Database and the EXTPROC VM will be colocated in the same VCN, even if in different (private and public) subnets.
      • For Network Strategy, choose "Use Existing VCN and Subnet". As stated in a previous section, at this stage of your ExtProc development effort, you may have the system architecture in place with a VCN, AVMC, and Autonomous AI Database.

        Note:

        The other choice is to create a new VCN and Subnet with its own configuration strategy for the EXTPROC VM Agent. As stated in the System Configuration for External Procedures and Scripts, if you choose to place the EXTPROC VM in a VCN different than that of the Autonomous AI Database, you must peer the VCNs.
      • Skip the next entry for EXTPROC Agent Access Type. This field is not applicable for Autonomous AI Database on Dedicated Exadata Infrastructure.
    9. Next, in the field labeled "Private Endpoint IP Addresses", enter ALL the Client IP addresses of the AVMC on which the database is configured. You can obtain these Client IP addresses from the OCI Console Dashboard for the AVMC.
    10. Next, provide the details of the VCN and the Subnet.

      Note:

      The EXTPROC VM must NOT be in a subnet whose CIDR range is 10.x.x.x. If the IP address of the EXPROC VM is 10.x.x.x, it will not be able to communicate with the Autonomous AI Database.
    11. The final part of the configuration page involves the compute configuration for the EXTPROC VM Agent.
      • For Compartment, choose the compartment name from the pulldown menu. It is recommended that you colocate the VM in the same compartment as that of the Autonomous AI Database on Dedicated Infrastructure, the VCN and the subnet to minimize troubleshooting.
      • For Shape, Number of OCPUs, and Memory size (GBs), enter values based on the workload characteristics of your external procedures.
      • For Add SSH keys, generate the SSH public and private key files in your Linux OS, MacOS, or Windows system. Open a terminal window, and run one of the following commands (ED25519 is the modern, safer algorithm; older systems support the RSA encryption algorithm):
        ssh-keygen -t ed25519 -C "your_email@example.com" (OR)
ssh-keygen -t rsa -b 4096 -C "your_email@example.com"

        This will generate a private key file ssh-key-<date-time-id>.key and a public key file ssh-key-<date-time-id>.key.pub. Copy-paste the contents of the SSH public key into the window, or drag-drop the .pub file into the window.

      • Click Next.

    12. This takes you to the Review Page. Review all the inputs, then select the Run Apply checkbox, and click on Create.

      This will launch the OCI Resource Manager to create the EXTPROC VM Stack. Once the ORM Job is completed successfully, the EXTPROC VM will be created and running.

  2. Gather EXTPROC VM system information for DBMS_CLOUD_FUNCTION API input

    You must provide EXTPROC VM system information as inputs to the DBMS_CLOUD_FUNCTION APIs in the Autonomous AI Database to enable invocation of external procedures from the database.

    1. From the Oracle Cloud Infrastructure left navigation menu, drop down to Compute, choose the compartment you specified for the EXTPROC VM provisioning, and review the dashboard listing all the VMs in the compartment. The ORM Job will have created the EXTPROC VM with a canonical name extproc-agent, or extproc-agent-<setofnumbers>. This will be the hostname of your EXTPROC VM instance. Clicking on this entry will take you to the page that shows the system details - the IPV4 Public and Private Addresses (IPv4 and IPv6), the subnet, and the image details.
    2. Login to the ExtProc VM using the SSH key that you provided during ExtProc Stack creation, and the IP address. The default user on the VM is opc. sudo to become user oracle.
      ➜  ~ ssh -i ssh-key-<date-time-id>.key opc@<Public-IP-Address>
Wed Nov 19 10:46:25 GMT 2025: EXTPROC Agent intialization completed.

Activate the web console with: systemctl enable --now cockpit.socket

Last login: Thu Nov 20 20:10:54 2025 from <client-IP-address>

[opc@extproc-agent-170798 ~]$ whoami
opc

[opc@extproc-agent-170798 ~]$ sudo su - oracle
Last login: Wed Nov 19 19:07:24 GMT 2025 on pts/0

      The EXTPROC VM will have a running Podman container with a Oracle SQL*Net listener. Within the container, an Oracle client environment with the necessary configuration files like sqlnet.ora, listener.ora will be available, along with a wallet file for secure communication with the Autonomous AI Database.

    3. Collect the system information required for the PL/SQL APIs - at the OS level, and next, from the Podman container that is configured in the VM. The container runs the Oracle Listener to receive remote execution requests from the SQL and PL/SQL application on Autonomous AI Database, and is the runtime execution engine for the external procedure.
      • From the Linux OS, the FQDN (fully qualified domain name) of the EXTPROC VM host.
      • From the Podman container, from the listener status, the Port on the EXTPROC VM on which the database connects to the VM.
      • From the Podman container, from the the sqlnet.ora specification, the location of the wallet file.
      • From an inspection of the wallet file using the orapki tool, the Distinguished Name (DN) parameter value for the wallet certificate.
      [oracle@extproc-agent-170798 ~]$ hostname -f
extproc-agent-170798.subnet-name.vcn-name.oraclevcn.com

[oracle@extproc-agent-170798 ~]$ podman ps
CONTAINER ID  IMAGE                              COMMAND     CREATED       STATUS                 PORTS       NAMES
86d81c4df6ff  ghcr.io/oracle/adb-extproc:latest              23 hours ago  Up 23 hours (healthy)              adb-extproc

[oracle@extproc-agent-170798 ~]$ podman exec -it 86d81c4df6ff bash

(base) [oracle@extproc-agent-170798 admin]$ lsnrctl status

LSNRCTL for Linux: Version 23.26.0.0.0 - for Oracle Cloud and Engineered Systems on 20-NOV-2025 09:25:54

Copyright (c) 1991, 2025, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 23.26.0.0.0 - for Oracle Cloud and Engineered Systems
Start Date                19-NOV-2025 17:19:58
Uptime                    0 days 16 hr. 5 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/23.0.0.0/client_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/extproc-agent-170798/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=0.0.0.0)(PORT=16000)))
Services Summary...
Service "extproccontainer.com" has 1 instance(s).
  Instance "extproccontainer", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

(base) [oracle@extproc-agent-170798 /]$ cd $ORACLE_HOME/network/admin

(base) [oracle@extproc-agent-170798 admin]$ cat sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
WALLET_LOCATION =(SOURCE = (METHOD = FILE)(METHOD_DATA = (DIRECTORY = /u01/app/oracle/wallets/extproc_wallet)))
tcp.invited_nodes=(20.63.19.141,20.63.19.141,20.63.19.143,20.63.19.144)
tcp.validnode_checking=yes

(base) [oracle@extproc-agent-170798 admin]$ orapki wallet display -wallet /u01/app/oracle/wallets/extproc_wallet/cwallet.sso
Oracle PKI Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2025, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=extproc-agent-170798
Trusted Certificates:
Subject:        CN=extproc-agent-170798
(base) [oracle@extproc-agent-170798 admin]$ exit
exit
      In the above example, we have collected the following inputs for the DBMS_CLOUD_FUNCTION.CREATE_CATALOG API.
      • The string formed by FQDN + Port Number will be the input for the library_listener_url parameter. In this example, it is 'extproc-agent-170798.subnet-name.vcn-name.oraclevcn.com:16000'.
      • sqlnet.ora contains the location of the wallet file in the VM: /u01/app/oracle/wallets/extproc_wallet/cwallet.sso. Note the IP address list for tcp.invited_nodes. It must contain the list of Client IP addresses that you provided as input for network configuration while creating the EXTPROC VM Stack.
      • The Certificate Distinguished Name (DN) will be the input for library_ssl_server_cert_dn parameter: 'CN=extproc-agent-170798'.
  3. Build the C Library and place it in the specified location in the EXTPROC VM Filesystem

    Code the C Functions, build it, and place the library in the designated directory in the ExtProc Agent VM's filesystem.

    The name of the library must exactly match the name(s) you provided during the creation of the ExtProc Stack. In case you misplaced it, you can find it in the EXTPROC_DLLS entry of the ExtProc container initextproccontainer.ora file as shown below, or in the "Variables" section of the ORM (Oracle Resource Manager) Job that created the ExtProc Stack. You must copy the libraries to the /u01/app/oracle/extproc_libs directory on the EXTPROC agent VM.
    • The full file pathname (in this example "/u01/app/oracle/extproc_libs/helloCextproc.so") will be the input to the parameter library_remote_path of DBMS_CLOUD_FUNCTION.CREATE_CATALOG(). 
    (base) [oracle@extproc-agent-170798 client_1]$ cat $ORACLE_HOME/hs/admin/initextproccontainer.ora
SET TRACE_LEVEL=ON
SET _EXTPROC_REMOTE=TRUE
SET WHOAMI=FROMDOCKER
SET LD_LIBRARY_PATH=/u01/app/oracle/product/23.0.0.0/client_1/lib:/u01/app/oracle/extproc_libs:/opt/conda/lib
SET EXTPROC_DLLS=ONLY:/u01/app/oracle/product/extprocutils.so:/u01/app/oracle/product/23.0.0.0/client_1/lib/libgsfextproc.so:/u01/app/oracle/extproc_libs/helloCextproc.so
SET PYTHONHOME=/opt/conda
SET SCRIPTS_FOLDER_LOC_ENV=/u01/app/oracle/extproc_scripts
SET TRACE_FILE_LOC_ENV=/u01/app/oracle/extproc_logs
SET PYTHONPATH=/tmp:/u01/app/oracle/extproc_scripts
(base) [oracle@extproc-agent-170798 client_1]$ exit

[oracle@extproc-agent-170798 ~]$ cat > helloCextproc.c
#include <stdio.h>
const char* helloCextproc() {
        return ("\nHello C Extproc from FQDN: extproc-agent-170798.subnet-name.vcn-name.oraclevcn.com\n");
}
^D

[oracle@extproc-agent-170798 ~]$ gcc -shared -fPIC -o /u01/app/oracle/extproc_libs/helloCextproc.so helloCextproc.c

[oracle@extproc-agent-170798 ~]$ ls -al /u01/app/oracle/extproc_libs/
total 8
drwxr-xr-x. 2 oracle oinstall   30 Nov 19 11:14 .
drwxr-xr-x. 6 root   root       91 Nov 14 02:19 ..
-rwxr-xr-x. 1 oracle oinstall 8184 Nov 19 11:14 helloCextproc.so
[oracle@extproc-agent-170798 ~]$

    This will complete all the parameter inputs that we require for creating the Catalog/Library object in the database application.

  4. Upload the wallet file from the ExtProc VM to OCI Object Storage

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

    To execute remote procedures 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.

    Note:

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

    As a prerequisite, you must export the wallet from the /u01/app/oracle/extproc_wallet directory on the VM where EXTPROC runs, to OCI Object Storage.

    Note:

    • Secure 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.
  5. Create the catalog, define SQL Functions, and Invoke External Procedures as SQL Functions

    From the previous steps, we have all the inputs we need for the DBMS_CLOUD_FUNCTION APIs to execute remote procedures from SQL or PL/SQL database applications.

    Next, import the wallet, cwallet.sso, containing the certificates for the EXTPROC agent instance from Object Storage into a DIRECTORY in your Autonomous AI Database.

    Create a credential to access your Object Storage where you stored the wallet file cwallet.sso. See CREATE_CREDENTIAL Procedure for information about the username and password parameters for different object storage services.

    Create a DIRECTORY object in the database, and download the wallet into the directory using DBMS_CLOUD.GET_OBJECT API.

    SQL> SET DEFINE OFF

SQL> BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
	credential_name => 'OCI_CREDENTIAL',
	user_ocid       => '<oci_user_ocid>',
	tenancy_ocid    => '<oci_tenancy_ocid>',
	private_key     => '<API-key-dot-pem-file-contents>',
	fingerprint     => '<fingerprint-created-with-API-key>');
END;
/

SQL> CREATE DIRECTORY extprocwalletdir AS 'extprocwalletdir';

Directory created.

SQL> SELECT directory_name, directory_path FROM dba_directories WHERE directory_name LIKE '%EXTPROC%';

DIRECTORY_NAME    DIRECTORY_PATH
----------------  -------------------------------------------------------------------------
EXTPROCWALLETDIR  /u02/data/dbfs/<adbd-name>/42E945D608E16DF9E0630301000AF88D/extprocwalletdir

SQL> BEGIN
  DBMS_CLOUD.GET_OBJECT (
  credential_name     => 'OCI_CREDENTIAL',
  object_uri          => 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/aN.../n/zr.../b/bucket-name-20260129/o/cwallet.sso',
  directory_name      => 'EXTPROCWALLETDIR'
  );
END;
/

    Next, create the Library Object in the database that represents the C Library in the ExtProc, with the input parameters you have gathered in the previous step.

    SQL> BEGIN
  DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
     library_name               => 'EXTPROC_LIBRARY',
     library_listener_url       => 'extproc-agent-170798.subnet-name.vcn-name.oraclevcn.com:16000',
     library_wallet_dir_name    => 'EXTPROCWALLETDIR',
     library_ssl_server_cert_dn => 'CN=extproc-agent-170798',
     library_remote_path        => '/u01/app/oracle/extproc_libs/helloCextproc.so'
  );
END;
/
PL/SQL procedure successfully completed.

SQL> SELECT catalog_name from DBA_CLOUD_FUNCTION_CATALOG WHERE catalog_name LIKE '%EXTPROC%';

CATALOG_NAME
---------------
EXTPROC_LIBRARY

    Create the SQL function mapped to the ExtProc C function. See the name of the C function from the program written above.

    SQL> CREATE OR REPLACE FUNCTION HELLOCEXTPROC RETURN VARCHAR2 AS
    LANGUAGE C
    LIBRARY EXTPROC_LIBRARY
    NAME "helloCextproc";
/

Function created.

SQL>

    Invoke the SQL Function. It is a successful run (see the body of the C Function from C routine above).

    SQL> SELECT HELLOCEXTPROC() FROM dual;

HELLOCEXTPROC()
----------------------------------------------------------------------------------
Hello C Extproc from FQDN: extproc-agent-170798.subnet-name.vcn-name.oraclevcn.com