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?
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.
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.
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.
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
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With