Oracle Stored Procedure PLS-00306: wrong number or types of arguments

debugcn Published at Dev

narue1992

I am receiving the following oracle errors:

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

I read over this error and people say that if you are passing a different value type to the stored procedure type can make this error but it doesn't seem to be the case for me.

enter image description here

Most of my coding is almost identical to what I have(and they worked). Only difference with this one is I am using a Boolean as well so I am not sure if that is causing the issues too.

Protected Sub BtnNew_Click(sender As Object, e As EventArgs) Handles BtnNew.Click
    Dim conn As OleDbConnection = New OleDbConnection("Provider=""*******"";user id=" & strUserID & ";data source=" & strDatabase & ";password=" & strPssWd)

    If Page.IsValid Then

        If CatText.Text <> "" Then


            Dim ClassifiedStr As New OleDbCommand

            ClassifiedStr.CommandType = CommandType.StoredProcedure
            'name of stored procedure = 'insert_category'
            ClassifiedStr.CommandText = "insert_category"
            ClassifiedStr.Connection = conn

            'Must be organized based on Stored Procedure
            ClassifiedStr.Parameters.Add("val_category", OleDbType.VarChar, 40).Value = CatText.Text
            conn.Open()

            'Makes sure the Category doesn't already exist****************
            Dim myParm As OleDbParameter = ClassifiedStr.Parameters.Add("val_newcat", OleDbType.Boolean)
            myParm.Direction = ParameterDirection.Output

            ClassifiedStr.ExecuteNonQuery()
            'myParm needs Execute
            Dim standardid As Integer = myParm.Value
            '***********************************************

            If standardid = False Then

                conn.Close()

                Response.Write("<script language=""javascript"">alert('Thanks! Record Has Been Added.');</script>")
                Response.Redirect("DisplayCategories.aspx")
            Else
                Response.Write("<script language=""javascript"">alert('Sorry! Record Already Exist.');</script>")
                Exit Sub
            End If

        Else
            Response.Write("<script language=""javascript"">alert('You must insert a record.');</script>")
            Exit Sub
        End If

    End If

End Sub

Stored Procedure

CREATE OR REPLACE PROCEDURE insert_category
(val_category        TABLENAME.Category%type, 
 val_newcat out boolean) 

as num_catid number;  

begin   

select category_seq.nextval into num_catid from dual;   

  INSERT INTO TABLENAME  
   (select num_catid, val_category from TABLENAME WHERE Category != val_Category);  

commit; 

  val_newcat := SQL%FOUND; 

end;

narue1992

Updates that fixed issue:

Dim ClassifiedStr As New OleDbCommand

ClassifiedStr.CommandType = CommandType.StoredProcedure
'name of stored procedure = 'insert_category'
ClassifiedStr.CommandText = "insert_category"
ClassifiedStr.Connection = conn

'Must be organized based on Stored Procedure
ClassifiedStr.Parameters.Add("val_category", OleDbType.VarChar, 40).Value = CatText.Text
conn.Open()

'Makes sure the Category doesn't already exist
Dim myParm As OleDbParameter = ClassifiedStr.Parameters.Add("val_newcat", OleDbType.VarChar, 10)
myParm.Direction = ParameterDirection.Output

ClassifiedStr.ExecuteNonQuery()
'myParm needs Execute before declared

Dim standardid As String = myParm.Value

'***********************************************

If standardid = "TRUE" Then

    conn.Close()
    etc....

Stored Procedure:

(val_category        t_category.Category%type,     
val_newcat out varchar2)     

as num_catid number;      

begin       

select newCategory_seq.nextval into num_catid from dual;         

INSERT INTO TABLENAME      
select num_catid, val_category from dual WHERE not exists (select * from TABLENAME where Category = val_Category);      

val_newcat := case SQL%FOUND when TRUE then 'TRUE' else 'FALSE' end;    

commit;     

end;

Collected from the Internet

Please contact debug[email protected] to delete if infringement.