Using ChatGPT to write PL/SQL code…

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.

Question: Write the code to call a REST web service from PL/SQL using the APEX_WEB_SERVICE package

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.

Question: Do I need a network 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.

Question: Do I need a wallet?

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.

Question: Why can’t I see the output?

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…

VirtualBox 7.0.6

VirtualBox 7.0.6 maintenance release has arrived.

The downloads and changelog are in the usual places.

I’ve installed it on Windows 10 and 11 machines with no drama. I’ve also run Packer builds of all my Vagrant boxes, which you can find on Vagrant Cloud here.

So far so good. I’ve not had any drama.

I’ll be doing lots of testing over the next few days as I add the latest patches to my Vagrant builds. I’ll update here if I notice anything unusual.

Cheers

Tim…

Life Update : The first rule of Oracle Games Console…

Life has been a little quiet on the publishing front recently. You may have noticed I’ve not posted many new articles or blog posts of late. This situation is likely to continue for some time, and I thought I would drop a post to let you know why…

I’m currently spending most of my time playing with a certain beta product, and all of that is covered by a non disclosure agreement (NDA). Over the last few weeks I’ve written a bunch of articles, but I can’t hit the publish button on them yet. Over the coming months I’ll continue to write new articles and give feedback to Oracle, but of course you will not be seeing any of this.

Once the product goes live I’ll be able to release all this stuff, with the obligatory edits/rewrites to take account of the changes between the beta and live versions of course. The total amount of content will be no different in the long run, but there will be a baron period for a few months followed by a glut of content. I suspect this situation will be similar for a number of folks in the Oracle community.

The rules are a bit different for Oracle employees, so you will be seeing teasers for new functionality from them, but not from the rest of the community…

Over the next few months I’ll mostly be posting memes and “from the vault” links on social media, just so you don’t forget I exist, but it is going to be a relatively quiet time…

Cheers

Tim…

UKOUG Breakthrough 2022 : Day 2

Day 2 started pretty much the same as day 1. I arrived late to avoid the traffic.

The first session I went to was Martin Nash with “Oracle Databases in a Multicloud World”. I was a bit late to this session, but from what I saw it seemed the general view was “don’t be stupid, stupid”. Multi-cloud can add some complexity and latency, but if it’s what you need, it’s all manageable. If you do have a system where multi-cloud is not suitable, don’t do it for that system. Most things can be migrated, but some things are easier than others. Pick your fights. Sorry if I came to the wrong conclusion… šŸ™‚

I often get the feeling that some people think everything has to be all or nothing. We have Oracle Cloud Apps on Oracle Cloud. A bunch of stuff on Azure, with more to come. One of our major systems is moving to AWS in the next couple of years. Then of course we still have a load of stuff on-prem. This isn’t because we are desperate to be multi-cloud. It’s just the way things have happened. I’m sure we’ll run into some issue along the way, but I’m also sure we’ll solve them. Once size does not fit all…

BTW Martin now works for Google, so we have to hate him. šŸ™‚

Next up was Jasmin Fluri with “The Science of Database CI/CD”. I already had the long form of this presentation because I read Jasmin’s masters thesis, but I was interested to see how she summarised some of it into a presentation. She did a great job of getting the main points into a 45 minute session, which can’t have been easy. It was also a little depressing, because I’ve come a long way , but I’ve still got such a long way to go. Ah well…

After Jasmin was Erik van Roon with “Scripting in SQLcl – You Can Never Have Enough of a Good Thing”. The session discussed how to extend the functionality of SQLcl with your own commands written in JavaScript. I get the distinct impression Erik has too much time on his hands. If anyone wants to join me in staging an intervention, just let me know. šŸ™‚ I’m not sure if I will use this functionality, but it’s always good to know it exists, because you never know when it might come in handy, and knowing it’s possible is the first step.

Last up for me was “The Death of the Data Scientist, But Long Live Data Science” by Brendan Tierney. To summarise Brendan talked about the recent mass layoffs of data scientists, suggesting a number of factors including a glut of data scientists on the market, low return on investment from many data science teams, and the simplification and automation of data science to the point where it had now been integrated into products and domain-specific staff roles. It’s typical hype cycle stuff. We’ve moved from the “Peak of Inflated Expectations” to the “Trough of Disillusionment”, and the job market has corrected itself because of that. It doesn’t sound like a move from DBA to data scientist is a great career move right now. šŸ™‚

I spent some time chatting to Brendan, then it was off to beat the traffic home so I could return to real life again.

UKOUG Breakthrough 2022 is over now, and it was a good introduction back into the world of face to face conferences for me. I’m still very nervous about the thought of travelling, but based on the last few days I’m hoping I can get my conference mojo back. Just don’t expect too much too soon. šŸ™‚

Thanks to all the conference organisers and speakers for giving up their time to make this happen. See you all again soon.

Cheers

Tim…

UKOUG Breakthrough 2022 : Day 1

The evening before the conference the Oracle ACEs met up for some food at a curry place in the city centre. Thanks to the Oracle ACE Program for organising this! Earlier that day I presented my first face to face session in 3 years, and now it was time for my first social event in a similar timescale. I was pretty nervous going into it, and quite standoffish at first, but I gradually relaxed and “conference Tim” started to come back. By the end of the evening I was feeling a lot more comfortable with the situation. It was nice to get to meet up with a bunch of people I had not seen for a long time. It also made me feel a bit more relaxed about going to the conference the next day.

The one downside of going to a conference in your hometown is commuting. I don’t live far away from the venue, but regardless of the mode of transportation, commuting during rush hour is a nightmare. Instead I chose to wait for the rush hour traffic to die down and be fashionably late. That was the right move.

The first session I went to was Simon Haslam speaking about “Platform Engineering for the Modern Oracle World”. I’ve got a lot of time for Simon. Him and Lonneke Dikmans sowed some seeds in my brain a long time ago, which have ultimately had a big influence on me over the years. In this session he talked about the various approaches to automation over the years, culminating in where many people find themselves today. I found myself nodding my head in agreement with most of what Simon was saying during this session. I joked later that his session gave me post traumatic stress disorder (PTSD) when I thought back through many of those stages. 😁 A group of us continued the conversation about the topic after the session, which is always fun.

Next I moved on to the Oracle ACE Briefing. The first rule of the Oracle ACE Briefing is don’t talk about the Oracle ACE Briefing. Once again, it was good to see a lot of familiar faces, and some new ones. Once the session was over, and we knew everything there was to know about the Oracle Games Console (#OGC), I spent some time talking with Dominic Giles, while he desperately looked for ways to get rid of me. He didn’t succeed. šŸ˜‰

By the time I finally let Dom go, it was time to watch Jasmin Fluri and Gianni Ceresa presenting “Git Branching – the battle of the ages”, or “Development Workflows: The Battle of The Ages!”, depending on which title you prefer. The session was a celebrity death match between trunk-based development (Jasmin) and Gitflow style development (Gianni). Gianni fought dirty, but ultimately Jasmin was able to overpower him and grind him into the dirt. At least that’s how I saw it. šŸ™‚ Both sides gave compelling reasons for their preferred method, and ultimately there is a lot more similarity between them than some people would have you believe. As is often the case, there is no “best”, but what is “best for you”. Not surprisingly, this sparked another conversation at the end of the session, with a few war stories thrown in for good measure.

At that point I had to head off to beat the traffic across town, as I had some “real life” things to do.

So that’s was day 1 of UKOUG Breakthrough 2022. I know it will sound silly to most people, but I was stressing about the conference and it turned out to be a lot easier than I expected. Fingers crossed day 2 will go well also.

Cheers

Tim…

Fedora 37 and Oracle

Fedora 37 was released recently. Here comes the standard warning.

Here are the usual things I do when a new version of Fedora comes out.

Why do I do this? As mentioned in the first link, Fedora is a proving ground for future versions of RHEL, and therefore Oracle Linux. I like to see what is coming around the corner. Doing this has no ā€œreal worldā€ value, but I’m a geek, and this is what geeks do. 

I pushed Vagrant builds to my GitHub.

If you want to try these you will need to build a Fedora 37 box. You can do that using Packer. There is an example of that here.

https://github.com/oraclebase/vagrant/tree/master/packer/f37

So now you know how to do it, please don’t! šŸ™‚

What’s New?

So what’s new with Fedora 37? You can read about it here.

Cheers

Tim…

VirtualBox 7.0.4 and Vagrant 2.3.3 – Another VirtualBox Fail (For Me)…

VirtualBox 7.0.4

Please read the update at the bottom of this post before making any conclusions. I wanted to leave the rest of the post unedited, but needed to update my current situation as it has changed since this post was written…

VirtualBox 7.0.4 has been released.

The downloads and changelog are in the normal places.

From my previous posts on VirtualBox 7.0.x you will know I’ve been having problems with it. They all seem to come down to networking. I can often, but not always, start up an existing VM, but if I try to build a new VM with Vagrant it will fail to setup the networking. Also, if I attempt to use Packer, it will fail to find the kickstart file, which it attempts to access over HTTPS. Both cases seem to be network related.

I’ve done all the usual firewall and antivirus stuff, and trawled the internet to see if anyone else has a solution. None of that has helped.

When I saw VirtualBox 7.0.4 I was hoping this might solve my problem, but no. I get the same issues on Windows 11, Windows 10 and macOS (Intel). This makes VirtualBox 7.0.4 unusable for me.

When I revert back to VirtualBox 6.1.40, everything works as expected…

I don’t know if this is just me, or if a lot of people are having problems with VirtualBox. It seems odd that I am getting the same result on multiple machines on two fundamentally different architectures though.

So my current suggestion would be use this at your own risk…

Vagrant 2.3.3

Vagrant 2.3.3 has also been born. Upgrading to this didn’t cause or solve any issues for me. Even though I’ve had to revert back to VirtualBox 6.1.40, this new version of Vagrant is working fine for me.

Packer 1.8.4

At some point since my last run of Packer builds version 1.8.4 was released. This follows the same pattern. It works great with VirtualBox 6.1.40, but doesn’t work at all with VirtualBOx 7.0.x.

Conclusion

Sadly VirtualBox 7.0.x is still a bust for me. Maybe I’m the only one on the planet, but it is unusable for me. I hope this changes in the future as I rely on VirtualBox bigtime…

Cheers

Tim…

Update: I’ve now switched to using VirtualBox 7.0.4 completely, and all issues have been resolved. This post by Frits Hoogland explained what my problem was. This is solved by adding the “–nat-localhostreachable1” parameter. That solved my Packer problem, and subsequent VM problems. Happy days…

Update 2: An update from Simon Coter suggests the issue is due to the way Vagrant and Packer use the VirtualBox CLI, rather than the API. The workaround mentioned in the previous update is still the easiest way to move forward.

APEX 22.2 : Vagrant and Docker Updates

I know it’s hard to believe that anything happened last week other than the implosion of Twitter, but APEX 22.2 was also released.

As normal, this resulted in a bunch of updates to my builds.

Vagrant

All relevant Vagrant builds were updated to include APEX 22.2. Many had been updated recently to bring them in line with the latest Oracle security patches. You can find the build here.

https://github.com/oraclebase/vagrant

Docker/Container

As with Vagrant, all the relevant Docker/Container builds have been updated to 22.2. You can find the build here.

https://github.com/oraclebase/dockerfiles

Real World

If the release had been a couple of weeks earlier I would have been able to push it out during this quarters patching cycle. Unfortunately this release will now how to wait until the January 2023 patching cycle for me to push it out at work.

Our APEX upgrades/patches are automated (as mentioned here), so I could push this release out at the press of a button, but all the relevant teams would have to do their testing, and that probably isn’t going to happen until the next patching cycle, so it’s just going to wait until then. šŸ™

My Suggestion

Even if your work environment moves forward at a slower pace than you would like, it still makes sense to keep a test/play environment at the latest and greatest versions, so you can learn the new stuff and see what issues are coming round the corner for your applications.

All my home builds are now on APEX 22.2, running on the beta release of the Oracle Games Console (#OGC). šŸ˜‰

Cheers

Tim…

VirtualBox 7.0.2 and Vagrant 2.3.2

VirtualBox 7.0.2

Hot on the heels of VirtualBox 7.0, we now have VirtualBox 7.0.2. I’m not going to lie, I expected this release to happen really quickly, as VirtualBox 7.0 gave me a number of headaches.

The downloads and changelog are in the normal places.

I’m hoping this fix solves a lot of my problems.

  • Main: Fixed issue when VBoxSVC could become unresponsive if Extension Pack was not installed (bug #21167)

I’ve installed 7.0.2 on a couple of Windows machines (10 and 11), and I’m going to play with it today.

Vagrant 2.3.2

As mentioned in my post about VirtualBox 7.0, Vagrant 2.3.1 doesn’t support VirtualBox 7.0 directly, so I was expecting a quick release of Vagrant and here it is. Vagrant 2.3.2 has a single feature in its changelog.

  • provider/virtualbox: Add support for VirtualBox 7.0 [GH-12947]

I’ll be attempting some new Packer builds of my Vagrant boxes, then working through my Vagrant and Docker builds to add in the new patches. That should give it a pretty good test. I’ll update this post with the results.

If this doesn’t work out, I’ll be reverting to VirtualBox 6.1.40…

Cheers

Tim…

Updates

Update 1: The good news is VMs seem to start OK now using Vagrant 2.3.2 and VirtualBox 7.0.2. (not true, see below)

The bad news is I can’t get Packer to work, so I’m unable to build new Vagrant boxes with the 7.0.2 guest additions. I’ve tried on Windows 10, Windows 11 and macOS (Intel) and all result in the same issue. It seems the kickstart is not working, like the networking on VirtualBox 7.0.2 is screwed up somewhere.

I’ve downgraded my Windows 10 PC to use VirtualBox 6.1.40 and I’m building new Vagrant boxes on that. So far so good. I’ll upload them to Vagrant Cloud and attempt to use them with VirtualBox 7.0.2. The guest additions will be out of date, but it should still work fine, I hope. I’ll keep updating…

Update 2: After a couple of successes starting up VMs using VirtualBox 7.0.2 and Vagrant 2.3.2, it all seems to have caved in now.

I’m going to switch to VirtualBox 6.1.40 for the moment. I don’t have more time to waste on this. VirtualBox and Vagrant are tools I use to make my life easier, not an end in themselves, so I don’t have the time to keep working on this. I’m sure a future version of VirtualBox will have sorted out its networking issues and I’ll be able to use it…

Update 3: Everything is reverted to VirtualBox 6.1.40. Packer builds of OL7, OL8 and OL9 are completed and uploaded to Vagrant Cloud. I’ve started to do some Vagrant builds with them now and they seem to be working fine, so what I’m seeing is on VirtualBox 6.1.40 everything works as usual. On VirtualBox 7.0.2 I see these two issues.

  • Packer just doesn’t work. It fails getting the HTTP access to the kickstart file. I get the same issue on Windows 10, Windows 11 and macOS (Intel).
  • Starting VirtualBox VMs with Vagrant is hit-or-miss. Sometimes they work, but sometimes they fail. It also seems network related, but I can’t be 100% about that.

Update 4: An update from Simon Coter suggests the issue is due to the way Vagrant and Packer use the VirtualBox CLI, rather than the API. The workaround for this was posted by Frits Hoogland here.

Joel Kallman Day 2022 : It’s a Wrap! (#JoelKallmanDay)

Yesterday was an Oracle community day called the Joel Kallman Day (#JoelKallmanDay) 2022.

I would like to say a big thank you to everyone who took the time to join in. Here is the list of posts I saw in chronological order. If I missed you out, give me a shout and I’ll add you. 

Thanks to everyone who got involved with blog posts, tweets and retweets. It’s always fun to see what people come up with on this day. It’s also good to see people making contributions in their own language too. It’s easy for us to forget that people out there are doing good work for the community in their primary language!

A few people mentioned Joel specifically, and included some pictures, which was both nice and sad at the same time.

Thanks again, and remember you’ve got a year to get ready for the 2023 event! šŸ™‚

Cheers

Tim…