Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

retrieve out parameter from stored procedure?

Tags:

oracle

plsql

I have created one stored procedure in oracle:

PROCEDURE string_opp(input_string IN varchar2,output_string OUT varchar2)

Now the problem is how to execute this stored procedure and retrieve the output parameter.i've followed in sql developer:

SET SERVEROUTPUT ON
DECLARE
  outputString VARCHAR;
BEGIN
  EXEC string_opp('input String',:outputString);
END;

When i tried this i didn't get anything, could someone help me?

like image 466
Thiyagu ATR Avatar asked Feb 01 '13 15:02

Thiyagu ATR


People also ask

Can stored procedure have output parameter?

The Output Parameters in Stored Procedures are used to return some value or values. A Stored Procedure can have any number of output parameters. The simple logic is this — If you want to return 1 value then use 1 output parameter, for returning 5 values use 5 output parameters, for 10 use 10, and so on.

What is output parameter in stored procedure?

Output parameter is a parameter whose value is passed out of the stored procedure/function module, back to the calling PL/SQL block. An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module.

Can stored procedure return value?

A stored procedure does not have a return value but can optionally take input, output, or input-output parameters. A stored procedure can return output through any output or input-output parameter.


2 Answers

Just a couple of issues:

SET SERVEROUTPUT ON
DECLARE
   outputString VARCHAR(20);
BEGIN
  string_opp('input String', outputString);
  dbms_output.put_line(outputString);
END;

You can use as the same variable:

SET SERVEROUTPUT ON
DECLARE
   outputString VARCHAR(20);
BEGIN
  outputString := 'input String';
  string_opp(outputString);
  dbms_output.put_line(outputString);
END;

Just define your procedure parameter as IN OUT in place of just OUT.

Check this resource:

http://psoug.org/snippet/FUNCTIONS-IN-OUT-parameter_873.htm

like image 84
gustavodidomenico Avatar answered Oct 20 '22 18:10

gustavodidomenico


Let say:

If you have Store procedure with output parameter:

Create procedure test(name out varchar2(50))
as
begin
name:='testing output parameter';
-- dbms_output.put_line('Output parameter value ' || name );
end;

Now execute the above procedure :

declare
l_name varchar2(50);
begin
test(l_name);
dbms_output.put_line( 'name = ' || l_ename );
end;
like image 32
user2001117 Avatar answered Oct 20 '22 19:10

user2001117