Open AI
Package provides implementation of Open AI API
example
sql
DECLARE
c_api_key VARCHAR2(2000 CHAR) := '****************';
r_message CLOB;
r_error VARCHAR2(2000 CHAR);
BEGIN
DBMS_OUTPUT.PUT_LINE('Testing OpenAI API');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('RESPONSES');
pck_api_openai.responses(
p_api_key => c_api_key,
p_model => 'gpt-5',
p_input => 'Please tell me a joke about databases.',
r_output => r_message,
r_error => r_error
);
IF r_error IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || r_error);
RETURN;
END IF;
DBMS_OUTPUT.PUT_LINE(r_message);
DBMS_OUTPUT.PUT_LINE('');
END;
/specification
sql
create or replace
PACKAGE ODBVUE.pck_api_openai AS -- Package provides implementation of Open AI API PROCEDURE responses (
p_api_key VARCHAR2, -- OpenAI API Key
p_model VARCHAR2, -- Model (tts-1, tts-1-hd)
p_input VARCHAR2, -- Text prompt
p_options CLOB DEFAULT NULL, -- Additional options in JSON format
r_output OUT CLOB, -- Response output
r_error OUT VARCHAR2 -- Error message
);
PROCEDURE speech ( -- Procedure generates speech from text, https://platform.openai.com/docs/api-reference/audio
p_api_key VARCHAR2, -- OpenAI API Key
p_model VARCHAR2, -- Model (tts-1, tts-1-hd)
p_input VARCHAR2, -- Text
p_voice VARCHAR2, -- Voice (alloy, echo, fable, onyx, nova, shimmer)
p_response_format VARCHAR2, -- File format (mp3, opus, aac, flac)
p_speed FLOAT, -- Speed (0.25..4)
r_speech OUT BLOB, -- Audio file
r_error OUT VARCHAR2 -- Error message
);
PROCEDURE speech ( -- Procedure generates speech from text, https://platform.openai.com/docs/api-reference/audio/createSpeech
p_api_key VARCHAR2, -- OpenAI API Key
p_input VARCHAR2, -- Text
r_speech OUT BLOB, -- Audio file
r_error OUT VARCHAR2 -- Error message
);
PROCEDURE transcript ( -- Procedure transctipts audio file, https://platform.openai.com/docs/api-reference/audio/createTranscription
p_api_key VARCHAR2, -- OpenAI API Key
p_file BLOB, -- File
p_filename VARCHAR2, -- Filename
p_model VARCHAR2, -- Model (whisper-1)
p_language VARCHAR2, -- Language (ISO-639-1)
p_prompt VARCHAR2, -- Prompt
p_response_format VARCHAR2, -- Response format (json, text, srt, verbose_json, vtt)
p_temperature VARCHAR2, -- Temperature (0..1)
r_transcript OUT VARCHAR2, -- Transcript
r_error OUT VARCHAR2 -- Error message
);
PROCEDURE translations ( -- Procedure translates audio file, https://platform.openai.com/docs/api-reference/audio/createTranslations
p_api_key VARCHAR2, -- OpenAI API Key
p_file BLOB, -- File
p_filename VARCHAR2, -- Filename
p_model VARCHAR2, -- Model (whisper-1)
p_language VARCHAR2, -- Language (ISO-639-1)
p_prompt VARCHAR2, -- Prompt
p_response_format VARCHAR2, -- Response format (json, text, srt, verbose_json, vtt)
p_temperature VARCHAR2, -- Temperature (0..1)
r_transcript OUT VARCHAR2, -- Transcript
r_error OUT VARCHAR2 -- Error message
);
PROCEDURE completion ( -- Procedure serves dialog with Open AI, https://platform.openai.com/docs/api-reference/chat
p_api_key VARCHAR2, -- OpenAI API Key
p_model VARCHAR2, -- Model (gpt-4 and dated model releases, gpt-4-1106-preview, gpt-4-vision-preview, gpt-4-32k and dated model releases, gpt-3.5-turbo and dated model releases, gpt-3.5-turbo-16k and dated model releases, fine-tuned versions of gpt-3.5-turbo)
p_prompt VARCHAR2, -- Prompt
p_message CLOB, -- Message
r_message OUT CLOB, -- Message
r_error OUT VARCHAR2 -- Error message
);
PROCEDURE moderations ( -- Procedure provides moderation, https://platform.openai.com/docs/api-reference/moderations
p_api_key VARCHAR2, -- OpenAI API Key
p_model VARCHAR2, -- Model (text-moderation-stable, text-moderation-latest)
p_prompt VARCHAR2, -- Prompt
r_moderations OUT CLOB, -- Moderation results
r_error OUT VARCHAR2 -- Error message
);
PROCEDURE vision ( -- Procedure provides image analysis capabilities, https://platform.openai.com/docs/guides/vision
p_api_key VARCHAR2, -- OpenAI API Key
p_model VARCHAR2, -- Model (gpt-4-vision-preview)
p_prompt VARCHAR2, -- Prompt
p_image CLOB, -- image in base64 format
r_message OUT CLOB, -- Vision response
r_error OUT VARCHAR2 -- Error message
);
PROCEDURE image ( -- Procedure generates images, https://platform.openai.com/docs/api-reference/images
p_api_key VARCHAR2, -- OpenAI API Key
p_model VARCHAR2, -- Model (dall-e-2, dall-e-3, or gpt-image-1)
p_prompt VARCHAR2, -- Prompt
p_n NUMBER, -- Number of images (1..10)
p_size VARCHAR2, -- Image size ('1024x1024', '1024x1792', or '1792x1024')
r_error OUT VARCHAR2, -- Error message
r_image OUT CLOB -- Image in base64 format
);
END;
/
-- sqlcl_snapshot {"hash":"f278095da11c44111459178a77147ebd8abafb76","type":"PACKAGE_SPEC","name":"PCK_API_OPENAI","schemaName":"ODBVUE","sxml":""}implementation
sql
CREATE OR REPLACE PACKAGE BODY odbvue.pck_api_openai AS -- PRIVATE
-- PRIVATE
FUNCTION j (
p_string CLOB
) RETURN CLOB AS
l_clob CLOB;
BEGIN
IF p_string IS NULL THEN
RETURN NULL;
END IF;
-- remove CR/LF and collapse double spaces
l_clob := replace(
replace(
replace(p_string,
chr(10),
' '),
chr(13),
' '
),
' ',
' '
);
RETURN l_clob;
END j;
FUNCTION input (
p_model VARCHAR2,
p_input VARCHAR2,
p_options CLOB
) RETURN CLOB AS
v_payload json_object_t;
BEGIN
-- start from options if provided, otherwise empty object
IF p_options IS NOT NULL THEN
v_payload := json_object_t.parse(p_options);
ELSE
v_payload := json_object_t();
END IF;
v_payload.put('model', p_model);
v_payload.put('input', p_input);
RETURN v_payload.to_clob();
END input;
-- PUBLIC
PROCEDURE responses (
p_api_key VARCHAR2,
p_model VARCHAR2,
p_input VARCHAR2,
p_options CLOB DEFAULT NULL,
r_output OUT CLOB,
r_error OUT VARCHAR2
) AS
v_req utl_http.req;
v_payload CLOB;
v_messages CLOB;
BEGIN
r_error := NULL;
r_output := NULL;
v_payload := input(p_model, p_input, p_options);
pck_api_http.request(v_req, 'POST', 'https://api.openai.com/v1/responses');
pck_api_http.request_auth_token(v_req, p_api_key);
pck_api_http.request_content_type(v_req, 'application/json');
pck_api_http.request_charset(v_req, 'UTF-8');
pck_api_http.request_json(v_req, v_payload);
pck_api_http.response_text(v_req, v_messages);
r_error := JSON_VALUE(v_messages, '$.error.message');
IF r_error IS NOT NULL THEN
RETURN;
END IF;
SELECT
JSON_VALUE(v_messages, '$.output[1].content[0].text')
INTO r_output
FROM
dual;
END responses;
PROCEDURE speech (
p_api_key VARCHAR2,
p_model VARCHAR2,
p_input VARCHAR2,
p_voice VARCHAR2,
p_response_format VARCHAR2,
p_speed FLOAT,
r_speech OUT BLOB,
r_error OUT VARCHAR2
) AS
v_payload CLOB;
v_req utl_http.req;
v_blob BLOB;
v_clob CLOB;
BEGIN
r_error := NULL;
v_payload := '{'
|| '"model": "'
|| p_model
|| '"'
|| ', "input": "'
|| j(p_input)
|| '"'
|| ', "voice": "'
|| p_voice
|| '"'
|| ', "response_format": "'
|| p_response_format
|| '"'
|| ', "speed": "'
|| p_speed
|| '"'
|| '}';
pck_api_http.request(v_req, 'POST', 'https://api.openai.com/v1/audio/speech');
pck_api_http.request_auth_token(v_req, p_api_key);
pck_api_http.request_content_type(v_req, 'application/json');
pck_api_http.request_charset(v_req, 'UTF-8');
pck_api_http.request_json(v_req, v_payload);
pck_api_http.response_binary(v_req, v_blob);
v_clob := pck_api_lob.blob_to_clob(v_blob);
r_error := JSON_VALUE(v_clob, '$.error.message');
IF r_error IS NOT NULL THEN
RETURN;
END IF;
r_speech := v_blob;
END;
PROCEDURE speech (
p_api_key VARCHAR2,
p_input VARCHAR2,
r_speech OUT BLOB,
r_error OUT VARCHAR2
) AS
BEGIN
speech(p_api_key, 'tts-1', -- model
p_input, 'alloy', -- voice
'mp3', --format
1, -- speed
r_speech, r_error);
END;
PROCEDURE transcript (
p_api_key VARCHAR2,
p_file BLOB,
p_filename VARCHAR2,
p_model VARCHAR2,
p_language VARCHAR2,
p_prompt VARCHAR2,
p_response_format VARCHAR2,
p_temperature VARCHAR2,
r_transcript OUT VARCHAR2,
r_error OUT VARCHAR2
) AS
v_req utl_http.req;
v_response VARCHAR2(32767);
BEGIN
r_error := NULL;
pck_api_http.request(v_req, 'POST', 'https://api.openai.com/v1/audio/transcriptions');
pck_api_http.request_auth_token(v_req, p_api_key);
pck_api_http.request_multipart_start(v_req);
pck_api_http.request_multipart_varchar2(v_req, 'model', p_model);
pck_api_http.request_multipart_varchar2(v_req, 'language', p_language);
pck_api_http.request_multipart_varchar2(v_req, 'prompt', p_prompt);
pck_api_http.request_multipart_varchar2(v_req, 'response_format', p_response_format);
pck_api_http.request_multipart_varchar2(v_req, 'temperature', p_temperature);
pck_api_http.request_multipart_blob(v_req, 'file', p_filename, p_file);
pck_api_http.request_multipart_end(v_req);
pck_api_http.response_text(v_req, v_response);
r_error := JSON_VALUE(v_response, '$.error.message');
IF r_error IS NOT NULL THEN
RETURN;
END IF;
r_transcript := v_response;
END;
PROCEDURE translations (
p_api_key VARCHAR2,
p_file BLOB,
p_filename VARCHAR2,
p_model VARCHAR2,
p_language VARCHAR2,
p_prompt VARCHAR2,
p_response_format VARCHAR2,
p_temperature VARCHAR2,
r_transcript OUT VARCHAR2,
r_error OUT VARCHAR2
) AS
v_req utl_http.req;
v_response VARCHAR2(32767);
BEGIN
r_error := NULL;
pck_api_http.request(v_req, 'POST', 'https://api.openai.com/v1/audio/translations');
pck_api_http.request_auth_token(v_req, p_api_key);
pck_api_http.request_multipart_start(v_req);
pck_api_http.request_multipart_varchar2(v_req, 'model', p_model);
pck_api_http.request_multipart_varchar2(v_req, 'language', p_language);
pck_api_http.request_multipart_varchar2(v_req, 'prompt', p_prompt);
pck_api_http.request_multipart_varchar2(v_req, 'response_format', p_response_format);
pck_api_http.request_multipart_varchar2(v_req, 'temperature', p_temperature);
pck_api_http.request_multipart_blob(v_req, 'file', p_filename, p_file);
pck_api_http.request_multipart_end(v_req);
pck_api_http.response_text(v_req, v_response);
r_error := JSON_VALUE(v_response, '$.error.message');
IF r_error IS NOT NULL THEN
RETURN;
END IF;
r_transcript := v_response;
END;
PROCEDURE completion (
p_api_key VARCHAR2,
p_model VARCHAR2,
p_prompt VARCHAR2,
p_message CLOB,
r_message OUT CLOB,
r_error OUT VARCHAR2
) AS
v_payload CLOB;
v_messages CLOB;
v_req utl_http.req;
BEGIN
r_error := NULL;
v_payload := '{
"model": "'
|| p_model
|| '",
"messages": [
{"role": "system","content": "'
|| j(p_prompt)
|| '"},
{"role": "user","content": "'
|| j(p_message)
|| '"}
]
}';
pck_api_http.request(v_req, 'POST', 'https://api.openai.com/v1/chat/completions');
pck_api_http.request_auth_token(v_req, p_api_key);
pck_api_http.request_content_type(v_req, 'application/json');
pck_api_http.request_charset(v_req, 'UTF-8');
pck_api_http.request_json(v_req, v_payload);
pck_api_http.response_text(v_req, v_messages);
r_error := JSON_VALUE(v_messages, '$.error.message');
IF r_error IS NOT NULL THEN
RETURN;
END IF;
SELECT
JSON_VALUE(v_messages, '$.choices[0].message.content')
INTO r_message
FROM
dual;
END;
PROCEDURE moderations (
p_api_key VARCHAR2,
p_model VARCHAR2,
p_prompt VARCHAR2,
r_moderations OUT CLOB,
r_error OUT VARCHAR2
) AS
v_req utl_http.req;
v_payload CLOB;
v_response CLOB;
BEGIN
r_error := NULL;
v_payload := '{"model": "'
|| p_model
|| '", "input": "'
|| j(p_prompt)
|| '"}';
pck_api_http.request(v_req, 'POST', 'https://api.openai.com/v1/moderations');
pck_api_http.request_auth_token(v_req, p_api_key);
pck_api_http.request_content_type(v_req, 'application/json');
pck_api_http.request_charset(v_req, 'UTF-8');
pck_api_http.request_json(v_req, v_payload);
pck_api_http.response_text(v_req, v_response);
r_error := JSON_VALUE(v_response, '$.error.message');
IF r_error IS NOT NULL THEN
RETURN;
END IF;
r_moderations := v_response;
END;
PROCEDURE vision (
p_api_key VARCHAR2,
p_model VARCHAR2,
p_prompt VARCHAR2,
p_image CLOB,
r_message OUT CLOB,
r_error OUT VARCHAR2
) AS
v_req utl_http.req;
v_payload CLOB;
v_response CLOB;
BEGIN
r_error := NULL;
v_payload := '{
"model": "'
|| p_model
|| '",
"input": [
{
"role": "user",
"content": [
{
"type": "input_text",
"text": "'
|| j(p_prompt)
|| '"
},
{
"type": "input_image",
"image_url": "data:image/png;base64,'
|| j(p_image)
|| '"
}
]
}
],
"max_output_tokens": 5000
}';
pck_api_http.request(v_req, 'POST', 'https://api.openai.com/v1/responses');
pck_api_http.request_auth_token(v_req, p_api_key);
pck_api_http.request_content_type(v_req, 'application/json');
pck_api_http.request_charset(v_req, 'UTF-8');
pck_api_http.request_json(v_req, v_payload);
pck_api_http.response_text(v_req, v_response);
r_error := JSON_VALUE(v_response, '$.error.message');
IF r_error IS NOT NULL THEN
RETURN;
END IF;
r_message := JSON_VALUE(v_response, '$.output[1].content[0].text' RETURNING CLOB);
END;
PROCEDURE image (
p_api_key VARCHAR2,
p_model VARCHAR2,
p_prompt VARCHAR2,
p_n NUMBER,
p_size VARCHAR2,
r_error OUT VARCHAR2,
r_image OUT CLOB
) AS
v_url VARCHAR2(4000 CHAR);
v_payload CLOB;
v_req utl_http.req;
v_blob BLOB;
v_clob CLOB;
j_obj json_object_t;
j_arr json_array_t;
j_val json_object_t;
BEGIN
r_error := NULL;
v_url := 'https://api.openai.com/v1/images/generations';
v_payload := '{
"model": "'
|| p_model
|| '",
"prompt": "'
|| j(p_prompt)
|| '",
"n": '
|| p_n
|| ',
"size": "'
|| p_size
|| '"
}';
pck_api_http.request(v_req, 'POST', v_url);
pck_api_http.request_auth_token(v_req, p_api_key);
pck_api_http.request_content_type(v_req, 'application/json');
pck_api_http.request_charset(v_req, 'UTF-8');
pck_api_http.request_json(v_req, v_payload);
pck_api_http.response_binary(v_req, v_blob);
v_clob := pck_api_lob.blob_to_clob(v_blob);
r_error := JSON_VALUE(v_clob, '$.error.message');
IF r_error IS NOT NULL THEN
RETURN;
END IF;
j_obj := json_object_t.parse(v_clob);
j_arr := j_obj.get_array('data');
j_val := treat(j_arr.get(0) AS json_object_t);
r_image := j_val.get_clob('b64_json');
END;
END;
/
-- sqlcl_snapshot {"hash":"a72dff1d677f728ade8ec7b71ad9d07644b5c72b","type":"PACKAGE_BODY","name":"PCK_API_OPENAI","schemaName":"ODBVUE","sxml":""}COMPLETION
Procedure serves dialog with Open AI, https://platform.openai.com/docs/api-reference/chat
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_API_KEY | IN | VARCHAR2 | OpenAI API Key | |
| P_MODEL | IN | VARCHAR2 | Model (gpt-4 and dated model releases, gpt-4-1106-preview, gpt-4-vision-preview, gpt-4-32k and dated model releases, gpt-3.5-turbo and dated model releases, gpt-3.5-turbo-16k and dated model releases, fine-tuned versions of gpt-3.5-turbo) | |
| P_PROMPT | IN | VARCHAR2 | Prompt | |
| P_MESSAGE | IN | CLOB | Message | |
| R_MESSAGE | OUT | CLOB | Message | |
| R_ERROR | OUT | VARCHAR2 | Error message |
IMAGE
Procedure provides image analysis capabilities, https://platform.openai.com/docs/guides/vision
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_API_KEY | IN | VARCHAR2 | OpenAI API Key | |
| P_MODEL | IN | VARCHAR2 | Model (gpt-4 and dated model releases, gpt-4-1106-preview, gpt-4-vision-preview, gpt-4-32k and dated model releases, gpt-3.5-turbo and dated model releases, gpt-3.5-turbo-16k and dated model releases, fine-tuned versions of gpt-3.5-turbo) | |
| P_PROMPT | IN | VARCHAR2 | Prompt | |
| P_N | IN | NUMBER | Number of images (1..10) | |
| P_SIZE | IN | VARCHAR2 | Image size ('1024x1024', '1024x1792', or '1792x1024') | |
| R_ERROR | OUT | VARCHAR2 | Error message | |
| R_IMAGE | OUT | CLOB | Image in base64 format |
MODERATIONS
Procedure provides moderation, https://platform.openai.com/docs/api-reference/moderations
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_API_KEY | IN | VARCHAR2 | OpenAI API Key | |
| P_MODEL | IN | VARCHAR2 | Model (text-moderation-stable, text-moderation-latest) | |
| P_PROMPT | IN | VARCHAR2 | Prompt | |
| R_MODERATIONS | OUT | CLOB | Moderation results | |
| R_ERROR | OUT | VARCHAR2 | Error message |
RESPONSES
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_API_KEY | IN | VARCHAR2 | OpenAI API Key | |
| P_MODEL | IN | VARCHAR2 | Model (tts-1, tts-1-hd) | |
| P_INPUT | IN | VARCHAR2 | Text prompt | |
| P_OPTIONS | IN | CLOB | NULL | Additional options in JSON format |
| R_OUTPUT | OUT | CLOB | Response output | |
| R_ERROR | OUT | VARCHAR2 | Error message |
SPEECH
Procedure generates speech from text, https://platform.openai.com/docs/api-reference/audio
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_API_KEY | IN | VARCHAR2 | OpenAI API Key | |
| P_MODEL | IN | VARCHAR2 | Model (tts-1, tts-1-hd) | |
| P_INPUT | IN | VARCHAR2 | Text | |
| P_VOICE | IN | VARCHAR2 | Voice (alloy, echo, fable, onyx, nova, shimmer) | |
| P_RESPONSE_FORMAT | IN | VARCHAR2 | File format (mp3, opus, aac, flac) | |
| P_SPEED | IN | FLOAT | Speed (0.25..4) | |
| R_SPEECH | OUT | BLOB | Audio file | |
| R_ERROR | OUT | VARCHAR2 | Error message |
SPEECH
Procedure generates speech from text, https://platform.openai.com/docs/api-reference/audio
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_API_KEY | IN | VARCHAR2 | OpenAI API Key | |
| P_INPUT | IN | VARCHAR2 | Text | |
| R_SPEECH | OUT | BLOB | Audio file | |
| R_ERROR | OUT | VARCHAR2 | Error message |
TRANSCRIPT
Procedure transctipts audio file, https://platform.openai.com/docs/api-reference/audio/createTranscription
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_API_KEY | IN | VARCHAR2 | OpenAI API Key | |
| P_FILE | IN | BLOB | File | |
| P_FILENAME | IN | VARCHAR2 | Filename | |
| P_MODEL | IN | VARCHAR2 | Model (whisper-1) | |
| P_LANGUAGE | IN | VARCHAR2 | Language (ISO-639-1) | |
| P_PROMPT | IN | VARCHAR2 | Prompt | |
| P_RESPONSE_FORMAT | IN | VARCHAR2 | Response format (json, text, srt, verbose_json, vtt) | |
| P_TEMPERATURE | IN | VARCHAR2 | Temperature (0..1) | |
| R_TRANSCRIPT | OUT | VARCHAR2 | Transcript | |
| R_ERROR | OUT | VARCHAR2 | Error message |
TRANSLATIONS
Procedure translates audio file, https://platform.openai.com/docs/api-reference/audio/createTranslations
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_API_KEY | IN | VARCHAR2 | OpenAI API Key | |
| P_FILE | IN | BLOB | File | |
| P_FILENAME | IN | VARCHAR2 | Filename | |
| P_MODEL | IN | VARCHAR2 | Model (whisper-1) | |
| P_LANGUAGE | IN | VARCHAR2 | Language (ISO-639-1) | |
| P_PROMPT | IN | VARCHAR2 | Prompt | |
| P_RESPONSE_FORMAT | IN | VARCHAR2 | Response format (json, text, srt, verbose_json, vtt) | |
| P_TEMPERATURE | IN | VARCHAR2 | Temperature (0..1) | |
| R_TRANSCRIPT | OUT | VARCHAR2 | Transcript | |
| R_ERROR | OUT | VARCHAR2 | Error message |
VISION
Procedure provides image analysis capabilities, https://platform.openai.com/docs/guides/vision
| Argument name | In Out | Data type | Default value | Description |
|---|---|---|---|---|
| P_API_KEY | IN | VARCHAR2 | OpenAI API Key | |
| P_MODEL | IN | VARCHAR2 | Model (gpt-4-vision-preview) | |
| P_PROMPT | IN | VARCHAR2 | Prompt | |
| P_IMAGE | IN | CLOB | image in base64 format | |
| R_MESSAGE | OUT | CLOB | Vision response | |
| R_ERROR | OUT | VARCHAR2 | Error message |