How to return output from stored procedure - Oracle

Question

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;

Why do you need to do this instead of using the error handling that's built into PL/SQL? No need to return a message if the procedure is successful or not; instead if an error is raised, you know it's failed, otherwise it was successful. — Boneist
– Boneist, Commented Jul 15, 2022 at 14:19
Just to comment on the code, SQLCODE || '-' || SQLERRM is redundant because sqlerrm already contains sqlcode. For example, if sqlerrm is ORA-12345 Some error message here, then sqlcode is -12345 and you'll return -12345-ORA-12345 Some error message here. Why do that? — William Robertson
– William Robertson, Commented Jul 24, 2022 at 10:24

Littlefoot · Accepted Answer · 2022-07-15 12:56:29Z

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

EJ Egyed · Accepted Answer · 2022-07-15 12:55:06Z

1

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;

answered Jul 15, 2022 at 12:55

EJ Egyed

6,1341 gold badge11 silver badges25 bronze badges

2 Comments

Littlefoot Over a year ago

That's questionable. If this is a function, it can't perform DDL as - if you call it using SELECT statement - you'll get "ORA-14551: cannot perform a DML operation inside a query" unless it is an autonomous transaction. It would work if it is used in an assignment statement, though, but - generally, DDL should be used in a procedure, not in a function.

astentx Over a year ago

As an addition: function with side effects is not what a consumer would generally expect (except, possibly, logging).

Collectives™ on Stack Overflow

How to return output from stored procedure - Oracle

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related