Skip to main content
A
Anonymous
March 28, 2013
Answered

30002 Error/ORA-00942 table or view does not exist

  • March 28, 2013
  • 1 reply
  • 3875 views

Hi,

We're running RoboServer 9 with Tomcat 7 on a Windows 2008 R2 machine. The database we are trying to connect to is an Oracle 11g database on a Linux x86_64 host. I'm connecting using the Oracle drivers via the ODBC. When I click 'test connection' from the ODBC administrator or from within the configuration application, I get a "connection successful" message. Here's the problem.

When trying to run our reporting, especially 'Frequently Accessed CSH' or 'Frequently Accessed Topics' we get the following error.

Checking the localhost.log in Tomcat, we get this:

Mar 28, 2013 10:37:41 AM org.apache.catalina.core.ApplicationContext log

SEVERE: java.sql.SQLException: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

java.sql.SQLException: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

    at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)

    at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

    at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)

    at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)

    at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)

    at adobe.robohelp.server.FlexReports.AreasReqHelpReport.SetData(AreasReqHelpReport.java:84)

    at adobe.robohelp.server.FlexReports.Report.ProcessReport(Report.java:295)

    at adobe.robohelp.server.ReportManager.doCommand(ReportManager.java:395)

    at adobe.robohelp.server.RoboHelpServer.doGet(RoboHelpServer.java:144)

    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)

    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)

    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)

    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:470)

    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)

    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)

    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)

    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)

    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)

    at java.lang.Thread.run(Unknown Source)

It looks like all the tables exist on the database--here's what we have:

TEST:

ROBOTST4> select object_name from dba_objects where object_type='TABLE' and owner='MCVICKER';

OBJECT_NAME

--------------------------------------------------------------------------------

TACTIONTYPE

TERRORTYPE

THELPPROJECT

THELPSPACEERROR

TUSER

TPROBLEM

TSOLUTIONACTION

THELPTOPICS

TUSERPREFS

TSETTING

TSESSION

TACCESSTYPE

TROBOGROUPS

TROBOUSER

TROBOUSERGROUPS

TLDAPUSERGROUPS

TGROUPPERMISSIONS

TLDAPGROUPS

TCOMMENTS

TDELETEDCOMMENTS

TUSERTOPICRATING

TAVGTOPICRATING

22 rows selected.


This topic has been closed for replies.
Correct answer

OK, more information on this topic:

I believe that VSolutionAction is a table that doesn't exist because it's actually a view. The sample database is built in Microsoft Access and calls this a 'query' instead of a view.

My guess right now is that the database user that we used to set up the ODBC connection and the configuration manager had permissions to create tables but not views.

So my question at this point is: how can I trigger Robohelp Server to set up new tables and views on the connected database?


I think we got it fixed. Here's the deal.

For some reason, the Database set up all the tables but no views initially. My DBA created the views manually using the definitions from the included access database. In Access, if you go into queries, then right click on the table, select "design view".  Right click on the design view, select 'SQL view' and you can view the SQL statements used to define the view.  You should be seeing something like this:

She slightly modified them--here's what she created:

Created the views for  MCVICKER in ROBOTST.

create or replace view mcvicker.VSolutionAction

SELECT

TSolutionAction.sa_SolutionActionID,

TSolutionAction.sa_Time,

TSolutionAction.se_SessionID,

TSolutionAction.pb_ProblemID,

TSolutionAction.sq_SequenceID,

TSolutionAction.hp_HelpProjectID,

TSolutionAction.at_ActionTypeID,

TSolutionAction.sa_SortData,

Ltrim(sa_DisplayName,255) AS sa_GroupDisplayName,

--Left$([sa_DisplayName],255) AS sa_GroupDisplayName,

TSolutionAction.sa_DisplayName,

TSolutionAction.sa_NumericData,

Ltrim(sa_StringData,255) AS sa_GroupStringData,

--Left$([sa_StringData],255) AS sa_GroupStringData,

TSolutionAction.sa_StringData,

TSolutionAction.sa_ExtraData,

TSolutionAction.sa_ProblemSolved,

TSolutionAction.ha_AreaName

FROM TSolutionAction;

create or replace view mcvicker.VHelpProject

as

SELECT

THelpProject.hp_HelpProjectID,

Ltrim(hp_Name,255) AS hp_GroupName,

--Left$([hp_Name],255) AS hp_GroupName,

THelpProject.hp_Name,

THelpProject.ha_AreaName

FROM mcvicker.THelpProject;

create or replace view mcvicker.VProblem

as

SELECT

TProblem.pb_ProblemID,

TProblem.se_SessionID,

TProblem.pb_InitiationTime,

Ltrim(pb_ReferringURL,255) AS pb_GroupReferringURL,

--Left$([pb_ReferringURL],255) AS pb_GroupReferringURL,

TProblem.pb_ReferringURL,

Ltrim(pb_ReferringParams,255) AS pb_GroupReferringParams,

--Left$([pb_ReferringParams],255) AS pb_GroupReferringParams,

TProblem.pb_ReferringParams,

Ltrim(pb_RequestedTopic,255) AS pb_GroupRequestedTopic,

--Left$([pb_RequestedTopic],255) AS pb_GroupRequestedTopic,

TProblem.pb_RequestedTopic,

TProblem.pb_ExternTransactionID,

TProblem.pb_ExternUserID,

TProblem.pb_AppData,

TProblem.pb_ProblemSolved,

TProblem.ha_AreaName

FROM mcvicker.TProblem;


Hopefully this helps someone out.

My open questions are: When does the Application actually create tables and views? Is this done when tomcat is restarted? On a schedule? It seems like when I set up the connection and leave things overnight they work. Is there some sort of scheduled task that does database maintenance? Can we make this a bit more transparent in future versions?

Thanks.

1 reply

A
Anonymous
April 11, 2013

Update-- the SQL statement that the database is returning the error on seems to be


SELECT COUNT(sa_SolutionActionID) AS countDATA FROM VSolutionAction WHERE at_ActionTypeID=30 AND pb_ProblemID = 397;

VSolutionAction is the table that doesn't exist.

I'm not seeing a VSolutionAction table in the sample database, either.

Anyone have thoughts on this? Engineers? The front line Adobe Support has been less than helpful for me.

johndaigle
johndaigle
Legend
April 11, 2013

Hi, J. McVicker and welcome to the forums.

The forums are strictly peer-to-peer volunteers and your question is a pretty esoteric one given the Oracle and Linux combination.

Normally, the best practice is to follow the Matrix combinations shown here:

http://help.adobe.com/en_US/RoboHelp/9.0/RoboEngine/WSbe3e657580667f14494180ac12b9fd08c8d-8000.html

and here

http://helpx.adobe.com/robohelp/kb/recommended-os-database-combinations-robohelp.html

To my (limited) knowledge, the databases tested were on Windows-based OSes and I don't know about the Linux support?

Do you have a support contract with Adobe? If you would send me an off-forum email to my Profile address with your contact info, I will try to forward your issue to Adobe. If you have already contacted Adobe Tech Support, please include any names or Case #s.

Thanks

John Daigle

Adobe Certified RoboHelp and Captivate Instructor

Evergreen, Colorado

www.showmethedemo.com

John DaigleAdobe Certified RoboHelp and Captivate InstructorNewport, Oregon
A
Anonymous
April 18, 2013

OK, more information on this topic:

I believe that VSolutionAction is a table that doesn't exist because it's actually a view. The sample database is built in Microsoft Access and calls this a 'query' instead of a view.

My guess right now is that the database user that we used to set up the ODBC connection and the configuration manager had permissions to create tables but not views.

So my question at this point is: how can I trigger Robohelp Server to set up new tables and views on the connected database?

Firefly Community Gallery
Adobe Firefly

Remix with Firefly Community Gallery

Thousands of free creations to fall in love with and remix in Firefly.

Explore now
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices