executing a stored proc with output parameter

Question

I am using SQLDEVELOPER TO make a stored procedure. this is my procedure

create or replace PROCEDURE SPP
(
 inpt IN VARCHAR2,
 opt OUT VARCHAR2
)
AS
BEGIN
  SELECT U_ID 
    INTO opt 
    FROM TABLE_NAME 
   WHERE U_ID=inpt;
END;

so it should return the value in opt which i can then print or whatever. i dont know how to execute it i tried running this script

var opt VARCHAR2;

exec SPP('test_user') := opt 

but it doesnt work this is my first time doing stored procs and I'm really confused any help is appreciated

how do i make it run? i can make a stored proc run with input variables but with an output variable i mess up and I cant do it without using the SELECT (item) INTO format.

Answer 1

In order to call the procedure, you'd need to pass in a variable that the procedure can populate. Something like

DECLARE
  l_output table_name.u_id%type;
BEGIN
  spp( 'test_user', l_output );
END;

Now, that being said, declaring a stored procedure whose only purpose is to query the database and return a value is generally the wrong architectural choice. That's why functions exist.

CREATE OR REPLACE FUNCTION some_function( p_input IN table_name.u_id%type )
  RETURN table_name.u_id%type
IS
  l_uid table_name.u_id%type;
BEGIN
  SELECT u_id
    INTO l_uid
    FROM table_name
   WHERE u_id = p_input;

  RETURN l_uid;
END;

which you can then call either in a PL/SQL block

DECLARE
  l_output table_name.u_id%type;
BEGIN
  l_output := some_function( 'test_user' );
END;

or in a SQL statement

SELECT some_function( 'test_user' )
  FROM dual;