ORA-06550

[Guest]

Hi,

This is Ali Kazim, Web Application Developer based in Pakistan. I'm
developing a web application using ASP.net having Oracle 9i as database of
choice. I have a developed a stored procedure in Oracle to add a record in
the database table. Here is my schema:

SQL> desc tbl_inventory_cats
Name Null? Type
----------------------------------------- -------- ------------
INVENTORY_CAT_ID NOT NULL NUMBER(11)
INVENTORY_CAT NOT NULL VARCHAR2(30)
MIN_ORDER_QTY NUMBER(11)
START_DATE NOT NULL DATE
END_DATE DATE
SESSION_ID NUMBER(19)
USER_ID NUMBER(19)
LOG_ID NUMBER(19)
ITEM_TYPE_ID NOT NULL NUMBER(11)

PROCEDURE PRC_ADD_INVENTORY_CAT2(p_INVENTORY_CAT2 IN VARCHAR2,
p_MIN_ORDER_QTY2 IN NUMBER,
p_SESSION_ID2 IN NUMBER,
p_USER_ID2 IN NUMBER,
p_LOG_ID2 IN NUMBER,
p_ITEM_TYPE_ID2 IN NUMBER,
p_INVENTORY_CAT_ID2 OUT NUMBER) AS
v_INVENTORY_CATS_COUNT NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_INVENTORY_CATS_COUNT
FROM TBL_INVENTORY_CATS
WHERE INVENTORY_CAT LIKE p_INVENTORY_CAT2 AND END_DATE IS NULL;
IF v_INVENTORY_CATS_COUNT = 0 THEN
BEGIN

SELECT SEQ_INVENTORY_CAT_ID.NEXTVAL
INTO p_INVENTORY_CAT_ID2
FROM DUAL;
INSERT INTO TBL_INVENTORY_CATS
(INVENTORY_CAT_ID,
INVENTORY_CAT,
MIN_ORDER_QTY,
SESSION_ID,
USER_ID,
LOG_ID,
ITEM_TYPE_ID)
VALUES
(p_INVENTORY_CAT_ID2,
p_INVENTORY_CAT2,
p_MIN_ORDER_QTY2,
p_SESSION_ID2,
p_USER_ID2,
p_LOG_ID2,
p_ITEM_TYPE_ID2);
END;
ELSE
p_INVENTORY_CAT_ID2 := 0;
END IF;
END PRC_ADD_INVENTORY_CAT2;

I can execute this within Oracle enviornment successfully. Now I have
created a form in ASP.net and have written the following code in the .vb file
of the .aspx page. Please note I'm using Oracle Client data provedr came in
..net framework 1.1. Excerpt of my .vb file:

Imports System.Data
Imports System.Data.OracleClient
Imports System.Web.UI.WebControls

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
' If Not Page.IsPostBack Then
' End If
End Sub

Private Sub OracleConn()

Try
conn.ConnectionString = "data source=ORACLE;user
id=sts;password=sts2005;"
conn.Open()
'lblConnect.Text = "Connection Succeeded"

Catch ex As OracleException
Response.Write(ex.Message)
'Finally
'If conn.State = ConnectionState.Open Then
' conn.Close()
' End If
'conn.Dispose()
End Try

End Sub


Private Sub AddItem()

'Try
With cmd
.Connection = conn
.CommandText = "PRC_ADD_INVENTORY_CAT2"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("p_INVENTORY_CAT2", OracleType.VarChar,
30).Value = "Far" 'txtInvCat.Text
.Parameters.Add("p_MIN_ORDER_QTY2", OracleType.Number, 11).Value
= 20 'CInt(txtMinOrderQty.Text)
.Parameters.Add("p_SESSION_ID2", OracleType.Number, 19).Value =
101
.Parameters.Add("p_USER_ID2", OracleType.Number, 19).Value = 102
.Parameters.Add("p_LOG_ID2", OracleType.Number, 19).Value = 103
.Parameters.Add("p_ITEM_TYPE_ID2", OracleType.Number, 11).Value
= 5 'CInt(ddlItemType.SelectedValue) 'txtItemTypeID.Text
.Parameters.Add("p_INVENTORY_CAT_ID2", OracleType.Number,
11).Direction = ParameterDirection.Output
.ExecuteNonQuery()
End With

Now when I run the form and try to submit it I get following error:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments
in call to 'PRC_ADD_INVENTORY_CAT2' ORA-06550: line 1, column 7: PL/SQL:
Statement ignored
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

Exception Details: System.Data.OracleClient.OracleException: ORA-06550: line
1, column 7: PLS-00306: wrong number or types of arguments in call to
'PRC_ADD_INVENTORY_CAT2' ORA-06550: line 1, column 7: PL/SQL: Statement
ignored

I have rechecked the number and types of arguments and they are fine as u
can see from the code above. What seems to be the problem. I've tried looking
for solution over the Internet but couldn't find it. Plz help.

Thanks in advance.

Ali Kazim Gardezi.