-- 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
No comments:
Post a Comment