Re: Problem with stored Proceedure

On Jun 28, 1:04 pm, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> Problematic coder wrote:
> > 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()
>
> The error "PLS-00306: wrong number or types of arguments in call to
> 'PROC_UPD_MYTABLE'" says it all...
>
> You have defined a stored procedure that accepts zero parameters yet you
> are passign parameters to it. As such, Oracle does not think this makes
> sense... If you want to pass parameters to the stored proc, you need to
> code it so that it can accept those params.
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> d...@nospam.peasland.nethttp://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -
>
> - Show quoted text -

Thanks, that makes sense, however I am still not sure how to accomplish that?
Could somebody give an example of how to alter the SP to expect the correct arguments.
Sorry I am new at this, just trying to learn.

Thanks again Received on Thu Jun 28 2007 - 17:08:34 CDT