Re: default values for Oracle stored procedure

Hervé wrote on Mon, 19 Jun 2006 06:50:02 -0700:


--
herve

"Daniel Crichton" wrote:

Hervé wrote on Mon, 19 Jun 2006 04:21:02 -0700:

I'm trying to use default values for Oracle stored procedure in an asp
page and ADO

When the parameter isn't define in the asp page , an error message
occurs.

example :

stored procedure :
CREATE PROCEDURE UPW_S_ECHEANCES_ALEAS(
@P_INDIVIDU integer,
@P_DATEDEBUT varchar(10),
@P_DATEFIN varchar(10),
@P_INDIV_VISUALISE integer default -1)
As
blabla
end;

asp page
set cmdDB = Server.CreateObject("ADODB.Command")
cmdDB.ActiveConnection = DBConnexion
cmdDB.CommandType = adCmdStoredProc
cmdDB.CommandText = "UPW_S_ECHEANCES_ALEAS"
cmdDB.Parameters.Append cmdDB.CreateParameter("P_INDIVIDU",
adInteger, adParamInput, , Cookies_INDIVIDU)
cmdDB.Parameters.Append cmdDB.CreateParameter("DATEDEBUT",
adVarChar, adParamInput, 10, DateDebutQuery) cmdDB.Parameters.Append
cmdDB.CreateParameter("DATEFIN", adVarChar, adParamInput, 10,
DateFinQuery) set AleasSet = cmdDB.Execute

the following message occurs :

Microsoft OLE DB Provider for Oracle erreur '80040e14'

ORA-06550: Ligne 1, colonne 7 : PLS-00306: numéro ou types d'arguments
erronés dans appel à 'UPW_S_ECHEANCES_ALEAS' ORA-06550: Ligne 1, colonne
7
:
PL/SQL: Statement ignored

/premium/21/mdb_agenda_planaction.asp, ligne 378

Thanks.

I think you still have to define the parameters, just not give them a
value. The names used in the parameters collection in ADO are not the
actual names of the parameters, you can call them anything you like -
when ADO passes the SQL to the provider it simply uses the ordinal
positions of the parameters collection to pass in the values.

Dan

Yes, but I'd like to not define the parameters in the asp page, that's
the problem !!

Unfortunately it appears the ORA driver is forcing you to. You could try
cmdDB.Parameters.Refresh instead of your .Append lines to retrieve the
parameter definitions from the server, and then set the values for the ones
you want (although you'll probably find the names have @ in them when you
do).

Dan


.

Relevant Pages

  • Re: default values for Oracle stored procedure
    ... When the parameter isn't define in the asp page, an error message ... The names used in the parameters collection in ADO are not the actual names ...
    (microsoft.public.data.ado)
  • Re: default values for Oracle stored procedure
    ... When the parameter isn't define in the asp page, an error message ... The names used in the parameters collection in ADO are not the actual names ...
    (microsoft.public.data.ado)
  • Re: Calling ASP function asynchronously
    ... but, since you managed to work async with a stored procedure through ADO, ... Btw, I'm curious how that async stored-procedure magic works in ASP though, ...
    (microsoft.public.inetserver.asp.general)
  • Stored Procedure not found
    ... with ASP and ADO. ... I created a stored procedure as a user and granted ... I try to user the webuser user I get the 'Could not find stored ...
    (microsoft.public.inetserver.asp.db)
  • Error message in asp page when submitting to database
    ... I get the error message above when submitting to a stored procedure, from an asp page. ... I have checked the contents of the form and even if I leave it blank, as a test, I still get the same error message. ...
    (microsoft.public.sqlserver.programming)