CODEWITHSUNDEEP
oracle-databasestored-proceduresvb6oledb
Andrew Florko
Andrew Florko

Reputation: 7750

Oracle function call from VB6. Function is not a procedure error

I have Oracle function declared as 

create or replace FUNCTION CheckScan(
  pMode IN number, 
  pAgrISN in number, 
  pAgrId in varchar2, 
  pDocISN in number, 
  pRefundId in varchar2) RETURN NUMBER IS  ...

And client Visual Basic 6 code that connects to Oracle as administrator like:

... 

cmd.ActiveConnection = conn
cmd.CommandText = "CheckScan"
cmd.CommandType = 4 'adCmdStoredProc

Dim pMode As Integer
Dim pAgrISN As Integer
Dim pAgrId As String
Dim pDocISN As Integer
Dim pRefundId As String

pMode = 2
pAgrISN = 12345
pAgrId = "Some-Id"
pDocISN = 12345
pRefundId = "Some-id"

cmd.Parameters.Append cmd.CreateParameter("pMode", 131, 1, 10, pMode)
cmd.Parameters.Append cmd.CreateParameter("pAgrISN", 131, 1, 10, pAgrISN)
cmd.Parameters.Append cmd.CreateParameter("pAgrId", 200, 1, 255, pAgrId)
cmd.Parameters.Append cmd.CreateParameter("pDocISN", 131, 1, 255, pDocISN)
cmd.Parameters.Append cmd.CreateParameter("pRefundId", 200, 1, 255, pRefundId)

cmd.Execute

This code ends with: 

PLS-00221: "CHECKSCAN" is not a procedure or is undefined

What is wrong here? Function was compiled successfully.

Thank you in advance!

Upvotes: 1

Views: 3485

Answers (4)

Codo
Codo

Reputation: 78825

Since you have a function (and not a procedure), you have to do something with the return value. Add the following parameter:

With cmd
    .Parameters.Append .CreateParameter("pRetval", adNumeric, adParamReturnValue)
    .Parameters.Append .CreateParameter("pMode", adNumeric, adParamInput, 10, pMode)
    .Parameters.Append .CreateParameter("pAgrISN", adNumeric, adParamInput, 10, pAgrISN)
    .Parameters.Append .CreateParameter("pAgrId", adVarChar, adParamInput, 255, pAgrId)
    .Parameters.Append .CreateParameter("pDocISN", adNumeric, adParamInput, 255, pDocISN)
    .Parameters.Append .CreateParameter("pRefundId", adVarChar, adParamInput, 255, pRefundId)

    .Execute
End With

Update:

The order of the parameters is relevant. It must start with the return type. Then all parameters of the function must follow in the order they were declared. The names of the parameters are irrelevant however because positional (and not named) parameters are used internally. This is obvious if you look at the CommandText property of cmd:

"{ ? = call CheckScan(?, ?, ?, ?, ?) }"

Upvotes: 3

Aaron
Aaron

Reputation: 57748

What does this function do? Oracle has certain restrictions on functions (not allowed to do updates in certain situations, etc...). Check out this doc, and make sure you're not violating anything under the Restrictions on User-Defined Functions section. You might have to create it as a stored procedure (or wrap it with one).

Upvotes: 1

Gaius
Gaius

Reputation: 2595

This is simply caused because you have defined it as a function, but are calling it as if it was a procedure. Either call it as a function (with SELECT), or use an IN OUT parameter in place of RETURNING.

Upvotes: 3

chris
chris

Reputation: 37460

I don't know if you can call a function by itself. Instead, I'd try executing a select, something like:

select CheckScan(:pMode, :pAgrISN, :pArgId, :pDocISN, :pRefundId) from dual;

Upvotes: 2

Related Questions