If you remember the previous post, I showed how moving some logic from an anonymous block into a scalar function caused the program to start reporting an SQL0577N, saying that the underlying procedure was trying to modify SQL data even though it was doing no such thing. The clue to the cause of the problem is in the title and the first paragraph of the previous post: defaults.
The default behavior for a stored procedure when you don’t specify whether it MODIFIES SQL DATA or READS SQL DATA is to act as if you had specified MODIFIES SQL DATA. The weird part is the confusing message: SQL0577N User defined routine “KMCDONAL.EQ” (specific name “SQL100516224914100”) attempted to modify data but was not defined as MODIFIES SQL DATA, especially since the problem is not that it modifies without saying “MODIFIES”, but that its default says “MODIFIES” and a procedure declared as such cannot be used in the context in which I’m attempting to use it (i.e. inside a scalar function).
The solution is to explicitly state CONTAINS SQL as I do on line 5 below (specifying READS SQL DATA will also work, but I don’t read SQL in the EQ procedure either, so I may as well use CONTAINS SQL):
<!--start_raw-->SET SERVEROUTPUT ON@
CREATE OR REPLACE TYPE TEST_T AS ROW(ONE INTEGER)@
CREATE OR REPLACE PROCEDURE EQ(IN LEFT TEST_T, IN RIGHT TEST_T, OUT RESULT INTEGER)
CONTAINS SQL
BEGIN
IF (LEFT.ONE = RIGHT.ONE) THEN
SET RESULT = 1;
ELSE
SET RESULT = 0;
END IF;
END@
CREATE OR REPLACE FUNCTION EQ_FN(LEFT TEST_T, RIGHT TEST_T)
RETURNS INTEGER
BEGIN
DECLARE RESULT INTEGER;
PREPARE S1 FROM 'CALL EQ(?, ?, ?)';
EXECUTE S1 INTO RESULT USING LEFT, RIGHT;
RETURN RESULT;
END@
BEGIN
DECLARE T1 TEST_T;
DECLARE T2 TEST_T;
DECLARE RESULT INTEGER;
SET T1.ONE = 1;
SET T2.ONE = 1;
SET RESULT = EQ_FN(T1, T2);
CALL DBMS_OUTPUT.PUT_LINE('Result = ' || RESULT);
END@<!--end_raw-->
This succeeds, resulting in the following output:
<!--start_raw-->DB20000I The SET SERVEROUTPUT command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. Result = 1<!--end_raw-->