Calling OpenAI API with PL/SQL

ChatGPT or DALL·E 2 hardly needs any introductions since it has been on everyone’s lips these days. OpenAI, the company behind these cool products, also offers APIs.

The OpenAI API can be applied to virtually any task that involves understanding or generating natural language or code. We offer a spectrum of models with different levels of power suitable for different tasks, as well as the ability to fine-tune your own custom models. These models can be used for everything from content generation to semantic search and classification.

source

So I thought would be fun to test a call to the OpenAI API using PL/SQL. However, since I use ChatGTP in my browser, I simply asked it to generate the code for me.

So I asked ChatGPT the following:

Write a pl/sql block that calls the openai api for answering this question. Why should developers learn SQL? You must use apex_web_service.make_rest_request.

It does generate code, but the code does not work for several reasen. The first code it suggested uses utl_http, so I asked ChatGPT to generate the code without using utl_http.

Great, it generated the code for me! But, If I execute that code in database action I get:

PLS-00302: component 'T_HTTP_HEADERS' must be declared

Not so great it seems!

I don’t know who it comes up with apex_web_service.t_http_headers, which simply doesn’t exists. I did try to hint ChatGTP several times, but still no luck to make a valid call.

However, it gave me what I was looking for, the API name to use to answer my question (Why should developers learn SQL?)

The l_url variable is defined as https://api.openai.com/v1/engines/davinci-codex/completions in the code that ChatGPT generated. So that was a fast way for me to find out which API to call.

ChatGPT also tells me, that I need an API key to make the call. So the next step is to sign up for OpenAI account and generate the key. ( I will not do that here).

Now I can try to execute PL/SQL block that I wrote. Replacing “YOUR_API_KEY” with the API token.

DECLARE
    lv_resp_body CLOB;
BEGIN
    apex_web_service.g_request_headers(1).name := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json';
    apex_web_service.g_request_headers(2).name := 'Authorization';
    apex_web_service.g_request_headers(2).value := 'YOUR_API_KEY';
    
    lv_resp_body := apex_web_service.make_rest_request(
        p_url         => 'https://api.openai.com/v1/engines/davinci-codex/completions',
        p_http_method => 'POST',
        p_body => '{
"prompt": "Why should developers learn SQL?",
                    "temperature": 0.5,
                    "max_tokens": 50
 
                   }'
);
   dbms_output.put_line(lv_resp_body);
END;
/

The result:

You exceeded your current quota, please check your plan and billing detail

So using the ChatGPT from the browser is not the same as calling the API:s. The solution is to add a credit card to your OpenAI account profile.

From your OpenAI account, you can also control if you want to expand beyond the trial.

So now when the card have been added to the profile , let’s execute once again.

DECLARE
    l_resp_body CLOB;
BEGIN
    apex_web_service.g_request_headers(1).name := 'Content-Type';
    apex_web_service.g_request_headers(1).value := 'application/json';
    apex_web_service.g_request_headers(2).name := 'Authorization';
    apex_web_service.g_request_headers(2).value := 'Bearer YOUR_API_KEY';
    l_resp_body := apex_web_service.make_rest_request(
        p_url         => 'https://api.openai.com/v1/engines/davinci-codex/completions',
        p_http_method => 'POST',
        p_body => '{
            "prompt": "Why should developers learn SQL?",
            "temperature": 0.5,
            "max_tokens": 50
        }'
       
    );
   DBMS_OUTPUT.PUT_LINE (l_resp_body);
END;
/

{"id":"cmpl-6X75dOPOwor1k4el1guxdnGlZzvdA","object":"text_completion","created":
1673350097,"model":"davinci-codex","choices":[{"text":"</h3>\n# \n# SQL is the
main language used to interact with many computer systems, both for storing and
retrieving data. Understanding SQL gives you the ability to write simple
commands to query and extract the data from complex
databases.","index":0,"logprobs":null,"finish_reason":"length"}],"usage":{"promp
t_tokens":5,"completion_tokens":50,"total_tokens":55}}

Now finally, ChatGPT’s answer to my question “Why should developers learn SQL?” is

“SQL is the main language used to interact with many computer systems, both for storing and retrieving data. Understanding SQL gives you the ability to write simple commands to query and extract the data from complex databases.”

Since you can do this in PL/SQL, you can do it APEX. Either call the PL/SQL code or define your module declarative in APEX.

Over & Out