HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How to use simple output parameters?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
simpleoutputhowuseparameters

Problem

I'm new to Oracle, so I apologize for such a simple question, but I've already wasted several hours on it.

I have the following stored procedure:

create or replace procedure MyTestProc (outparam OUT VARCHAR2) AS
begin
   select 'test' into outparam from dual;
end;


How do I call this stored procedure, and insert the value of outparam into a variable? I'd like to be able to do this so I can then use the result for other things, e.g., printing it to output.

Using functions, I was able to do this as easy as:

variable tmpString VARCHAR2(100);
call MyTestFunc('test') INTO :tmpString;
print tmpString;


How do I do this with stored procedures?

Solution

If you are still using SQL*Plus variables

variable tmpString VARCHAR2(100);
begin
  myTestProc( :tmpString );
end;
/
print tmpString;


If you want to do it in PL/SQL

SET SERVEROUTPUT ON;
DECLARE
  tmpString VARCHAR2(100);
BEGIN
  myTestProc( tmpString );
  dbms_output.put_line( tmpString );
END;

Code Snippets

variable tmpString VARCHAR2(100);
begin
  myTestProc( :tmpString );
end;
/
print tmpString;
SET SERVEROUTPUT ON;
DECLARE
  tmpString VARCHAR2(100);
BEGIN
  myTestProc( tmpString );
  dbms_output.put_line( tmpString );
END;

Context

StackExchange Database Administrators Q#20320, answer score: 6

Revisions (0)

No revisions yet.