Oracle APEX 26.1 Agentic AI Demo Scripts & APP

[admin]

Log Tables

Code: Select all

CREATE TABLE "HR_ACTION_LOG" 
   (	"ACTION_ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"ACTION_TYPE" VARCHAR2(100) NOT NULL ENABLE, 
	"EMPLOYEE_ID" NUMBER, 
	"ACTION_DETAIL" VARCHAR2(4000), 
	"OLD_VALUE" VARCHAR2(500), 
	"NEW_VALUE" VARCHAR2(500), 
	"RECOMMENDED_BY" VARCHAR2(255), 
	"EXECUTED_BY" VARCHAR2(255), 
	"STATUS" VARCHAR2(20) DEFAULT 'RECOMMENDED' NOT NULL ENABLE, 
	"ACTION_AT" TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL ENABLE, 
	 CONSTRAINT "CHK_HR_ACTION_STATUS" CHECK (STATUS IN ('RECOMMENDED','APPROVED','REJECTED','EXECUTED')) ENABLE, 
	 PRIMARY KEY ("ACTION_ID")
  USING INDEX  ENABLE
   ) ;


CREATE TABLE "AI_TOOL_LOG" 
   (	"LOG_ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"TOOL_NAME" VARCHAR2(100) NOT NULL ENABLE, 
	"EXECUTED_BY" VARCHAR2(255), 
	"EXECUTED_AT" TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL ENABLE, 
	"PARAMETERS" VARCHAR2(4000), 
	 PRIMARY KEY ("LOG_ID")
  USING INDEX  ENABLE
   ) ;

Conscent Procedures

Code: Select all

CREATE OR REPLACE PROCEDURE app_ai_request_handler (
    p_param  IN            apex_ai.t_chat_request_handler_param,
    p_result IN OUT NOCOPY apex_ai.t_chat_request_handler_result )
AS
BEGIN
    INSERT INTO AI_TOOL_LOG (TOOL_NAME, EXECUTED_BY, PARAMETERS)
    VALUES ('REQUEST', V('APP_USER'), TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS'));
END;
/

CREATE OR REPLACE PROCEDURE app_ai_response_handler (
    p_param  IN            apex_ai.t_chat_response_handler_param,
    p_result IN OUT NOCOPY apex_ai.t_chat_response_handler_result )
AS
BEGIN
    INSERT INTO AI_TOOL_LOG (TOOL_NAME, EXECUTED_BY, PARAMETERS)
    VALUES ('RESPONSE', V('APP_USER'), TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI:SS'));
END;
/

System Prompt

Code: Select all

You are an HR Management Assistant with access to employee, salary, 
job and department data.

Always address the logged-in user by their first name from context.

When recommending salary changes always show current salary, 
recommended salary, and the reason based on job grade.

Always ask for confirmation before executing any update.

Present all data in clean formatted tables.
Use $ prefix for salary values.
Keep responses professional and concise.

Augment System Prompt

Code: Select all

DECLARE
    l_clob          CLOB;
    l_total_emp     NUMBER;
    l_total_dept    NUMBER;
    l_avg_salary    NUMBER;
    l_max_salary    NUMBER;
    l_min_salary    NUMBER;
    l_err           VARCHAR2(4000);
BEGIN
    SELECT COUNT(*)                    INTO l_total_emp  FROM OEHR_EMPLOYEES;
    SELECT COUNT(*)                    INTO l_total_dept FROM OEHR_DEPARTMENTS;
    SELECT ROUND(AVG(SALARY),2),
           MAX(SALARY),
           MIN(SALARY)
    INTO   l_avg_salary, l_max_salary, l_min_salary
    FROM   OEHR_EMPLOYEES;

    INSERT INTO AI_TOOL_LOG (TOOL_NAME, EXECUTED_BY, PARAMETERS)
    VALUES ('get_hr_context', V('APP_USER'), 'Augment System Prompt');
    COMMIT;

    l_clob :=
        '=== HR SYSTEM CONTEXT ===' || CHR(10) ||
        'Current User      : ' || NVL(V('APP_USER'), 'Guest')              || CHR(10) ||
        'Current Date      : ' || TO_CHAR(SYSDATE, 'DD-Mon-YYYY HH24:MI') || CHR(10) ||
        CHR(10) ||
        '=== WORKFORCE SUMMARY ===' || CHR(10) ||
        'Total Employees   : ' || l_total_emp                              || CHR(10) ||
        'Total Departments : ' || l_total_dept                             || CHR(10) ||
        'Average Salary    : $' || TO_CHAR(l_avg_salary, '999,999,990.00')|| CHR(10) ||
        'Highest Salary    : $' || TO_CHAR(l_max_salary, '999,999,990.00')|| CHR(10) ||
        'Lowest Salary     : $' || TO_CHAR(l_min_salary, '999,999,990.00')|| CHR(10) ||
        CHR(10) ||
        '=== INSTRUCTIONS ===' || CHR(10) ||
        'You are an HR Management Assistant.'                               || CHR(10) ||
        'Always confirm with the user before executing any salary update.'  || CHR(10) ||
        'Log every action using log_hr_action tool.'                        || CHR(10) ||
        'For all data queries use the available On Demand tools.';

    RETURN l_clob;

EXCEPTION
    WHEN OTHERS THEN
        l_err := SQLERRM;
        INSERT INTO AI_TOOL_LOG (TOOL_NAME, EXECUTED_BY, PARAMETERS)
        VALUES ('get_hr_context', V('APP_USER'), l_err);
        COMMIT;
        RETURN 'HR Context unavailable: ' || l_err;
END;

Use this tool when the user asks about department size, headcount,
department salary cost, or workforce distribution across departments.

Returns headcount, total salary cost, average salary, minimum and
maximum salary per department ordered by highest headcount first.

Code: Select all

SELECT
    D.DEPARTMENT_NAME,
    L.CITY,
    COUNT(E.EMPLOYEE_ID)                    AS HEADCOUNT,
    SUM(E.SALARY)                           AS TOTAL_SALARY_COST,
    ROUND(AVG(E.SALARY), 2)                 AS AVG_SALARY,
    MIN(E.SALARY)                           AS MIN_SALARY,
    MAX(E.SALARY)                           AS MAX_SALARY
FROM
    OEHR_DEPARTMENTS D
    JOIN OEHR_EMPLOYEES E  ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
    JOIN OEHR_LOCATIONS L  ON L.LOCATION_ID   = D.LOCATION_ID
GROUP BY
    D.DEPARTMENT_NAME, L.CITY
ORDER BY
    HEADCOUNT DESC
    

f1289.zip