Invoke External Procedures as SQL Functions

Shows the steps to invoke external procedures using PL/SQL within your database.

External Procedures Overview
External procedures are functions written in a third-generation language and callable from within PL/SQL or SQL as if they were a PL/SQL routine or function.
About Using External Procedures in Autonomous Database
You can invoke and use external procedures in your Autonomous Database with user defined functions.
Define the C Procedure
Define the C procedure using one of these prototypes.
Create a Shared Library (.so) File
Create a shared object (.so file) library. The shared object library contains the C procedure (external procedure) which was defined in the previous step.
Get the OCI Marketplace EXTPROC Stack Application
Shows the steps to get the OCI Marketplace EXTPROC Stack Application.
Launch EXTPROC Stack Application
Launch the EXTPROC Stack Application from the EXTPROC Application Details page.
Create Stack for EXTPROC Agent Application
Shows the steps to create Stack for EXTPROC 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 Extrpoc agent instance.
Steps to Invoke an External Procedure as a SQL Function
Shows the steps to invoke an external Procedure as a SQL function.

Parent topic: Invoke User Defined Functions

External Procedures Overview

External procedures are functions written in a third-generation language and callable from within PL/SQL or SQL as if they were a PL/SQL routine or function.

External procedures promote reusability, efficiency, and modularity. Existing dynamic link libraries (DLLs) written in other languages can be called from PL/SQL programs. The DLLs are loaded only when needed and they can be enhanced without affecting the calling programs.

Using external procedures also enhances performance, because third-generation languages perform certain tasks more efficiently than PL/SQL, which is better suited for SQL transaction processing.

External procedures are useful when:

Solving scientific and engineering problems
Analyzing data
Controlling real-time devices and processes

See What Is an External Procedure? for more information.

Parent topic: Invoke External Procedures as SQL Functions

About Using External Procedures in Autonomous Database

You can invoke and use external procedures in your Autonomous Database with user defined functions.

You do not install external procedures on an Autonomous Database instance. To use an external procedure, the procedure is hosted remotely on a VM running in an Oracle Cloud Infrastructure Virtual Cloud Network (VCN).

External procedures are only supported when your Autonomous Database is on a private endpoint. The EXTPROC agent instance is hosted on a private subnet and the Autonomous Database access the EXTPROC agent through a Reverse Connection Endpoint (RCE).

Note:

Autonomous Database only supports C language external procedures.

External procedures are deployed by using:

An Oracle provided container image with EXTPROC agent installed and configured as a part of the Oracle Cloud Infrastructure (OCI) Marketplace stack.

The EXTPROC agent instance is hosted remotely on a VM running in an Oracle Cloud Infrastructure Virtual Cloud Network (VCN). The secure communication between your Autonomous Database and the EXTPROC agent instance is ensured 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 execute external procedures on port 16000.
PL/SQL procedures to create a library and to register and invoke external functions and procedures.

See DBMS_CLOUD_FUNCTION Package for more information.

Follow these steps to invoke an external procedure on Autonomous Database:

Define the C procedure. See Define the C Procedure.
Create a shared object (.so file) library. See Create a Shared Library (.so) File.
Launch the Autonomous Database EXTPROC stack application. See Get the OCI Marketplace EXTPROC Stack Application.
Provision and configure Oracle Autonomous Database EXTPROC agent. See Create Stack for EXTPROC Agent Application for more information.
Configure your Autonomous Database to connect to the EXTPROC agent instance. See Upload Wallet to Create Secure Connection to the EXTPROC Agent Instance for more information.
Create a remote library using DBMS_CLOUD_FUNCTION.CREATE_CATALOG. See Steps to Invoke an External Procedure as a SQL Function for more information.
Use the user defined function you created in the previous step. See Steps to Invoke an External Procedure as a SQL Function for more information.

Parent topic: Invoke External Procedures as SQL Functions

Define the C Procedure

Define the C procedure using one of these prototypes.

Kernighan & Ritchie style prototypes. For example:
```
void UpdateSalary(x)
 float x;
...
```
ISO/ANSI prototypes other than numeric data types that are less than full width (such as float, short, char). For example:
```
void UpdateSalary(double x)
...
```
Other data types that do not change size under default argument promotions.

This example changes size under default argument promotions:
```
void UpdateSalary(float x)
...
```

Parent topic: Invoke External Procedures as SQL Functions

Create a Shared Library (.so) File

Create a shared object (.so file) library. The shared object library contains the C procedure (external procedure) which was defined in the previous step.

You generate a shared object library using the following command:

gcc -I/u01/app/oracle/extproc_libs/ -shared -fPIC -o extproc.so UpdateSalary.c

This creates the shared object (.so), extproc.so library. The UpdateSalary procedure, defined in the previous step, is contained in the extproc.so library. The shared object (.so) libraries are dynamically loaded at run time.

Parent topic: Invoke External Procedures as SQL Functions

Get the OCI Marketplace EXTPROC Stack Application

Shows the steps to get the OCI Marketplace EXTPROC Stack Application.

Perform the following steps:

Sign in to the OCI Console at http://cloud.oracle.com. See Sign in to the Oracle Cloud Infrastructure Console for more information.
From the Oracle Cloud Infrastructure left navigation menu click Marketplace and then, under Marketplace click All Applications. This takes you to the Marketplace All Applications dashboard.
Enter EXTPROC in the search field and click search.
Click the EXTPROC widget of Type: Stack.

Description of the illustration adbs_extproc_application.png

This takes you to the Oracle Autonomous Database EXTPROC Agent details page.

Parent topic: Invoke External Procedures as SQL Functions

Launch EXTPROC Stack Application

Launch the EXTPROC Stack Application from the EXTPROC Application Details page.

On the Oracle Autonomous Database EXTPROC Agent page, under Type Stack, perform the following:
- From the Version drop-down list, select the package version of the stack. By default, the menu displays the latest version.
- From the Compartment drop-down list, select the name of the compartment where you want to launch the instance.
  
  Note:
  If you don't have permission to launch the instance in the selected compartment, the instance is launched in the root compartment.
- Select the I have reviewed and accept the Oracle Standard Terms and Restrictions checkbox.
Click Launch Stack.

Description of the illustration adb_launch_stack.png

This takes you to the Create stack page that allows you to create stack for the EXTPROC agent.

Parent topic: Invoke External Procedures as SQL Functions

Create Stack for EXTPROC Agent Application

Shows the steps to create Stack for EXTPROC instance.

In the Create stack wizard, perform the following steps:

On the Stack information page, review and edit the following information as necessary:
- Stack information
- Custom providers
- Name (Optional): You can edit the default stack name. Avoid entering confidential information.
- Description (Optional): You can edit the default stack description. Avoid entering confidential information.
- Create in compartment
- Terraform version
- Tags: Provide the following to assign tags to the stack.
  - Tag namespace: To add a defined tag, select an existing namespace. To add a free-from tag, leave the value blank.
  - Tag key: To add a defined tag, select an existing tag key. To add a free-form tag, type the key name that you want.
  - Tag value: Type the tag value that you want.
  Add tag: Click to add another tag.
  
  See Resource Tags for more information on tagging.
Description of the illustration adbs_extproc_createstack_updated.png
Click Next.
This takes you to the Configure variables page which enables you to configure variables for the infrastructure resources that the stack creates when you run the apply job for this execution plan.
On the Configure variables page enter the information in the areas: Configure the EXTPROC Agent, Network Configuration, and Compute configuration.
1. Provide information in the Configure the EXTPROC Agent area.
  - External Libraries: Provide a list of libraries, separated by comma (,), which you want to allow to be invoked from your Autonomous Database. For example, extproc.so, extproc1.so.
    
    After you create the stack, you must copy the libraries to the /u01/app/oracle/extproc_libs directory on the EXTPROC agent VM.
  - Wallet Password: Provide the wallet password.
    
    The wallet and a self-signed certificate is generated for mutual TLS authentication between the Autonomous Database and the EXTPROC agent VM. The wallet is created in the /u01/app/oracle/extproc_wallet directory.
    
    Note:
    After the wallet is created, the wallet password cannot be changed.
  Description of the illustration adbs_configure_var_extproc_agent.png
2. Provide information in the Network Configuration area.
  - Compartment: From the drop-down list, choose the compartment where you want to place the configuration.
  - Network Strategy: Choose one of the options from the drop-down list, Create New VCN and Subnet or Use Existing VCN and Subnet.
    - Create New VCN and Subnet: Choose this option if a private endpoint is not configured for your Autonomous Database. This creates a new VCN with public and private subnet that are preconfigured with security rules.
      
      If you select this option the page also shows the Configuration Strategy drop-down list:
      
      Choose Use Recommended Configuration from the Configuration Strategy drop-down list.
      
      Description of the illustration adb_extproc_network_config.png
    - Use Existing VCN and Subnet: Select this option to create the EXTPROC agent using an existing VCN. This creates the EXTPROC agent instance in the provided subnet.
      
      When you select this option, provide the following information for the existing VCN and Subnet:
      
      Under Virtual Cloud Network:
      
      From the Existing VCN drop-down list choose an existing VCN. If the specified VCN does not exist, a new VCN is created.
      
      Under EXTPROC Subnet:
      
      From the Existing Subnet drop-down list choose an existing subnet.
      
      When you choose to use an existing VCN and subnet, add an ingress rule for the EXTPROC agent instance's port 16000. You also add an egress rule on public subnet.
      
      See Configuring Network Access with Private Endpoints for more information.
      
      Description of the illustration adbs_extproc_network_configuration_existing_vcn.png
  - EXTPROC Agent Access type: Choose one of the following options from the drop-down list.
    - Secure access from specific ADB-S Private Endpoint databases in your VCN: Choose this option to allow only specified private endpoint IPs inside your Virtual Cloud Network (VCN) to connect to your EXTPROC agent.
      
      When this option is chosen, you provide a list of allowed private endpoint IP Addresses in the next step.
    - Secure access from all ADB-S Private Endpoint databases in your VCN: Choose this option to allow any private endpoint inside your Virtual Cloud Network (VCN) to connect to your EXTPROC agent.
  - Private Endpoint IP Addresses
    
    Provide a list of private endpoint IP addresses separated by comma (,) for the Private Endpoint IP Addresses variable. For example, 10.0.0.0, 10.0.0.1.
    
    Note:
    This field only shows when you select Secure access from specific ADB-S Private Endpoint databases in your VCN for the EXTPROC Agent Access type.
3. Provide the Compute configuration information.
  - Compartment: Select the compartment where you want to create the stack.
  - Shape: Select a shape based on the workload requirements of the EXTPROC agent instance. The shape determines the resources allocated to the EXTPROC agent instance.
  - Number of OCPUs: Choose the number of OCPUs that you want to allocate to the EXTPROC agent instance.
  - Memory size (GBs): Choose the amount of memory in Gigabytes (GB) that you want to allocate to the EXTPROC agent instance.
  - Add SSH keys: Upload an SSH public key or paste the public key. Select one of the following options:
    
    Choose SSH key file: Upload the public key portion of your key pair. Either browse to the key file that you want to upload, or drag and drop the file into the box.
    
    Paste SSH key: Paste the public key portion of your key pair in the box.
  Description of the illustration adbs_extproc_compute_config.png
Click Next.

This takes you to the Review page.
On the Review page perform the following steps:
1. Verify the configuration variables.
2. Select the Run apply checkbox under Run apply on the created stack?
3. Click Create.
Note:
This area does not show variables that have default values or variables that you have not changed.

Description of the illustration adbs_extproc_review.png

Resource Manager runs the apply job to create stack resources accordingly. This takes you to the Job details page and the job state is Accepted. When the apply job starts the status is updated to In Progress.

Description of the illustration adbs_extproc_job_details.png

Note:
The information you need to connect to the instance created as part of the stack is available in the Application Information tab.

Parent topic: Invoke External Procedures as SQL Functions

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 Extrpoc agent instance.

To execute remote procedures at the EXTPROC agent instance, the Autonomous 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 Mutual TLS (mTLS) Authentication 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/extproc_wallet directory on the VM where EXTPROC runs.

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

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.
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.
Create a directory on 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.
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 Database instance.

Parent topic: Invoke External Procedures as SQL Functions

Steps to Invoke an External Procedure as a SQL Function

Shows the steps to invoke an external Procedure as a SQL function.

After you launch the OCI Marketplace EXTPROC stack application and configure it to run external procedures, you create a library of SQL wrapper functions that reference and call their respective external procedures.

As a prerequisite, the whitelisted libraries must be copied into the /u01/app/oracle/extproc_libs directory on the EXTPROC VM.

Follow these steps to create a library in your Autonomous Database and register C routines as an external procedure in the library:

Create a library.

An external procedure is a C language routine stored in a library. To invoke external procedures with Autonomous Database, you create a library.
Run DBMS_CLOUD_FUNCTION.CREATE_CATALOG to create a library. For example:
```
BEGIN
    DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
        library_name               => 'demolib',
        library_listener_url       => 'remote_extproc_hostname:16000',
        library_wallet_dir_name    => 'wallet_dir',
        library_ssl_server_cert_dn => 'CN=VM Hostname',
        library_remote_path        => '/u01/app/oracle/extproc_libs/library name'
);
END;
/
```
This creates the demolib library in your Autonomous Database and registers the dynamic link library in your database. The EXTPROC agent instance is pre-configured to host external procedures on port 16000.

See CREATE_CATALOG Procedure for more information.

Query DBA_CLOUD_FUNCTION_CATALOG View and USER_CLOUD_FUNCTION_CATALOG View views to retrieve the list of all the catalogs and libraries in your database.

Query the USER_CLOUD_FUNCTION_ERRORS View view to list any errors generated during the connection validation to the remote library location.
After you create the library, use DBMS_CLOUD_FUNCTION.CREATE_FUNCTION to create PL/SQL wrapper functions that refer to the external procedures (C functions).
Example:
```
DECLARE
    plsql_params clob    := TO_CLOB('{"sal": "IN, FLOAT", "comm" :"IN, FLOAT"}');
    external_params clob := TO_CLOB('sal FLOAT, sal INDICATOR SHORT, comm FLOAT, comm INDICATOR SHORT,
    RETURN INDICATOR SHORT, RETURN FLOAT');
BEGIN
DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
    LIBRARY_NAME    => 'demolib',
    FUNCTION_NAME   => '"PercentComm"',
    PLSQL_PARAMS    => plsql_params,
    EXTERNAL_PARAMS => external_params,
    API_TYPE        => 'FUNCTION',
    WITH_CONTEXT    => FALSE,
    RETURN_TYPE     => 'FLOAT'
);
END;
/
```
This creates the PercentComm function and registers the PercentComm external procedure in the DEMOLIB library.

The PercentComm function in the library is a reference to the respective external procedure whose name is referenced by the FUNCTION_ID parameter.

In this example the FUNCTION_ID parameter is not provided, the value provided for the FUNCTION_NAME parameter is used as a FUNCTION_ID.
Example:
```
DECLARE
    plsql_params clob := TO_CLOB('{"row_id": "IN,CHAR"}');
    external_params clob := TO_CLOB('CONTEXT, row_id STRING, row_id LENGTH SB4');
BEGIN
DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
    LIBRARY_NAME    => 'demolib',
    FUNCTION_NAME   => 'UpdateSalary_local',
    FUNCTION_ID     => '"UpdateSalary"',
    PLSQL_PARAMS    => plsql_params,
    EXTERNAL_PARAMS => external_params,
    API_TYPE        => 'PROCEDURE',
    WITH_CONTEXT    => TRUE
);
END;
/
```
This creates the UPDATESALARY_LOCAL procedure and registers the UpdateSalary procedure in the DEMOLIB library.

The UPDATESALARY_LOCAL procedure in the library is a reference to the respective external procedure UpdateSalary whose name is referenced by the FUNCTION_ID parameter.
You can query DBA_CLOUD_FUNCTION View and USER_CLOUD_FUNCTION View views to retrieve the list of all the functions that are available to use in your database.

See CREATE_FUNCTION Procedure for more information.
After creating the function, you can DESCRIBE it. For example:
```
DESC "PercentComm";
```
You can drop an existing function using DROP_FUNCTION procedure. For example:
```
EXEC DBMS_CLOUD_FUNCTION.DROP_FUNCTION (LIBRARY_NAME => 'demolib', FUNCTION_NAME => '"PercentComm"');
```
This drops the PercentComm function from the DEMOLIB library.

See DROP_FUNCTION Procedure for more information.
You can drop an existing library using DROP_CATALOG procedure. For example:
```
BEGIN
    DBMS_CLOUD_FUNCTION.DROP_CATALOG (
      LIBRARY_NAME  => 'demolib'
  );
END;
/
```
This drops the DEMOLIB library.

See DROP_CATALOG Procedure for more information.

Parent topic: Invoke External Procedures as SQL Functions