VB & Database, Error calling Oracle store procedures using ADO

Error calling Oracle store procedures using ADO

Author	Message
Eugene Zilberley #1 / 3	Error calling Oracle store procedures using ADO I have VB 6 code calling SP in Oracle 8.i The code used to work until recently I started to get strange errors. One of every 5-10 calls produce "Unspecified error" I tried different drivers -Oracle OLEDB, Microsoft OLEDB for Oracle, Microsoft ODBC for Oracle but it did not help, I still getting errors with different drivers The Oracle support could not help, the only thing we found that error is risen by ADODB Execute statement but before control goes inside store procedure. Any help or suggestion would be greatly appreciated Here is a list of errors produced by different drivers: **** 09/06/2001 10:22:42 am : OraOLEDB ERROR User: IUSR_MP-DEV-WEB2 Virtual Directory: Deltabase_Web Procedure: DeltabaseUtil::OracleSecurity::GetUsersRoles() Error Number: -2147467259 Error Description: Unspecified error Error Source: OraOLEDB ** 09/06/2001 10:45:27 am : Microsoft OLE DB Provider for Oracle ERROR User: IUSR_MP-DEV-WEB2 Virtual Directory: Deltabase_Web Procedure: DeltabaseUtil::OracleSecurity::GetUsersRoles() Error Number: -2147217900 Error Description: PL/SQL procedure name is invalid. Error Source: Microsoft OLE DB Provider for Oracle **** 09/06/2001 10:48:30 am : Microsoft OLE DB Provider for ODBC Drivers ERROR User: IUSR_MP-DEV-WEB2 Procedure: DeltabaseUtil::OracleSecurity::GetUsersRoles() Error Number: -2147217900 Error Description: [Microsoft][ODBC driver for Oracle][Oracle]ORA-20004: Error Source: Microsoft OLE DB Provider for ODBC Drivers ______________________________ Here is an exapmle of VB code I use to call sp: Dim oCon As ADODB.Connection Dim oCmd As ADODB.Command Dim oParam As ADODB.Parameter Set oCon = New ADODB.Connection Set oCmd = New ADODB.Command oCon.Open "<connection string>" With oCmd Set .ActiveConnection = oCon .CommandType = adCmdStoredProc .CommandText = "MY_SCHEMA_NAME.MY_PACKAGE_NAME" Set oParam = .CreateParameter("p_specify_logon", adVarChar, adParamI nput, 30, UCase(sLogin)) .Parameters.Append oParam Set oParam = .CreateParameter("p_password", adVarChar, adParamInput, 30, UCase(sLogin)) .Parameters.Append oParam Set oParam = .CreateParameter("p_message", adVarChar, adParamOutput, 200) .Parameters.Append oParam End With oCmd.Execute sRetVal = IIf(IsNull(oCmd.Parameters(2).Value), "", Trim(oCmd.Parameters(2).Value))
Mon, 01 Mar 2004 02:22:33 GMT

Peter Dunba #2 / 3	Error calling Oracle store procedures using ADO It looks to me like something on the Oracle side has changed. What happen around the time you noticed the errors appearing. I would look for compile errors in other packages. I would also look for security changes made to any Role or Users on DB objects. Peter Quote: > I have VB 6 code calling SP in Oracle 8.i > The code used to work until recently I started to get strange errors. > One of every 5-10 calls produce "Unspecified error" > I tried different drivers -Oracle OLEDB, Microsoft OLEDB for Oracle, > Microsoft ODBC for Oracle but it did not help, I still getting errors with > different drivers > The Oracle support could not help, the only thing we found that error is > risen by ADODB Execute statement but before control goes inside store > procedure. > Any help or suggestion would be greatly appreciated > Here is a list of errors produced by different drivers: > **** > 09/06/2001 10:22:42 am : OraOLEDB ERROR > User: IUSR_MP-DEV-WEB2 > Virtual Directory: Deltabase_Web > Procedure: DeltabaseUtil::OracleSecurity::GetUsersRoles() > Error Number: -2147467259 > Error Description: Unspecified error > Error Source: OraOLEDB > ** > 09/06/2001 10:45:27 am : Microsoft OLE DB Provider for Oracle ERROR > User: IUSR_MP-DEV-WEB2 > Virtual Directory: Deltabase_Web > Procedure: DeltabaseUtil::OracleSecurity::GetUsersRoles() > Error Number: -2147217900 > Error Description: PL/SQL procedure name is invalid. > Error Source: Microsoft OLE DB Provider for Oracle > **** > 09/06/2001 10:48:30 am : Microsoft OLE DB Provider for ODBC Drivers ERROR > User: IUSR_MP-DEV-WEB2 > Procedure: DeltabaseUtil::OracleSecurity::GetUsersRoles() > Error Number: -2147217900 > Error Description: [Microsoft][ODBC driver for Oracle][Oracle]ORA-20004: > Error Source: Microsoft OLE DB Provider for ODBC Drivers > ______________________________ > Here is an exapmle of VB code I use to call sp: > Dim oCon As ADODB.Connection > Dim oCmd As ADODB.Command > Dim oParam As ADODB.Parameter > Set oCon = New ADODB.Connection > Set oCmd = New ADODB.Command > oCon.Open "<connection string>" > With oCmd > Set .ActiveConnection = oCon > .CommandType = adCmdStoredProc > .CommandText = "MY_SCHEMA_NAME.MY_PACKAGE_NAME" > Set oParam = .CreateParameter("p_specify_logon", adVarChar, adParamI > nput, 30, UCase(sLogin)) > .Parameters.Append oParam > Set oParam = .CreateParameter("p_password", adVarChar, adParamInput, > 30, UCase(sLogin)) > .Parameters.Append oParam > Set oParam = .CreateParameter("p_message", adVarChar, adParamOutput, > 200) > .Parameters.Append oParam > End With > oCmd.Execute > sRetVal = IIf(IsNull(oCmd.Parameters(2).Value), "", > Trim(oCmd.Parameters(2).Value))
Wed, 03 Mar 2004 22:12:36 GMT

Steve Lia #3 / 3	Error calling Oracle store procedures using ADO Q176086 - HOWTO: Retrieve Recordsets from Oracle Stored Procs Using ADO You can find it at: http://support.microsoft.com/support/kb/articles/q176/0/86.asp The ORA-20004 seems to be related with syntax error. To further isolation the problem, I suggest you: 1. Make a sampel SP on Oracle with no parameter, can you call it in VB ADO without problem? 2. Add the same parameter, can you do it without error? 3. Could you call it several times without error in SQL Plus? In addition, as to these specific errors, I suggest you submit a service request to Microsoft Support Service to get more efficient help.
Fri, 05 Mar 2004 14:55:24 GMT

Page 1 of 1

[ 3 post ]