Devart Forums

rmontoya

Thank you.

rmontoya

Is there a way to construct a valid OracleParameter when the list is empty?

            if (myIds.Count > 0)
            {
                OracleParameter param = new OracleParameter("P_My_ID", OracleDbType.Integer, ParameterDirection.InputOutput);
                param.ArrayLength = myIds.Count;
                param.Value = myIds.ToArray();
                return param;
            }
            else
            {
                // Can a parameter be constructed for an empty collection?
            }

Or are you saying that I need to create some dummy value and then handle on DB side?

Code: Select all

            if (myIds.Count == 0)
                myIds.Add(-1); // Test for this value and treat as empty on the DB side

            OracleParameter param = new OracleParameter("P_My_ID", OracleDbType.Integer, ParameterDirection.InputOutput);
            param.ArrayLength = myIds.Count;
            param.Value = myIds.ToArray();
            return param;

rmontoya

Ok, it's happening when the list is empty.

Change:

Code: Select all

List myIds = new List {1,2};

to:

Code: Select all

List myIds = new List();

In our scenario, we are building the list from another source so it can be empty. How can I specify a valid parameter for an empty list?

rmontoya

Hi mineevev-

If the DevArt exception was remotable it would require the DevArt assemblies to be on the client machine. A better approach would be to catch the DevArt exceptions on the remote side and then wrap them in a custom remotable exception that can be shipped with the client. The constructor of the remotable exception could then take the properties you are interested in:

Code: Select all

public DatabaseException(string source, string helpLink, int number, string message, Exception innerException)
            : base(message, innerException)

Catch and wrap:

Code: Select all

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (OracleException oe)
                {
                    // Wrap the OracleException details in a remotable exception
                    throw new DatabaseException(oe.Source, oe.HelpLink, oe.Code, oe.Message, oe.InnerException);
                }

This example is for an OracleException but you could modify the constructor to take any properties you wanted for the Linq exception.

rmontoya

I want to pass associative arrays as Input parameters and have followed the instructions in the documentation as best I could but am not having success. I have the following package:

Code: Select all

  CREATE OR REPLACE PACKAGE PA_My_Package is
... 

  TYPE IntTyp IS TABLE OF int
        INDEX BY BINARY_INTEGER;

...

  function fn_Save(P_My_ID in IntTyp) return int;
...

I then have the following helper method which prepares my parameter:

Code: Select all

        protected IDbDataParameter GetMyIdParameter(List myIds)
        {
            OracleParameter param = new OracleParameter("P_My_ID", OracleDbType.Integer, ParameterDirection.Input);
            param.ArrayLength = myIds.Count;
            param.Value = myIds.ToArray();

            return param;
        }

When I execute I get the following error:

System.InvalidCastException: Unable to cast object of type 'System.Int32[]' to type 'System.IConvertible'.
at System.Convert.ToInt32(Object value)
at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Object A_2, Byte[] A_3, Hashtable A_4, Int32 A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, String A_13, au A_14, Boolean& A_15)
at Devart.Data.Oracle.OracleParameter.a(ab& A_0, Boolean A_1, OracleConnection A_2, Byte[] A_3, Hashtable A_4, au A_5, Boolean& A_6, Int32 A_7)
at Devart.Data.Oracle.OracleCommand.a(y A_0, Int32 A_1, OracleParameterCollection A_2, au A_3, Boolean& A_4)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean no...).

rmontoya

Are there any workarounds?

rmontoya

I get the following error:

OCI-22303: type ""."DOC_IN_PAGES_REC" not found

My types are defined within a package:

Code: Select all

  CREATE OR REPLACE PACKAGE PA_MYPACKAGE is

...

  type doc_in_pages_rec is record
      (alias          varchar2(20),
       doc_type       varchar2(5),
       doc_xml        sys.xmltype);
       
  type doc_in_pages_tab is table of doc_in_pages_rec
      index by binary_integer;
	  
  function fn_Save(p_documents in doc_in_pages_tab) return int;	  
	  
...
  end PA_MYPACKAGE;

Is there a way to obtain a reference to the record and table types within a package?

I tried to qualify the name but it didn't work either:

Code: Select all

OracleType recordType = OracleType.GetObjectType("PA_MYPACKAGE.doc_in_pages_rec", conn);

rmontoya

I have the following types defined:

Code: Select all

  type doc_in_pages_rec is record
      (alias          varchar2(20),
       doc_type       varchar2(5),
       doc_xml        sys.xmltype);

  type doc_in_pages_tab is table of doc_in_pages_rec
      index by binary_integer;

Then I have the following package function:

Code: Select all

function fn_Save(p_documents in doc_in_pages_tab) return int;

Would someone please provide an example of how I could create the appropriate parameter and populate it with a few rows in C# and pass into the function? All the examples I see in the documentation show how to pass in arrays of a single type (string, int, etc) but I'd like to know how to pass in a table with a Record type composed of various types as above.

Thank you

Search found 8 matches