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