Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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#.
 
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"
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-Apr-13 16:24pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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>
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 587
1 Sergey Alexandrovich Kryukov 479
2 Maciej Los 305
3 Mathew Soji 195
4 BillWoodruff 170
0 OriginalGriff 7,356
1 Sergey Alexandrovich Kryukov 6,777
2 DamithSL 5,461
3 Manas Bhardwaj 4,916
4 Maciej Los 4,475


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 11 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100