Problem with stored Proceedure

From: Problematic coder <gnewsham_at_gmail.com>
Date: Thu, 28 Jun 2007 19:26:46 -0000
Message-ID: <1183058806.500433.189750@i13g2000prf.googlegroups.com>

Hi,
I am unsure whether to post this in Oracle or VB.NET since I am not sure where the problem lay, anyway since I don't want to get flamed for double posting i will try here first.

I am tring to do an update using a stored proceedure which I am calling from a VB.Net page, I will include all relevant code below and the error and hope somebody can spot the problem(s)

I apologize for the length of the post, but wanted to make enough info available.

Strored proc:

CREATE OR REPLACE
PROCEDURE PROC_UPD_MYTABLE AS

        v_USERID VARCHAR2(9);
        v_FIELD1 CLOB;
        v_FIELD2 CLOB;
        v_FIELD3 CLOB;
        v_APP_STATUS VARCHAR2(20);

BEGIN

        UPDATE SCHOL_APP_INT_FORM SET
        MYTABLE.FIELD1 = v_FIELD1,
        MYTABLE.FIELD2 = v_FIELD2,
        MYTABLE.FIELD3 = v_FIELD3,
        MYTABLE.APP_STATUS = v_APP_STATUS
        WHERE MYTABLE.USERID = v_USERID;

END; Now the VB Code:

Dim Oraclecon As New
System.Data.OracleClient.OracleConnection(utilities.getConnectionString)

        Dim myCMD As New Data.OracleClient.OracleCommand
        Try
            Oraclecon.Open()
            myCMD.Connection = Oraclecon
            myCMD.CommandText = "PROC_UPD_MYTABLE "
            myCMD.CommandType = Data.CommandType.StoredProcedure

            myCMD.Parameters.Add(New
System.Data.OracleClient.OracleParameter("v_USERID", _
            System.Data.OracleClient.OracleType.VarChar)).Value =
strUID
            myCMD.Parameters.Add(New
System.Data.OracleClient.OracleParameter("v_FIELD1", _
            System.Data.OracleClient.OracleType.Clob)).Value = strF1
            myCMD.Parameters.Add(New
System.Data.OracleClient.OracleParameter("v_FIELD2", _
            System.Data.OracleClient.OracleType.Clob)).Value = strF2
            myCMD.Parameters.Add(New
System.Data.OracleClient.OracleParameter("v_FIELD3", _
            System.Data.OracleClient.OracleType.Clob)).Value = strF3
            myCMD.Parameters.Add(New
System.Data.OracleClient.OracleParameter("v_APP_STATUS", _
            System.Data.OracleClient.OracleType.VarChar)).Value =

"Complete"

            myCMD.ExecuteNonQuery()
            myCMD.Dispose()
            Oraclecon.Close()

OK here is the error:

System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to
'PROC_UPD_MYTABLE' ORA-06550: line 1, column 7: PL/SQL: Statement
ignored at
System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) at
System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) at
System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQuery() at interest_form.updatePG6() in myApp.aspx.vb:line
'myCMD.ExecuteNonQuery()
Received on Thu Jun 28 2007 - 14:26:46 CDT