locked
Problem in reading data from Oracle Database RRS feed

  • Question

  • Hi,

    I am using C# DataReader. In my application, if I open an oracle connection and then from enterprise manager modify the table structure.

    and then try to read data from that table then it throws ORA-01007 exception saying variable not in select list.

    If I restart my application then this error does not occur, and I can read the data.

    Is there any way so that I do not have to restart my application after the table structure modification

    Regards
    Nitin Jain
    • Moved by Harry Zhu Monday, April 20, 2009 6:06 AM I'm moving the thread relating to reading oracle table from c# forum to ADO.NET Data Providers, you will get better and quicker responses from the experts there
    Thursday, April 16, 2009 5:06 AM

Answers

  • In general, we do not recommend modifying the table structure while there are applications that use it. This is due to various table metadata cache that might be stored by Oracle's modules as well as other intermediate modules to speed up performance.

    Only if you have to support table modifications in production, try these tips:
    * create a new OracleConnection instance when you detect table modifications. This might help or might not help, we are not familiar with Oracle programming internface (OCI) internals. To enforce all connections in pool to be closed, make sure to call OracleConnection.ClearPool(connection string) or ClearAllPools().
    * have an external process to monitor table structure changes. When such detected, this process restarts all client processes.


    Postings are provided "As Is" with no warranties and confer no rights.
    Tuesday, April 21, 2009 9:43 PM
  • What version of the .NetFx are you using?  The ClearPool method in OracleConnection is only in version 2.0 and up.  If you are using NDP1.1 or NDP1.1SP1; this was introduced in NDP2.0.
    The ClearPool method is a client side operation and it does not affect the Oracle server you are using.
    See link for more details: http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleconnection.clearpool(VS.80).aspx

    HTH.
    Kathy Lu -MSFT
    -Postings are provided "As Is" with no warranties and confer no rights.
    Wednesday, April 22, 2009 5:14 PM

All replies

  • In general, we do not recommend modifying the table structure while there are applications that use it. This is due to various table metadata cache that might be stored by Oracle's modules as well as other intermediate modules to speed up performance.

    Only if you have to support table modifications in production, try these tips:
    * create a new OracleConnection instance when you detect table modifications. This might help or might not help, we are not familiar with Oracle programming internface (OCI) internals. To enforce all connections in pool to be closed, make sure to call OracleConnection.ClearPool(connection string) or ClearAllPools().
    * have an external process to monitor table structure changes. When such detected, this process restarts all client processes.


    Postings are provided "As Is" with no warranties and confer no rights.
    Tuesday, April 21, 2009 9:43 PM
  • Hi,

    There is no ClearPool method in OracleConnection class. I am using Oracle 9i.

    Wednesday, April 22, 2009 9:32 AM
  • What version of the .NetFx are you using?  The ClearPool method in OracleConnection is only in version 2.0 and up.  If you are using NDP1.1 or NDP1.1SP1; this was introduced in NDP2.0.
    The ClearPool method is a client side operation and it does not affect the Oracle server you are using.
    See link for more details: http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleconnection.clearpool(VS.80).aspx

    HTH.
    Kathy Lu -MSFT
    -Postings are provided "As Is" with no warranties and confer no rights.
    Wednesday, April 22, 2009 5:14 PM
  • Hi,

    I am using .NetFx 3.5

    Regards
    Nitin Jian
    Wednesday, May 27, 2009 11:32 AM
  • I just gonna Say: Thankuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu  <3 This really helped me!!!
    Monday, February 5, 2018 9:07 PM