0
CREATE OR REPLACE PROCEDURE myStoredProcedure (idParam IN VARCHAR2,
                                               outputParam OUT VARCHAR2)
AS
BEGIN
    SELECT OUTPUTCOL INTO outputParam FROM MyTable WHERE ID = idParam;
END;

DECLARE
    v_OutputResults VARCHAR2(20);

BEGIN
    myStoredProcedure('123', v_OutputResults);
    SELECT v_OutputResults AS ColumnResult FROM DUAL;
END;
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
numbawan
  • 3
  • 1
  • 1
    Why do you want to select the value as a column when you already have that value in v_OutputResults. – Ankit Bajpai Jun 01 '21 at 15:58
  • Hi Ankit, would you know how could I select v_OutputResults as a column for the final result of the Oracle SQL statement that I have posted? – numbawan Jun 01 '21 at 16:01
  • You must take a look at table values functions. – Ankit Bajpai Jun 01 '21 at 16:02
  • An anonymous block can't have a naked `select` statement. You could open a cursor, potentially a cursor that is defined as a bind variable. You could `select` data into a local variable but that wouldn't make a whole lot of sense. Your caller could be a stored procedure with an `out` parameter of type `sys_refcursor`. You could define a pipelined table function instead of using an anonymous block (though that's a bit more code). It isn't clear to me whether any of these options work for you. – Justin Cave Jun 01 '21 at 16:34

2 Answers2

0

No but you can do so using a stored function.

0

If we understand your goal, you should be using a function, not a procedure:

First, we set up the example:

SQL> -- conn system/halftrack@pdb01
SQL> conn scott/tiger@pdb01
Connected.
SQL> --
SQL> CREATE TABLE my_table (
  2    user_id number not null,
  3    Name varchar2(10)
  4  )
  5  ;

Table created.

SQL> --
SQL> insert into my_table values (1,'Bob');

1 row created.

SQL> insert into my_table values (2,'Carol');

1 row created.

SQL> insert into my_table values (3,'Ted');

1 row created.

SQL> insert into my_table values (4,'Alice');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from my_table;

   USER_ID NAME
---------- ----------
         1 Bob
         2 Carol
         3 Ted
         4 Alice

4 rows selected.

Now we create the function, then use it:

SQL> --
SQL> create or replace function my_function (id_param number)
  2    return varchar2
  3  is
  4    v_name varchar2(10);
  5  begin
  6    select name
  7    into v_name
  8    from my_table
  9    where user_id = id_param
 10    ;
 11  --
 12    return v_name;
 13  end;
 14  /

Function created.

SQL> show errors
No errors.
SQL> --
SQL> select my_function(1) from dual;

MY_FUNCTION(1)
--------------------------------------------------------------------------------
Bob

1 row selected.

And clean up our example:

SQL> --
SQL> drop table my_table purge;

Table dropped.
EdStevens
  • 3,708
  • 2
  • 10
  • 18
  • In MS SQL, I can declare a variable and assign a value to it i.e. a result of a stored procedure. Then after assigning a value to it, I could select it AS alias column, and that would be the final result of the statements. This is what I am trying to achieve in Oracle SQL. – numbawan Jun 01 '21 at 21:44
  • _"In MS SQL,"_ First rule of DBA life - "No Two Products Are the Same." In oracle you do that with a _function_, not a _procedure". – EdStevens Jun 01 '21 at 23:40