4

I have a procedure

create or replace PROCEDURE proc
(
  p1 IN varchar2,
  p2 IN varchar2,
  p3 OUT varchar2
) AS
BEGIN
 p3:= p1 || ' ' || p2
END proc

I call it by

Declare
  P3 varchar(50);
Begin
 proc('foo', 'bar', P3)
END;

I can print out the value of P3 using

Dbms_Output.Put_line('P3: ' || P3)

But I need to get the result as a table I can select from. Best way I can think of is

Declare
  P3 varchar(50);
Begin
 proc('foo', 'bar', P3)
END;
SELECT &&P3 from dual;

But that gives me an error

Error report -
ORA-06550: line 5, column 12:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 5, column 5:
PL/SQL: SQL statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilatiopn error.
*Action:

Is there some way of selecting the value of a parameter as a column/row value?

I need this all done inside the single sql query because I need to executed it from another server via linked server.

I don't have permission to create any other stored procedures, functions or tables on the database.

Tomas
  • 181
  • 2
  • 10

2 Answers2

5

To me, the most straightforward option is to create a function - it is supposed to return a value:

create or replace function proc (p1 IN varchar2, p2 IN varchar2)
  return varchar2
AS
BEGIN
  return p1 || ' ' || p2;
END proc;

Then you'd call it as e.g.

select proc('a', 'b') from dual;

Saying that you "don't have permission to create ... functions" - well, if you are granted to create a PROCEDURE, then you are also granted to create a FUNCTION. It is the same privilege.

If you can only use procedures that are already created, then:

SQL> create or replace PROCEDURE proc
  2  (
  3    p1 IN varchar2,
  4    p2 IN varchar2,
  5    p3 OUT varchar2
  6  ) AS
  7  BEGIN
  8    p3:= p1 || ' ' || p2;
  9  END ;
 10  /

Procedure created.

SQL> var result varchar2(20)
SQL>
SQL> exec proc('a', 'b', :result);

PL/SQL procedure successfully completed.

SQL> print :result

RESULT
--------------------------------
a b
    
SQL> select :result from dual;

:RESULT
--------------------------------
a b

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Like I said I don't have permission to create or modify any stored procedures or functions. The function is created by the owner of the server and all I've got is a log in with select permission. – Tomas Feb 10 '21 at 12:32
  • Aha; so you can only use what's already been created. OK; I added some more code into the answer, have a look. – Littlefoot Feb 10 '21 at 12:38
  • If I add ```select :result from dual;``` it asks me to enter binds for ```result``` and then just selects the value I enter instead of the value the procedure returns. – Tomas Feb 10 '21 at 12:47
  • Disable that feature in a tool you use (for this particular task). Reenable it later. – Littlefoot Feb 10 '21 at 12:48
  • Would you by any chance happen to know how to do that? I'm using oracle sql developer 20.4.0.379.2205 x64. – Tomas Feb 10 '21 at 12:54
  • In SQL Developer, select ("paint" it blue) the SELECT statement and run it **as script** (F5 keyboard shortcut). – Littlefoot Feb 10 '21 at 13:13
  • Well that works. But this gives me the result as a script output and not as a query result and I still won't be able to select it via my linked server. – Tomas Feb 10 '21 at 13:28
  • Can you switch to SQL*Plus, then? I don't know what else to do in SQL Developer as you're quite "restricted" by privileges you have. – Littlefoot Feb 10 '21 at 13:30
  • 1
    Ok in SQL*Plus your solution works perfectly. Thank you for all your help and all the time you spent on this question. I'm completely new to oracle. – Tomas Feb 10 '21 at 13:58
4

If you're on Oracle 12c onwards you can use WITH FUNCTION feature added to that version. With it you can wrap your procedure into PL/SQL block of locally defined function and use this function within SELECT. No CREATE PROCEDURE needed for this to work.

create procedure proc (
  p1 in int,
  p2 in int,
  pout out int
)
as
begin
  pout := p1 + p2;
end;
/

✓

with function f(
  p1 in int,
  p2 in int
) return int
as
  pout int;
begin
  proc(p1, p2, pout);
  return (pout);
end;

select f(1,2)
from dual

| F(1,2) |
| -----: |
|      3 |

db<>fiddle here

astentx
  • 6,393
  • 2
  • 16
  • 25