Re: Problem with stored Proceedure

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
dba_at_nospam.peasland.net
http://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 from http://www.teranews.com