Skip to content

Instantly share code, notes, and snippets.

Last active May 27, 2023 20:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save plamen9/c4495c564abff055e71419500c6671f6 to your computer and use it in GitHub Desktop.
Save plamen9/c4495c564abff055e71419500c6671f6 to your computer and use it in GitHub Desktop.
A procedure to allow reusing ChatGPT prompts and responses from a local table
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);
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
select response_text
into l_response_text
from chatgpt_requests
where, l_prompt) = 0;
when no_data_found then
l_response_text := null;
-- 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
-- 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 );
when others then
raise_application_error(-20001,'ChatGPT could not generate response. Please try again later.');
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;
when others then raise_application_error(-20001,'Error while reading ChatGPT response. Try again later.');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment