Re: Problem with stored Proceedure

On Jun 28, 3:08 pm, Problematic coder <gnews..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Got it!

Thanks for pointing me in the right direction

altered SP like so, in case this helps anyone in the future:

CREATE OR REPLACE
PROCEDURE PROC_UPD_MYTABLE

        (
        v_USERID VARCHAR2,
        v_FIELD1 CLOB,
        v_FIELD2 CLOB,
        v_FIELD3 CLOB,
        v_APP_STATUS VARCHAR2
        )

BEGIN Received on Thu Jun 28 2007 - 18:12:25 CDT