Calling Oracle SPs w/Parameters (PLS-00306)

[Guest]

Hello,

I am trying to call several Oracle Stored Procedure using the "MS OLE DB for
Oracle" driver, and have gotten the same error regardless of the SP I call.

The error is:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'ISR_PMPM_PARAMETERINSPROC'
ORA-06550: line 1, cloumn 7:
PL/SQL: Statement Ignored

The main SP I am trying to call looks like this (not all of them have out
values, though):

CREATE OR REPLACE PROCEDURE ISR_PMPM_PARAMETERInsProc
(
v_PMPM_NAME                                IN VARCHAR2,
v_PMPM_DESC		          IN VARCHAR2,
v_PMPM_SIZE                                  IN NUMBER,
v_PMPM_DEFAULT_VALUE                IN VARCHAR2,
v_PMPM_VALUE                               IN VARCHAR2,
v_PMPM_DISPLAY_ORDER                 IN NUMBER,
v_PMPM_AUDIT_USER                      IN VARCHAR2,
v_PMPM_AUDIT_DATE                      IN DATE,
v_PMPS_UID                                    IN NUMBER,
v_KTTR_VALUE_TYPE_UID                IN NUMBER,
v_PMPM_UID                                   OUT NUMBER
)
AS
BEGIN
INSERT INTO ISR_PMPM_PARAMETER(PMPM_NAME,
PMPM_DESC,
PMPM_SIZE,
PMPM_DEFAULT_VALUE,
PMPM_VALUE,
PMPM_DISPLAY_ORDER,
PMPM_AUDIT_USER,
PMPM_AUDIT_DATE,
PMPS_UID,
KTTR_VALUE_TYPE_UID)
VALUES(v_PMPM_NAME,
v_PMPM_DESC,
v_PMPM_SIZE,
v_PMPM_DEFAULT_VALUE,
v_PMPM_VALUE,
v_PMPM_DISPLAY_ORDER,
v_PMPM_AUDIT_USER,
v_PMPM_AUDIT_DATE,
v_PMPS_UID,
v_KTTR_VALUE_TYPE_UID)
Returning PMPM_UID into v_PMPM_UID;

END;

The code I am using to create the call to the procedure is:

Dim com As New OracleCommand("ISR_PMPM_PARAMETERInsProc", oraCon)
com.CommandType = CommandType.StoredProcedure

Dim outValue As New OracleParameter("v_PMPM_UID", OracleType.Number, 22)
outValue.Direction = ParameterDirection.Output

com.Parameters.Add(New OracleParameter("v_PMPM_NAME",
OracleType.VarChar, 50)).Value = ParameterName
com.Parameters.Add(New OracleParameter("v_PMPM_DESC", OracleType.VarChar,
100)).Value = ParameterDescription
com.Parameters.Add(New OracleParameter("v_PMPM_SIZE", OracleType.Number,
3)).Value = 3
com.Parameters.Add(New OracleParameter("v_PMPM_VALUE",
OracleType.VarChar, 999)).Value = ParameterValue
com.Parameters.Add(New OracleParameter("v_PMPS_UID", OracleType.Number,
10)).Value = ParameterSetID
com.Parameters.Add(New OracleParameter("v_KTTR_VALUE_TYPE_UID",
OracleType.Number, 10)).Value = ParameterDataType
com.Parameters.Add(outValue)

And the actual code to call it is:

Try
If oraCon.State = ConnectionState.Closed Or _
oraCon.State = ConnectionState.Broken Then
oraCon.Open()
com.ExecuteNonQuery()
end if
catch...
end try

If anyone has any advice or ideas, I would greatly appreciate it.

Thanks,
Scott

 

[Joyjit Mukherjee]

Hi,

From the code I could see that the number of parameters for the
OracleCommand object does not matches the Actual Oracle SP parameters. These
two should match.

regards
Joyit

Hello,

I am trying to call several Oracle Stored Procedure using the "MS OLE DB for
Oracle" driver, and have gotten the same error regardless of the SP I call.

The error is:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'ISR_PMPM_PARAMETERINSPROC'
ORA-06550: line 1, cloumn 7:
PL/SQL: Statement Ignored

The main SP I am trying to call looks like this (not all of them have out
values, though):

CREATE OR REPLACE PROCEDURE ISR_PMPM_PARAMETERInsProc
(
v_PMPM_NAME                                IN VARCHAR2,
v_PMPM_DESC           IN VARCHAR2,
v_PMPM_SIZE                                  IN NUMBER,
v_PMPM_DEFAULT_VALUE                IN VARCHAR2,
v_PMPM_VALUE                               IN VARCHAR2,
v_PMPM_DISPLAY_ORDER                 IN NUMBER,
v_PMPM_AUDIT_USER                      IN VARCHAR2,
v_PMPM_AUDIT_DATE                      IN DATE,
v_PMPS_UID                                    IN NUMBER,
v_KTTR_VALUE_TYPE_UID                IN NUMBER,
v_PMPM_UID                                   OUT NUMBER
)
AS
BEGIN
INSERT INTO ISR_PMPM_PARAMETER(PMPM_NAME,
PMPM_DESC,
PMPM_SIZE,
PMPM_DEFAULT_VALUE,
PMPM_VALUE,
PMPM_DISPLAY_ORDER,
PMPM_AUDIT_USER,
PMPM_AUDIT_DATE,
PMPS_UID,
KTTR_VALUE_TYPE_UID)
VALUES(v_PMPM_NAME,
v_PMPM_DESC,
v_PMPM_SIZE,
v_PMPM_DEFAULT_VALUE,
v_PMPM_VALUE,
v_PMPM_DISPLAY_ORDER,
v_PMPM_AUDIT_USER,
v_PMPM_AUDIT_DATE,
v_PMPS_UID,
v_KTTR_VALUE_TYPE_UID)
Returning PMPM_UID into v_PMPM_UID;

END;

The code I am using to create the call to the procedure is:

Dim com As New OracleCommand("ISR_PMPM_PARAMETERInsProc", oraCon)
com.CommandType = CommandType.StoredProcedure

Dim outValue As New OracleParameter("v_PMPM_UID", OracleType.Number, 22)
outValue.Direction = ParameterDirection.Output

com.Parameters.Add(New OracleParameter("v_PMPM_NAME",
OracleType.VarChar, 50)).Value = ParameterName
com.Parameters.Add(New OracleParameter("v_PMPM_DESC", OracleType.VarChar,
100)).Value = ParameterDescription
com.Parameters.Add(New OracleParameter("v_PMPM_SIZE", OracleType.Number,
3)).Value = 3
com.Parameters.Add(New OracleParameter("v_PMPM_VALUE",
OracleType.VarChar, 999)).Value = ParameterValue
com.Parameters.Add(New OracleParameter("v_PMPS_UID", OracleType.Number,
10)).Value = ParameterSetID
com.Parameters.Add(New OracleParameter("v_KTTR_VALUE_TYPE_UID",
OracleType.Number, 10)).Value = ParameterDataType
com.Parameters.Add(outValue)

And the actual code to call it is:

Try
If oraCon.State = ConnectionState.Closed Or _
oraCon.State = ConnectionState.Broken Then
oraCon.Open()
com.ExecuteNonQuery()
end if
catch...
end try

If anyone has any advice or ideas, I would greatly appreciate it.

Thanks,
Scott

 

[Paul Clement]

¤ Hello,
¤
¤ I am trying to call several Oracle Stored Procedure using the "MS OLE DB for
¤ Oracle" driver, and have gotten the same error regardless of the SP I call.
¤
¤ The error is:

¤ ORA-06550: line 1, column 7:
¤ PLS-00306: wrong number or types of arguments in call to
¤ 'ISR_PMPM_PARAMETERINSPROC'
¤ ORA-06550: line 1, cloumn 7:
¤ PL/SQL: Statement Ignored

¤
¤ The main SP I am trying to call looks like this (not all of them have out
¤ values, though):

You need to supply all of the parameters defined in your Oracle SP regardless of whether they have
values. I would also recommend adding them to the collection in the order they appear in the SP
declaration.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)