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.
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>