CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Bayou Bob
Bayou Bob

Reputation: 103

Oracle stored procedures ... any problems putting an OUT parameter before an IN parameter?

Should the ordering of parameter types matter when calling a stored procedure in oracle?

For example (please forgive syntax errors)

my_proc (
param_a IN NUMBER
, param_b IN STRING
, param_c OUT NUMBER
, param_d OUT STRING
) begin
param_c = param_a
param_d = param_b
end

When we do something like the above we get the values passed in back out in the OUT parameters (again please forgive the specific syntax).

However, when we move param_c over param_b we get 0.0 and NULL instead of the values passed in.

my_proc (
param_a IN NUMBER
, param_c OUT NUMBER
, param_b IN STRING
, param_d OUT STRING
) begin
param_c = param_a
param_d = param_b
end

We are testing this in PL/SQL.

Is there something we're overlooking?

Thanks BayouBob

Upvotes: 1

Views: 2369

Answers (2)

Bayou Bob
Bayou Bob

Reputation: 103

Thanks to everyone who considered this, but it turns out to be something kinda simple. It appears the way you call a stored procedure through ojdbc6 makes a big difference.

I should confess this was originally a java / oracle question we were trying to confirm with PL/SQL.

If we called the stored procedure as 'call my_proc(...)' the ordering of IN and OUT parameters appears to be important; whereas, 'begin my_proc(...) end' works just fine.

Note: this applies to building a callablestatement in Java 6 with ojdbc6.

Upvotes: 1

wweicker
wweicker

Reputation: 4963

You can define your IN/OUT parameters in whatever order you want. How are you calling this procedure? If you pass in an empty string you will get a NULL output value. For example:

declare
   param_c   NUMBER;
   param_d   VARCHAR2(100);
begin

  my_proc (
    0,
    param_c,
    '',
    param_d
  );

  dbms_output.put_line(param_c);

  dbms_output.put_line(nvl(param_d, 'null'));

end;

This should output:

0
null

Upvotes: 1

Related Questions