How to use user-defined-type input and output parameters in Oracle Function using ODP.NET
-
Hi, I'm facing the problem when i'm executing the oracle function with input and output parameters which are object type. 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.
-
Hi, I'm facing the problem when i'm executing the oracle function with input and output parameters which are object type. 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.
Try the database forum to start with. Oh and change the userid and password on the database.
scott/tigershould never be used - it is the first user anyone would try on an oracle db.
Panic, Chaos, Destruction. My work here is done.
-
Hi, I'm facing the problem when i'm executing the oracle function with input and output parameters which are object type. 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.
-
Ohh Sorry, thnxs for reply me. I forgot to paste that lines . Now i applied that applied then another errors is coming. 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; OracleCommand cmd = new OracleCommand("GETDATA", con); cmd.CommandType = CommandType.StoredProcedure; GET1 objget1 = new GET1(); GET2 objget2 = new GET2(); objget1.DEPTNO = 10; objget1.DEPTNOIsNull = false; //GET2[] get22 = null; OracleParameter retval = new OracleParameter(); retval.OracleDbType = OracleDbType.Varchar2; retval.ParameterName = "retval"; retval.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(retval); OracleParameter inparam = new OracleParameter(); inparam.OracleDbType = OracleDbType.Object; inparam.Direction = ParameterDirection.Input; inparam.ParameterName = "id"; inparam.UdtTypeName = "GET1"; inparam.Value = objget1; cmd.Parameters.Add(inparam); OracleParameter outparam = new OracleParameter(); outparam.OracleDbType = OracleDbType.Object; outparam.Direction = ParameterDirection.Output; outparam.ParameterName = "data"; outparam.UdtTypeName = "GET2"; outparam.Value = objget2; cmd.Parameters.Add(outparam); con.Open(); cmd.ExecuteNonQuery(); Error like "ORA-03113: end-of-file on communication channel\nProcess ID: 0\nSession ID: 20 Serial number: 25". pls help.
-
Ohh Sorry, thnxs for reply me. I forgot to paste that lines . Now i applied that applied then another errors is coming. 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; OracleCommand cmd = new OracleCommand("GETDATA", con); cmd.CommandType = CommandType.StoredProcedure; GET1 objget1 = new GET1(); GET2 objget2 = new GET2(); objget1.DEPTNO = 10; objget1.DEPTNOIsNull = false; //GET2[] get22 = null; OracleParameter retval = new OracleParameter(); retval.OracleDbType = OracleDbType.Varchar2; retval.ParameterName = "retval"; retval.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(retval); OracleParameter inparam = new OracleParameter(); inparam.OracleDbType = OracleDbType.Object; inparam.Direction = ParameterDirection.Input; inparam.ParameterName = "id"; inparam.UdtTypeName = "GET1"; inparam.Value = objget1; cmd.Parameters.Add(inparam); OracleParameter outparam = new OracleParameter(); outparam.OracleDbType = OracleDbType.Object; outparam.Direction = ParameterDirection.Output; outparam.ParameterName = "data"; outparam.UdtTypeName = "GET2"; outparam.Value = objget2; cmd.Parameters.Add(outparam); con.Open(); cmd.ExecuteNonQuery(); Error like "ORA-03113: end-of-file on communication channel\nProcess ID: 0\nSession ID: 20 Serial number: 25". pls help.
-
Does a simple query work ?? i suspect this to be some connection problem. i have no other clue :(
Thanxs for reply, Is any wrong in my code or functions code? then can u pls tell me a simple functions with object type parameters and c# code. anil.