Search This Blog

Tuesday, October 3, 2023

Webservice to call Oracle package with Array parameter in c#

 -- Create the table

CREATE TABLE TBLTEST (testID NUMBER, name VARCHAR2(50));

CREATE SEQUENCE seq_test
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

CREATE OR REPLACE PACKAGE pkgTestArrayBinding
AS 
    -- Define an local scope associative array type called T_ASSOCIATIVE_ARRAY and make it as the type of input parameter
    TYPE T_ASSOCIATIVE_ARRAY IS TABLE OF VARCHAR(50) INDEX BY PLS_INTEGER;
    PROCEDURE TestArrayBinding(
        Param1 IN T_ASSOCIATIVE_ARRAY,
        Param2 IN T_ASSOCIATIVE_ARRAY);
END pkgTestArrayBinding;
/

CREATE OR REPLACE PACKAGE BODY pkgTestArrayBinding
AS
    PROCEDURE TestArrayBinding(
        Param1 IN  T_ASSOCIATIVE_ARRAY,
        Param2 IN  T_ASSOCIATIVE_ARRAY)
    AS
    BEGIN
        -- for all loop to insert them in a batch
        FORALL indx IN 1..Param1.COUNT
            INSERT INTO tblTest VALUES(seq_test.nextval, Param1(indx));

        FORALL indx IN 1..Param2.COUNT
            INSERT INTO tblTest VALUES(seq_test.nextval, Param2(indx));
    END TestArrayBinding;
END pkgTestArrayBinding;
/

c# from below
using Newtonsoft.Json;
using Oracle.ManagedDataAccess.Client;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Web.Http;
using EServices.Models;
using System;
using System.Linq;

namespace EServices.Controllers
{
    public class TestArrayParamController : ApiController
    {


        public string Post([FromBody] TestArrayParamController.inputs req)
        {
              {
                  OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["Mobile"].ConnectionString);
                {   
                    con.Open();
                    Console.WriteLine("Connected to Oracle" + con.ServerVersion);

                    // create command to run your package
                    var cmd = new OracleCommand("BEGIN pkgTestArrayBinding.TestArrayBinding(:Param1, :Param2); END;", con);

                    var param1 = cmd.Parameters.Add("Param1", OracleDbType.Varchar2);
                    var param2 = cmd.Parameters.Add("Param2", OracleDbType.Varchar2);

                    param1.Direction = ParameterDirection.Input;
                    param2.Direction = ParameterDirection.Input;

                    // Specify that we are binding PL/SQL Associative Array
                    param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                    param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

                    //// Setup the values for PL/SQL Associative Array
                    //param1.Value = new[] { "First Element", "Second Element ", "Third Element_" };
                    //param2.Value = new[] { "Fourth Element", "Fifth Element ", "Sixth Element " };

                    param1.Value = req.Arrayparam1 ;
                    param2.Value = req.Arrayparam2 ;


                    // Specify the maximum number of elements in the PL/SQL Associative Array
                    // this should be your array size of your parameter Value.
                    //param1.Size = 3;
                    //param2.Size = 3;

                    param1.Size = req.Arrayparam1.Count();  
                    param2.Size = req.Arrayparam2.Count();


                    // Setup the ArrayBindSize for each elment in the array, 
                    // it should be bigger than the original length of element to avoid truncation
                    //param1.ArrayBindSize = new[] { 13, 14, 13 };

                    List<int> ArraySizeOfChars = new List<int>();
                    foreach (var item in req.Arrayparam1)
                    {
                        ArraySizeOfChars.Add(item.Length);
                     
                    }
                    param1.ArrayBindSize = ArraySizeOfChars.ToArray();

                    List<int> ArraySizeOfChars2 = new List<int>();
                    foreach (var item in req.Arrayparam2)
                    {
                        ArraySizeOfChars2.Add(item.Length);

                    }
                    param2.ArrayBindSize = ArraySizeOfChars2.ToArray();

                    // Setup the ArrayBindSize for Param2
                    //param2.ArrayBindSize = new[] { 20, 20, 20 };
                    //param2.ArrayBindSize = new[] { req.Arrayparam2[0].Length, req.Arrayparam2[1].Length };//, req.Arrayparam2[2].Length };

                    // execute the cmd
                    cmd.ExecuteNonQuery();

                    // I am lazy to query the database table here, but you should get you data now.
                    // watch what happened to element "Third Element_"

                    // Close and Dispose OracleConnection object
                    con.Close();
                    con.Dispose();
                    //resultsOutput2.Output1 = "Hi";
                    return null;// JsonConvert.SerializeObject((object)resultsOutput2);
                }
            }
          }
          public class inputs
        {
              public string[] Arrayparam1 { get; set; }
              public string[] Arrayparam2 { get; set; }
        }

        public class Results
        {
            public string Output1 { get; set; }
        }


    }
}

testing in postman:
http://localhost:41956/api/TestArrayParam
 {
  "Arrayparam1":["First Element", "Second Element"  , "Third Element_"],
  "Arrayparam2":["Fourth Element", "Fifth Element"  , "Sixth Element "]
 }

Test in the oracle table with select * from TBLTEST;
Ref:https://docs.oracle.com/en/database/oracle/oracle-database/18/odpnt/featOraCommand.html#GUID-05A6D391-E77F-41AF-83A2-FE86A3D98872