locked
Access Oracle 10g remote dataBase from Asp.net c#.net 2008 RRS feed

  • Question

  • User1624600859 posted

    Dear all,

    I want to access a stored procedure that is in oracle 10g place remotely from my local machine

    Connection string being used in

     OleDbConnection myConnection = new OleDbConnection("Server=xxx.xxx.x.x;Provider=OraOLEDB.Oracle.1;User ID=test;password=test;Data Source=tstdb;Persist Security Info=False");

    error The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.

    details i have about server and db are 

    objOracleCommand = new OleDbCommand("PROC_CHECK", myConnection);
    objOracleCommand.CommandType = CommandType.StoredProcedure;
    objOracleCommand.Parameters.Add("id", OleDbType.VarChar).Value = s;

    OleDbDataReader dr = objOracleCommand.ExecuteReader();
    DataTable dt = new DataTable();
    dt.Load(dr);

    Server IP : xxx.xxx.x.x

    DB Name : 

    Username : 

    Password : 

    Procedure Name :ProcdeureName(IN var,OUT var)

    Response : YES

    Tuesday, April 8, 2014 4:04 AM

Answers

  • User1624600859 posted

    hi smrinov

    issue was i dont have  permission  to access the server yesterday

    which i have now

    now getting

    ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

    connectionstring  tried

    string oradb="Data Source=serverip;User Id=dnd;Password=dnd";

    string AnyName = "(DESCRIPTION =" +
    "(ADDRESS = (PROTOCOL = TCP)(HOST =serverip)(PORT = 1521))" +
    "(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = serverip)))";
    string conStr = "Data Source=" + AnyName + ";user ID=dnd;password=dnd";


    OracleConnection myConnection = new OracleConnection(oradb);

    myConnection.open();


    test

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 9, 2014 4:50 AM
  • User1508394307 posted

    In the dialog window try to specify the database name, e.g.

    192.168.9.140/tstdb

    instead of just

    192.168.9.140

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 9, 2014 5:30 AM
  • User1624600859 posted

    Smirnov thanks for all your help

    for other i am copying the code below for help 

    string oradb = "Data Source=(DESCRIPTION="
    + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=serverip)(PORT=1521)))"
    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=remoteDatabaseName)));"
    + "User Id=Username;Password=Password;";

    //so the above connetionstring covers all the remote db server access details  

    id,password,ipofserver,dbname and port which is default


    OracleConnection myConnection = new OracleConnection(oradb);
    myConnection.Open();

    OracleCommand objOracleCommand = new OracleCommand();
    objOracleCommand.Connection = myConnection;
    objOracleCommand.CommandText = "Storeprocedurename";
    objOracleCommand.CommandType = CommandType.StoredProcedure;
    objOracleCommand.Parameters.Add("param1", Convert.ToDecimal(s));                    param1 IN parameter
    objOracleCommand.Parameters.Add("param2", OracleDbType.Varchar2, 3);         param2 OUT parameter
    objOracleCommand.Parameters["param2"].Direction = ParameterDirection.Output;
    objOracleCommand.ExecuteNonQuery();
    string str = Convert.ToString(objOracleCommand.Parameters["param2"].Value);

    {

    now do what ever you want to
    }
    myConnection.Close();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 9, 2014 7:52 AM

All replies

  • User1508394307 posted

    The error says that it cannot find Oracle Data Access Components (ODAC).

    You need to install

    64-bit Oracle Data Access Components (ODAC) Downloads:http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
    32-bit Oracle Data Access Components (ODAC) Downloads:http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html

    If this is already done, then it might be that you need to re-register the component

    Steps to fix
    ----------------------------------

    This is an authentication problem which can be resolved as follows:

    - Open Explorer and select the ORACLE_HOME directory, e.g. c:\Oracle\Ora92 Be sure that you have Tools - Folder Options.. - View
    - Use simple file sharing unchecked Right click on the directory and look at its properties.

    Go to the Security tab and select Authenticated Users.

    Unclick and then re-click the Read & Execute permission under the Allow column.

    Click the advanced button and ensure that the Authenticated Users entry allows Read & Execute on This Folder,subfolders and files.

    Click OK and then Apply.

    Reboot the machine and all should be OK.fails,

    If this fails, try :

    Open a command prompt in administrator mode

    cd \oracle\product\11.2.0\client_64\BIN
    c:\Windows\system32\regsvr32.exe OraOLEDB11.dll

    (Note, the path and dll name could be different, depends on your setup).

    Tuesday, April 8, 2014 4:16 AM
  • User1624600859 posted

    ok after installing 

    using Oracle.DataAccess.Client;

    OracleConnection myConnection = new OracleConnection("User Id=dnd;Password=dnd;Data Source=192.168.9.140;");

    what would be the connectionstring its still not connecting using the above connection string

    i am  using oracleconnection and oraclecommand classes

    server and oracle db details are

    Server IP : xxx.xxx.x.x

    DB Name : 

    Username : 

    Password : 

    Procedure Name :ProcdeureName(IN var,OUT var)

    Response : YES

    Tuesday, April 8, 2014 7:14 AM
  • User1508394307 posted

    What is the issue now, do you get a new error? Did you try to use any other tool to connect the database?

    The format of the connection string is correct, see http://www.connectionstrings.com/oracle/ 

    Tuesday, April 8, 2014 8:16 AM
  • User1624600859 posted

    i am using this

    OracleConnection myConnection = new OracleConnection("User Id=xxx;Password=xxx;Data Source=ip address;");

    error getting on myconnection.open()

    ORA-12170: TNS:Connect timeout occurred

    the oracle 10 g db is on remote server(linux) in order to use particular db i have to mention server also but it doesnt accepts it 

    Tuesday, April 8, 2014 8:53 AM
  • User1624600859 posted

    i also tried

    string AnyName = "(DESCRIPTION =" +
    "(ADDRESS = (PROTOCOL = TCP)(HOST = ipaddressof server)(PORT = 1521))" +
    "(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = db name)))";

    string conStr = "Data Source=" + AnyName + ";user ID=dnd;password=dnd";

    OracleConnection myConnection = new OracleConnection(conStr);

    this error getting

    ORA-12170: TNS:Connect timeout occurred

    Tuesday, April 8, 2014 9:07 AM
  • User1508394307 posted

    Did you try to connect to it through SQLPlus, SQL Developer etc. ? 

    Tuesday, April 8, 2014 9:33 AM
  • User1624600859 posted

    well dear i am new to this oracle world via asp.net so havnt tried the above..if there is any link 

    or help that is appreciable

     

    Tuesday, April 8, 2014 9:38 AM
  • User1508394307 posted

    There could be different reasons for connection problems 

    - server is down
    - firewall blocks connection
    - your workstation is not allowed to connect
    - timeout is set too low

    and many others.

    When I asked if you tried to connect through any other tool I wanted to make sure that the server is up and connection string (namely sever, port, user id and password) are correct and you are able to connect to the server from your local machine. If this works, then we will know that this is not because of connection string but because of anything else. So, I would suggest you either use sql+ or vs.net and make sure that connection works.

    See here for more details 

    http://docs.oracle.com/cd/E11882_01/appdev.112/e10767/building_odt.htm 
    http://connect.lightningtools.com/default.aspx?action=ViewPosts&fid=2&tid=246

    Hope this helps.

    Tuesday, April 8, 2014 10:06 AM
  • User1624600859 posted

    Thanks smirnov for the reply..i will try this and reply

    Tuesday, April 8, 2014 10:14 AM
  • User1624600859 posted

    hi smrinov

    issue was i dont have  permission  to access the server yesterday

    which i have now

    now getting

    ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

    connectionstring  tried

    string oradb="Data Source=serverip;User Id=dnd;Password=dnd";

    string AnyName = "(DESCRIPTION =" +
    "(ADDRESS = (PROTOCOL = TCP)(HOST =serverip)(PORT = 1521))" +
    "(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = serverip)))";
    string conStr = "Data Source=" + AnyName + ";user ID=dnd;password=dnd";


    OracleConnection myConnection = new OracleConnection(oradb);

    myConnection.open();


    test

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 9, 2014 4:50 AM
  • User1508394307 posted

    In the dialog window try to specify the database name, e.g.

    192.168.9.140/tstdb

    instead of just

    192.168.9.140

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 9, 2014 5:30 AM
  • User1624600859 posted

    dear smirnov first of all thanks for all your help it worked

    i was able to connect the db 

    now problem is how to access SP

    data is not coming the same procedure is being accessed by other developers in java

    in .net how to get that and what is alternate db type for number in OracleDbType(not contains number)

    procedure details

    IN    @ IN_MOBILE number,OUT    @ OUT_STATUS 

    code

    objOracleCommand = new OracleCommand("PROC_CHECK_DND", myConnection);


    //objOracleCommand = new OracleCommand("select count(1) from TBL_DND_MIS_DUMMY", myConnection);  works
    objOracleCommand.CommandType = CommandType.StoredProcedure;
    objOracleCommand.Parameters.Add("IN_MOBILE",OracleDbType.Double).Value = Convert.ToDouble(s);
    objOracleCommand.Parameters.Add("OUT_STATUS", OracleDbType.Varchar2,3);
    objOracleCommand.Parameters["OUT_STATUS"].Direction = ParameterDirection.Output;

    OracleDataReader dr = objOracleCommand.ExecuteReader();
    if (dr.HasRows)

    {

    not comes here

    }

    Wednesday, April 9, 2014 6:45 AM
  • User1624600859 posted

    Smirnov thanks for all your help

    for other i am copying the code below for help 

    string oradb = "Data Source=(DESCRIPTION="
    + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=serverip)(PORT=1521)))"
    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=remoteDatabaseName)));"
    + "User Id=Username;Password=Password;";

    //so the above connetionstring covers all the remote db server access details  

    id,password,ipofserver,dbname and port which is default


    OracleConnection myConnection = new OracleConnection(oradb);
    myConnection.Open();

    OracleCommand objOracleCommand = new OracleCommand();
    objOracleCommand.Connection = myConnection;
    objOracleCommand.CommandText = "Storeprocedurename";
    objOracleCommand.CommandType = CommandType.StoredProcedure;
    objOracleCommand.Parameters.Add("param1", Convert.ToDecimal(s));                    param1 IN parameter
    objOracleCommand.Parameters.Add("param2", OracleDbType.Varchar2, 3);         param2 OUT parameter
    objOracleCommand.Parameters["param2"].Direction = ParameterDirection.Output;
    objOracleCommand.ExecuteNonQuery();
    string str = Convert.ToString(objOracleCommand.Parameters["param2"].Value);

    {

    now do what ever you want to
    }
    myConnection.Close();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 9, 2014 7:52 AM