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

        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
        acl        => 'jsonplaceholder.typicode.com_acl.xml',
        principal  => 'APEX_220200',
        is_grant   => TRUE,
        privilege => 'resolve'
        acl  => 'jsonplaceholder.typicode.com_acl.xml',
        host => ''

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

  l_principal varchar2(20) := 'APEX_220200';
  dbms_network_acl_admin.append_host_ace (
    host       => '', 
    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)); 

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

    l_url       VARCHAR2(4000) := ''; -- Replace with your API endpoint
    l_response  CLOB;
    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
        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
  "userId": 1,
  "id": 1,
  "title": "delectus aut autem",
  "completed": false

PL/SQL procedure successfully completed.



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.



Oracle Code : India – Bengaluru to Birmingham

Following my normal pattern, sleep was a little fitful the night before a flight. I got up early and walked over to meet Sebastian at his hotel. We were on the same first flight, so we shared a taxi to the airport.

We purposely started early to avoid the traffic, making it a really easy journey in to the airport. We got through check-in and security really quickly, which left us with about three hours before the flight. Neither of us had the correct status for this flight, so we paid to get into the lounge and chill for a few hours. Happy days…

As we were boarding my boarding pass dinged on the machine and I had a free upgrade to business, which was nice. The flight to Dubai took about 3.5 hours and I was able to work for the whole of it, which was great. I answered a few emails and did an upgrade of one of our Dev systems. Travelling would be so much easier if it was always like this… 🙂

After a 2 hour connection I was on a 7.5 hour flight to Birmingham. Unfortunately there was no free upgrade for this flight, so it was back to cattle class… The flight itself was fine, bit I was getting a bit desperate to land by the end of it.

Once I landed in Birmingham it was a quick taxi ride home and Oracle Code : India was done. 🙂

These are the posts I wrote during this trip.

Thanks to all the Oracle Code crew for inviting me and sorting everything out. Thanks to the Oracle ACE Program and Oracle Developer Champions Program for letting me continue to fly the flag. Thanks to all the attendees that help make these events really successful, especially all the folks that came to my sessions and spoke to me after the sessions.

I spoke to Sai about coming back to Bengaluru in December for the Sangam 2018 event. Let’s see if that works out… 🙂

I’m not looking forward to working out how much of my own money I spent on this trip… 🙂



Oracle Code : Bengaluru – 2018

Today was Oracle Code : Bengaluru.

I spent the last few days working from my hotel room so I was feeling a little stir crazy and was ready to get out and meet people. Then I destroyed my demo… 🙁 After spending some time putting it back together again I headed off to the conference venue, which was across the road from my hotel, so that involved waiting for a gap in the traffic for 10 minutes. 🙂

The event was really busy. I had a slot before lunch and once everyone filed in it was standing room only for my Make the RDBMS Relevant Again with RESTful Web Services and JSON session.

After the session I spent hours chatting to people and posing for photos. I had a lot of questions about ORDS, so I opened my laptop on a counter surface and talked some more, answering questions by working through articles I had written. It was really good fun. I had a short break, to record a video for the event, then it was back to chatting to folks. As a result, I didn’t get to see anyone else’s presentations, but these events are all about getting us together and enthusing about the technology, so it didn’t matter. Before I knew it the event was over.

I hope everyone had a great day. I know I did. 🙂 Thanks everyone for coming along, being so enthusiastic and coming to speak to me. 🙂 Thanks to the Oracle Code crew for putting on a great event and inviting me to it. Thanks to the Oracle ACE Program and the Oracle Developer Champions program for letting me be involved in these events.

After the event I went over to meet with the Oracle Code crew and Developer Champions to get some food. After eating far too much and saying my goodbyes it was back to the hotel to get some sleep before the flight home tomorrow!

Thanks you all and see you all soon!



Oracle Code : India – Hyderabad to Bengaluru

I mentioned in the previous post I ducked out of the evening dinner as I needed to get some sleep. I had very little up to this point, and having to get up at 03:00 to get a flight suddenly seemed like a very bad idea. 🙂

I got some really nice food from the hotel and went to bed. In typical Tim style I was so nervous about not waking up in time for the plane I didn’t sleep. When 03:00 rolled up I felt quite bad. I went down to the desk to check out and meet Lori and Sebastian, who were on the same flight as me. At that point my stomach started grinding, I started to get the sweats and shiver. I won’t go into the details, but I started to wonder if I would make it to Bengaluru with any dignity intact…

In the taxi I felt quite bad, until I noticed the driver falling asleep and then the adrenalin made me feel super focused. I had a flashback to my taxi ride from hell between Jalandhar and Noida. We made it to the airport in one piece. Once the adrenalin subsided I felt terrible again.

The flight was really short and easy, but I started to feel like I was going to puke once we had landed. I was stopping in a different hotel to the others, but I piggy-backed on their ride, which was a big car with great aircon, which made me feel much better.

By the time we got to their hotel I was feeling quite good. I walked down the road to my hotel and went to bed at 08:30. Once I had some sleep I was feeling good, so I logged in to work to for the afternoon shift (UK time).

I think all this drama was caused by a lack of sleep. Debra can tell you some stories about what happens when I don’t sleep properly (hospital in Jalandhar and Machu Picchu). I’m such a delicate petal. 🙂

I have to “work from home (from India)” for a few days before the Oracle Code : Bengaluru event. See you there!



PS. On a strange note, my room number in the new hotel is the same as in the previous hotel. Freaky!

Oracle Code : Hyderabad – 2018

Today was Oracle Code : Hyderabad.

I woke up in a bit of a daze, so rather than jumping straight into the fray I worked through my demos again just to settle my nerves. 🙂

I headed down for my session to find there was no lectern and they wanted to run my presentation from the audio desk. I said that wasn’t going to work as I had live demos, so they guys went off to find one. The keynote had overrun, so people were late coming into the session, which bought me some time. 🙂

The presentation was a little tricky. The sound on the wireless head mic kept dropping out or getting feedback, so I switched to a hand mic part way through, which made typing difficult. There was a large back-lit screen, which looked great, but didn’t work with a laser pointer, so that made life harder. Despite the issues, I think the talk went OK. It was a little choppy, but I think I got the message across. The main thing was I got through it without my laptop giving up. 🙂

After my session I spent a lot of time chatting to people and continuing the discussion of Oracle Databases on Docker, which filtered into lunch.

After lunch I did a periscope live stream with Connor McDonald chatting about a variety of things including my website, presenting and Oracle databases on Docker.

After that I went to watch Connor do a session on SQL. You know you are doing something right when they have to add a few extra rows of seats to the room and you still have people standing. 🙂

From there is was back to the developer lounge to look at the demos. One of the guys doing the IoT demos was a machine. He was so enthusiastic and went all day.

The developer lounge is also a good way to connect with more people. Some people are quite intimidated about asking a question in the session, but they will happily come up to you in private and ask. I always like this bit of the conference.

And before I knew it the day was over. Some of the folks went out to eat together, but I ducked out because I had been so short on sleep already. I decided to go back to my room and crash, but more on that in the next post… 🙂

Overall I think the event went really well. The turnout was great and people were really enthusiastic and open. Thanks everyone, especially those folks that came to speak to me during the day. It makes the events even more fun for me.

So tomorrow I travel to Bengaluru, where I will be “working from home (from India)” for a few days before the next event. I’ve got to save my holidays for events later in the year. 🙂



Oracle Code : India – The Journey Begins

If you’ve been following my tweets recently you will know I’ve had a bit of a nightmare lead up to these events. Problems with my laptop and problems with my seat on one of the planes.

About 15 minutes before the taxi was due to pick me up I finally got most of the laptop things sorted. It was a bad morning…

I got to the airport in plenty of time and went to the check-in desk to try and get an aisle seat. The lady there said she could lock an aisle seat for me, but couldn’t give me a boarding pass for it as I had ordered veggie food, and moving me to a different to a different floor in the plane was a problem. When I got on the plane there was a bit of confusion, but eventually they told me to sit in the new seat location and see if anyone else turned up to claim it. They didn’t so I got the aisle seat, which was a big relief.

The flight went well, and the guy sitting next to me was cool so I spent a lot of time chatting. Unfortunately I didn’t get any food, but faced with the choice between a window seat with food or an aisle seat with no food, the aisle seat wins every time. I also watched Star Wars : The Last Jedi, which I enjoyed.

I got to Dubai on time and about 14 hours after waking up I finally got to eat something. A Starbucks muffin. 🙂

The flight from Dubai to Hyderabad was only about 3 hours, but by the time it was over I had been awake for about 20 hours and was feeling it. I got to watch Get Out, which was very cool.

I got a Taxi from Hyderabad Airport to the hotel, which was interesting… The driver wasn’t too interested in reading the hotel name or address, which I had written down. He eventually dropped me off at the wrong hotel and they had to explain to him it wasn’t the correct place. He then managed to reverse out of the hotel carpark into oncoming traffic and hit someone. The bumper of our taxi was then pushed back into place before we drove off and finally got to the correct hotel. 🙂

I was about 3 hours early for check-in, but they had a room for me, so it was time for a shower and bed. Sleep was a bit fitful, but at least I got some.

In the evening I met up with some of the other speakers and folks from the Dev Champion Program to get some food, then it was back to bed.

So I made it to Hyderabad. The Oracle Code : Hyderabad event starts tomorrow.



Upcoming events I’m attending…

Just a quick note to mention some of the events I’ll be at over the next few months.


I’ll be at a couple of Oracle Code events in India in the next couple of weeks.

I’ve submitted for a few of the European Oracle Code events too, but I have no idea if I will be selected or not. When I know I’ll post an update. 🙂

Unfortunately I can’t make the “Oracle Code: London” event this year as I’m already confirmed for another conference on those dates. Apart from the Oracle Code events, I’m already confirmed for these two events.

See you there!



Oracle Code : Prague – The Journey Home

I had to be at the airport for 12:15, which meant I got a lie in and I actually slept a bit, which was nice. 🙂

When I woke up I got some breakfast, edited a couple of short Oracle Code videos and caught up with my blog posts. After that was done I went through all the work emails I’ve missed over the last week, just so I don’t have that unpleasant job to do when I get back to work on Tuesday. Once that was done a went back to bed for an hour before check-out. I know it’s sad, but I’ve had so little sleep the previous two days I felt dead.

It was a quick taxi ride to the airport and I barely broke my stride walking through security, which meant I was two hours early for the flight. Better early than late! 🙂

Because I fluked a business class ticket on the way out I had a baggage allowance of two cases and two pieces of hand luggage, of which I used two pieces of hand luggage. The journey back was economy, so I had an allowance of one piece of hand luggage. Why do companies sell return tickets with different baggage allowances? Luckily I didn’t have much, so I could cram it into the once bag.

As soon as they announced boarding, most people ignored the queue and barged to the front. The flight from Prague to Frankfurt took about an hour. As soon as I got off I checked the gate for the next flight and it had been moved to the other terminal, so I stomped off in a bit of a panic, but made it in plenty of time.

As it happened, boarding was delayed for the second flight because one of the trolley dollies was late. Once again, as soon as boarding was announced a bunch of people jumped the queue. I will not tell you what I was wishing would happen to the queue jumpers… The second flight took at 75 minutes, which wasn’t so bad.

After a quick taxi ride home, Oracle Code : Prague was complete! I did all my thank you messages in the last post, but once again thanks to everyone involved for making the event go so well. See you soon!



Oracle Code : Prague – The Journey Begins

Another stupidly early start, so another night of no sleep, worrying about missing my flight. 🙂

I was up at 04:00 to get a taxi into the airport. The queues were massive, but for some reason I had a business ticket for the outward leg, so I could use the express lane and walk straight past the hordes of people and into the lounge. Happy days.

The first flight was Birmingham to Munich, which took about 90 minutes. I had enough time between flights to head to the lounge and eat “free” peanuts. The flight from Munich to Prague was delayed, but I bumped into Frank Nimphius, who is in his medium-floppy hair phase at the moment, and we chatted about the previous Oracle Code events, while he casually flicked his hair. 🙂

The flight to Prague took about 50 minutes. I had arranged a pick-up from the hotel shuttle, so there was a man with a sign waiting to pick us up, which was nice.

After a few minutes and I was in the hotel in Prague with an afternoon to spare. I know I should go and have a look round, but sleep feels so much more important at this point!

The Oracle Code : Prague event is tomorrow, so I hope to see you there!



Oracle Code : London

As usual with early starts, I had a terrible nights sleep, worrying I would sleep through my alarm. 🙂 I’ve been quite nervous about this event ever since I found out I got accepted. I’m pretty comfortable in front of your typical Oracle crowd, but having no idea about the makeup of this audience freaked me out. My feeling was if the crowd was made up of Oracle techies looking to find out about different stuff I should fit in OK. If it was a bunch of non-Oracle people looking to see what Oracle was offering in this space my session would not go down well at all. Time would tell…

I got a taxi to the station, grabbed a coffee and got on the train. I had a seat with a table and a power socket. For £5 I got 24 hours of internet, which made the journey far more productive. When I got to Euston Station it was a quick tube ride to Moorgate Station, then a walk round the corner to CodeNode London. After registration and a few quick hellos it was down to business.

First up was Adam Bien with “Enterprise A Slideless Keynote”. The session was a live demo of using Java EE to code microservices that are built and deployed using Docker. Live demos of anything to do with Docker are always fun to watch as things happen so quickly. 🙂

Next up was Deepak Patil with “Harnessing the Power of Cloud to Develop Next Gen Killer Apps”. This session was a description of the infrastructure that underpins Oracle Cloud, with a guest spot by Mark Shuttleworth of Cononical. I asked the question on Twitter whether this meant Oracle would start to support their products on Ubuntu. I did not get an answer. 🙂

After that session the conference was split into multiple tracks and I went to see Jeff Richmond with “Move Data Between Apache Hadoop and Oracle Database for Customer 360 Analytics”. This session covered quite a bit of ground in a single session, including talk of Oracle Big Data SQL and a bunch of Oracle Cloud Services.

Next I went to see Davide Fiorentino with “A Practical Guide to Docker and Service Deployments”. As the name suggests, this was an overview session about Docker, giving an idea of how and why you would use it.

After lunch it was me with “Make the RDBMS Relevant Again with RESTful Web Services and JSON“. As I said earlier I was really nervous about this because I didn’t know if I would be a good fit for the audience, or even if anyone would show up. The room was really busy and a quick straw poll at the start alleviated my nerves. There was a strong contingent of existing Oracle developers in the room, who were looking for “what comes next”, and my session seemed to go down pretty well. That was a really welcome surprise. 🙂

[silly aside] I keep mentioning we are doing an Oracle Cloud Apps implementation at work and I am nothing to do with the project. For anyone that doubts this, one of the questions at the end of my session was by someone who is working on that project, who I don’t know. 🙂 When I finished, two guys came up and introduced themselves. Turns out they are working on our on-prem WebCentre Content implementation. You gotta laugh! I’m sure our paths will cross how we have been introduced.[/silly aside]

Next up was Lucas Jellema with “Event Bus as Backbone for Decoupled Microservice Choreography”. I really liked the ground work Lucas put in before he launched into the demos. The term “microservice” is often used in a rather random manner, with many people describing what they are doing as microservices, when they really aren’t. Lucas did a top-notch job of describing what a microservice is and some of the basic “rules” which it should probably conform to. He then did a rather ambitious demo made up of a bunch of microservices running on his laptop and the cloud, all interacting with each other using an event bus in each location that were being synced, and it worked. I should have expected nothing less. 🙂

I missed the next session as I was chatting to a few people, including some of those guys that I “almost” work with. 🙂

Next up was Dan Mcghan with “Making RESTful Web Services the Easy Way with Node.js”. This was how the cool kids do what I do with PL/SQL and ORDS. 🙂 The combination of Node.js and the Oracle Driver for Node seems like a pretty good combo for this type of thing.

That was the last session of the day for me. We all piled downstairs for pizza, drinks and more chatting. Pretty soon it was time to leave. 🙂

Big thanks to the organisers of Oracle Code for letting me come to play. Not only was it fun to present, but I got to see and learn a lot of really useful stuff! Thanks also to the Oracle ACE Program for letting me fly the flag. I gave you a shout out in my session! Thanks to all the attendees that came to support the event, and to everyone that came to my session and came and chatted to me during the day. These events can’t happen unless you turn up! 🙂

The journey was pretty straight forward. Round the corner to Moorgate, tube to Euston, train to Birmingham and taxi home! Happy days!

It all happens again in Prague next Friday! 🙂