Tag Archives: Oracle

Oracle: ODP.NET is the devil

Well, those were my words yesterday.
The errors Oracle was throwing up were:
ORA-06550: line X, column X: PLS-00224: object 'X' must be of type function or array to be used this way
and
ORA-06550: line X, column X: PLS-00382: expression is of wrong type
followed by
ORA-06550: line X, column X: PL/SQL: ORA-00904: : invalid identifier
No matter what I did I had these problems, these errors were just annoyingly vague and did not help at all, Oracle might as well have just said, “ERROR”.

I noticed if I had one too many parameters I got the error:
ORA-06550: line X, column X: PLS-00382: expression is of wrong type
which made no sense as they were all Varchar2 fields and the parameters were too (well, one was a PLSQLAssociativeArray).

Today I found out why I was getting such odd behaviour that I couldn’t for the life of me figure it out.
First port of call is to check your command is correct and you have the parameter names correct.
Secondly, are sure of that are you binding parameters in order or by name? The default is to bind by order (BindByName = false). This was my mistake and I needed to set:
cmd.BindByName = true;
where cmd is my OracleCommand object.

Best of luck and I hope I remember to read this post next time.

Advertisements

Oracle: Bulk insert using an array

You really don’t want to generate an insert for each row you want to insert, this will be slow to create, slow to transmit, slow to parse and slow to process. I also don’t like creating CSVs and importing these via SQL Plus. I’m an application developer, so I want to do bulk operations via ODP.NET.

Instead I use arrays, they truly are wonderful and often overlooked.
A syntax like so is really useful to remember:
DECLARE iVal INT;
BEGIN
FOR iVal IN 1..200 LOOP
INSERT INTO [TABLE] (column1, column2) VALUES (:array1[iVal], :array2[iVal]);
END LOOP;
END;

where array1 and array2 are PLSQLAssociativeArray arrays.

See also Oracle: Using PLSQLAssociativeArray with ODP.NET

Oracle: PLSQLAssociativeArray TimeStamp workaround

Oracle (at time of writing) doesn’t support an PLSQLAssociativeArray of type OracleDbType.TimeStamp.
So what you need is to use a standard string instead then convert it to a timestamp. For this you will need to set the timestamp format via:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD/MM/YYYY HH24:MI:SS'

Then in your OracleCommand CommandText you can use:

TO_TIMESTAMP(:t(i))

where t is an input OracleParameter of type OracleDbType.Varchar2 with CollectionType of OracleCollectionType.PLSQLAssociativeArray and the values are formatted as per the NLS_TIMESTAMP_FORMAT.

Oracle: Using PLSQLAssociativeArray with ODP.NET

In Oracle and ODP.NET you can create arrays (of certain types, not timestamps, see Oracle: PLSQLAssociativeArray TimeStamp workaround about that) and use them as parameters to any kind of SQL using the standard syntax.

Here’s an example for a string array:

OracleParameter p = new OracleParameter("p", OracleDbType.Int32) { Direction = ParameterDirection.Input, CollectionType = OracleCollectionType.PLSQLAssociativeArray, Value = myArray, Size = myArray.Length };

where myArray is a normal C# String[].
For an example of really making use of this for bulk inserts see Oracle: Bulk insert using an array.

See Oracle Documentation for more information

Copied for reference if link is dead:

OracleCommand cmd = new OracleCommand(
        "begin MyPack.TestVarchar2(:1, :2, :3); end;", con);
 
      OracleParameter Param1 = cmd.Parameters.Add(...);
      OracleParameter Param2 = cmd.Parameters.Add(...);
      OracleParameter Param3 = cmd.Parameters.Add(...);
 
      Param1.Direction = ParameterDirection.Input;
      Param2.Direction = ParameterDirection.InputOutput;
      Param3.Direction = ParameterDirection.Output;
 
      // Specify that we are binding PL/SQL Associative Array
      Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      Param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      Param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
 
      // Setup the values for PL/SQL Associative Array
      Param1.Value = new string[3]{"First Element",
                                   "Second Element ",
                                   "Third Element ",
      Param2.Value = new string[3]{"First Element",
                                   "Second Element ",
                                   "Third Element ",
      Param3.Value = null;
 
      // Specify the maximum number of elements in the PL/SQL Associative Array
      Param1.Size = 3;
      Param2.Size = 3;
      Param3.Size = 3;
 
      // Setup the ArrayBindSize for Param1
      Param1.ArrayBindSize = new int[3]{13, 14, 13};  
 
      // Setup the ArrayBindStatus for Param1 
      Param1.ArrayBindStatus = new OracleParameterStatus[3]{
        OracleParameterStatus.Success,
        OracleParameterStatus.Success,
        OracleParameterStatus.Success};
 
      // Setup the ArrayBindSize for Param2
      Param2.ArrayBindSize = new int[3]{20, 20, 20};
 
      // Setup the ArrayBindSize for Param3
      Param3.ArrayBindSize = new int[3]{20, 20, 20};
 
      // execute the cmd
      cmd.ExecuteNonQuery();