Click here to Skip to main content
14,932,748 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,
i have an issue with Oracle Client.
i have oracle database 10g, i have installed oracle client 12c all applications works fine, but one of the application uses below code for login, but when executing the command the two output parameters return incomplete data, they return exactly half data length.

* i have windows server 2008 r2.
* iis 7.
* Oracle Client 64 bit.
* Solution under Framework 4.

C#
cmd = new OracleCommand("<SP_NAME>", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("USERNAME", OracleType.VarChar).Value = <username>;
            cmd.Parameters.Add("PASSWORD", OracleType.VarChar).Value = <password>;

            OracleParameter p1 = new OracleParameter("LOGIN_STATUS", OracleType.Number, 1);
            p1.Direction = ParameterDirection.Output;


            OracleParameter p2 = new OracleParameter("MESSAGE", OracleType.VarChar, 4000);
            p2.Direction = ParameterDirection.Output;


            OracleParameter p3 = new OracleParameter("SES_ID", OracleType.Char, 16);
            p3.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            cmd.Parameters.Add(p3);

            OpenConnection();
            cmd.ExecuteNonQuery();

            string[] result = { "", "", "", "", "" };

            result[0] = cmd.Parameters["LOGIN_STATUS"].Value.ToString();
            result[1] = cmd.Parameters["MESSAGE"].Value.ToString();
            result[2] = cmd.Parameters["SES_ID"].Value.ToString();


This application works fine on old server.
please any advice.
Posted
Updated 10-Sep-17 22:12pm
Comments
PIEBALDconsult 16-Feb-15 10:34am
   
Maybe the fields are actually wide characters? Check the definition of the column -- it may be VARCHAR2 ( 4000 char ) which indicates (wide) characters rather than bytes.
PIEBALDconsult 16-Feb-15 11:01am
   
Also, which Provider are you using?

You should be using one from Oracle
Oracle.DataAccess.Client.OracleParameter p1 = new Oracle.DataAccess.Client.OracleParameter ( "P1", Oracle.DataAccess.Client.OracleDbType.Varchar2 , 4000 , System.Data.ParameterDirection.Output ) ;

Not the one from Microsoft -- (The types in System.Data.OracleClient are deprecated. The types remain supported in the current version of.NET Framework but will be removed in a future release. Microsoft recommends that you use a third-party Oracle provider.)
System.Data.OracleClient.OracleParameter p0 = new System.Data.OracleClient.OracleParameter ( "p0" , System.Data.OracleClient.OracleType.VarChar , 4000 , System.Data.ParameterDirection.Output ) ;
Jörgen Andersson 9-Sep-15 5:01am
   
Check the datatypes, if it should be NVarchar instead of Varchar

set
OracleParameter("MESSAGE", OracleType.Char, 4000);


and after execute
result[1] = cmd.Parameters["MESSAGE"].Value.ToString().Trim();

or install 11 client...
   
v2
Don't use the System.Data.Oracle.Client anymore (with 12c client).
You can use the Oracle.DataAccess which is part of oracle client installation
or oracle.ManagedDataAccess instead.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900