none
WCF Custom Oracle Adapter Error RRS feed

  • Question

  • Hi

    Iam trying to execute a oracle stored procedure in Oracle 11 g from Biztalk 2010.
    Iam getting the error 
    Oracle.DataAccess.Client.OracleException: ORA-00942 table or view does not exist

    The same configuration was working in my Test environment.
    Now when i move it to production it gives out error.
    I can use the command prompt to login to oracle database and view the tables without issue.

    Any idea on how to debug this issue?

    Thanks
    Monday, April 20, 2015 12:35 PM

All replies

  • The error message clearly states that the table or view does not exist.

    Could be that there are alternation from the Oracle test to production environment (such as using different schema/package names) (Remember that Schema/Package names are hardcoded in your generated Oracle Schemas and must match though out all environments).

    Morten la Cour


    • Edited by la Cour Monday, April 20, 2015 3:59 PM
    Monday, April 20, 2015 3:57 PM
  • Problem

    When starting your application, you get this error:

    ORA-00942: Table or view does not exist

    This error typically indicates you do not have sufficient privileges to access the Oracle database underlying your application.  This frequently occurs when putting an application that runs properly on your development machine into production for the first time. 

    Solution

    In many cases, the owner of the database of the created application does not exist in the production environment.  This owner name should be created in the production environment for the application to run.

    To change the database schema name:

    Step 1:  Change the schema name in all of the table binding files.

    In your application’s ..\DataAccess folder is a set of XML files containing schema information for each table and database view used by your application.  Update these XML files to replace the old database schema name with the new (production) database schema name.

    Step 2:  Change the schema name in your application’s Web.config file.

    Step 3:  Rebuild your application.  This regenerates your application’s code files, SQL queries, and database stored procedures to use the new database schema name.

    Step 4:  Ensure that your app has permissions to access the schema.

    Refer: http://www.ironspeed.com/Designer/3.2.3/WebHelp/Part_VI/ORA_00942_Table_or_view_does_not_exist.htm


    Please mark as answer or vote as helpful if my reply does

    Monday, April 20, 2015 4:06 PM
    Moderator
  • As the error message suggest either the view doesn't exist or you don't have sufficient access.

    Also, check the schemas generated for all the environment, they will have the environment name, database and table/view name in the schema.

    If that's the case you will have to come up with a plan to adjust for all the scenarios.

    I ended up using Dynamic Send Port where the outgoing message was created in MessageAssignment shape.

    This allowed me to totally fabricate the output message, also use Passthrough pipeline.

    Regarding permissions, the user of Host instance should have required permissions on Oracle tables/views.


    Thanks,
    Prashant
    ----------------------------------------
    Please mark this post accordingly if it answers your query or is helpful.

    Monday, April 20, 2015 5:11 PM
  • Thanks for the reply.

    I have checked the privileges in the Oracle DB it is same as in UAT.

    Also as  mentioned earlier i can access the table from the sqlplus command prompt.

    The names of owner, package , table , username are all same as in UAT. So I need not change anything in  the oracle adapter.

    Is there any log or trace at adapter level that will go some clue?

    Tuesday, April 21, 2015 4:26 AM
  • I did enable the tracing in the Oracle Adapter.

    Following error is recorded in the trace.

    Failed to open Microsoft.ServiceModel.Channels.Common.Channels.AdapterChannelListener`1[System.ServiceModel.Channels.IInputChannel]

    <TraceIdentifier>http://msdn.microsoft.com/en-US/library/System.ServiceModel.CommunicationObjectOpenFailed.aspx</TraceIdentifier><Description>Failed to open Microsoft.BizTalk.Adapter.Wcf.Runtime.BtsServiceHost

    Can Somebody provide more info on this?

    Thanks

    Thursday, April 23, 2015 12:46 PM