How to set user-defined-type as Oracle SP input parameter in .NET
-
Hi, Pls tell me How to set user-defined-type as Oracle SP input parameter in .NET 2005 with oracle database 10g client. example : I created simple object type create or replace type TYP_address as object ( -- Attributes address VARCHAR2(500), ZIPCODE Varchar2(5) ) create table TAB_ADDRESS ( ADDRESS VARCHAR2(200), ZIPCODE VARCHAR2(5) ); create or replace procedure test_udt_sp(obj_address IN TYP_address ) is begin INSERT INTO tab_address(address,ZIPCODE) VALUES(obj_address.address,obj_address.ZIPCODE); COMMIT; end test_udt_sp; Now How i call this procedure "test_udt_sp" with what type of input parameter of obj_address. Pls help me in this. Thanks in Advance. :confused: Anil
-
Hi, Pls tell me How to set user-defined-type as Oracle SP input parameter in .NET 2005 with oracle database 10g client. example : I created simple object type create or replace type TYP_address as object ( -- Attributes address VARCHAR2(500), ZIPCODE Varchar2(5) ) create table TAB_ADDRESS ( ADDRESS VARCHAR2(200), ZIPCODE VARCHAR2(5) ); create or replace procedure test_udt_sp(obj_address IN TYP_address ) is begin INSERT INTO tab_address(address,ZIPCODE) VALUES(obj_address.address,obj_address.ZIPCODE); COMMIT; end test_udt_sp; Now How i call this procedure "test_udt_sp" with what type of input parameter of obj_address. Pls help me in this. Thanks in Advance. :confused: Anil
-
Hi, Pls tell me How to set user-defined-type as Oracle SP input parameter in .NET 2005 with oracle database 10g client. example : I created simple object type create or replace type TYP_address as object ( -- Attributes address VARCHAR2(500), ZIPCODE Varchar2(5) ) create table TAB_ADDRESS ( ADDRESS VARCHAR2(200), ZIPCODE VARCHAR2(5) ); create or replace procedure test_udt_sp(obj_address IN TYP_address ) is begin INSERT INTO tab_address(address,ZIPCODE) VALUES(obj_address.address,obj_address.ZIPCODE); COMMIT; end test_udt_sp; Now How i call this procedure "test_udt_sp" with what type of input parameter of obj_address. Pls help me in this. Thanks in Advance. :confused: Anil
Hi, i givng my code below. I created two types for input and output parameter for a functions. type get1 as object(deptno number(5))NOT FINAL; TYPE GET2 AS OBJECT(DNAME VARCHAR2(14),LOC VARCHAR2(13))NOT FINAL; ///Function code : function getdata(id in get1,data out get2) return varchar2 is retval varchar(10); begin select get2(dname,loc) into data from dept where deptno = get1.deptno; retval := 'Result'; return retval; end; ///My .net code : OracleConnection con = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.2.3)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORACLE)));User Id=scott;Password=tiger"); OracleCommand cmd = new OracleCommand("getdata", con); cmd.CommandType = CommandType.StoredProcedure; GET1 objget1 = new GET1(); GET2 objget2 = new GET2(); objget1.DEPTNO = 10; GET2[] get22 = null; OracleParameter outparam = new OracleParameter(); outparam.OracleDbType = OracleDbType.Object; outparam.Direction = ParameterDirection.Output; outparam.ParameterName = "data"; outparam.UdtTypeName = "GET2"; outparam.Value = objget2; OracleParameter inparam = new OracleParameter(); inparam.OracleDbType = OracleDbType.Object; inparam.Direction = ParameterDirection.Input; inparam.ParameterName = "id"; inparam.UdtTypeName = "GET1"; inparam.Value = 10; con.Open(); cmd.ExecuteNonQuery(); when i'm executing the below error is coming. "ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'GET_DATA_NEW'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored" Pls help me.
modified on Tuesday, February 24, 2009 1:59 AM