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

   
   

   
   

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.
   
   

   
   

   
   

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.

   
   

   
   

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

      
      

      
      

      
      
   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.

          
          

          
          

          
          

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

          
          

          
          

          
          

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

      
      

      
      

      
      
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.

   
   

   
   

   
   

   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.

   
   

   
   

   
   

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

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

3. After creating the function, you can DESCRIBE it. For example:

   DESC "PercentComm";

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

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