Category: Oracle

I’ve been having a play with ChatGPT over the last few weeks, and I wanted to give a little example of using it to try and write some PL/SQL code. Importantly, I wanted to know how close it would get to generating runnable code against an Oracle database.

Calling a web service from PL/SQL

Let’s start by asking it a pretty straight forward question.

I think that’s pretty impressive, and not much different to my own example on the website. If someone gave me that answer in an interview question I would think they had done a reasonable job, but does it work? Not without some additional stuff.

What about the network ACL?

The first issue is it doesn’t mention we need to create a network ACL to allow us to make a callout from the database.

Does ChatGPT understand we need an ACL? Let’s see. I ask it about the ACL, and it gives me an explanation of using an ACL.

It’s using the pre-12c syntax, but let’s create it with the correct values.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
        acl         => 'jsonplaceholder.typicode.com_acl.xml',
        description => 'Example ACL for REST web service',
        principal   => 'APEX_220200',
        is_grant    => TRUE,
        privilege   => 'connect',
        start_date  => NULL,
        end_date    => NULL
    );
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
        acl        => 'jsonplaceholder.typicode.com_acl.xml',
        principal  => 'APEX_220200',
        is_grant   => TRUE,
        privilege => 'resolve'
    );
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
        acl  => 'jsonplaceholder.typicode.com_acl.xml',
        host => 'jsonplaceholder.typicode.com'
    );
END;
/

I would normally prefer to use the 12c+ syntax, shown below, but we will continue with what ChatGPT suggests rather than using the newer syntax.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

declare
  l_principal varchar2(20) := 'APEX_220200';
begin
  dbms_network_acl_admin.append_host_ace (
    host       => 'jsonplaceholder.typicode.com', 
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => l_principal,
                              principal_type => xs_acl.ptype_db)); 
end;
/

What about the wallet?

The next issue is it doesn’t tell us to create a wallet to hold the root certificate for the URL. It’s using a HTTPS address, so I have to use a wallet. I downloaded the root certificate from the site and put it in the “/tmp” location on the database server, then created the wallet as follows.

mkdir -p /u01/wallet
orapki wallet create -wallet /u01/wallet -pwd WalletPasswd123 -auto_login
orapki wallet add -wallet /u01/wallet -trusted_cert -cert "/tmp/Baltimore CyberTrust Root.crt" -pwd WalletPasswd123

Because it didn’t tell me the wallet was needed, it also neglected to tell me the P_WALLET_PATH parameter was necessary in the MAKE_REST_REQUEST call.

Does ChatGPT understand we need a wallet? I ask it about the wallet, and include a typo. 🙂 It didn’t care about the typo, and still gave us an answer. It doesn’t tell us how to create the wallet itself, and it thinks the SET_WALLET procedure is in the APEX_WEB_SERVICE package, but it’s in the UTL_HTTP package.

I feel like that is a fail. Not only did is get the wrong package name, but we would typically use the P_WALLET_PATH parameter with APEX_WEB_SERVICE. Even so, it might give us a clue about where to look next.

What about the output from the DBMS_OUTPUT package?

Finally, it didn’t tell use to turn on serveroutput to display the output from the DBMS_OUTPUT.PUT_LINE call. If this code was called from an IDE that might not matter, but from SQL*Plus or SQLcl it’s important if we want to see the result. I asked ChatGPT why I couldn’t see the output and it produced a lot of text, that kind-of eluded to the issue, but didn’t flat out tell us what to do.

Did the final solution work?

With the ACL and wallet in place, adding the P_WALLET_PATH parameter to the MAKE_REST_REQUEST call and turning on serveroutput, the answer is yes.

conn testuser1/testuser1@//localhost:1521/pdb1

set serveroutput on

DECLARE
    l_url       VARCHAR2(4000) := 'https://jsonplaceholder.typicode.com/todos/1'; -- Replace with your API endpoint
    l_response  CLOB;
BEGIN
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).name := 'Content-Type';
    APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).value := 'application/json'; -- Replace with your desired content type
    l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
        p_url         => l_url,
        p_http_method => 'GET', -- Replace with your desired HTTP method
        p_wallet_path => 'file:/u01/wallet'
    );
    -- Do something with the response, for example print it to the console
    DBMS_OUTPUT.PUT_LINE(l_response);
END;
/
{
  "userId": 1,
  "id": 1,
  "title": "delectus aut autem",
  "completed": false
}


PL/SQL procedure successfully completed.

SQL>

Thoughts

Overall it is pretty impressive. Is it perfect? No.

The interesting thing is we can ask subsequent questions, and it understands that these are in the context of what came before, just like when we speak to humans. This process of asking new questions allows us to refine the answer.

Just as we need some “Google-fu” when searching the internet, we also need some “ChatGPT-fu”. We need to ask good questions, and if we know absolutely nothing about a subject, the answers we get may still leave us confused.

We get no references for where the information came from, which makes it hard to fact check. The Bing integration does include references to source material.

Currently ChatGPT is based around a 2021 view of the world. It would be interesting to see what happens when this is repeated with the Bing integration, which does live searches of Bing for the base information.

When we consider this is AI, and we remember this is the worst it is ever going to be, it’s still very impressive.

Cheers

Tim…