Oracle Stored Procs with Parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We are connecting to an Oracle 8.1.7 database using System.Data.OracleClient.
Executing SELECT statements is easy. Modifying data is a cinch. Calling
Stored Procedures was no problem until I tried to call a stored procedure
with parameters. Calling a stored procedure with parameters generates the
error: {ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'SP_CUSTREV_BY_INDVD' ORA-06550: line 1, column 7:
PL/SQL: Statement ignored}.

In an attempt to fix the problem, I have been through the posts at many
newsgroups including this one, but to no avail. I have no doubt that I am
missing something obvious, but would appreciate any help that can be had.

KS

----------Oracle Stored Proc Header-----------------
PROCEDURE SP_CUSTREV_BY_INDVD
(
p_Indvd_Type IN varchar2,
p_Indvd_Id IN varchar2,
p_OrderNme IN varchar2,
p_SortType IN varchar2,
p_DecisionCode IN varchar2,
outlist OUT types.ref_cursor
)
IS

---------------.net code-----------------------
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim conn As New OracleConnection("User
ID=ME_USER;PASSWORD=ME_PASS;Data Source=WELLSPRING")
Dim ds As New DataSet
Dim xcp As Exception
Dim oda As OracleDataAdapter

conn.Open()

Dim Cmd As New OracleCommand
Cmd.Connection = conn
Cmd.CommandText = "ME_SCHEMA.SP_CUSTREV_BY_INDVD"
Cmd.CommandType = CommandType.StoredProcedure

Cmd.Parameters.Add(New OracleParameter("p_Indvd_Type",
OracleType.VarChar)).Value = "TSO"
Cmd.Parameters.Add(New OracleParameter("p_Indvd_Id",
OracleType.VarChar)).Value = "T000066700"
Cmd.Parameters.Add(New OracleParameter("p_OrderNme",
OracleType.VarChar)).Value = "CustNme"
Cmd.Parameters.Add(New OracleParameter("p_SortType",
OracleType.VarChar)).Value = "ASC"
Cmd.Parameters.Add(New OracleParameter("p_DecisionCode",
OracleType.VarChar)).Value = "All"

'Result Set
Cmd.Parameters.Add(New OracleParameter("io_cursor",
OracleType.Cursor)).Direction = ParameterDirection.Output

Try
oda = New OracleDataAdapter(Cmd)
oda.Fill(ds)

Me.dgr.DataSource = ds.Tables(0)
Me.dgr.DataBind()
Catch xcp
xcpMessage = xcp.Message.ToString
End Try
conn.Close()
End Sub
 
Oh brother. As I sat looking over my carefully crafted posting, the cause of
the the problem stared me right back in the face. The names of the result
set in the VB code and in the Stored Proc are different. Sorry to bother.

Kasabaarde Sumta
 
Kasabaarde Sumta said:
We are connecting to an Oracle 8.1.7 database using
System.Data.OracleClient.
Executing SELECT statements is easy. Modifying data is a cinch. Calling
Stored Procedures was no problem until I tried to call a stored procedure
with parameters. Calling a stored procedure with parameters generates the
error: {ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'SP_CUSTREV_BY_INDVD' ORA-06550: line 1, column 7:
PL/SQL: Statement ignored}.

In an attempt to fix the problem, I have been through the posts at many
newsgroups including this one, but to no avail. I have no doubt that I am
missing something obvious, but would appreciate any help that can be had.

KS

----------Oracle Stored Proc Header-----------------
PROCEDURE SP_CUSTREV_BY_INDVD
(
p_Indvd_Type IN varchar2,
p_Indvd_Id IN varchar2,
p_OrderNme IN varchar2,
p_SortType IN varchar2,
p_DecisionCode IN varchar2,
outlist OUT types.ref_cursor
)
IS
Click to expand...

. . .
'Result Set
Cmd.Parameters.Add(New OracleParameter("io_cursor",
OracleType.Cursor)).Direction = ParameterDirection.Output
Click to expand...

.. . .

OracleClient binds these parameters by name, so you must name your parameter
correctly. The parameter is named "outlist", so the OracleParameter must be
named the same thing.

David
 
Kasabadre, The names of the Proc in SQL ( SP_CUSTREV_BY_INDVD) is different
from the one in code(ME_SCHEMA.SP_CUSTREV_BY_INDVD). Can you just try using
SP_CUSTREV_BY_INDVD?
 
Back
Top