Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
   select 'test' into outparam from dual;

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?

share|improve this question
What have you tried on outputting it? How have you tried calling it already? – jcolebrand Jul 3 '12 at 15:17
Are you calling this procedure from code? – swasheck Jul 3 '12 at 15:18
I've tried Lots of things. I felt that posting what I've tried would only bring this question deeper into oblivion--it should be simple. – Jay Sullivan Jul 3 '12 at 15:18
up vote 6 down vote accepted

If you are still using SQL*Plus variables

variable tmpString VARCHAR2(100);
  myTestProc( :tmpString );
print tmpString;

If you want to do it in PL/SQL

  tmpString VARCHAR2(100);
  myTestProc( tmpString );
  dbms_output.put_line( tmpString );
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.