How to return output from stored procedure

I have create a PL/SQL stored procedure from which I want to return output and display it.

I want to return the output either the success or failure message into variable : MSG

How do I need to do this any idea

PL/SQL code:

CREATE OR REPLACE PROCEDURE CHECKFILED
(
    MY_NAME EMP.FIRSTNAME%TYPE,
    MY_ID   EMP.ID%TYPE

) RETURN VARCHAR2

IS 
     V_MSG VARCHAR(4000 CHAR);  

BEGIN
       
     SELECT FIRSTNAME INTO MY_NAME FROM EMP WHERE ID=MY_ID;

     IF MY_NAME IS NOT NULL THEN 
          INSERT INTO CUSTOMER VALUES (UID,FIRSTNAME);  
          V_MSG='FIELD IS NOT EMPTY';
     ELSE
          RAISE_APPLICATION_ERROR(-20000,'FIELD IS EMPTY'); 
     END IF; 
     


     EXCEPTION 
            WHEN OTHERS THEN
                V_MSG := SQLCODE || '-' || SQLERRM;  
                RETURN(V_MSG);

     RETURN(V_MSG);  

END;

Calling the stored procedure:

DECLARE 
     MSG VARCHAR2(4000 CHAR);
BEGIN  
     SELECT CHECKFILED('10A') AS MSG FROM DUAL; 
END;

CodePudding user response：

PROCEDUREs do not have any return value. If you want to return a value, it needs to be a FUNCTION. Try the code below.

CREATE OR REPLACE FUNCTION CHECKFILED (MY_NAME EMP.FIRSTNAME%TYPE, MY_ID EMP.ID%TYPE)
    RETURN VARCHAR2
IS
    V_MSG   VARCHAR (4000 CHAR);
BEGIN
    SELECT FIRSTNAME
      INTO MY_NAME
      FROM EMP
     WHERE ID = MY_ID;

    IF MY_NAME IS NOT NULL
    THEN
        INSERT INTO CUSTOMER
             VALUES (UID, FIRSTNAME);

        V_MSG := 'FIELD IS NOT EMPTY';
    ELSE
        RAISE_APPLICATION_ERROR (-20000, 'FIELD IS EMPTY');
    END IF;

    RETURN (V_MSG);
EXCEPTION
    WHEN OTHERS
    THEN
        V_MSG := SQLCODE || '-' || SQLERRM;
        RETURN (V_MSG);
END;

CodePudding user response：

If it is a procedure, it should have an OUT parameter which will then be used to return some value.

Something like this; note that it is probably useless to check whether select returned null (employees do have names; select would return no row and therefore raise no_data_found exception).

CREATE OR REPLACE PROCEDURE checkfiled (my_id  IN     emp.id%TYPE,
                                        v_msg     OUT VARCHAR2)
IS
   my_name  emp.firstname%TYPE;
BEGIN
   SELECT firstname
     INTO my_name
     FROM emp
    WHERE id = my_id;

   INSERT INTO customer
        VALUES (UID, firstname);

   v_msg := 'FIELD IS NOT EMPTY';
EXCEPTION
   WHEN OTHERS
   THEN
      v_msg := SQLCODE || '-' || SQLERRM;
END;

You'd then call it as

DECLARE
   msg  VARCHAR2 (4000 CHAR);
BEGIN
   checkfiled ('10A', msg);

   DBMS_OUTPUT.put_line ('Procedure returned ' || msg);
END;
/