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

Question

1.00/5 (1 vote)

See more:

What I Have written,

create or replace package body PMS_HRMS_LINK AS

 Procedure Proc_HRMS_Employee(P_EMP_ID          in Varchar2,
                             P_EMP_FNAME        in Varchar2,
                             P_EMP_TYPE         in Varchar2,
                             P_Joining_Date     in Varchar2,
                             P_Birth_Date       in Varchar2,
                             P_H_ID             in number,
                             P_CREATED_USER_ID  in Varchar2,
                             P_Op_flg           in Varchar2,
                             P_Status           in Varchar2,
                             P_P_ID             in number,
                             P_DML_Msg          out varchar2) As
  
    Var_Emp_Exist number := 0;

  Begin
    
  
  Select count(*)
      into Var_Emp_Exist
      from td_hrms_employee p
     where p.employee_id = P_EMP_ID
       AND p.dateof_retirement = To_Date(P_Birth_Date, 'dd-mm-yyyy');
  
IF P_Status = 'Create' then     
    /*for checking the existance of value*/
    
      IF Var_Emp_Exist > 0 Then
        P_DML_Msg := 'Employee Already Added:';
        Return;
      End if;
      
    /*for insertion*/ 
       
   insert into td_hrms_employee
         (P_ID,
           EMPLOYEE_ID,
           EMPLOYEE_FNAME,
           EMPLOYEEMENT_TYPE,
           DATEOF_JOINING,
           CREATED_USER_ID,
           OP_FLAG,
           DATEOF_RETIREMENT,
           H_ID)       
   VALUES
          ('1',
          P_EMP_ID,
          P_EMP_FNAME,
          P_EMP_TYPE,
          To_Date(P_Joining_Date, 'DD/MM/YYYY'),
          P_CREATED_USER_ID,
          P_Op_flg,
          To_Date(P_Birth_Date, 'DD/MM/YYYY'),
          P_H_ID);
   
   P_DML_Msg := 'Record Entered Successfully';

    
    /*for delete*/
    
    else
    
      update td_hrms_employee d
         set d.dml_status_flag    = 2,
             d.active_flag        = 'Y',
             d.modified_timestamp = sysdate,
             d.modified_user_id   = P_CREATED_USER_ID
         where d.employee_id=P_EMP_ID;
     
     P_DML_Msg := 'Record Deleted Successfully';
    


End if;
                
End Proc_HRMS_Employee;
    
  
   
End PMS_HRMS_LINK;

What I have tried:

.net page;

public string WebApi(BO_HRMSDataProcess p1)
       {
           string msg = "";
           OracleConnection con = new OracleConnection(ConnStr);
           try
           {
               //CultureInfo provider = CultureInfo.InvariantCulture;
               con.Open();
               OracleCommand cmd = new OracleCommand();
               cmd.Connection = con;
               cmd.CommandText = "PMS_HRMS_LINK.Proc_HRMS_Employee";
               cmd.CommandType = CommandType.StoredProcedure;



               cmd.Parameters.Add("P_EMP_ID", OracleDbType.Varchar2).Value = p1.EMP_NO;
               cmd.Parameters.Add("P_EMP_FNAME", OracleDbType.Varchar2).Value = p1.FULL_NAME;
               cmd.Parameters.Add("P_EMP_TYPE", OracleDbType.Varchar2).Value = p1.EMPLOYMENT_STATUS;
               cmd.Parameters.Add("P_Joining_Date", OracleDbType.Varchar2).Value = p1.JOINING_DATE;
               cmd.Parameters.Add("P_Birth_Date", OracleDbType.Varchar2).Value = p1.DATE_OF_BIRTH;
               cmd.Parameters.Add("P_H_ID", OracleDbType.Int32).Value = p1.H_ID;
               cmd.Parameters.Add("P_CREATED_USER_ID", OracleDbType.Varchar2).Value = p1.CREATED_USER_ID;
               cmd.Parameters.Add("P_Op_flg", OracleDbType.Varchar2).Value = p1.op1_flag;
               cmd.Parameters.Add("P_DML_Msg", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;
               cmd.Parameters.Add("P_Status", OracleDbType.Varchar2).Value = p1.Status;
               cmd.ExecuteNonQuery();
               msg = cmd.Parameters["P_DML_Msg"].Value.ToString();
               con.Close();
               return msg;
           }
           catch (Exception ex)
           {
               //return "Unable Get Employee Data";
               throw (ex);
           }

       }

Posted 18-Apr-22 23:26pm

Mousumi Sarkar

Updated 20-Apr-22 0:20am

Add a Solution

3 solutions

Solution 1

You add a P_DML_Msg parameter twice when you call it, but don't add a "P_P_ID" parameter. And the second one is added after it's been executed!

Posted 18-Apr-22 23:36pm

OriginalGriff

Updated 18-Apr-22 23:37pm

v2

Solution 2

After removing that

P_P_ID

when I am trying to insert the data into the database table it's not working !!

Posted 19-Apr-22 0:46am

Mousumi Sarkar

Comments

Richard Deeming 19-Apr-22 12:44pm

Your comment is not a "solution" to your question.

And "not working" tells us precisely nothing.

Click the green "Improve question" link and update your question to show the relevant parts of your code, the full details of any errors, and a clear description of what you have tried and where you are stuck.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mousumi Sarkar · Accepted Answer · 2022-04-20T00:20:00

Thank you for the reply it's working fine now ...

create or replace package body PMS_HRMS_LINK AS

 Procedure Proc_HRMS_Employee(P_EMP_ID          in Varchar2,
                             P_EMP_FNAME        in Varchar2,
                             P_EMP_TYPE         in Varchar2,
                             P_Joining_Date     in Varchar2,
                             P_Birth_Date       in Varchar2,
                             P_H_ID             in number,
                             P_P_ID             in number,
                             P_CREATED_USER_ID  in Varchar2,
                             P_Op_flg           in Varchar2,
                             P_Status           in Varchar2,
                             P_DML_Msg          out varchar2) As
  
    Var_Emp_Exist number := 0;

  Begin
    
  
  Select count(*)
      into Var_Emp_Exist
      from td_hrms_employee p
     where p.employee_id = P_EMP_ID
       AND p.DATEOF_BIRTH = To_Date(P_Birth_Date, 'dd-mm-yyyy');
  
IF P_Status = 'Create' then     
    /*for checking the existance of value*/
    
      IF Var_Emp_Exist > 0 Then
        P_DML_Msg := 'Employee Already Added:';
        Return;
      End if;
      
    /*for insertion*/ 
 if Var_Emp_Exist = 0 Then
        
     insert into td_hrms_employee
           (P_ID,
           EMPLOYEE_ID,
           EMPLOYEE_FNAME,
           EMPLOYEEMENT_TYPE,
           DATEOF_JOINING,
           CREATED_USER_ID,
           OP_FLAG,
           DATEOF_BIRTH,
           H_ID)       
       VALUES
          (P_P_ID,
          P_EMP_ID,
          P_EMP_FNAME,
          P_EMP_TYPE,
          To_Date(P_Joining_Date, 'DD/MM/YYYY'),
          P_CREATED_USER_ID,
          P_Op_flg,
          To_Date(P_Birth_Date, 'DD/MM/YYYY'),
          P_H_ID);
   
   P_DML_Msg := 'Record Entered Successfully';
 
 End if;
    
    /*for delete*/
    
/*    else*/
    
     /* update td_hrms_employee d
         set d.dml_status_flag    = 2,
             d.active_flag        = 'Y',
             d.modified_timestamp = sysdate,
             d.modified_user_id   = P_CREATED_USER_ID
         where d.employee_id=P_EMP_ID;*/
     
/*     P_DML_Msg := 'Recorded Successfully';*/
    


End if;
                
End Proc_HRMS_Employee;
    
  
   
End PMS_HRMS_LINK;

Stream responseStream = response.GetResponseStream();
            using (var reader = new StreamReader(responseStream))
            {
                string responseText = reader.ReadToEnd();
                
                    var jsonResult = JsonConvert.DeserializeObject(responseText).ToString();
                    JObject _jsonResponse = JObject.Parse(jsonResult);
                    string EMP_NO = Convert.ToString(_jsonResponse["Table"][0]["EMP_NO"]);
                    string FULL_NAME = Convert.ToString(_jsonResponse["Table"][0]["FULL_NAME"]);
                    string EMPLOYMENT_STATUS = Convert.ToString(_jsonResponse["Table"][0]["EMPLOYMENT_STATUS"]);
                    string JOINING_DATE = Convert.ToString(_jsonResponse["Table"][0]["JOINING_DATE"]);
                    string DATE_OF_BIRTH = Convert.ToString(_jsonResponse["Table"][0]["DATE_OF_BIRTH"]);

                    data.EMP_NO = EMP_NO;
                    data.FULL_NAME = FULL_NAME;
                    data.DATE_OF_BIRTH = DATE_OF_BIRTH;
                    data.JOINING_DATE = JOINING_DATE;
                    data.EMPLOYMENT_STATUS = EMPLOYMENT_STATUS;
                    string Cuid = Session["Uname"].ToString();
                    data.CREATED_USER_ID = Cuid;
                    data.MODIFIED_USER_ID = Cuid;
                    data.H_ID = HID;
                    data.op1_flag = "I";
                    data.P_ID = int.Parse(pID1[i].ToString());
                    data.Status = ViewState["status"].ToString();
                    BL_HRMSDataProcess obj = new BL_HRMSDataProcess();
                    string status = obj.WebApi(data);

                    ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('" + status + "'); ", true);
                
                   {
                        DataSet ds;
                        //ds = obj.SelectEnteredData(EMP_NO);
                        ds = obj.SelectEnteredData(pID1[i].ToString());
                        GridEmpID.DataSource = ds;
                        if (ds.Tables[0].Rows.Count == 0)
                        {
                            ClientScript.RegisterClientScriptBlock(this.GetType(), "", "alert('No Record Found');", true);
                        }
                        GridEmpID.DataBind();
                   }

            }