To get a simple procedure involving MERGE statement to work.

Summary: Shibukumar Nattuvetty, identified as Shibukumar Nattuvetty, is experiencing issues with a database procedure involving a MERGE statement. While able to test the code individually and compile the procedure successfully, they encounter errors during execution. The problem arises specifically at the point where the procedure attempts to retrieve RESULT_SCAN values into variables. They have shared the code snippet for further discussion and troubleshooting.

AI Summary

I am able to test this code individually in worksheet and I am able to compile the procedure also, but Im not able to run it without errors. Its failing at the statement where it gets the RESULT_SCAN values to variables.

CREATE OR REPLACE PROCEDURE merge_source_to_target()

RETURNS VARCHAR

LANGUAGE SQL

EXECUTE AS CALLER -- Enables access to session variables

DECLARE

v_merge_query_id VARCHAR;

v_inserts INTEGER := 0;

v_updates INTEGER := 0;

BEGIN

-- Perform the MERGE

MERGE INTO target_ns AS t

USING source_ns AS s

ON t.ID = s.ID

WHEN MATCHED AND t.value != s.value THEN

UPDATE SET t.value = s.value

WHEN NOT MATCHED THEN

INSERT (ID, value) VALUES (s.ID, s.value);

-- Capture MERGE query ID AFTER the MERGE statement

LET v_merge_query_id := LAST_QUERY_ID();

-- Get counts using RESULT_SCAN

LET v_updates := (SELECT rows_updated FROM TABLE(RESULT_SCAN(v_merge_query_id)));

LET v_inserts := (SELECT rows_inserted FROM TABLE(RESULT_SCAN(v_merge_query_id)));

-- Return the result

RETURN 'Merge completed: ' || v_updates || ' updates, ' || v_inserts || ' inserts.';

EXCEPTION

WHEN OTHER THEN

RETURN 'Error: ' || SQLERRM;

END;

$$;

0 comments