What a nice Feature..
Yesterday: I have seen this:
https://dbdev01555.wordpress.com/2023/01/10/calling-openai-api-with-pl-sql/
Now I can make that happend:
I want to explain my View-SQL Statement as Text so it is more Human readable:
The IDEA:
select chatgpt_sql_analyzer('explain ' || text)
from user_view
where view_name = 'SYNC_STATUS';
Response:
VIEW_NAME RESPONSE
--------- -------------------------------------------------------------
SYNC_STATUS \n\nThis query finds the last synchronization date and the next
synchronization date for the CHARGE_TRACKING, STATUS_TRACKING, and GET_REPORT
jobs in the user_scheduler_jobs table. \n\nThe query first finds the maximum
status date from the eniro table where the json_type is STATUS. This is
stored in the max_last_sync_dat subquery. \n\nThe next subquery, minnextdat,
finds the minimum of the next run date or the last start date from the
user_scheduler_jobs table where the next run date or last start date is
greater than the current system date and the job name is one of
CHARGE_TRACKING, STATUS_TRACKING, or GET_REPORT. \n\nThe next_sync_dat
subquery finds the next run date from the user_scheduler_jobs table that
matches the minimum found in the minnextdat subquery. \n\nThe final query
selects the last synchronization date, the next synchronization date, and the
next run date from the eniro table, the max_last_sync_dat subquery, and the
next_sync_dat subquery. If the system date is greater than the next run date,
the next synchronization date is set to the next run date plus 30 minutes.
Otherwise, the next synchronization date is set to the next run date plus 2
minutes."
First i need to convert LONG Datatype in Oracle to CLOB.. thx asktom here..
--asktom
--CONVERT LONG TO CLOB:
create or replace
function long_length_modified( p_query in varchar2,
p_owner in varchar2,
p_owner_value in varchar2,
p_name in varchar2,
p_value in varchar2 )
return clob
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val clob;
l_long_piece clob;
l_long_len number;
l_buflen number := 32760;
l_curpos number := 0;
l_return_value number;
begin
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
dbms_sql.bind_variable( l_cursor,p_owner, p_owner_value );
dbms_sql.bind_variable( l_cursor,p_name, p_value );
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor)>0)
then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
l_long_val, l_long_len );
l_curpos := l_curpos + l_long_len;
l_return_value := nvl(l_return_value,0) + l_long_len;
exit when l_long_len = 0;
l_long_piece := l_long_piece||l_long_val; -- added
end loop;
end if;
dbms_sql.close_cursor(l_cursor); -- added
return l_long_piece;
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end long_length_modified;
Then make a function to communicate with chatgpt: Thx APEX Team:
create or replace function explain_sql (v_sql in clob) return clob as
l_resp_body CLOB;
BEGIN
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
apex_web_service.g_request_headers(2).name := 'Authorization';
apex_web_service.g_request_headers(2).value := 'Bearer YOUR_API_KEY';
/* https://beta.openai.com/docs/models/gpt-3 */
l_resp_body := apex_web_service.make_rest_request(
p_url => 'https://api.openai.com/v1/engines/text-davinci-003/completions',
p_http_method => 'POST',
p_body => q'#{
"prompt": "#' || v_sql || q'#" ,
"temperature": 0.5,
"max_tokens": 500
}#'
);
return (l_resp_body);
END;
Now a view that calls the explain_sql function:
instead of 'erkläre in deutsch' you can just type 'explain ' or 'tune ' or.. :
create or replace view chatgpt_sql_analyzer as
with view_text as (
select view_name, text_length,
replace(replace(long_length_modified( 'select text
from user_views
where view_name = :x and owner = :owner',
':owner','tom',':x', view_name ),'"',''),'''','') ll
from all_views
where view_name = 'SYNC_STATUS' and owner ='TOM'),
listaggit as (select view_name,replace(replace(listagg(ll),chr(13),' '),chr(10),' ') ll
from view_text group by view_name)
select view_name,explain_sql('erkläre in deutsch ' || ll) response from listaggit
;
I know.. replace chr(13) and encoding is missing.. and some string issues.. yes.. i know..
select * from chatgpt_sql_analyzer;
VIEW_NAME RESPONSE
--------- -----------------------------------------------------------
SYNC_STATUS "text":"\n\nDiese Abfrage gibt die letzte Synchronisationszeit,
den nächsten Zeitpunkt für die nächste Synchronisation und das nächste
Synchronisationsdatum an. Es wird die maximale Status-Datum-Spalte aus der
Tabelle Eniro abgerufen, die dem Typ STATUS entspricht. Anschließend wird das
Minimum der nächsten Laufzeit oder des letzten Startdatums, das größer als
heute ist, aus der Tabelle User_Scheduler_Jobs abgerufen. Danach wird das
nächste Synchronisationsdatum aus der Tabelle User_Scheduler_Jobs abgerufen.
Zuletzt wird die letzte Synchronisationszeit, der nächste Zeitpunkt für die
nächste Synchronisation und das nächste Synchronisationsdatum
angezeigt.","index