Re: Problem with executing an oracle stored procedure through VB

Jo Collins wrote:

>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,
>>
>>

Great. Now fix your Exception handler. It doesn't do anything.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)