unified_logging_framework/05_procedure_template.sql at main · ollionorg/unified_logging_framework · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- =============================================================================
-- FILE: 05_procedure_template.sql
-- PURPOSE: Standard template updated for AI Context Capture.
-- =============================================================================

CREATE OR REPLACE PROCEDURE DATABASE_NAME.SCHEMA_NAME.SP_TEMPLATE_AI_READY(
    "P_PARAM1" STRING DEFAULT 'default_value',
    "P_QUERY_TAG" VARCHAR DEFAULT 'SP_TEMPLATE'
)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS '
DECLARE
    -- Execution Tracking
    v_run_id           STRING;
    v_start_time       TIMESTAMP_NTZ;

    -- AI Context Tracking (Crucial for Cortex)
    v_step_intent      STRING DEFAULT ''INITIALIZATION'';

    -- Exception Handling
    v_exception_defs   OBJECT;
    -- Declare specific exceptions (or use helper)
    v_exception_invalid_param EXCEPTION (-20003, ''Invalid parameter'');

BEGIN
    v_run_id := UUID_STRING();
    v_start_time := CURRENT_TIMESTAMP();

    -- Load hints
    CALL C360.SP_GET_EXCEPTION_DEFINITIONS() INTO :v_exception_defs;

    -- STEP 1: Validation
    v_step_intent := ''Validating input parameters for nulls'';
    IF (P_PARAM1 IS NULL) THEN
        RAISE v_exception_invalid_param;
    END IF;

    -- STEP 2: Logic
    v_step_intent := ''Inserting data into TARGET_TABLE from SOURCE_STAGING'';

    /* INSERT INTO TARGET_TABLE ...
       SELECT ...
    */

    RETURN OBJECT_CONSTRUCT(''status'', ''SUCCESS'', ''run_id'', v_run_id);

EXCEPTION
    -- Case 1: Known Exception
    WHEN v_exception_invalid_param THEN
        RETURN C360.SP_HANDLE_EXCEPTION(
            p_exception_type => ''INVALID_PARAMETER'',
            p_error_code => -20003,
            p_error_message => ''Parameter P_PARAM1 was NULL'',
            p_run_id => v_run_id,
            p_step_status => v_step_intent, -- Pass the intent
            p_start_time => v_start_time,
            p_query_tag => p_query_tag,
            p_additional_details => OBJECT_CONSTRUCT(
                ''input_param'', p_param1,
                ''resolution_hint'', v_exception_defs[''invalid_parameter''][''resolution_hint'']
            )
        );

    -- Case 2: Unexpected Error (Catch-All)
    WHEN OTHER THEN
        RETURN C360.SP_HANDLE_EXCEPTION(
            p_exception_type => ''UNEXPECTED_ERROR'',
            p_error_code => SQLCODE,
            p_error_message => SQLERRM,
            p_run_id => v_run_id,
            p_step_status => v_step_intent, -- Pass the intent
            p_start_time => v_start_time,
            p_query_tag => p_query_tag,
            p_sql_state => SQLSTATE,
            p_additional_details => OBJECT_CONSTRUCT(
                ''stack_trace_intent'', v_step_intent,
                ''variable_snapshot'', OBJECT_CONSTRUCT(''param1'', P_PARAM1)
            )
        );
END;
';