A procedure to allow reusing ChatGPT prompts and responses from a local table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment