Problem with executing an oracle stored procedure through VB

From: Jo Collins <jo.collins_at_gvagrimley.co.uk>
Date: 23 Sep 2003 04:01:51 -0700
Message-ID: <eb8df310.0309230301.49de3b82@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 - 06:01:51 CDT