Oracle SP Parameter Error - Invalid Parameter Type

Oracle SP Parameter Error - Invalid Parameter Type

Post by Stella Desir » Wed, 11 Oct 2000 04:00:00



I am getting the following error:

[Microsoft][ODBC driver for Oracle]Invalid parameter type

With no freaking help at all!!!  I don't know if it's a data
conversion type or what.  PLEASE HELP!!!

Oracle Function:

function BeginAging(p_date varchar2, p_commit int default 1,
p_output_file varchar2 default '',p_maxrows number default 0)
return number;

PL/SQL Script that works:

declare
rc number(5);
begin
rc:= ProArch_pkg.BeginAging('10/01/1999', 0, 'proarch', 10);
dbms_output.put_line(to_char(rc));
end;

VB/ADO function that is in error:

With objCmd
   .CommandText = "proarch_pkg.BeginAging"
   .CommandType = adCmdStoredProc
   .ActiveConnection = objConn
   .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 20,
strDate)
   .Parameters.Append .CreateParameter(, adInteger, adParamInput, ,
intCommit)
   .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50,
strLog)
   .Parameters.Append .CreateParameter(, adDouble, adParamInput, ,
intMaxRows)
   .Parameters.Append .CreateParameter(, adDouble, adParamReturnValue)
   .Execute
End With

The Command object looks perfectly fine in the Watch window.  Any help
is GREATLY appreciated.

Stella

 
 
 

Oracle SP Parameter Error - Invalid Parameter Type

Post by Michael D. Lon » Wed, 11 Oct 2000 04:00:00


Do a Parameters.Refresh and examine the collection
to see how ADO interprets the parameters. Sometimes
the results are not what you would expect.

--
Michael D. Long
http://extremedna.homestead.com


Quote:> I am getting the following error:

> [Microsoft][ODBC driver for Oracle]Invalid parameter type

> With no freaking help at all!!!  I don't know if it's a data
> conversion type or what.  PLEASE HELP!!!

> Oracle Function:

> function BeginAging(p_date varchar2, p_commit int default 1,
> p_output_file varchar2 default '',p_maxrows number default 0)
> return number;

> PL/SQL Script that works:

> declare
> rc number(5);
> begin
> rc:= ProArch_pkg.BeginAging('10/01/1999', 0, 'proarch', 10);
> dbms_output.put_line(to_char(rc));
> end;

> VB/ADO function that is in error:

> With objCmd
>    .CommandText = "proarch_pkg.BeginAging"
>    .CommandType = adCmdStoredProc
>    .ActiveConnection = objConn
>    .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 20,
> strDate)
>    .Parameters.Append .CreateParameter(, adInteger, adParamInput, ,
> intCommit)
>    .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50,
> strLog)
>    .Parameters.Append .CreateParameter(, adDouble, adParamInput, ,
> intMaxRows)
>    .Parameters.Append .CreateParameter(, adDouble, adParamReturnValue)
>    .Execute
> End With

> The Command object looks perfectly fine in the Watch window.  Any help
> is GREATLY appreciated.

> Stella


 
 
 

Oracle SP Parameter Error - Invalid Parameter Type

Post by Stella Desir » Wed, 11 Oct 2000 04:00:00



Quote:

>Do a Parameters.Refresh and examine the collection
>to see how ADO interprets the parameters. Sometimes
>the results are not what you would expect.

Tried that.  I saw that from an earlier post (I believe yours) but the
stupid Oracle OLEDB gives me an error:

Run-time error '3021'

Either BOF or EOF is true, or the current record has been deleted.
Requested operation requires a current record.

AND

?objConn.Errors.Count
 0

Can you believe that CRAP!

When I go to the Watch window it also has my Return parameter in the
statement as a regular parameter.  And I can't expand the Parameters
collection.  

I've also tried using driver={Microsoft ODBC for Oracle} AND MSDASQL.

 
 
 

Oracle SP Parameter Error - Invalid Parameter Type

Post by Carl Prothma » Wed, 11 Oct 2000 04:00:00


Stella,
You can use the following chart to figure out the data type mapping between
ADO and Oracle
http://www.able-consulting.com/ADODataTypeEnum.htm

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com

Please reply in the newsgroup.  I am unable to respond to requests for
assistance via private e-mail.



> >Do a Parameters.Refresh and examine the collection
> >to see how ADO interprets the parameters. Sometimes
> >the results are not what you would expect.

> Tried that.  I saw that from an earlier post (I believe yours) but the
> stupid Oracle OLEDB gives me an error:

> Run-time error '3021'

> Either BOF or EOF is true, or the current record has been deleted.
> Requested operation requires a current record.

> AND

> ?objConn.Errors.Count
>  0

> Can you believe that CRAP!

> When I go to the Watch window it also has my Return parameter in the
> statement as a regular parameter.  And I can't expand the Parameters
> collection.

> I've also tried using driver={Microsoft ODBC for Oracle} AND MSDASQL.

 
 
 

Oracle SP Parameter Error - Invalid Parameter Type

Post by Stella Desir » Thu, 12 Oct 2000 04:00:00


Your chart is VERY USEFUL!  Unfortunately I am still getting the same
error.  I did switch all int parameters to adNumeric.  This is very
frustrating.

Anyone with Oracle Package and Procedure experience, please step up to
the plate and help me and other who suffer with this same nightmare!!!


>Stella,
>You can use the following chart to figure out the data type mapping between
>ADO and Oracle
>http://www.able-consulting.com/ADODataTypeEnum.htm

 
 
 

Oracle SP Parameter Error - Invalid Parameter Type

Post by Anoop Georg » Thu, 12 Oct 2000 04:00:00


The problem is with your OLEDB provider for ORACLE and the data access pack.
You should have data access pack 2.5 (download from microsoft site)or
upwards. Once that is installed try this.

dim objConn
objConn.ConnectionString = "PROVIDER=MSDAORA.1;User
ID=scott;Password=tiger;Data Source=testdata;"
objConn.open

now u can use ur code to continue.


> Your chart is VERY USEFUL!  Unfortunately I am still getting the same
> error.  I did switch all int parameters to adNumeric.  This is very
> frustrating.

> Anyone with Oracle Package and Procedure experience, please step up to
> the plate and help me and other who suffer with this same nightmare!!!


> >Stella,
> >You can use the following chart to figure out the data type mapping
between
> >ADO and Oracle
> >http://www.able-consulting.com/ADODataTypeEnum.htm

 
 
 

Oracle SP Parameter Error - Invalid Parameter Type

Post by Stella Desir » Fri, 13 Oct 2000 04:00:00


I switched libraries and it still did not work.  We are going to
attempt to use OO4O (not very much doc. to work with).  In the
meantime, if there is anyone with GOOD experience in calling Package
Functions from ADO, I'd love your input!

Thanks to all that have responded.


>The problem is with your OLEDB provider for ORACLE and the data access pack.
>You should have data access pack 2.5 (download from microsoft site)or
>upwards. Once that is installed try this.

>dim objConn
>objConn.ConnectionString = "PROVIDER=MSDAORA.1;User
>ID=scott;Password=tiger;Data Source=testdata;"
>objConn.open

>now u can use ur code to continue.



>> Your chart is VERY USEFUL!  Unfortunately I am still getting the same
>> error.  I did switch all int parameters to adNumeric.  This is very
>> frustrating.

>> Anyone with Oracle Package and Procedure experience, please step up to
>> the plate and help me and other who suffer with this same nightmare!!!


>> >Stella,
>> >You can use the following chart to figure out the data type mapping
>between
>> >ADO and Oracle
>> >http://www.able-consulting.com/ADODataTypeEnum.htm

 
 
 

Oracle SP Parameter Error - Invalid Parameter Type

Post by Anoop Georg » Sun, 15 Oct 2000 04:00:00


See the following code for calling packages from asp using ado.

<%
set oConn = Server.CreateObject( "ADODB.Connection" )
oConn.ConnectionString = Session("ConnectString")
oConn.Open

set oCmd = server.CreateObject ("ADODB.Command")
with oCmd
  set .ActiveConnection = oConn
  .Prepared = true
  .CommandText   = "YTD_Svar.by_govt"
  .CommandType   = adCmdStoredProc
  set oPrm = .CreateParameter("Rtype", adVarChar ,adParamInput  , 1  ,
sType)
  .Parameters.Append oPrm
end with

set rsRet = Server.CreateObject ( "ADODB.Recordset" )
set rsRet = oCmd.execute
%>

I am using oracle 8, mdac 2.5
Oracle ODBC driver Version 8.00.05.00
I have faced ur exact problem. But on updating my data access pack i was
able to retrieve recordsets from oracle packages.


> I switched libraries and it still did not work.  We are going to
> attempt to use OO4O (not very much doc. to work with).  In the
> meantime, if there is anyone with GOOD experience in calling Package
> Functions from ADO, I'd love your input!

> Thanks to all that have responded.


> >The problem is with your OLEDB provider for ORACLE and the data access
pack.
> >You should have data access pack 2.5 (download from microsoft site)or
> >upwards. Once that is installed try this.

> >dim objConn
> >objConn.ConnectionString = "PROVIDER=MSDAORA.1;User
> >ID=scott;Password=tiger;Data Source=testdata;"
> >objConn.open

> >now u can use ur code to continue.



> >> Your chart is VERY USEFUL!  Unfortunately I am still getting the same
> >> error.  I did switch all int parameters to adNumeric.  This is very
> >> frustrating.

> >> Anyone with Oracle Package and Procedure experience, please step up to
> >> the plate and help me and other who suffer with this same nightmare!!!


> >> >Stella,
> >> >You can use the following chart to figure out the data type mapping
> >between
> >> >ADO and Oracle
> >> >http://www.able-consulting.com/ADODataTypeEnum.htm