Oracle APEX and the use of ChatGPT

AI came to help us and today all over the world people are talking about GPT Chat and here I also join this talk!

I want to show you in this post, how I have developed a small application in Oracle APEX that uses the GPT Chat API to get a meal plan and an exercise plan 😊.

We all at some point see ourselves with the need to go on a diet or improve our lifestyle by eating healthier and exercising, in my case I need it urgently! 😊 Today thanks to AI I want to show you how to call the OpenAI API from APEX to create this very simple demo.

First of all, I would like to thank a fellow Oracle APEX expert, Michellewho thanks to your post motivated me to get into this world of GTP and APEX 😊 thanks Michelle!

Now, let's see what Chat GPT is. The ChatGPT is a model that is capable of following a prompt and providing a detailed response.

The capabilities of this tool are that:

• Remember what the user said earlier in the conversation.
• Allows the user to provide follow-up corrections
• Trained to reject inappropriate requests

As for limitations, the tool warns of the following:

• Occasionally may generate incorrect information
• Occasionally may produce harmful instructions or biased content
• Limited knowledge of the world and events after 2021.

In order to use the chat we need to create an account at the following link: https://openai.com/

Once we have the account we go to https://platform.openai.com/playground to understand how we can interact with the API.

En esta pantalla podemos ver un elemento de tipo select list que nos permite cargar distintos ejemplos:

When loading the example we can see how the API is instructed to perform the translation from English to French and Japanese.

Another example is to explain code:

When you click on the Submit button, the explanation of the code begins.

In the article by Michelle, she explains very well the basics to keep in mind to generate a better prompt to make a call to OpenAI.

On the other hand, we should mention that there are different models to use when making an API call.

GPT-3 Models

According to the documentation GPT-3 models can understand and generate natural language. They offer four main models with different power levels suitable for different tasks. Davinci is the most capable and Ada is the fastest.

LATEST MODEL	DESCRIPTION	MAXIMUM REQUEST	TRAINING DATA
texto-davinci-003	The most capable GPT-3 model. It can perform any task that the other models can perform, often with higher quality, longer duration and better instruction tracking. It also supports the insertion of in-text completions.	4,000 tokens	Until June 2021
texto-curie-001	Very capable, but faster and lower cost than Davinci.	2,048 tokens	Until October 2019
texto-babbage-001	Capable of performing simple, very fast and lower cost tasks.	2,048 tokens	Until October 2019
texto-ada-001	Capable of very simple tasks, generally the fastest model of the GPT-3 series and the lowest cost.	2,048 tokens	Until October 2019

Versions prior to GPT-3

Davinci

Davinci is the most capable family of models and can perform any task that the other models can perform and often with less instruction. For applications that require a lot of content understanding, such as summaries for a specific audience and creative content generation, Davinci will produce the best results. These greater capabilities require more computing resources, so Davinci costs more per API call and is not as fast as the other models.

Another area in which Davinci shines is in understanding the intent of the text. Davinci is quite good at solving many types of logic problems and explaining the motives of characters. Davinci has been able to solve some of the most challenging AI problems involving cause and effect.

Good at: complex intent, cause and effect, audience summary

Curie

Curie is extremely powerful, and very fast. While Davinci is stronger when it comes to parsing complicated text, Curie is quite capable for many nuanced tasks such as classification and sentiment summarization. Curie is also quite good at answering questions and performing Q&A and as a general service chatbot.

Good at: language translation, complex classification, text sentiment, summarization

Babbage

Babbage can perform simple tasks such as simple classification. It is also quite capable when it comes to semantic search that classifies how well documents match search queries.

Good at: moderate classification, semantic search classification

Ada

Ada is usually the fastest model and can perform tasks such as parsing text, correcting addresses, and certain types of sorting tasks that do not require much nuance. Ada's performance can often be improved by providing more context.

According to the tool, it tells us to use the Davinci model as it is an excellent way to find out what the API is capable of.

To learn more about the API I leave here the link:

https://platform.openai.com/docs/guides/completion/prompt-design

To be able to use the API in our Oracle APEX application it is necessary first of all to generate our API key. To do this in our profile we access to View API keysand there we can create a new API Key.

Example of promp:

Note: it is better to have a much shorter prompt, since it is easier to get what we really want to get and also we would not have error in the playground when we want to test our prompt. For this demonstration I have made it very simple to have a first approach to this tool. Let's see what the AI recommends us according to the prompt we enter.

"Act like a nutritionist and create a 3-day diet for me. Create a Detox Diet. The diet should have one serving of protein and two servings of fat and three servings of vegetables and one serving of carbohydrates, for each meal. Prioritize foods that are high in the micronutrients on this list: Iron, Magnesium. Also, I am allergic to or do not like: Salmon. I want you to describe recipes with step-by-step for each meal on a new line. Describe what micronutrients each meal contains."

Ingreso al chat GPT:

Y vemos que el chat nos responde con la dieta que pedimos.

Oracle APEX con GPTchat

Ahora creamos una página en APEX para poder seleccionar las opciones. Luego al seleccionar las opciones que deseamos, hacemos clic en el botón Obtener Mi Plan para realizar la llamada a la API de OpenAI.

Esta página contiene:

• 1 elemento de página de tipo Select List (Cantidad de Días).
• 1 elemento de página de tipo Radio Group  (Tipos de Dietas).
• 4 elementos de página de tipo Checkbox  (proteínas, Vegetales, Grasa, y Carbohidratos).
• 3 elementos de página de tipo Textarea (Alergias, Incluir Micronutrientes, Comentarios).
• 2 elementos de página de tipo Switch (Incluir Información Nutricional, Incluir Receta).
• 1 elemento de tipo Textarea para mostrar el resultado.

Un botón para llamar a la API de OpenAI y otro botón para limpiar la pantalla que simplemente limpia todos los elementos de la pantalla.

El proceso que utilizamos para darle la indicación a la API de nuestro prompt es el siguiente:

“Actúa como un nutricionista y crea una dieta para mí de 3 días. Elabora una Dieta Detox. La dieta debe tener una porción de proteína y dos porciones de grasa y tres porciones de vegetales y una porción de carbohidratos, para cada comida. Prioriza las comidas que tienen alto valor de los micronutrientes de esta lista: Hierro, Magnesio. Además, soy alérgico o no me gusta: Salmón. Quiero que describas recetas con el paso a paso por cada comida en una nueva línea. Describa qué micronutrientes contiene cada comida.”

Lo resaltado en negrita serían nuestras opciones. Y las demás opciones se activarán en caso que seleccionemos lo elementos para obtener los requerimientos.

El siguiente código PL/SQL lo agregamos como un proceso cuando se hace clic en el botón Obtener Mi Plan.

declare

l_prompt varchar2(4000);

begin

    l_prompt:='Actúa como un nutricionista y crea una dieta para mí de ' ||:P4_DIAS_SOLICITADOS||' días.';

    if :P4_TIPO is not null then   
        l_prompt:=l_prompt||'Elabora una '||:P4_TIPO||'.';
    end if;

    if :P4_PROTEINAS = 'Y' then
        l_prompt:=l_prompt||'La dieta debe tener una porción de proteína y ';
    end if;

    if :P4_GRASAS = 'Y' then
        l_prompt:=l_prompt||'dos porciones de grasas y ';
    end if;

    if :P4_VEGETALES = 'Y' then
        l_prompt:=l_prompt||'tres porciones de vegetales y ';
    end if;

    if :P4_CARBOHIDRATOS  = 'Y' then
        l_prompt:=l_prompt||'una porción de carbohidratos para cada comida.';
    end if;

    if :P4_INCLUIR_MICRONUTRIENTES is not null then   
        l_prompt:=l_prompt||' Prioriza las comidas que tienen alto valor de los micronutrientes de esta lista: '||:P4_INCLUIR_MICRONUTRIENTES||'.';
    end if;

    if :P4_EVITAR_COMIDA is not null then
        l_prompt:=l_prompt||'Además, soy alérgico o no me gusta estos alimentos: '||:P4_EVITAR_COMIDA||'.';
    end if;

    if :P4_INCLUIR_RECETA_DIRECCIONES = 'Y' then
        l_prompt:=l_prompt||'Quiero que describas recetas con el paso a paso por cada comida en una nueva línea.'; 
    else
        l_prompt:=l_prompt||'No incluya receta para cada comida.'; 
    end if;   

    if :P4_INCLUIR_INFO_NUTRICIONAL = 'Y' then
        l_prompt:=l_prompt||'Describa qué micronutrientes contiene cada comida.'; 
    end if;

    if :P4_COMENTARIOS is not null then   
        l_prompt:=l_prompt||'. '||:P4_COMENTARIOS||'.';
    end if;

    chatgpt_pkg.get_completion(
        in_parm1 => l_prompt,
        out_plan => :P4_RESPUESTA);

end;

El paquete que necesitamos para que se realice la llamada, fue creado por Michelle, gracias Michelle por este gran aporte! 😊 Visita su blog aquí.

Qué interesante lo que puede hacer la IA!, replico lo que ella preguntó para poder hacer la llamada con pl/sql, me encantó!

Cuál es el código PL/SQL requerido para usar apex_web_service.make_rest_request para hacer una llamada a GPT3 usando el modelo text-davinci-003.

Aquí está el paquete creado que podemos usar según las especificaciones dadas.

create or replace PACKAGE "CHATGPT_PKG" is 
/* 
Purpose: PL/SQL package for OpenAI API 
Remarks: Allows us to post requests to OpenAI API 
Who Date Description 
—— ———- ————————————- 
MS 14.1.2023 Created package 
*/ 
procedure get_completion ( 
in_parm1 in varchar2, 
out_plan out clob 
); 
end chatgpt_pkg; 

create or replace PACKAGE BODY "CHATGPT_PKG" is 
g_gpt_api_url constant varchar2(255) := 'https://api.openai.com/v1/completions'; 
g_api_secret constant varchar2(255) := 'API Key'; 
 
 
procedure get_completion ( 
in_parm1 in varchar2, 
out_plan out clob 
) 
is 
l_result clob; 
 
/* the parameters in the request could/should be variables for a more flexible call */ 
 
l_request_text varchar2(4000) := '{"prompt": "'||in_parm1||'", "model": "text-davinci-003","temperature":0.7,"max_tokens":2000}'; 
 
l_json_table clob; 
 
l_status_code number; 
 
begin 
apex_web_service.g_request_headers.delete(); -- clear the header 
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 '||g_api_secret; 
l_result := apex_web_service.make_rest_request( 
p_url => g_gpt_api_url, 
p_http_method => 'POST', 
p_body=>l_request_text 
); 
--Get the status code from the response 
l_status_code := apex_web_service.g_status_code; 
--Raise an exception if the status code is not 200 
IF l_status_code != 200 THEN 
raise_application_error(-20000, 'La solicitud de API falló con el código de estado: ' || l_status_code); 
END IF; 
for i in (select * from json_table(l_result, '$.choices[*]' 
columns ( 
value varchar2(4000) path '$.text' 
) 
)) 
loop 
out_plan:=out_plan||i.value; 
end loop; 
end get_completion; 
 
end chatgpt_pkg;

En la demo también he creado una página para obtener un plan de ejercicios 😊

¡Te invito a visitar la app y juegues con ella!

App Mi Plan

Usuario: demogpt

contraseña: demogpt2023

¡Si tienes comentarios o aprendes algo nuevo sobre este nuevo mundo del chatGPT y Oracle APEX por favor escribe en los comentarios!

¡Hay mucho por aprender todavía, pero esto es un gran inicio y agradezco a todas las personas que siempre están ayudando y compartiendo lo que aprenden con la comunidad! ¡En este caso va mi agradecimiento especial a Michelle por este gran aporte!

See you next time!