This discussion is archived
9 Replies Latest reply: Mar 26, 2013 10:27 AM by SMerrill8 RSS

VS2010 errs ORA-00942 and ORA-00904

SMerrill8 Newbie
Currently Being Moderated
Platform: Windows XP 2002 SP3, VS2010

After making connections to Oracle in the Server Explorer Data Connections node +(so I can use the VS2010 tools for Oracle)+, the following two errors occur spontaneously after a compile of a Solution:

ORA-00942: table or view does not exist

ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier

I seem to get one pair of these popups per connection. When I remove the connection(s), the errors go away. I have been forced to use Oracle SQL Developer instead.

Do you have any clue?
  • 1. Re: VS2010 errs ORA-00942 and ORA-00904
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    SMerrill8 wrote:
    Platform: Windows XP 2002 SP3, VS2010

    After making connections to Oracle in the Server Explorer Data Connections node +(so I can use the VS2010 tools for Oracle)+, the following two errors occur spontaneously after a compile of a Solution:

    ORA-00942: table or view does not exist

    ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier

    I seem to get one pair of these popups per connection. When I remove the connection(s), the errors go away. I have been forced to use Oracle SQL Developer instead.

    Do you have any clue?
    What my be happening is that your DBA has been very restrictive on some tables and packages that are usually available to normal Oracle user accounts. Our tools need to get metadata about various database objects including (in this case) Java stored procedures.

    What we can do is have you run a trace so I can see the SQL being executed with errors. Then, after I let you know, you can tell your DBA what privileges you don't have, and I can check with our developers to see if hiding some of the nodes in Server explorer via the options pages or connection filters could prevent this.

    To create the trace do the following:
    1) Close Visual Studio and all applications that are connected to Oracle
    2) In the same Oracle Home as the ODT install, please find the SQLNET.ORA file
    3) In that file, add a line:
    TRACE_LEVEL_CLIENT=16
    4) Start Visual Studio
    5) Reproduce the errors as efficiently as possible (with no extra steps)
    6) Close Visual Studio immediately
    7) Search for the newly created SQLNET.TRC file
    8) Zip the file and email to christian(dot)shay(at)oracle(dot)com
    9) Remove the TRACE_LEVEL_CLIENT entry in SQLNET.ORA
    10) Post here to let me know you emailed it.
  • 2. Re: VS2010 errs ORA-00942 and ORA-00904
    SMerrill8 Newbie
    Currently Being Moderated
    we do not use the sqlnet.ora file, only the tnsnames.ora file.

    If I do not specify a persistent password, the login dialog comes up only when I check in the project to TFS.
    That is, when I execute the Check In command, I get the two login dialogs which I cancel, then the Check In dialog box appears.

    Also I notice that if I have a connection to our two Oracle servers, the connection is named "Oracle Data Provider for .NET"; but the version is different on the two connections. One is 10.2.1.0, and the other is 9.2.0.6.0.
  • 3. Re: VS2010 errs ORA-00942 and ORA-00904
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    SMerrill8 wrote:
    Also I notice that if I have a connection to our two Oracle servers, the connection is named "Oracle Data Provider for .NET"; but the version is different on the two connections. One is 10.2.1.0, and the other is 9.2.0.6.0.
    Just create a file named SQLNET.ORA and put it in the same directory as your TNSNAMES.ORA then follow those instructions. If you have more than one TNSNAMES.ORA you want the one in the Oracle Home where Oracle Developer Tools is installed.

    Is that the database version you are referring to, or the client version? (I assume db version)

    What version of ODP.NET and Oracle Developer Tools are you using?

    Not sure the login dialogs matter much since you are getting ORA errors.

    The sqlnet trace will be useful I think.
  • 4. Re: VS2010 errs ORA-00942 and ORA-00904
    SMerrill8 Newbie
    Currently Being Moderated
    These are the server versions. The odp.net version is 4.112.3.0. The ODP version is 11.2 Release 5 Production (11.2.0.3.0).


    The login dialogs do not matter, except that they tell me exactly when the failing query is executed.

    I have followed your instructions, and the entire C: drive does not contain the trace file, with the exception of "OracleMTSRecoveryService(1044).trc" and "ora_5756_5216.trc" which is huge.
    I e-mailed it to you per your instructions.
  • 5. Re: VS2010 errs ORA-00942 and ORA-00904
    SMerrill8 Newbie
    Currently Being Moderated
    Christian,

    Eventually, I realized that there were a few folders which did not exist on my machine.
    In three iterations, the logs asked for the folder %oracle_home%\log\diag\clients to be created.

    Then after the last, it created the folder %oracle_home%\log\diag\clients \user_myusername\host_332527008_80\trace\ .
    It placed the SQLNET.LOG file in that new folder with this content:

    -----
    Thu Mar 21 15:18:21 2013
    Create Relation ADR_CONTROL
    Create Relation ADR_INVALIDATION
    Create Relation INC_METER_IMPT_DEF
    Create Relation INC_METER_PK_IMPTS
    -----

    I think that will be what you are looking for. There is also a .TRC and a .TRM within it which I will e-mail you.

    ~ SMerrill8
  • 6. Re: VS2010 errs ORA-00942 and ORA-00904
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    SMerrill8 wrote:
    Christian,

    Eventually, I realized that there were a few folders which did not exist on my machine.
    In three iterations, the logs asked for the folder %oracle_home%\log\diag\clients to be created.

    Then after the last, it created the folder %oracle_home%\log\diag\clients \user_myusername\host_332527008_80\trace\ .
    It placed the SQLNET.LOG file in that new folder with this content:
    Hi,

    I never received any trace file from you. If you sent it, can you make sure you zipped it? Also make sure that there are no executables or dlls being included in what you are zipping up.

    Please post here to the forums once you have sent it.

    Thanks,
    Christian
  • 7. Re: VS2010 errs ORA-00942 and ORA-00904
    SMerrill8 Newbie
    Currently Being Moderated
    OK, I posted it to you - please check your e-mail for a link.
  • 8. Re: VS2010 errs ORA-00942 and ORA-00904
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    Thanks, I received it.

    A few things to try:

    1) The trace shows that the problem is occurring in the 9.2 database. I am a little worried about that as it is very old and out of support. Can you try removing all references to that DB from Visual Studio and see if you can reproduce this against your 10.2 DB only?

    2) The errors are occurring in schemas associated with XML Database and Java. So perhaps we can try to hide those nodes in server explorer and see if it helps. In the Tool->Option->ODT->Data Connections, uncheck "XML Database" and "Java Classes".

    Let me know what you find out. I have sent the trace to our developers to have a look.
  • 9. Re: VS2010 errs ORA-00942 and ORA-00904
    SMerrill8 Newbie
    Currently Being Moderated
    I believe switching off those two checkboxes has resolved my issue. I will keep an eye out for this behavior. It does not seem to error even though the old database connection is not removed.

    Thanks for your attention to this matter, Christian.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points