The Future of Development: Configuring AI Assistant API for Oracle APEX (OpenAI ChatGpt) ~ APEX Note

In this blog, I will show you two methods to establish a connection to the AI service.

Method 1: Using Oracle APEX 24.1

Step 1: Set Up AI Service

Select AI Service
Choose the OpenAI provider.
Obtain API Key
Register to get an API key: https://platform.openai.com/
Set API Key
Set your API key:
name: Service_OpenAIapy

Step 2: Define AI Model

View Models
Here are the models you can use: https://platform.openai.com/docs/models
Check Pricing
Here are the prices: https://openai.com/api/pricing/

Step 3: Create AI Service in Oracle APEX 24.1

Navigate to AI Services
In Oracle APEX 24.1, go to SQL Workshop > RESTful Services.
Create New AI Service
Click on Create and select AI Service.
Enter Service Details
Service Name: Service_OpenAI
API Key: Enter your API key.
Endpoint URL: https://api.openai.com/v1
Define Parameters
Add the required parameters, e.g.:
prompt
max_tokens
Save and Test Service
Save the service and test it to ensure the connection works.

Method 2: Direct Use with PL/SQL (For Advanced Users)

If you have an older version of Oracle APEX and still want to use AI, you can do this directly via PL/SQL.

Step 1: Insert PL/SQL Code

Here is the code you can use. Replace the value API_KEY with your actual API key.

SQL

xxxxxxxxxx
 
DECLARE
    l_response      CLOB;
    l_url           VARCHAR2(500) := 'https://api.openai.com/v1/chat/completions';
    l_body          CLOB;
    l_chat_response CLOB; 
    l_api_key       VARCHAR2(500) := 'Bearer API_KEY'; -- Replace API_KEY with your actual API key
BEGIN
    -- Set necessary headers for JSON Content-Type and Authorization
    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 := l_api_key; 
    
    -- Body for the POST request with the prompt
    l_body := '{
        "model": "gpt-3.5-turbo",
        "messages": [{"role": "user", "content": "Hello, how are you?"}],
        "max_tokens": 100
    }';
​
    -- Execute the POST request
    l_response := apex_web_service.make_rest_request(
        p_url => l_url,
        p_http_method => 'POST',
        p_body => l_body
    );
​
    -- Parse the response using JSON_TABLE to extract the description
    FOR r IN (
        SELECT message_content
        FROM JSON_TABLE(
            l_response, '$'
            COLUMNS (
                message_content CLOB PATH '$.choices[0].message.content'
            )
        )
    ) LOOP
        -- Store the chat response in the variable
        l_chat_response := r.message_content;
    END LOOP;
​
    -- Output the response using DBMS_OUTPUT
    DBMS_OUTPUT.PUT_LINE('ChatGPT Response: ' || l_chat_response);
​
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Step 2: Adjust and Execute Code

Insert API Key
Replace 'API_KEY' with your actual API key.
Execute SQL Script
Execute the SQL script in your Oracle APEX environment.

Note: This guide aims to help you establish a connection between Oracle APEX and OpenAI. If you have further questions, you can use the official documentation and resources from OpenAI.

Resources:

Good luck with your implementation!

{fullWidth}

APEX Note