A procedure to allow reusing ChatGPT prompts and responses from a local table

chatgpt_requests.sql

declare
    l_prompt            clob;
    l_response          clob;
    l_response_text     clob;
    l_prompt_tokens     number;
    l_completion_tokens number;
    l_total_tokens      number;

    chatgpt_error EXCEPTION;
    PRAGMA exception_init(chatgpt_error, -20001);
begin

    l_prompt := :P1_PROMPT;

    -- check if prompts has already been used (by comparing current prompt vs already used prompts)
    --  if so - reuse the response and save $$$ by not calling the ChatGPT API again
    begin
        select response_text 
        into l_response_text 
        from chatgpt_requests
        where dbms_lob.compare(prompt, l_prompt) = 0;
    exception
        when no_data_found then 
            l_response_text := null;
    end;

    -- if the request is a new one and is not among the previous made to ChatGPT, call the API
    if l_response_text is null then

        begin
	    -- execute a call to my custom procedure, doing a call to ChatGPT API
            chatgpt_util.ask_chatgpt (
                in_prompt    => l_prompt,
                out_response => l_response );
        exception
            when others then
                raise_application_error(-20001,'ChatGPT could not generate response. Please try again later.');
        end;

        select jt.value, to_number(jt1.prompt_tokens), to_number(jt1.completion_tokens) , to_number(jt1.total_tokens)  
        into l_response_text, l_prompt_tokens, l_completion_tokens, l_total_tokens
        from json_table (l_response, '$.choices[*]'
                            columns ( value clob path '$.text')
                        ) jt,
            json_table (l_response, '$.usage[*]'
                        columns ( prompt_tokens clob path '$.prompt_tokens',
                                  completion_tokens clob path '$.completion_tokens',
                                  total_tokens clob path '$.total_tokens' )
                    ) jt1;

        insert into chatgpt_requests (prompt, response_text, prompt_tokens, completion_tokens, total_tokens, username)
        values(l_prompt, l_response_text, l_prompt_tokens, l_completion_tokens, l_total_tokens, :app_user);   

    end if;                           

    :P1_RESPONSE_HTML := l_response_text;     

exception
    when others then raise_application_error(-20001,'Error while reading ChatGPT response. Try again later.');
end;