在 Oracle 11g 上从 vb6 执行 PL/SQL 过程 [英] Execute PL/SQL procedure from vb6 on Oracle 11g

问题描述

我正在尝试调用具有 Visual Basic 6 函数输出值的 PL/SQL 过程，但它不起作用.这是我的代码.

I am trying to call a PL/SQL procedure which has an output value from a Visual Basic 6 function, but it does not work. Here my code.

PL/SQL 代码(到目前为止，它只是一个模拟):

PL/SQL code (so far, it is just a mock):

create or replace
PROCEDURE IS_SINISTRO_ABS_MOCK 
(   numPol      in VARCHAR2,
    codGaranzia in VARCHAR2,
    res out BOOLEAN
    ) AS 
BEGIN
  res := TRUE;
END IS_SINISTRO_ABS_MOCK;

VB6 代码:

Private Function IsSinistroInABS(NumPol As String, CodGaranzia As String) As Boolean
Dim dbConn As New ADODB.Connection

With dbConn
    .Provider = "OraOLEDB.Oracle"
    .Properties("Data Source") = "*********"
    .Properties("User Id") = "ROUTING"
    .Properties("Password") = "***********"
    .Open
End With


Dim dbCmd As ADODB.Command
Dim result As Boolean
Set dbCmd = New ADODB.Command
dbCmd.ActiveConnection = dbConn
dbCmd.CommandTimeout = 300
dbCmd.CommandType = adCmdStoredProc
dbCmd.CommandText = "{CALL ROUTING.IS_SINISTRO_ABS_MOCK(?,?,?)}"

dbCmd.Parameters.Append dbCmd.CreateParameter(, adLongVarChar, adParamInput, _ 
Len(NumPol), NumPol)
dbCmd.Parameters.Append dbCmd.CreateParameter(, adLongVarChar, adParamInput, _ 
Len(CodGaranzia), CodGaranzia)
dbCmd.Parameters.Append dbCmd.CreateParameter(, adBoolean, adParamOutput, , _ 
result)
dbCmd.Prepared = True

dbCmd.Execute

IsSinistroInABS = dbCmd.Parameters("res").value

dbConn.Close

End Function

数据库连接正常工作，确实我成功执行了标准 SQL 查询，但是当我尝试运行该过程时出现未指定的错误.我还成功启动了一个没有任何参数的程序.结果，问题应该出在它们的使用上.

The DB connection works properly, indeed I succeeded in executing a standard SQL query, but I get an unspecified error when I try to run the procedure. I succeeded also in launching a procedure without any parameters. As a result, the problem is supposed to be in the use of them.

请注意，该过程是一个独立的过程.换句话说，它不包含在任何包中.

Note that the procedure is a standalone one. In other words, it is not included in any package.

推荐答案

也许我有点晚了(您在 2.5 年前发布了您的问题)，但我遇到了同样的问题.
经过大量的挖掘和挫折，我发现当存储过程具有数字输出参数(VARCHAR和任何输入参数都可以)时会发生错误.

Maybe I'm a bit late (you posted your question 2.5 years ago), but I got the same problem.
After a lot of digging and frustration, I found out the error occurs when the stored procedure has numeric output parameters (VARCHAR is OK as well as any input parameter).

我终于发现，当您使用古老的 DB 提供程序 MSDAORA.1 时，一切正常.

I finally found out that everything works correctly when you use the ancient DB-provider MSDAORA.1.

这篇关于在 Oracle 11g 上从 vb6 执行 PL/SQL 过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！