Oracle APEX – Build Chatbot using OpenAI Assistant Version 2

Posted on by

OpenAI has recently introduced a new APIs for version 2. This new version is more robust and with better control of using different assistant for your different type of query. You can find more details about the APIs here.

Purpose of this blog post is to introduce you on how you can use OpenAI version 2 APIs in your APEX application using PL/SQL.

Following are the important steps to consider for executing an OpenAI request.

  • List All Assistant – GET – https://api.openai.com/v1/assistants
    • You can view all your existing Assistant. Assistant is primary and most important component for your query to be answered. You may also define the purpose of assistant.
  • Create new Assistant – POST – https://api.openai.com/v1/assistants
    • You may create new Assistant by providing basic information like Instructions, Name, Tools and Model.
  • Create new Thread – POST – https://api.openai.com/v1/threads
    • To create new conversation with Assistant, you need to create new thread.
  • Create new Message – POST – https://api.openai.com/v1/threads/{thread_id}/messages
    • Once Thread is created, you may add one or more messages to the thread.
  • Run Thread – POST – https://api.openai.com/v1/threads/{thread_id}/runs
    • Next, is to run the Thread. This will trigger your actual OpenAI request to answer the questions posted in previous step.
  • View Thread – GET – https://api.openai.com/v1/threads/{thread_id}/runs/{run_id}
    • You can see the status of your Run thread by querying View Thread API.
  • List Messages – GET – https://api.openai.com/v1/threads/{thread_id}/messages
    • On successfully completing the request, you should view the answers or complete thread using this API.
g_openai_host varchar2(200) := 'https://api.openai.com/v1/';
g_api_key     varchar2(200) := '{api_key_here}';
Create new Thread
  procedure createThread(
    p_thread_id out varchar2
  ) as
    l_url varchar2(200) := g_openai_host||'threads';
    l_method varchar2(10) := 'POST';
    l_response clob;
  begin
    APEX_WEB_SERVICE.CLEAR_REQUEST_HEADERS;
    
    APEX_WEB_SERVICE.SET_REQUEST_HEADERS(
      p_name_01   => 'Authorization',
      p_value_01  => 'Bearer '||g_api_key,
      p_name_02   => 'OpenAI-Beta',
      p_value_02  => 'assistants=v2',
      p_name_03   => 'Content-Type',
      p_value_03  => 'application/json'
    );

    l_response := apex_web_service.make_rest_request(
      p_url           => l_url,
      p_http_method   => l_method
    );
    
    p_thread_id := json_value(l_response, '$.id');
  end createThread;
Create New Message

  procedure createMessage(
    p_thread_id in varchar2
   ,p_role in varchar2
   ,p_message in varchar2
   ,p_message_id out varchar2
  ) as
    l_url varchar2(200) := g_openai_host||'threads/'||p_thread_id||'/messages';
    l_method varchar2(10) := 'POST';
    l_response clob;
    l_input clob;
  begin
    APEX_JSON.initialize_clob_output;
    APEX_JSON.open_object;
    APEX_JSON.write('role', p_role);
    APEX_JSON.write('content', p_message);
    APEX_JSON.close_object;
    l_input := APEX_JSON.get_clob_output;
    APEX_JSON.free_output;

    APEX_WEB_SERVICE.CLEAR_REQUEST_HEADERS;
    
    APEX_WEB_SERVICE.SET_REQUEST_HEADERS(
      p_name_01   => 'Authorization',
      p_value_01  => 'Bearer '||g_api_key,
      p_name_02   => 'OpenAI-Beta',
      p_value_02  => 'assistants=v2',
      p_name_03   => 'Content-Type',
      p_value_03  => 'application/json'
    );

    l_response := apex_web_service.make_rest_request(
      p_url           => l_url,
      p_http_method   => l_method,
      p_body          => l_input
    );
    
    p_message_id := json_value(l_response, '$.id');
  end createMessage;
Run Thread

  procedure runThread(
    p_thread_id in varchar2
   ,p_assistant_id in varchar2
   ,p_stream in boolean default false
   ,p_run_id out varchar2
  ) as
    l_url varchar2(200) := g_openai_host||'threads/'||p_thread_id||'/runs';
    l_method varchar2(10) := 'POST';
    l_response clob;
    l_input clob;
  begin
    APEX_JSON.initialize_clob_output;
    APEX_JSON.open_object;
    APEX_JSON.write('assistant_id', p_assistant_id);
    APEX_JSON.write('stream', p_stream);
    APEX_JSON.close_object;
    l_input := APEX_JSON.get_clob_output;
    APEX_JSON.free_output;

    APEX_WEB_SERVICE.CLEAR_REQUEST_HEADERS;
    
    APEX_WEB_SERVICE.SET_REQUEST_HEADERS(
      p_name_01   => 'Authorization',
      p_value_01  => 'Bearer '||g_api_key,
      p_name_02   => 'OpenAI-Beta',
      p_value_02  => 'assistants=v2',
      p_name_03   => 'Content-Type',
      p_value_03  => 'application/json'
    );

    l_response := apex_web_service.make_rest_request(
      p_url           => l_url,
      p_http_method   => l_method,
      p_body          => l_input
    );
    
    p_run_id := json_value(l_response, '$.id');
  end runThread;
View Thread

  procedure viewThread(
    p_thread_id in varchar2
   ,p_run_id in varchar2
   ,p_result out clob
  ) as
    l_url varchar2(200) := g_openai_host||'threads/'||p_thread_id||'/runs/'||p_run_id;
    l_method varchar2(10) := 'GET';
    l_response clob;
  begin
    APEX_WEB_SERVICE.CLEAR_REQUEST_HEADERS;
    
    APEX_WEB_SERVICE.SET_REQUEST_HEADERS(
      p_name_01   => 'Authorization',
      p_value_01  => 'Bearer '||g_api_key,
      p_name_02   => 'OpenAI-Beta',
      p_value_02  => 'assistants=v2',
      p_name_03   => 'Content-Type',
      p_value_03  => 'application/json'
    );

    p_result := apex_web_service.make_rest_request(
      p_url           => l_url,
      p_http_method   => l_method
    );
  end viewThread;
View Messages

  procedure viewMessages(
    p_thread_id in varchar2
   ,p_result out clob
  ) as
    l_url varchar2(200) := g_openai_host||'threads/'||p_thread_id||'/messages';
    l_method varchar2(10) := 'GET';
    l_response clob;
  begin
    APEX_WEB_SERVICE.CLEAR_REQUEST_HEADERS;
    
    APEX_WEB_SERVICE.SET_REQUEST_HEADERS(
      p_name_01   => 'Authorization',
      p_value_01  => 'Bearer '||g_api_key,
      p_name_02   => 'OpenAI-Beta',
      p_value_02  => 'assistants=v2',
      p_name_03   => 'Content-Type',
      p_value_03  => 'application/json'
    );

    p_result := apex_web_service.make_rest_request(
      p_url           => l_url,
      p_http_method   => l_method
    );
  end viewMessages;
You can use below Page Process to process your AI request and store them in a APEX Collection.

DECLARE
  P_THREAD_ID VARCHAR2(200);
  P_ROLE VARCHAR2(200);
  P_MESSAGE VARCHAR2(200);
  P_MESSAGE_ID VARCHAR2(200);
  P_RUN_ID VARCHAR2(200);
  P_ASSISTANT_ID VARCHAR2(200);
  P_STREAM BOOLEAN;
  P_RESULT CLOB;
  L_CNT NUMBER := 0;
BEGIN
  P_THREAD_ID := :P1_THREAD_ID;
  P_ROLE := 'user';
  P_MESSAGE := :P1_CONTENT;
  P_ASSISTANT_ID := :P1_ASSISTANT_ID;
  P_STREAM := false;

  IF P_THREAD_ID IS NULL THEN
      OPENAI_PKG.CREATETHREAD(
        P_THREAD_ID => P_THREAD_ID
      );
  END IF;

  IF P_MESSAGE IS NOT NULL THEN
      OPENAI_PKG.CREATEMESSAGE(
        P_THREAD_ID => P_THREAD_ID,
        P_ROLE => P_ROLE,
        P_MESSAGE => P_MESSAGE,
        P_MESSAGE_ID => P_MESSAGE_ID
      );
  END IF;

  IF P_MESSAGE_ID IS NOT NULL AND P_THREAD_ID IS NOT NULL AND P_ASSISTANT_ID IS NOT NULL THEN
      OPENAI_PKG.RUNTHREAD(
        P_THREAD_ID => P_THREAD_ID,
        P_ASSISTANT_ID => P_ASSISTANT_ID,
        P_STREAM => P_STREAM,
        P_RUN_ID => P_RUN_ID
      );
  END IF;

  <<view_thread>>

  IF L_CNT <= 20 AND P_THREAD_ID IS NOT NULL AND P_RUN_ID IS NOT NULL THEN
        OPENAI_PKG.VIEWTHREAD(
          P_THREAD_ID => P_THREAD_ID,
          P_RUN_ID => P_RUN_ID,
          P_RESULT => P_RESULT
        );

        L_CNT := L_CNT + 1;
  
        IF json_value(P_RESULT, '$.status') = 'completed' THEN
          OPENAI_PKG.VIEWMESSAGES(
            P_THREAD_ID => P_THREAD_ID,
            P_RESULT => P_RESULT
          );

          APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => 'OPENAI_MESSAGES');
          APEX_COLLECTION.ADD_MEMBER(p_collection_name => 'OPENAI_MESSAGES', p_clob001 => P_RESULT);
        ELSE
          DBMS_SESSION.SLEEP(0.5);
          GOTO view_thread;
        END IF;
  END IF;

  :P1_THREAD_ID := P_THREAD_ID;
  :P1_RUN_ID := P_RUN_ID;
  :P1_MESSAGE_ID := P_MESSAGE_ID;
END;

Use below query to show the result from APEX collection as a result in a comment template of classic report region.

select J.c_role USER_NAME
     , J.c_content COMMENT_TEXT
     , J.c_created_at COMMENT_DATE
     , null ATTRIBUTE_1
     , null ATTRIBUTE_2
     , null ATTRIBUTE_3
     , null ATTRIBUTE_4
     , null ACTIONS
     , case when J.c_role = 'user' then 't-Chat--own' end comment_modifiers
     , case when J.c_role = 'user' then 'fa fa-user' else 'fa fa-chatbot' end ICON_MODIFIER
     , null USER_ICON
  from APEX_COLLECTIONS C, json_table(C.CLOB001,'$.data[*]' COLUMNS (
    c_role varchar2(50) PATH '$.role',
    c_content clob PATH '$.content[0].text.value',
    c_created_at number PATH '$.created_at'
)) J
order by J.c_created_at asc

Hope that helps!

Regards,
Jaydip Bosamiya
jbosamiya@gmail.com

Leave a Reply

Your email address will not be published. Required fields are marked *