Optional Parameters in Stored Procedures using OraOLEDB?

  • Thread starter Thread starter calvin ang
  • Start date Start date
C

calvin ang

Hi there,

I've been combing through the newsgroups for days now and haven't yet
found a solution for calling stored procedures with optional
parameters (i.e., using the default values) using any Oracle provider.

I'm sure it may work if I were to use a provider that supports named
parameters, but right now named parameters are not an option.

Is there any way to achieve optional parameters in Oracle through
positional passing of parameters?

Thanks in advance,
-calvin
 
calvin ang said:
Hi there,

I've been combing through the newsgroups for days now and haven't yet
found a solution for calling stored procedures with optional
parameters (i.e., using the default values) using any Oracle provider.

I'm sure it may work if I were to use a provider that supports named
parameters, but right now named parameters are not an option.

Is there any way to achieve optional parameters in Oracle through
positional passing of parameters?


Use CommandType.Text and code a PL/SQL block to invoke your procedure.

Like this

begin
MyPackage.MyProc(p_myParam => :myParam);
end;

Passing whichever parameters in you want, and binding only those you pass.

David
 
Thanks David, but is there any way to do it somehow without the use of
named parameters?
 
calvin ang said:
Thanks David, but is there any way to do it somehow without the use of
named parameters?

The trick is to use CommandType.Text and write a PL/SQL block.

Once you do that, then anything you can run in Sql*Plus, you can run from
..NET.

David
 
David and everyone,

Perhaps I wasn't being clear; I'm unable to use named parameters and I'd
like to use the standard ODBC call syntax.

Here is a rough outline of my code:

OleDbConnection odc = new OleDbConnection("Provider=OraOLEDB.Oracle;Data
Source=dsname;User ID=user;Password=pass;PLSQLRSet=1;OLEDB.NET=1");
OleDbCommand aCommand = new OleDbCommand("{call
sp_name(?,?,?,?,?,?,?)}", odc);
aCommand.CommandType = CommandType.Text;
aCommand.Parameters.Add(new OleDbParameter("?", (int)0);
aCommand.Parameters.Add(new OleDbParameter("?", 107);
aCommand.Parameters.Add(new OleDbParameter("?", 107);
aCommand.Parameters.Add(new OleDbParameter("?", "a");
aCommand.Parameters.Add(new OleDbParameter("?", "b");
aCommand.Parameters.Add(new OleDbParameter("?", "c");
aCommand.Parameters.Add(new OleDbParameter("?", "d");


The sp is defined as:
create or replace procedure sp_name
(
P_a IN INTEGER DEFAULT 0,
P_b IN INTEGER DEFAULT 107,
P_c IN INTEGER DEFAULT 107,
P_d IN VARCHAR2 DEFAULT NULL,
P_e IN VARCHAR2 DEFAULT NULL,
P_f IN VARCHAR2 DEFAULT NULL,
P_g IN VARCHAR2 DEFAULT NULL,
P_h IN OUT PK_COM_DEFS.CV_TYP )

Basically what I'd like to do is to force the value of, say, the 3rd
parameter to accept the default value of 107 if it's not specified. But
for positional binding, which OraOLEDB only supports when using the OLE
DB APIs, how would one "not specify" the value?

I've already tried passing in null and Convert.DBNull, but both seem to
end up passing in the Oracle NULL value.

Any ideas?

thanks,
-calvin
 
Calvin,

What you are trying to do will not work! using ODBC syntax is no different
to calling the stored procedure directly from TOAD or SqlPlus - if you have
sp_name(?,?,?,?,?,?,?) as the sql text, then there will be 7 bound
parameters in the call to sp_name. DEFAULT values will only be applied if
the stored procedure is called without a parameter. PL/SQL goes one step
beyond C++/C# with its default prameter handling and allows for named
parameter binding - this provides a way to call your stored procedure and
specify only an interior parameter.

In your case with position binding, if you do not want to speficy the
default value for the 3rd paramter, then you *must* use this sort of calling
:
"{call sp_name(?,?)}"
which implies the following - "do not provide any values in the procedure
call for the third parameter or any parameter to its right.." - otherwise
you must accept named binding.

regards
roy fine
 
Back
Top