I am trying to get my function to include the date and current user, but I keep getting an error that my function is in an invalid state. If I comment out or remove the two dbms_output… lines it works just fine. Any ideas of how to return that output? Using Oracle SQL Developer
CREATE OR REPLACE FUNCTION f_concatenate_strings(x VARCHAR2, y VARCHAR2) RETURN VARCHAR2 AS str1 VARCHAR2(10) := x; str2 VARCHAR2(10) := y; BEGIN RETURN str1 || str2; dbms_output.put_line('The result is ' || result); dbms_output.put_line('Date: ' || SYSDATE || ' user: ' || SYS_CONTEXT('USERENV','OS_USER')); END; / SELECT f_concatenate_strings('Crypto','Currency') FROM DUAL;
Advertisement
Answer
You have to declare the result Variable first, also enable the dbms_output on your SQL Developer.
This should work.
CREATE OR REPLACE FUNCTION f_concatenate_strings(x VARCHAR2, y VARCHAR2) RETURN VARCHAR2 AS str1 VARCHAR2(10) := x; STR2 VARCHAR2(10) := Y; result VARCHAR2(250); BEGIN result := str1 || str2; dbms_output.put_line('The result is ' || result); dbms_output.put_line('Date: ' || SYSDATE || ' user: ' || SYS_CONTEXT('USERENV','OS_USER')); RETURN result; END; / SELECT F_CONCATENATE_STRINGS('Crypto','Currency') FROM DUAL;