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

[Guest]

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.

[Guest]

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]

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

[Guest]

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?

[Guest · Accepted solution]

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.

[johndaigle]

Hi.

Glad you made progress. I don't have the expertise to address the database tables and views question you have. It doesn't mean it is not a valid question, but I have never heard anyone bring this up before.

The only thing I'm aware of in terms of scheduling can be found on the Settings panel of the Web Admin application.

Under the Database setting, you can schedule a time when you would like the server to delete all data over a certain span of time. This is to save space on your server.

Also, under Search settings you can schedule a time for documents to be rescanned by the search engine. This is helpful when you have a lot of PDFs, Word docs or other non-HTML documents that you want end-users to be able to bring up in search results. Because this search indexing can put pressure on the server, these actions are usually scheduled during off-peak times, such as a little after midnight or on the weekend.

John Daigle

Adobe Certified RoboHelp and Captivate Instructor

Evergreen, Colorado

www.showmethedemo.com