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);
}