The call is to Oracle EBS hr_employee_api.create_employee procedure.
I can call it using PL/SQL and it creates a user.
DECLARE
p_employee_number VARCHAR2 (200);
p_person_id NUMBER;
p_assignment_id NUMBER;
p_per_object_version_number NUMBER;
p_asg_object_version_number NUMBER;
p_per_effective_start_date DATE;
p_per_effective_end_date DATE;
p_full_name VARCHAR2 (200);
p_per_comment_id NUMBER;
p_assignment_sequence NUMBER;
p_assignment_number VARCHAR2 (200);
p_name_combination_warning BOOLEAN;
p_assign_payroll_warning BOOLEAN;
p_orig_hire_warning BOOLEAN;
BEGIN
hr_employee_api.create_employee (
--p_validate => false,
p_hire_date => TO_DATE('10-DEC-2023'), -- TRUNC(SYSDATE)
p_business_group_id => 202, --this is the defalt business group for the vision edithion of the EBS
p_last_name => 'Bobsky',
p_first_name => 'Bob', -- not required
p_sex => 'M',
p_employee_number => p_employee_number,
p_person_id => p_person_id,
p_assignment_id => p_assignment_id,
p_per_object_version_number => p_per_object_version_number,
p_asg_object_version_number => p_asg_object_version_number,
p_per_effective_start_date => p_per_effective_start_date,
p_per_effective_end_date => p_per_effective_end_date,
p_full_name => p_full_name,
p_per_comment_id => p_per_comment_id,
p_assignment_sequence => p_assignment_sequence,
p_assignment_number => p_assignment_number,
p_name_combination_warning => p_name_combination_warning,
p_assign_payroll_warning => p_assign_payroll_warning,
p_orig_hire_warning => p_orig_hire_warning);
commit;
DBMS_OUTPUT.PUT_LINE (p_employee_number); --To ensure that the data inserted successfully
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(SQLERRM);
END;
/
But when I try to call the same procedure using C# (.NET Framework 4.7.2) and Oracle.ManagedDataAccess.dll (Product version 4.122.21.1) - see code below - I get an error:
ORA-03115: unsupported network datatype or representation
I tried various formats for ‘p_hire_date’ , numeric parameters but nothing helps.
Is it a bug in Oracle.ManagedDataAccess.dll?
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Net;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System.Xml.Linq;
using System.Runtime.InteropServices;
namespace ConnectorProject
{
internal class Program
{
static void Main(string[] args)
{
var ret = CallPackageFunction();
}
private static object CallPackageFunction()
{
string conStr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS=(PROTOCOL=tcp)(HOST=x.x.x.x)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=EBSDB)));User Id=apps;Password=****;";
using (OracleConnection conn = new OracleConnection(conStr))
{
using (OracleCommand cmd = conn.CreateCommand())
{
//============================================================
cmd.CommandText = "HR_EMPLOYEE_API.create_employee";
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
//=======================================================================
cmd.Parameters.Add("p_validate", OracleDbType.Boolean, ParameterDirection.Input).Value = false;
cmd.Parameters.Add("p_hire_date", OracleDbType.Date, ParameterDirection.Input).Value = DateTime.Parse("10/12/2023").Date;
cmd.Parameters.Add("p_business_group_id", OracleDbType.Int32, ParameterDirection.Input).Value = 626;
cmd.Parameters.Add("p_last_name", OracleDbType.Varchar2, ParameterDirection.Input).Value = "Bobsky";
cmd.Parameters.Add("p_first_name", OracleDbType.Varchar2, ParameterDirection.Input).Value = "Bob";
cmd.Parameters.Add("p_sex", OracleDbType.Varchar2, ParameterDirection.Input).Value = "M";
cmd.Parameters.Add("p_employee_number", OracleDbType.Varchar2, ParameterDirection.InputOutput);
cmd.Parameters.Add("p_person_id", OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add("p_assignment_id", OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add("p_per_object_version_number", OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add("p_asg_object_version_number", OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add("p_per_effective_start_date", OracleDbType.Date, ParameterDirection.Output);
cmd.Parameters.Add("p_per_effective_end_date", OracleDbType.Date, ParameterDirection.Output);
cmd.Parameters.Add("p_full_name", OracleDbType.Varchar2, ParameterDirection.Output);
cmd.Parameters.Add("p_per_comment_id", OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add("p_assignment_sequence", OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add("p_assignment_number", OracleDbType.Varchar2, ParameterDirection.Output);
cmd.Parameters.Add("p_name_combination_warning", OracleDbType.Boolean, ParameterDirection.Output);
cmd.Parameters.Add("p_assign_payroll_warning", OracleDbType.Boolean, ParameterDirection.Output);
cmd.Parameters.Add("p_orig_hire_warning", OracleDbType.Boolean, ParameterDirection.Output);
//cmd.Parameters.Add("person_type_id", OracleDbType.Long, ParameterDirection.Input).Value = 13;
//cmd.Parameters.Add("middle_names", OracleDbType.Varchar2, ParameterDirection.Input);
//cmd.Parameters.Add("person_type_id", OracleDbType.Long, ParameterDirection.Input);
//cmd.Parameters.Add("title", OracleDbType.Varchar2, ParameterDirection.Input);
//cmd.Parameters.Add("email_address", OracleDbType.Varchar2, ParameterDirection.Input);
//cmd.Parameters.Add("marital_status", OracleDbType.Varchar2, ParameterDirection.Input);
//cmd.Parameters.Add("nationality", OracleDbType.Varchar2, ParameterDirection.Input);
//cmd.Parameters.Add("national_identifier", OracleDbType.Varchar2, ParameterDirection.Input);
//cmd.Parameters.Add("date_of_birth", OracleDbType.Date, ParameterDirection.Input);
//cmd.Parameters.Add("town_of_birth", OracleDbType.Varchar2, ParameterDirection.Input);
//cmd.Parameters.Add("region_of_birth", OracleDbType.Varchar2, ParameterDirection.Input);
//cmd.Parameters.Add("country_of_birth", OracleDbType.Varchar2, ParameterDirection.Input);
//cmd.Parameters.Add("supervisor_id", OracleDbType.Long, ParameterDirection.Input);
//cmd.Parameters.Add("job_id", OracleDbType.Long, ParameterDirection.Input);
//cmd.Parameters.Add("position_id", OracleDbType.Long, ParameterDirection.Input);
//cmd.Parameters.Add("set_of_books_id", OracleDbType.Long, ParameterDirection.Input);
//cmd.Parameters.Add("termination_date", OracleDbType.Date, ParameterDirection.Input);
//cmd.Parameters.Add("asg_effective_start_date", OracleDbType.Date, ParameterDirection.Input);
//cmd.Parameters.Add("default_expense_account", OracleDbType.Varchar2, ParameterDirection.Input);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (OracleException ex)
{
// Capture specific Oracle exception details
for (int i = 0; i < ex.Errors.Count; i++)
{
Console.WriteLine("Index #" + i + "\n" +
"Error: " + ex.Errors[i].ToString() + "\n");
}
}
catch (Exception ex)
{
System.Console.WriteLine("Exception: {0}", ex.ToString());
}
finally { conn.Close(); }
}
}
return "";
}
}
}
Here is stack trace:
at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
at ConnectorProject.Program.CallPackageFunction() in C:\1Identity\Products\Oracle EBS\HR Person\ConnectorProject\Program.cs:line 91
And this is Message from a declaring method: Method may only be called on a Type for which Type.IsGenericParameter is true.
Thank you.