Re: Problem with executing an oracle stored procedure through VB

Just in case anyone's interested I've now discovered the problem. There is a problem with executing an oracle stored proc which has boolean parameters due to the different ways in which a boolean can be handled by different systems.

I changed the parameter to an integer and this now works.

Jo Collins

jo.collins_at_gvagrimley.co.uk (Jo Collins) wrote in message news:<eb8df310.0309230301.49de3b82_at_posting.google.com>...
> Hi,
>
> I am having problems running an oracle 8i stored procedure through VB
> using MSADORA as the provider. Even though I have correctly specified
> the direction and the datatype of the input and the ouput parameter I
> am getting the following error when trying to execute the command:
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to
> 'PR_AUDITINGSWITCH'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> This is very bizarre as I have run lots of other oracle procedures
> with input and output parameters through the application with no
> problems at all?! Samples of the VB code calling the procedure and the
> PL/SQL for the stored procedure are below:
>
> VB CODE:
> --------
>
> Dim prmSwitch As Parameter
> Dim prmAuditSetting As Parameter
> Dim comDisableAuditingCommand As New Command
> Dim rsDisableAuditingResults As Recordset
>
> Set comDisableAuditingCommand.ActiveConnection = DatabaseConnection
> comDisableAuditingCommand.CommandType = adCmdStoredProc
> comDisableAuditingCommand.CommandText =
> "ARCHIVING_UTIL_PKG.pr_AuditingSwitch"
>
> 'Set up the input parameter
> Set prmSwitch = comDisableAuditingCommand.CreateParameter("iswitch",
> adBoolean, adParamInput)
> comDisableAuditingCommand.Parameters.Append prmSwitch
>
> 'Set up the output parameter
> Set prmAuditSetting =
> comDisableAuditingCommand.CreateParameter("oauditsetting", adInteger,
> adParamOutput)
> comDisableAuditingCommand.Parameters.Append prmAuditSetting
>
> 'Set the input parameter to turn auditing off
> prmSwitch = False
>
> Set rsDisableAuditingResults = comDisableAuditingCommand.Execute(, ,
> adExecuteNoRecords)
>
> ** at this point the error occurs
> ------------------------------------
> PL/SQL:
>
> /******************************************************************************/
> PROCEDURE pr_auditingswitch (
> iswitch IN BOOLEAN,
> oauditsetting OUT confauditoptions.performauditing%TYPE
> )
> IS
> /**
> If switch is 1 then switches auditing on
> If switch is off then switches auding off and saves back
> setting.
> Needs a parent (controller) to save original setting
> Switch BOOLEAN 0=OFF 1=RESTORE
> **/
> BEGIN
> IF iswitch IS NULL
> THEN
> RAISE invalid_input_parameter;
> END IF;
>
> IF iswitch = FALSE
> THEN -- DISABLE
> AUDITING
> SELECT performauditing
> INTO oauditsetting
> FROM confauditoptions;
>
> UPDATE confauditoptions
> SET performauditing = 0;
> ELSE -- Restore
> Auditing
> IF iswitch
> THEN
> UPDATE confauditoptions
> SET performauditing = 1;
> END IF;
> END IF;
>
> COMMIT;
> EXCEPTION
> WHEN OTHERS
> THEN
> ROLLBACK;
> RAISE;
> END pr_auditingswitch;
>
> /******************************************************************************/
>
> Any advice / help is greatly appreciated as this has been bugging me
> for a couple of days now!
>
> Thanks,
Received on Tue Sep 23 2003 - 08:28:11 CDT