prompt --application/deployment/install/install_chatbot_procedures begin -- Manifest -- INSTALL: INSTALL-Chatbot Procedures -- Manifest End wwv_flow_imp.component_begin ( p_version_yyyy_mm_dd=>'2024.05.31' ,p_release=>'24.1.2' ,p_default_workspace_id=>5893639279394007 ,p_default_application_id=>20011 ,p_default_id_offset=>0 ,p_default_owner=>'DEMO' ); wwv_flow_imp_shared.create_install_script( p_id=>wwv_flow_imp.id(16013830446391244) ,p_install_id=>wwv_flow_imp.id(16009640173256284) ,p_name=>'Chatbot Procedures' ,p_sequence=>20 ,p_script_type=>'INSTALL' ,p_script_clob=>wwv_flow_string.join(wwv_flow_t_varchar2( 'create or replace procedure "SEND_MESSAGE_TO_GEN_AI" (', ' ov_retcode OUT VARCHAR2,', ' ov_errmsg OUT VARCHAR2,', ' in_chat_id IN NUMBER,', ' in_chat_model IN VARCHAR2,', ' iv_app_user IN VARCHAR2)', 'AS', ' lc_json_payload CLOB;', ' lv_method VARCHAR2(30) := ''POST'';', ' lc_req_body CLOB;', ' lv_url VARCHAR(4000) := ''http://localhost:8000/send_message_to_gen_ai/tongyi/'';', ' lc_res_body CLOB;', ' lj_object JSON_OBJECT_T;', ' lv_result VARCHAR2(4000);', ' lv_result_message VARCHAR2(20000);', ' lv_message_res CLOB;', 'BEGIN', ' ov_retcode := ''0'';', ' ov_errmsg := NULL;', '--', ' SELECT', ' JSON_OBJECT(', ' KEY ''model'' VALUE in_chat_model,', ' KEY ''messages'' VALUE(', ' JSON_ARRAYAGG(', ' JSON_OBJECT(', ' KEY', ' ''role''', ' VALUE', ' CASE', ' ccm.message_type', ' WHEN ''U'' THEN ''user''', ' WHEN ''M'' THEN ''assistant''', ' END,', ' KEY ''text'' VALUE ccm.message_text', ' )', ' ORDER BY ccm.message_id', ' )', ' )', ' )', ' INTO', ' lc_req_body', ' FROM', ' cb_chat_messages ccm', ' WHERE', ' ccm.chat_id = in_chat_id', ' ORDER BY', ' ccm.message_id;', ' ', ' -- Call Web Service', ' apex_web_service.g_request_headers(1).name := ''Content-Type'';', ' apex_web_service.g_request_headers(1).Value := ''application/json; charset=utf-8'';', '', ' lc_res_body := APEX_WEB_SERVICE.make_rest_request(', ' p_url => lv_url,', ' p_http_method => lv_method,', ' p_body => lc_req_body', ' );', ' -- Parse Service Result', ' lj_object := JSON_OBJECT_T.parse(lc_res_body);', ' lv_result := lj_object.get_string(''result'');', ' lv_result_message := lj_object.get_string(''result_message'');', ' lv_message_res := lj_object.get_string(''message_res'');', '', ' -- Write AI answer to table', ' INSERT INTO cb_chat_messages(', ' chat_id,', ' message_type,', ' message_text,', ' user_name,', ' created_by,', ' last_updated_by', ' )', ' VALUES(', ' in_chat_id, -- chat_id', ' ''M'', -- message_type, Model', ' lv_message_res, -- message_text,', ' ''Model'', -- user_name,', ' iv_app_user, -- created_by,', ' iv_app_user -- last_updated_by', ' );', '', 'EXCEPTION', ' WHEN OTHERS THEN', ' -- Exception', ' ov_retcode := ''1'';', ' ov_errmsg := SQLERRM;', '', 'END SEND_MESSAGE_TO_GEN_AI;', '/ ')) ); wwv_flow_imp_shared.create_install_object( p_id=>wwv_flow_imp.id(16013946038391261) ,p_script_id=>wwv_flow_imp.id(16013830446391244) ,p_object_owner=>'#OWNER#' ,p_object_type=>'PROCEDURE' ,p_object_name=>'SEND_MESSAGE_TO_GEN_AI' ); wwv_flow_imp.component_end; end; /