Home » Infrastructure » Windows » Oracle table type passing data table from c# application (C#)
Oracle table type passing data table from c# application [message #689243] Fri, 27 October 2023 06:09 Go to next message
DorababuMeka
Messages: 11
Registered: October 2023
Junior Member
I have created the required object types and table types as follows

create or replace TYPE EMP_CSV_OBJ AS OBJECT
    (
       FirstName VARCHAR2(50),
       LastName VARCHAR2(50),
      MiddleName VARCHAR2(50)
   );
create or replace TYPE EMP_CSV_TABLE
   AS TABLE OF emp_csv_obj;
CREATE OR REPLACE PROCEDURE INSERT_EMPLOYEE(empCsv IN EMP_CSV_TABLE) AS
    BEGIN
      MERGE INTO Employees pt
      USING (select * from table(empCsv)) src
      ON (pt.FIRST_NAME = src.FIRSTNAME)
     WHEN NOT MATCHED THEN INSERT
      (pt.FIRST_NAME, pt.LAST_NAME, pt.MIDDLE_NAME)
      VALUES (src.FirstName , src.LastName , src.MIDDLENAME);
  
   -- COMMIT;
  END;
I have used the following code in my c# application

DataTable dt = new DataTable();
dt .Columns.Add("FirstName", typeof(string));
dt .Columns.Add("LastName", typeof(string));
dt .Columns.Add("MiddleName", typeof(string));
dt.Rows.Add("AALI", "THOR", "R");
dt.Rows.Add("Dereanna", "CARTER", "Michelle");

string connectionString = "myconstring";
using (OracleConnection connection = new OracleConnection(connectionString))
{
   connection.Open();
   using (OracleCommand cmd = new OracleCommand("INSERT_EMPLOYEE", connection))
   {
     cmd.CommandType = CommandType.StoredProcedure;
     OracleParameter param = new OracleParameter();
     param.OracleDbType = OracleDbType.Array;
     param.UdtTypeName = "EMP_CSV_TABLE";
     param.Value = dt; 
     cmd.Parameters.Add(param);
     cmd.ExecuteNonQuery();
   }
 }
But I am getting the exception as Invalid parameter binding. I am new to oracle integrating with c#, so can some one help me here what I am doing wrong. Is there any other way to achieve this if the method I am following is not valid. I need to parse an XML and store it to ORACLE table with out looping in C# code

[Updated on: Fri, 27 October 2023 06:35]

Report message to a moderator

Re: Oracle table type passing data table from c# application [message #689244 is a reply to message #689243] Fri, 27 October 2023 08:47 Go to previous messageGo to next message
DorababuMeka
Messages: 11
Registered: October 2023
Junior Member
I was able to achieve it with JSON

WITH sample_json AS (
 SELECT '[{"KCP_FirstName":"Test First","KCP_LastName":"Test Last","KCP_MiddleName":""}, {"KCP_FirstName":"Test First1","KCP_LastName":"Test Last1","KCP_MiddleName":""}]' AS json_data
 FROM DUAL
)
SELECT j.KCP_FirstName, j.KCP_LastName, j.KCP_MiddleName
FROM sample_json s
CROSS JOIN JSON_TABLE(s.json_data, '$[*]'
 COLUMNS (
   KCP_FirstName VARCHAR2(255) PATH '$.KCP_FirstName',
   KCP_LastName VARCHAR2(255) PATH '$.KCP_LastName',
   KCP_MiddleName VARCHAR2(255) PATH '$.KCP_MiddleName'
 )
) j;

I was able to parse the list to JSON and by using the following synax it worked

cmd.Parameters.Add("jsonData", OracleDbType.Clob).Value = json;
Re: Oracle table type passing data table from c# application [message #689245 is a reply to message #689244] Fri, 27 October 2023 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thank you for you feedback and workaround, it will help someone else with the same problem.

And maybe someone will post a solution with UDT.

Re: Oracle table type passing data table from c# application [message #689246 is a reply to message #689245] Fri, 27 October 2023 09:23 Go to previous message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

To use your initial code, I found these 2 old articles which may help you:
ODP.NET User Defined Type Implementation
How to use Oracle 11g ODP.NET UDT in an Oracle Stored Procedure's Where clause

[Updated on: Fri, 27 October 2023 09:23]

Report message to a moderator

Previous Topic: SQL Developer error on windows 10 64bit
Next Topic: Oracle.DataAccess.Client.OracleConnection issues
Goto Forum:
  


Current Time: Mon Jun 24 10:51:25 CDT 2024