Problem calling Oracle stored procedure from ole db

[Custom24]

Hi
I've got an load of sp's in an Oracle package, and I want to call them from my middle tier, which is using ole db (well, .net actually, but I don't think that matters) to talk to Oracle.
I've been using this quite successfully for a while for both input and output params, of all data types. Sometimes you have to jiggle around the parameter types on the ole db side of things to get it working right, but it works for me.

However, now I'm stumped. I'm trying to call an sp I've just written whose signature looks like this (please forgive the vb style names for the params!)

procedure pProcReformInfoRecord_Test(strDestVialRack in varchar2, strOPPlateName in varchar2, strNotebook in varchar2, 
		  dblWeight in number, dblUMoles in number, intFate in integer, datAssignedReformatting in date, 
		  datCompletedReformatting in date, datCompletedChemistQC in date, strPMCTS_Info in varchar2,
		  strUserName in varchar2, strFullPath in varchar2,
		  dat_Last_Modified in date, lngErrorCode out number, StrErrorMessage out Varchar2, strOracleErrorMessage out VarChar2)

but the app is coming back with

"ORA-06550: line 1, column 240:
PLS-00103: Encountered the symbol ">" when expecting one of the following:

   . ( ) , * @ % & = - + < / > at in mod not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like
   between is null is not || is dangling
ORA-06550: line 1, column 429:
PLS-00103: Encountered the symbol &quot;END&quot; 
ORA-06550: line 1, column 240:
PLS-00103: Encountered the symbol &quot;>&quot; when expecting one of the following:

   . ( ) , * @ % & = - + < / > at in mod not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like
   between is null is not || is dangling
ORA-06550: line 1, column 429:
PLS-00103: Encountered the symbol &quot;END&quot; &quot;

I think I know why - for this procedure, the last call in the V$sqlarea looks like this

BEGIN
   pmcts_app_funcs.pprocreforminforecord_test (strdestvialrack               => :v00001,
                                               stropplatename                => :v00002,
                                               strnotebook                   => :v00003,
                                               dblweight                     => :v00004,
                                               dblumoles                     => :v00005,
                                               intfate                       => :v00006,
                                               datassignedreformatting       => :v00007,
                                               datcompletedreformatting      => :v00008,
                                               datcompletedchemistqc         => :v00009,
                                               strpmcts_info                 => :v00010,
                                               strusername                   => :v00011,
                                               strfullpath                   => :v00012,
                                               dat_last_modified             => :v00013,
                                               lngerrorcode                  => :v00014,
                                               strerrormessage               => :v00015,
                                               stroracleerrormessage         => :v00016
                                              );
END;

Whereas another very similar procedure, which executes no problem, has a call which looks like this

BEGIN
   pmcts_app_funcs.pprepmacroinitial (:v00001,
                                      :v00002,
                                      :v00003,
                                      0,
                                      1,
                                      :v00006,
                                      :v00007,
                                      :v00008,
                                      :v00009,
                                      :v00010,
                                      :v00011,
                                      :v00012,
                                      :v00013,
                                      :v00014
                                     );
END;

So it appears to me that what Oracle does not like is the =>'s which ole db is putting into the first call.

Has anyone ever encountered this problem?

Thanks for reading this.
Mark

 

[sem]

In fact Oracle likes both methods (BY NAME and BY POSITION), they may be used from pl/sql. I suppose that something wrong with your provider: IT SHOULD bind variables rather than pass the raw call to Oracle. Regards, Dima

 

[Custom24]

Sem - thanks for explaining that to me.
I've figured it out after a lot of patience
I'd not assigned a value to a date parameter. I'm using the MS Ole db provider for Oracle, and if you assign a value to a parameter which does not exist in the parameter collection, it rightly throws an error.

However, I'd done the opposite - I'd forgotten about one of the parameters, and it came up with the misleading error I reported above.

The provider is pretty naff - it seems that only BY POSITION works - so you also have to add the parameters to the collection in the same order as they are in the method signature.

Anyway, I thought I'd post back in case anyone else has a similar problem
Mark