How to Call External HTTPS APIs with Oracle Wallet & Certificates

Oracle APEX: How to Call External HTTPS APIs with Oracle Wallet & Certificates – Step-by-Step Guide

As an Oracle APEX developer, you will eventually need to connect your database securely to external HTTPS APIs. This tutorial walks you through the entire process: from configuring the Oracle wallet and importing SSL certificates, to setting up network access and testing the connection. By the end, you’ll have a robust, reusable solution you can apply to any HTTPS endpoint!

1. Why Do You Need an Oracle Wallet?

Oracle databases cannot call HTTPS endpoints out-of-the-box unless the target certificate is stored in the Oracle wallet. The wallet acts as the database’s certificate store. Without the right certificate, you’ll get connection errors when trying to access secure APIs.

2. Preparation: Certificate & Access

• Obtain the SSL certificate (crt.crt) from your target API.
• Ensure you have access to your Oracle database’s wallet directory (for example, via Docker).

3. Create Wallet Directory & Import Certificate

First, create a new folder for your API in the wallet directory and copy the certificate file into it:

 

Bash

xxxxxxxxxx

 

1

cd /var/lib/docker/volumes/db_oracle-19c-data/_data/wallet/

2

mkdir apache_pdf

3

# Copy your certificate into the new folder

4

chmod -R 777 apache_pdf

Automated Script for Wallet Creation & Certificate Import

Use this shell script to initialize the wallet and import the certificate (adjust path and password as needed):

 

Bash

xxxxxxxxxx

35

 

1

WALLET_PATH="/opt/oracle/oradata/wallet/apache_pdf"

2

WALLET_PASSWORD="Sajjad"

3

CERT_FILE="crt.crt"

4

​

5

docker exec -it database bash -c "

6

echo '========================================='

7

echo '   🔐 Oracle Wallet Initialization Tool'

8

echo '========================================='

9

echo '📍 Wallet Folder:  $WALLET_PATH'

10

echo '🔑 Password:       ********'

11

echo '-----------------------------------------'

12

​

13

cd $WALLET_PATH

14

​

15

echo '➡️ Creating wallet if not exists...'

16

orapki wallet create -wallet . -pwd $WALLET_PASSWORD -auto_login 2>/dev/null ||

17

echo '✔️ Wallet already exists — continuing'

18

​

19

echo '➡️ Checking certificate $CERT_FILE exists...'

20

if [ ! -f $CERT_FILE ]; then

21

    echo '❌ ERROR — Certificate file '$CERT_FILE' not found in wallet directory!'

22

    echo '   Use: docker cp crt.crt database:$WALLET_PATH'

23

    exit 1

24

fi

25

​

26

echo '➡️ Importing certificate...'

27

orapki wallet add -wallet . -trusted_cert -cert $CERT_FILE -pwd $WALLET_PASSWORD 2>/dev/null ||

28

echo '✔️ Certificate already imported'

29

​

30

echo '-----------------------------------------'

31

echo '🔍 Current wallet contents:'

32

orapki wallet display -wallet . -pwd $WALLET_PASSWORD

33

echo '-----------------------------------------'

34

echo '🎉 Finished — wallet is ready for HTTPS connections'

35

"

4. Set Network ACL (Access Control List)

The database needs explicit permission to access external hosts. Use the following PL/SQL to grant access (adjust the host and user as needed):

 

SQL

xxxxxxxxxx

 

1

begin

2

  dbms_network_acl_admin.append_host_ace (

3

    host       => 'dev-pdf-apache.shsoftwaresolution.com', 

4

    lower_port => 443,

5

    upper_port => 443,

6

    ace        => xs$ace_type(privilege_list => xs$name_list('http'),

7

                              principal_name => 'ONBOARDING',

8

                              principal_type => xs_acl.ptype_db)); 

9

end;

5. Store Connection Parameters in the Database

Save the endpoint and wallet details as global parameters for easy reuse:

 

SQL

xxxxxxxxxx

 

1

INSERT INTO GLOBAL_PARAMETER (GLPA_TYPE, GLPA_NAME, GLPA_VALUE, GLPA_ACTIVE_YN)

2

VALUES ('SYSTEM','APACHE_PDF_ENDPOINT','https://dev-pdf-apache.shsoftwaresolution.com','YES');

3

​

4

INSERT INTO GLOBAL_PARAMETER (GLPA_TYPE, GLPA_NAME, GLPA_VALUE, GLPA_ACTIVE_YN)

5

VALUES ('SYSTEM','APACHE_PDF_WALLET_PATH','file:/opt/oracle/oradata/wallet/apache_pdf','YES');

6

​

7

INSERT INTO GLOBAL_PARAMETER (GLPA_TYPE, GLPA_NAME, GLPA_VALUE, GLPA_ACTIVE_YN)

8

VALUES ('SYSTEM','APACHE_PDF_WALLET_PASSWORD','Sajjad','YES');

6. Test: Make an HTTPS Request from the Database

Use this PL/SQL block to verify everything works:

 

SQL

xxxxxxxxxx

37

 

1

DECLARE

2

    v_url       VARCHAR2(4000);

3

    v_wallet    VARCHAR2(4000);

4

    v_pass      VARCHAR2(4000);

5

    v_response  VARCHAR2(32767);

6

BEGIN

7

    SELECT GLPA_VALUE INTO v_url

8

    FROM GLOBAL_PARAMETER

9

    WHERE GLPA_NAME = 'APACHE_PDF_ENDPOINT';

10

​

11

    SELECT GLPA_VALUE INTO v_wallet

12

    FROM GLOBAL_PARAMETER

13

    WHERE GLPA_NAME = 'APACHE_PDF_WALLET_PATH';

14

​

15

    SELECT GLPA_VALUE INTO v_pass

16

    FROM GLOBAL_PARAMETER

17

    WHERE GLPA_NAME = 'APACHE_PDF_WALLET_PASSWORD';

18

​

19

    DBMS_OUTPUT.PUT_LINE('🌍 URL:     ' || v_url);

20

    DBMS_OUTPUT.PUT_LINE('🔐 WALLET:  ' || v_wallet);

21

​

22

    v_response := UTL_HTTP.request(

23

        url              => v_url,

24

        proxy            => '',

25

        wallet_path      => v_wallet,

26

        wallet_password  => v_pass

27

    );

28

​

29

    DBMS_OUTPUT.PUT_LINE('---------------------------------------------');

30

    DBMS_OUTPUT.PUT_LINE('🟩 RESPONSE:');

31

    DBMS_OUTPUT.PUT_LINE(v_response);

32

​

33

EXCEPTION

34

    WHEN OTHERS THEN

35

        DBMS_OUTPUT.PUT_LINE('❗ SQL ERROR: ' || SQLERRM);

36

END;

37

/

Example output:

 

Plain Text

xxxxxxxxxx

 

1

🌍 URL: https://dev-pdf-apache.shsoftwaresolution.com

2

🔐 WALLET: file:/opt/oracle/oradata/wallet/apache_pdf

3

🟩 RESPONSE:

4

PDF API OK

Conclusion

With these steps, you as an Oracle APEX developer can securely and reliably call any external HTTPS API from your database. This approach is reusable for any API and certificate.

• Try it now: Follow these steps in your own Oracle APEX project and test your API integration!
• Questions or feedback? Share your experience or ask questions in the comments!
• More APEX tips: Subscribe to the blog for more Oracle APEX best practices and deep dives!

 {fullWidth}