How to call procedure of UDT OracleObject

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
dqrest
Posts: 32
Joined: Tue 15 Sep 2015 06:01

How to call procedure of UDT OracleObject

Post by dqrest » Wed 17 Feb 2021 06:43

Hello!
I have UDT OracleObject with the following procedure

Code: Select all

member procedure set_item(pitem t_variant_named) is
    xindex integer;
  begin
    check_items;
    xindex := index_of(pitem.name);
    if xindex = -1 then
      items.extend;
      items(items.last) := pitem;
    else
      items(xindex) := pitem;
    end if;
  end;
How to use ExecuteMethod in C#. Could you give me an example, please.

Now I have the following progress:

Code: Select all

        public void SetItem(TVariantNamed tVariantNamed)
        {        
            
            OracleParameterCollection xparams = new OracleParameterCollection()
            {
                new OracleParameter("pItem", OracleDbType.Object, tVariantNamed.OracleObject, ParameterDirection.Input)
            };
            var res = OracleObject.ExecuteMethod(_connection, "SET_ITEM", xparams);
            this.OracleObject = xparams["self"].Value as OracleObject;
        }
 

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: How to call procedure of UDT OracleObject

Post by Shalex » Wed 24 Feb 2021 18:19

We are processing your request and will contact you soon.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: How to call procedure of UDT OracleObject

Post by Shalex » Sat 03 Apr 2021 12:01

The bug with calling OracleObject.ExecuteMethod(), when OracleObject is created in application code without assigned attributes, is fixed in v9.14.1228: viewtopic.php?f=1&t=44776.

Here is an example:

Code: Select all

DROP TABLE F44475_TABLE;
DROP TYPE F44475_OBJECT_METHODS;
DROP TYPE F44475_OBJECT_BLOB;
DROP TYPE F44475_OBJECT;

CREATE TYPE F44475_OBJECT AS OBJECT (
  C_NUMBER NUMBER,
  C_VARCHAR VARCHAR2(30)
);
/

CREATE TYPE F44475_OBJECT_BLOB AS OBJECT (
  C_BLOB BLOB
);
/

CREATE TABLE F44475_TABLE (
  ID NUMBER GENERATED by default on null as IDENTITY PRIMARY KEY,
  C_OBJECT F44475_OBJECT,
  C_OBJECT_BLOB F44475_OBJECT_BLOB
);
--INSERT INTO F44475_TABLE (ID, C_OBJECT, C_OBJECT_BLOB) VALUES (NULL, F44475_OBJECT(55, '44$$'), F44475_OBJECT_BLOB(hextoraw('0123456789ABCDEF')));
--INSERT INTO F44475_TABLE (ID, C_OBJECT, C_OBJECT_BLOB) VALUES (NULL, F44475_OBJECT(55, '44$$'), NULL);
--INSERT INTO F44475_TABLE (ID, C_OBJECT, C_OBJECT_BLOB) VALUES (NULL, NULL, F44475_OBJECT_BLOB(hextoraw('0123456789ABCDEF')));
/

CREATE TYPE F44475_OBJECT_METHODS AS OBJECT (
  C_NOTHING NUMBER,
  MEMBER PROCEDURE INSERT_OBJECT (OBJ IN F44475_OBJECT),
  MEMBER PROCEDURE INSERT_OBJECT_BLOB (OBJ IN F44475_OBJECT_BLOB)
);
/

CREATE OR REPLACE TYPE BODY F44475_OBJECT_METHODS AS

  MEMBER PROCEDURE INSERT_OBJECT (OBJ IN F44475_OBJECT) IS
  BEGIN
    INSERT INTO F44475_TABLE (ID, C_OBJECT, C_OBJECT_BLOB) VALUES (NULL, OBJ, NULL);
  END;

  MEMBER PROCEDURE INSERT_OBJECT_BLOB (OBJ IN F44475_OBJECT_BLOB) IS
  BEGIN
    INSERT INTO F44475_TABLE (ID, C_OBJECT, C_OBJECT_BLOB) VALUES (NULL, NULL, OBJ);
  END;
END;
/

--DECLARE
--   r1 F44475_OBJECT_METHODS;
--BEGIN
-- r1 := F44475_OBJECT_METHODS(1);
-- r1.INSERT_OBJECT(F44475_OBJECT(55, '44$$'));
--END;

Code: Select all

      using (var _connection = new OracleConnection())
      {
        _connection.ConnectionString = "...";
        _connection.Open();

        var obj = new OracleObject("F44475_OBJECT", _connection);
        obj["C_NUMBER"] = 1;
        obj["C_VARCHAR"] = "abcd";

        var obj_blob = new OracleObject("F44475_OBJECT_BLOB", _connection);
        obj_blob["C_BLOB"] = new byte[] { 0, 1, 1, 0 };

        var obj_methods = new OracleObject("F44475_OBJECT_METHODS", _connection);

        OracleParameterCollection xparams = new OracleParameterCollection()
        {
          new OracleParameter("OBJ", OracleDbType.Object, (object)obj, ParameterDirection.Input),
        };
        obj_methods.ExecuteMethod(_connection, "INSERT_OBJECT", xparams);

        OracleParameterCollection xparams_blob = new OracleParameterCollection()
        {
          new OracleParameter("OBJ", OracleDbType.Object, obj_blob, ParameterDirection.Input),
        };
        obj_methods.ExecuteMethod(_connection, "INSERT_OBJECT_BLOB", xparams_blob);
      }

Post Reply