Invoke External Procedures as SQL Functions

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.

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

Define the C procedure using one of these prototypes.

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

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

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

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 click Marketplace and then, under Marketplace click All Applications. This takes you to the Marketplace All Applications dashboard.
3. Enter EXTPROC in the search field and click search.
4. Click the EXTPROC widget of Type: Stack.
   
   

   
   Description of the illustration adbs_extproc_application.png

   
   

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

1. 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.

2. Click Launch Stack.
   
   

   
   Description of the illustration adb_launch_stack.png

   
   

Shows the steps to create Stack for EXTPROC instance.

1. 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

   
   
2. 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.
3. 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 AI 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 AI 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 AI 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

      
      
4. Click Next.

   This takes you to the Review page.

5. 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.

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

1. Import the wallet, cwallet.sso, containing the certificates for the EXTPROC agent instance from Object Storage in your Autonomous AI 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 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 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 Database instance.

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

1. Create a library.

   An external procedure is a C language routine stored in a library. To invoke external procedures with Autonomous AI 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 AI 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.

2. Create the function.

   For example:

   CREATE OR REPLACE FUNCTION ftest(
         x VARCHAR2, 
         y VARCHAR2) 
   RETURN VARCHAR2 AS LANGUAGE C
         LIBRARY test
         NAME "demolib"
         PARAMETERS(
             x STRING, 
             y STRING)
         AGENT IN (x);
   /

3. 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.