ORA-06550

[AKG]

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_c ats
Name Null? Type
----------------------------------------- -------- ------------
INVENTORY_CAT_I D 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_INVENTO RY_CAT2(p_INVEN TORY_CAT2 IN VARCHAR2,
p_MIN_ORDER_QTY 2 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_CAT S_COUNT NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_INVENTORY_CAT S_COUNT
FROM TBL_INVENTORY_C ATS
WHERE INVENTORY_CAT LIKE p_INVENTORY_CAT 2 AND END_DATE IS NULL;
IF v_INVENTORY_CAT S_COUNT = 0 THEN
BEGIN

SELECT SEQ_INVENTORY_C AT_ID.NEXTVAL
INTO p_INVENTORY_CAT _ID2
FROM DUAL;
INSERT INTO TBL_INVENTORY_C ATS
(INVENTORY_CAT_ ID,
INVENTORY_CAT,
MIN_ORDER_QTY,
SESSION_ID,
USER_ID,
LOG_ID,
ITEM_TYPE_ID)
VALUES
(p_INVENTORY_CA T_ID2,
p_INVENTORY_CAT 2,
p_MIN_ORDER_QTY 2,
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_INVENTO RY_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.Ora cleClient
Imports System.Web.UI.W ebControls

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArg s) 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.Connection String = "data source=ORACLE;u ser
id=sts;password =sts2005;"
conn.Open()
'lblConnect.Tex t = "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_INVENT ORY_CAT2"
.CommandType = CommandType.Sto redProcedure
.Parameters.Add ("p_INVENTORY_C AT2", OracleType.VarC har,
30).Value = "Far" 'txtInvCat.Text
.Parameters.Add ("p_MIN_ORDER_Q TY2", OracleType.Numb er, 11).Value
= 20 'CInt(txtMinOrd erQty.Text)
.Parameters.Add ("p_SESSION_ID2 ", OracleType.Numb er, 19).Value =
101
.Parameters.Add ("p_USER_ID2 ", OracleType.Numb er, 19).Value = 102
.Parameters.Add ("p_LOG_ID2" , OracleType.Numb er, 19).Value = 103
.Parameters.Add ("p_ITEM_TYPE_I D2", OracleType.Numb er, 11).Value
= 5 'CInt(ddlItemTy pe.SelectedValu e) 'txtItemTypeID. Text
.Parameters.Add ("p_INVENTORY_C AT_ID2", OracleType.Numb er,
11).Direction = ParameterDirect ion.Output
.ExecuteNonQuer y()
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_INVENT ORY_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.Ora cleClient.Oracl eException: ORA-06550: line
1, column 7: PLS-00306: wrong number or types of arguments in call to
'PRC_ADD_INVENT ORY_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.