Click here to Skip to main content
15,892,927 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi~
I wanna call Oracle stored procedure with array as parameters by C#.
I have searched all possible question~
The procedure can be compiled in Oracle,but can't be call from C#.

SQL
CREATE OR REPLACE PACKAGE pk 
    AS
    TYPE VAY IS ARRAY(11)OF VARCHAR2(30);
    PROCEDURE OrderReport(V_List IN VAY);
END

CREATE OR REPLACE PACKAGE BODY pk AS
    <pre lang="sql">PROCEDURE OrderReport(V_List IN VAY) AS
    BEGIN
        INSERT INTO T020 (A, B,C, D, E, F, G, H, I) VALUES(V_List(0), V_List(1), V_List(2), V_List(3), V_List(4), V_List(5), V_List(6), V_List(7), to_timestamp(V_List(8), 'yyyy-mm-dd hh24:mi:ss.ff3'));
    END;
END;


The following codes can't work, the error message were "Invalid parameter"
C#
private static void OrderReport()
        {
            string[] _List =
            {
                "A",
                "B",
                "C", 
                "D",
                "E",
                "F",= 
                "G",
                "H",
                "102.2",
                "0",
                DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") 
            };
            String ConnectionString = ConfigurationManager.ConnectionStrings["ABC"].ConnectionString;
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = conn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText="PK.ORDERREPORT";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("V_List",OracleDbType.Array);
                        cmd.Parameters[0].Value = _List;
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    Console.WriteLine("1");
                    Console.ReadLine();
                }
            }
        }
Posted

1 solution

I Got Answer
ORACLE TYPE---
TYPE "TESTVARRAY" AS VARRAY(3000) OF VARCHAR(30)

ORACLE STORED PROCEDURE---

PROCEDURE TEST(V_List IN TESTVARRAY) AS
    BEGIN
        INSERT INTO AAAA (A, B,C, D, E, F, G, H, DATETIME) VALUES(V_List(1), V_List(2), V_List(3), V_List(4), V_List(5), V_List(6), V_List(7), V_List(8), to_timestamp(V_List(9), 'yyyy-mm-dd hh24:mi:ss.ff3'));
    END;</pre>
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900