Please Help !Query executing in oracle but not executing through code
-
<>Hi, This is ravindra ,I created the following function and able to execute the followed query successfully in sql plus but when i try to execute the same query through code,getting error like Unspecified error Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported. Create or Replace Function convert_time(datetime in timestamp, tz1 in varchar2, tz2 in varchar2) Return timestamp with time zone as retval timestamp with time zone; Begin retval := from_tz(datetime, tz1) at time zone tz2; return retval; End; select convert_time(to_timestamp('01/01/2006 23:45','mm/dd/yyyy hh24:mi'),'US/Eastern','Turkey') from dual; the .net code as follows string conn = "Provider=MSDAORA.1;Password=;User ID=;Data Source=naradaon;Extended Properties=Server=naradaon"; OleDbConnection con = new OleDbConnection(conn); con.Open(); DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter("select from_tz(to_timestamp('01/01/2006 23:45','mm/dd/yyyy hh24:mi'),'-05:00') as hio from dual", con); da.Fill(ds); Please help me thank you <>
-
<>Hi, This is ravindra ,I created the following function and able to execute the followed query successfully in sql plus but when i try to execute the same query through code,getting error like Unspecified error Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported. Create or Replace Function convert_time(datetime in timestamp, tz1 in varchar2, tz2 in varchar2) Return timestamp with time zone as retval timestamp with time zone; Begin retval := from_tz(datetime, tz1) at time zone tz2; return retval; End; select convert_time(to_timestamp('01/01/2006 23:45','mm/dd/yyyy hh24:mi'),'US/Eastern','Turkey') from dual; the .net code as follows string conn = "Provider=MSDAORA.1;Password=;User ID=;Data Source=naradaon;Extended Properties=Server=naradaon"; OleDbConnection con = new OleDbConnection(conn); con.Open(); DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter("select from_tz(to_timestamp('01/01/2006 23:45','mm/dd/yyyy hh24:mi'),'-05:00') as hio from dual", con); da.Fill(ds); Please help me thank you <>
Try writing a parameterised query. I'm surprised this SQL works, I thought you needed select *, not just select. Could be an Oraclism tho.
Christian Graus Please read this if you don't understand the answer I've given you "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
-
<>Hi, This is ravindra ,I created the following function and able to execute the followed query successfully in sql plus but when i try to execute the same query through code,getting error like Unspecified error Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported. Create or Replace Function convert_time(datetime in timestamp, tz1 in varchar2, tz2 in varchar2) Return timestamp with time zone as retval timestamp with time zone; Begin retval := from_tz(datetime, tz1) at time zone tz2; return retval; End; select convert_time(to_timestamp('01/01/2006 23:45','mm/dd/yyyy hh24:mi'),'US/Eastern','Turkey') from dual; the .net code as follows string conn = "Provider=MSDAORA.1;Password=;User ID=;Data Source=naradaon;Extended Properties=Server=naradaon"; OleDbConnection con = new OleDbConnection(conn); con.Open(); DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter("select from_tz(to_timestamp('01/01/2006 23:45','mm/dd/yyyy hh24:mi'),'-05:00') as hio from dual", con); da.Fill(ds); Please help me thank you <>
Use
OracleDataAdapterinstead. (using Oracle.DataAccess.Client) string conn = @"...."; // make sure you use the correct one string strSel = @"select from_tz(to_timestamp('01/01/2006 23:45','mm/dd/yyyy hh24:mi'),'-05:00') as hio from dual"; OracleDataAdapter da = new OracleDataAdapter(strSel, conn); DataSet ds = new DataSet(); da.Fill(ds);SkyWalker
modified on Thursday, May 1, 2008 10:52 AM
