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
x
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;