VB6 Oracle 10 Stored procedures with parameters

Hermes68

35 pts.

Tags:

Oracle 10g

Oracle error messages

Oracle stored procedures

Oracle upgrades

Visual Basic

Visual Basic 6

I have a vb6 - RDO code that works ok with Oracle8, recently there was an upgrade to Oracle 10g enterprise. Now I get the error 40041 -> Object Collection: Couldn't find item indicated by text.

1110    lstrSQL = "{call Pyramid.Load_Tables(?, ?, 0)}"
1120    Set lrdoQ = vConexionDatos.MiDataBase.CreateQuery("ProcessTables", lstrSQL)
1130    lrdoQ.rdoParameters(0).Direction = rdParamInput
1140    lrdoQ.rdoParameters(0).Type = rdTypeDATE
1150    lrdoQ.rdoParameters(0).value = lFromDate
1160    lrdoQ.rdoParameters(1).Direction = rdParamInput
1170    lrdoQ.rdoParameters(1).Type = rdTypeDATE
1180    lrdoQ.rdoParameters(1).value = lToDate
1210    lrdoQ.Execute
1230    lrdoQ.Close

the error occurs in the line 1130 Any idea?

Asked: December 5, 2008 8:20 PM Last updated: December 11, 2008 2:16 PM

Answer Wiki

Last updated: December 5, 2008 10:55 PM GMT

carlosdl84,495 pts.
History
Contributors
- Ordered by most recent
- carlosdl84,495 pts.

Thanks. We'll let you know when a new response is added.

Is the stored procedure signature exactly the same as it was on oracle 8 ?

Discuss This Question: 5 Replies

Hermes68 Dec 8, 2008 4:06 PM GMT

Yes, I supose 10g is more strict with something 8 isn´t. Naming, parameters?

35 pointsBadges:

report
carlosdl Dec 8, 2008 9:44 PM GMT

Hmmm, not sure about that. That error message usually fires when one is referring to parameters by name ("Couldn't find item indicated by text"), but your code is not doing that, but referring to them by position. Have you tried something like the following ? 1110 lstrSQL = "{call Pyramid.Load_Tables(?, ?, ?)}" 1120 Set lrdoQ = vConexionDatos.MiDataBase.CreateQuery("ProcessTables", lstrSQL) 1130 lrdoQ.rdoParameters(0).Direction = rdParamInput 1140 lrdoQ.rdoParameters(0).Type = rdTypeDATE 1150 lrdoQ.rdoParameters(0).value = lFromDate 1160 lrdoQ.rdoParameters(1).Direction = rdParamInput 1170 lrdoQ.rdoParameters(1).Type = rdTypeDATE 1180 lrdoQ.rdoParameters(1).value = lToDate 1160 lrdoQ.rdoParameters(2).Direction = rdParamInput 1170 lrdoQ.rdoParameters(2).Type = rdTypeINTEGER 1180 lrdoQ.rdoParameters(2).value = 0 1210 lrdoQ.Execute 1230 lrdoQ.Close

84,495 pointsBadges:

report
Hermes68 Dec 9, 2008 4:26 PM GMT

Doesn't work, for 10g what should be the full text (schema.user.package.procedure ¿?) to call a procedure in a package ?? thank you

35 pointsBadges:

report
carlosdl Dec 9, 2008 7:10 PM GMT

It should be schema.package.procedure

84,495 pointsBadges:

report
Hermes68 Dec 11, 2008 2:16 PM GMT

No way, I wil try without parameters

35 pointsBadges:

report