execute stored procedure PLS-00306 error

[banks]

Hi, I am runningt the following stored proecdure and getting an error:-

Unhandled Exception: System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_ADD_USER'

This is the SP:-

CREATE OR REPLACE  PROCEDURE sp_add_user
(
	i_user_name  IN tbl_user.user_name%TYPE,
	i_password				IN tbl_user.password%TYPE
)
IS
BEGIN

INSERT INTO TBL_USER (user_name, password) VALUES(i_user_name, i_password);

END;
/
show errors
/

The type of user_name/password is varchar2.

This is the vb.net code i'm using to execute the sp:-

	Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

		' create oracle
		' create connection
		Dim cn As OracleConnection = mtsmDba.getCn
		Dim dbadpt As OracleDataAdapter = New OracleDataAdapter

		Dim addUser As OracleCommand = New OracleCommand

		'Dim user As String = "MRMAN"
		'Dim pass As String = "bignuts"

		With addUser
			.Connection = mtsmDba.getCn
			.CommandText = "sp_add_user"
			.CommandType = CommandType.StoredProcedure
			.Parameters.Add("username", OracleType.VarChar).Value() = Me.txtUserName.Text
			.Parameters.Add("password", OracleType.VarChar).Value() = Me.txtPassword.Text
		End With

		mtsmDba.openDb()

		' execute the function
		addUser.ExecuteNonQuery()
		mtsmDba.closeDb()
		addUser.Dispose()


	End Sub
End Class

I have tried to use fixed variables to pass in but i get the same error.

Any help much apreciated, i just cant seem to crack this one!

Alex

 

[Steven McDonald]

I know NOTHING of Oracle but have you tried running the sp in Oracle with test values? Thats what I do with sql sp's to make sure there are no problems with them.


Not sure bout this though:

.CommandText = "sp_add_user"

are you sure that line retrieves the sp from the connection?

Like i said, I don't know oracle but thats my guess

 

[banks]

Thanks for your reply but it was the variable names in the sp didnt match with the ones i specified in the vb code

 

[Steven McDonald]

Its always the small things that back back to haunt us

 

[Anvoy]

I've been bitten by this one. there is one alternative though, if you use
System.Data.OleDb insted of System.Data.OracleClient, then the param names dont need to be same(order should be same) . but i think Oracle client is faster than Ole db.

Anvoy.

 

[cjard]

Heh.. but the whole idea of naming things is that the order doesnt matter..


It is better to rely on the name of something, than the order of something.. orders can change, names should not.

 

[Margaret]

pls-00306

You need to use

Imports Oracle.Dataaccess.client instead of
Imports System.Data.Dataclient.

This will allow you to say

param.name,OracleDbType = OracleDbType.Varchar2

Instead of

param_name.OracleType = OracleType.VarChar

I hope this helps.

Margaret

 

[baghzaad]

Hi,

I am having a similar problem but it occurs at irregular intervals. I have a code which refreshes data every 5 secs which it does correctly. But after every few mins(10-15 approx) I get the follwoing error.

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'S_PR_MSG_STAT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Below is my calling code.....

Public Shared Function fnRunSPReturnDataTable(ByVal strStorProcName As String, ByVal arlInpParmValu As ArrayList _
, ByRef arlOutParmValu As ArrayList, ByVal intNoOfParam As Integer) As DataTable
Try
If oConnection Is Nothing Or sDataSource <> oGlobal.gsDataSource Or sDBUserId <> oGlobal.gsDBUserId _
Or sDBPassword <> oGlobal.gsDBPassword Then
If subConnectDB() = "Failure" Then
Return Nothing
End If
End If
Dim outParm As OleDbParameter
Dim inptParm As OleDbParameter
oCommand = New OleDbCommand()
arlOutParmValu.Clear()
oCommand.Parameters.Clear()
oCommand.Connection = oConnection
oCommand.CommandType = CommandType.StoredProcedure
oCommand.CommandText = strStorProcName

For ctInpParam As Integer = 0 To arlInpParmValu.Count - 1
inptParm = oCommand.Parameters.Add("inptparm" & ctInpParam, OleDbType.Variant)
inptParm.Value = arlInpParmValu(ctInpParam)
Next

For ctOutParam As Integer = 0 To intNoOfParam - 1
outParm = oCommand.Parameters.Add("opparm" & ctOutParam, OleDbType.Variant)
outParm.Direction = ParameterDirection.Output
Next


oDataAdapter = New OleDbDataAdapter(oCommand)
oDataSet = New DataSet
oDataAdapter.Fill(oDataSet)

For ctParam As Integer = 0 To oCommand.Parameters.Count - 1
If CStr(oCommand.Parameters.Item(ctParam).ParameterName).Substring(0, 6) = "opparm" Then
arlOutParmValu.Add(oCommand.Parameters.Item(ctParam).Value)
End If
Next

If oConnection.State = ConnectionState.Open Then subDisConnectDB()
Return oDataSet.Tables(0)

Catch ex As Exception
subWriteErrorToDB(gsAppName, "clsDataAccess", "fnRunSPReturnDataTable", ex.Message)
Return Nothing
End Try
End Function