Oracle Cloud Infrastructure Documentation

PL/SQL Packages Notes for Autonomous Database

Notes for Oracle Database PL/SQL packages in Autonomous Database.

Unavailable PL/SQL Packages

  • DBMS_DEBUG_JDWP

  • DBMS_DEBUG_JDWP_CUSTOM

  • UTL_INADDR

PL/SQL Packages Notes

  • DBMS_LDAP

    • Specifying an IP address in the host name is not allowed.

    • The only allowed port is 636.

    • The SSLWRL and SSLWALLETPASSWD arguments to the OPEN_SSL procedure are ignored. The default value for the SSLWRL property is set to the wallet that is used by UTL_HTTP and DBMS_CLOUD for making outbound web requests on Autonomous Database.

    • The DBMS_LDAP.SIMPLE_BIND_S and DBMS_LDAP.BIND_S subprograms perform authentication to the directory server.

      The DBMS_LDAP.SIMPLE_BIND_S and DBMS_LDAP.BIND_S subprograms are modified to accept credential objects as an argument.

      Following are the usage notes and examples of these modified subprograms:

      • The modified SIMPLE_BIND_S and BIND_S subprograms enable you to pass credential objects to set directory server authentication. Credential objects are schema objects, hence they can be accessed only by privileged users and enable you to configure schema-level privileges to access control the credentials. Passing scheduler credentials is an appropriate and secure way to store and manage username/password/keys for authentication.

      • The modified SIMPLE_BIND_S and BIND_S subprograms are a secure and convenient alternative to previously existed SIMPLE_BIND_S and BIND_S subprogram.

        See FUNCTION simple_bind_s and FUNCTION bind_s for more information.

      • The CREDENTIAL argument of the SIMPLE_BIND_S and BIND_S functions is used to perform credential based authentication to the directory server.

      • For example:

        • Create a credential object:

          BEGIN DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'LDAP_CRED',
    username        => 'web_app_user',
    password        => 'password' );
END;

          This creates a credential object which creates a stored username/password pair.

          See CREATE_CREDENTIAL Procedure for more information.

          See Specifying Scheduler Job Credentials for more information.

        • Invoke DBMS_LDAP.SIMPLE_BIND_S: 

          DECLARE
      l_mail_conn DBMS_LDAP.INIT;
    BEGIN 
      l_ldap_conn   := DBMS_LDAP.INIT('ldap.example.com', 636);
      l_auth_result := DBMS_LDAP.SIMPLE_BIND_S(l_ldap_conn, 'LDAP_CRED');
      ...
END;

          The code in this example first invokes the DBMS_LDAP.INIT function which initializes a session with an LDAP server and establishes a connection with the LDAP server ldap.example.com at port number 636. The value l_ldap_conn in the SIMPLE_BIND_S function is the LDAP session handle and LDAP_CRED is the credentials name.

        • The function bind_s performs complex authentication to the directory server. For example: 

          DECLARE
      l_mail_conn DBMS_LDAP.INIT;
    BEGIN 
      l_ldap_conn   := DBMS_LDAP.INIT('ldap.example.com', 636);
      l_auth_result := DBMS_LDAP.BIND_S(l_ldap_conn, 'LDAP_CRED', METH => DBMS_LDAP.AUTH_SIMPLE);
      ...
END;

          The code in this example first invokes the DBMS_LDAP.INIT function which initializes a session with an LDAP server and establishes a connection with the LDAP server ldap.example.com at port number 636. The value l_ldap_conn in the BIND_S function is the LDAP session handle and LDAP_CRED is the credentials name. METH is the authentication method. The only valid value is DBMS_LDAP_UTL.AUTH_SIMPLE.

      • The EXECUTE privileges on DBMS_CLOUD or DWROLE is required to create scheduler credentials.

      • The passed credentials must be present in the current user schema and be in the enabled state.

      • A public or private synonym that points to a credential in a different user schema can be supplied as a value for the CREDENTIAL parameter provided you have the EXECUTE privilege on the base credential object pointed to by the synonym. See Overview of Synonyms for more information.

    • SSL/TLS is enforced for all communication happening between LDAP server and Autonomous Database.

    • When your Autonomous Database instance is configured with a private endpoint, set the ROUTE_OUTBOUND_CONNECTIONS database parameter to 'PRIVATE_ENDPOINT' to specify that all outgoing LDAP connections are subject to the Autonomous Database instance private endpoint VCN's egress rules. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

    To use DBMS_LDAP for a connection on a private endpoint, use DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE and specify the private_target parameter with value TRUE.

    Note

    If you set ROUTE_OUTBOUND_CONNECTIONS to PRIVATE_ENDPOINT, setting the private_target parameter to TRUE is not required in this API. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

  • UTL_HTTP

    • Connections through IP addresses are not allowed.

    • Only HTTPS is allowed when the Autonomous Database instance is on a public endpoint. When the Autonomous Database instance is on a private endpoint, both HTTPS and HTTP_PROXY connections are allowed (HTTP connections are disallowed for both public endpoints and private endpoints).

    • The UTL_HTTP.set_proxy API is allowed when the Autonomous Database instance is on a private endpoint.

    • When the Autonomous Database instance is on a private endpoint and you use HTTP_PROXY or the UTL_HTTP.SET_PROXY API:

      • DBMS_CLOUD requests do not honor the proxy server you set with UTL_HTTP.SET_PROXY. This includes DBMS_CLOUD.SEND_REQUEST and all object storage access for DBMS_CLOUD external tables that you define with DBMS_CLOUD.CREATE_EXTERNAL_TABLE, DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE, or DBMS_CLOUD.CREATE_HYBRID_PART_TABLE.

      • APEX_WEB_SERVICE requests do not honor the proxy server you set with UTL_HTTP.SET_PROXY.

    • All web services must be secured. The only allowed port is 443 when the Autonomous Database instance is on a public endpoint. When the Autonomous Database instance is on a private endpoint this restriction does not apply.

      Your Autonomous Database instance is preconfigured with an Oracle Wallet that contains more than 90 of the most commonly trusted root and intermediate SSL certificates. The Oracle Wallet is centrally managed. You can configure UTL_HTTP to use a wallet for a site that is protected using self-signed SSL certificates. See Use a Customer-Managed Wallet for External Calls with UTL_HTTP for more information.

    • The SET_AUTHENTICATION_FROM_WALLET procedure is disallowed.

    • The WALLET_PATH and WALLET_PASSWORD arguments for the CREATE_REQUEST_CONTEXT, REQUEST, and REQUEST_PIECES procedures are ignored.

    • The CREDENTIAL argument of the SET_CREDENTIAL procedure is used to pass the credential object as an input to the procedure. See Specifying Scheduler Job Credentials and CREATE_CREDENTIAL Procedure for more information.

    • The EXECUTE privileges on DBMS_CLOUD or DWROLE is required to create credential objects.

    • The passed credentials must be present in the current user schema and be in the enabled state.

    • A public or private synonym that points to a credential in a different user schema can be supplied as a value for the CREDENTIAL parameter provided you have the EXECUTE privilege on the base credential object pointed to by the synonym. See Overview of Synonyms for more information.

    • Oracle Wallet configuration cannot be altered. All arguments for SET_WALLET procedure are ignored.

    • When your Autonomous Database instance is configured with a private endpoint, set the ROUTE_OUTBOUND_CONNECTIONS database parameter to 'PRIVATE_ENDPOINT' to specify that all outgoing UTL_HTTP connections are subject to the Autonomous Database instance private endpoint VCN's egress rules. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

  • UTL_SMTP

    • The only supported email provider is Oracle Cloud Infrastructure Email Delivery service. See Overview of the Email Delivery Service for more information.

    • Mail with an IP address in the host name is not allowed.

    • The only allowed ports are 25 and 587.

    • The CREDENTIAL argument of the SET_CREDENTIAL function is used to pass the scheduler credentials object as an input to the function. See Specifying Scheduler Job Credentials and CREATE_CREDENTIAL Procedure for more information.

    • The EXECUTE privileges on DBMS_CLOUD or DWROLE is required to create credential objects.

    • The CREDENTIAL argument of the SET_CREDENTIAL procedure is used to pass the credential objects object as an input to the procedure. See Specifying Scheduler Job Credentials for more information.

    • The passed credentials must be present in the current user schema and be in the enabled state.

    • A public or private synonym that points to a credential in a different user schema can be supplied as a value for the CREDENTIAL parameter provided you have the EXECUTE privilege on the base credential object pointed to by the synonym. See Overview of Synonyms for more information.

    • When your Autonomous Database instance is configured with a private endpoint, set the ROUTE_OUTBOUND_CONNECTIONS database parameter to 'PRIVATE_ENDPOINT' to specify that all outgoing UTL_SMTP connections are subject to the Autonomous Database instance private endpoint VCN's egress rules. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

  • UTL_TCP

    • The IP address is not allowed in the host name.

    • The only allowed ports are: 443 (HTTP) 25 and 587 (SMTP).

    • For port 443, only HTTPS URLs are allowed.

    • The WALLET_PATH and WALLET_PASSWORD arguments for the OPEN_CONNECTION procedure are ignored. The default value for the WALLET_PATH and WALLET_PASSWORD property are set to the wallet that is used by UTL_HTTP and DBMS_CLOUD for making outbound web requests on Autonomous Database.

    • SSL/TLS is enforced for all communication happening over TCP/IP connections.

    • When your Autonomous Database instance is configured with a private endpoint, set the ROUTE_OUTBOUND_CONNECTIONS database parameter to 'PRIVATE_ENDPOINT' to specify that all outgoing UTL_TCP connections are subject to the Autonomous Database instance private endpoint VCN's egress rules. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

  • DBMS_NETWORK_ACL_ADMIN

    • Granting ACL privileges on IP addresses is not allowed.

    • The HTTP_PROXY ACL is allowed on private endpoints.

  • UTL_HTTP Errors

    The following table shows error messages and possible causes for these error messages when using UTL_HTTP:

    Error Message Potential Cause

    ORA-12545: Connect failed because target host or object does not exist

    Target host or object does not exist or it is private.

    ORA-24247: network access denied by access control list (ACL)

    Access control list (ACL) for the specified host could not be found.

    ORA-29024: Certificate validation failure

    Certificate of the host does not exist or is not among the supported certificates.

    ORA-29261: Bad argument

    Passed credentials are invalid or disabled or the user does not have sufficient privileges on the credential.

  • UTL_SMTP Error

    Error Message Potential Cause

    ORA-29261: Bad argument

    Passed credentials are invalid or disabled or the user does not have sufficient privileges on the credential.

  • DBMS_LDAP Error

    Error Message Potential Cause

    ORA-31400: Missing or invalid scheduler credential

    Passed credentials are NULL or invalid.

See UTL_HTTP, DBMS_LDAP, UTL_SMTP, UTL_TCP, and DBMS_NETWORK_ACL_ADMIN in PL/SQL Packages and Types Reference for more information.