Re: Problem with stored Proceedure

On Jun 28, 3:08 pm, Problematic coder <gnews...@xxxxxxxxx> wrote:
On Jun 28, 1:04 pm, Brian Peasland <d...@xxxxxxxxxxxxxxxxxxx> 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...@xxxxxxxxxxxxxxxxxxxxxxx://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
)
AS

BEGIN

.

Relevant Pages