61 lines
2.5 KiB
SQL
61 lines
2.5 KiB
SQL
-- Read-only latency and JSON-shape comparison for the game-mention extraction
|
|
-- stage. Korean input is reconstructed from UTF-8 base64 for SQLcl safety.
|
|
|
|
set serveroutput on size unlimited
|
|
|
|
DECLARE
|
|
v_question CLOB := utl_i18n.raw_to_char(
|
|
utl_encode.base64_decode(utl_raw.cast_to_raw(
|
|
'66Gc65Oc64KY7J247J20656RIOy5tOygnOuCmOydmCAyMDI264WEIDfsm5QgMTXsnbwgQVXrpbwg6rCB6rCBIOyVjOugpOykmC4='
|
|
)),
|
|
'AL32UTF8'
|
|
);
|
|
v_prompt CLOB;
|
|
v_result CLOB;
|
|
v_json JSON_OBJECT_T;
|
|
v_started PLS_INTEGER;
|
|
v_elapsed_seconds NUMBER;
|
|
|
|
PROCEDURE run_profile(p_profile_name IN VARCHAR2) IS
|
|
BEGIN
|
|
v_started := DBMS_UTILITY.GET_TIME;
|
|
v_result := DBMS_CLOUD_AI.GENERATE(
|
|
prompt => v_prompt,
|
|
profile_name => p_profile_name,
|
|
action => 'chat'
|
|
);
|
|
v_elapsed_seconds := (DBMS_UTILITY.GET_TIME - v_started) / 100;
|
|
v_json := JSON_OBJECT_T.parse(v_result);
|
|
DBMS_OUTPUT.PUT_LINE(
|
|
p_profile_name
|
|
|| '|elapsed_seconds=' || TO_CHAR(v_elapsed_seconds, 'FM9990D00')
|
|
|| '|scope_hint=' || NVL(v_json.get_string('scope_hint'), 'NULL')
|
|
|| '|mention_count=' || v_json.get_array('game_mentions').get_size
|
|
);
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
DBMS_OUTPUT.PUT_LINE(
|
|
p_profile_name || '|elapsed_seconds='
|
|
|| TO_CHAR(v_elapsed_seconds, 'FM9990D00')
|
|
|| '|ERROR|' || SQLCODE || '|' || SUBSTR(SQLERRM, 1, 300)
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE(
|
|
p_profile_name || '|raw_response=' || DBMS_LOB.SUBSTR(v_result, 1000, 1)
|
|
);
|
|
END;
|
|
BEGIN
|
|
v_prompt := 'Extract only game-name mentions from the user question. '
|
|
|| 'Metrics, acronyms, dates, filters, and database object or column names are not game names unless they are themselves an explicit game title. '
|
|
|| 'When a title-like noun directly qualifies a game data request such as user master, character, sales, AU, NRU, server, or game log, preserve that noun as a game-name mention even when it is not in a catalog. '
|
|
|| 'Do not discard an unknown title merely because it cannot be resolved. General scope words such as common, overall, all, total, or every are not game-name mentions unless they are part of an explicit title. '
|
|
|| 'Return exactly one JSON object with keys game_mentions (array of strings) '
|
|
|| 'and scope_hint (GLOBAL, SINGLE_GAME, MULTI_GAME, ALL_GAMES, UNKNOWN). '
|
|
|| 'Do not resolve names to IDs and do not generate SQL. '
|
|
|| 'Return raw JSON only: no prose, no Markdown, and no code fence. Question: '
|
|
|| v_question;
|
|
|
|
run_profile('SGMP_POC_OCI_GPT54MINI');
|
|
run_profile('SGMP_POC_OCI_LLAMA4SCOUT');
|
|
END;
|
|
/
|