Steps to Invoke Google Cloud Run Functions as SQL Functions

Shows the steps to invoke Google Cloud Run functions as SQL functions in your database.

1. To access Google Cloud Run functions from an Autonomous AI Database instance use your Google service account. You must grant the cloudfunctions.functions.list permission, and Cloud Functions Invoker and Cloud Run Invoker roles to the Google service account for the Google Cloud Platform (GCP) app.
   See the following for more information:

   • About Using a Google Service Account to Access Google Cloud Resources

   • Authorize access with IAM (1st gen)

   • Authorize access with IAM

2. Create a catalog. To create a catalog for Google Cloud Run functions with a user other than ADMIN you need to grant read privileges on the CLOUD_INTEGRATIONS view to that user.

   A catalog is a collection of wrapper functions that reference and call their respective cloud functions using their API endpoints.

   BEGIN
       DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
           credential_name  => 'GCP$PA', 
           catalog_name     => 'GCP_DEMO_CATALOG', 
           service_provider => 'GCP',
           cloud_params     => '{"project_id":"example_XXXXXX"}'
    );
   END;
   /
   

   The project_id value in the cloud_params is the project ID of the Google Cloud Platform (GCP).

   This creates the GCP_DEMO_CATALOG catalog and catalogs all the functions of the provided project_id.

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

3. List the functions in a catalog.

   Following is the example to list Google Cloud Run functions:

   VAR function_list CLOB;
   
   BEGIN
       DBMS_CLOUD_FUNCTION.LIST_FUNCTIONS (
           credential_name  => 'GCP$PA',
           catalog_name     => 'GCP_DEMO_CATALOG',
           function_list    => :function_list
    );
   END;
   /
   
   SELECT JSON_QUERY (:function_list, '$' RETURNING VARCHAR2(32676) pretty) AS search_results FROM dual;
   

   See LIST_FUNCTIONS Procedure for more information.

4. Run the DBMS_CLOUD_FUNCTION.SYNC_FUNCTIONS to create wrapper SQL functions. You can use one of the following methods to create the wrapper SQL functions in the catalog, that call their respective cloud functions:

   • SYNC_FUNCTIONS: SYNC_FUNCTIONS method to create wrapper SQL function is the quickest and simplest method, which automatically syncs (creates or deletes) 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. For example:

   
   BEGIN
       DBMS_CLOUD_FUNCTION.SYNC_FUNCTIONS (
           catalog_name => 'GCP_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.

   Run the following query to verify the sync.

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

   Note
   
   Keep a note of the current user in order to run this command.

   See SYNC_FUNCTIONS Procedure for more information.

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

   Example to create a function in the GCP_DEMO_CATALOG catalog.

   EXEC :function_args  := TO_CLOB('{"name": "VARCHAR2"}');

   BEGIN
       DBMS_CLOUD_FUNCTION.CREATE_FUNCTION (
           credential_name => 'GCP$PA',
           catalog_name    => 'GCP_DEMO_CATALOG',
           function_name   => 'gcp_testfunc',
           function_id     => 'function_id_path',
           input_args      => :function_args
    );
   END;
   /
   

   This creates the GCP_TESTFUNC function in the GCP_DEMO_CATALOG catalog.

   The GCP_TESTFUNC function in the catalog is a reference to the respective Google Cloud Run function whose project id is referenced by the FUNCTION_ID parameter. Invoking the function in the catalog along with its arguments runs the corresponding Google Cloud Run function and provides the output returned by the function.

   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.

5. After the function is created you can DESCRIBE and invoke it.

   DESC GCP_TESTFUNC
   COLUMN STATUS format a30
   COLUMN OUTPUT format a30

   SELECT GCP_TESTFUNC(NULL) FROM dual;

   This invokes the GCP_TESTFUNC function by calling the function reference specified in the function_id parameter.

6. You can drop an existing function using DROP_FUNCTION procedure. For example:

   EXEC DBMS_CLOUD_FUNCTION.DROP_FUNCTION (CATALOG_NAME => 'GCP_DEMO_CATALOG', FUNCTION_NAME => 'GCP_TESTFUNC');
   

   This drops the GCP_TESTFUNC function from the GCP_DEMO_CATALOG catalog.

   See DROP_FUNCTION Procedure for more information.

7. You can drop an existing catalog using DROP_CATALOG procedure. For example:

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

   This drops the GCP_DEMO_CATALOG from your database.

   See DROP_CATALOG Procedure for more information.