Skip to content
Advertisement

Adding output to function PL/SQL

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;
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement