请教一个有关系统表的问题

fzbk77

我这个问题估计多数人都没遇到过：

客户的服务器是Oracle,有一个主要的表空间是APP_MANAGER,由于种种原因吧，客户不希望我们直接登录APP_MANAGER,而是先登录其它用户，简单地说，就是最终的结果是，每个表不管是x5的系统表，还是自定义表，映射的真实的数据库表名都得是 APP_MANAGER.真实表名。

这样改自定义的表，没啥问题。但是把系统表也改了之后，问题来了，登陆不了。一登录就报下面的错：

Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150039; 提示: 执行sql: SELECT  r.sID, par.sID, p.sID AS p, p.sPermissionRoleID AS sPermissionRoleID, p.SPROCESS AS sProcess, p.SACTIVITYFNAME AS sActivityFName, p.SACTIVITY AS sActivity, p.SACTIONSNAMES AS sActionsNames, p.SACTIONS AS sActions, p.SSEMANTICDP AS sSemanticDP, p.SPERMISSIONKIND AS sPermissionKind, p.SDESCRIPTION AS sDescription, p.SSEQUENCE AS sSequence, p.SVALIDSTATE AS sValidState, p.VERSION AS version FROM APP_MANAGER.SA_OPRole par LEFT JOIN APP_MANAGER.SA_OPPermission p ON  (p.sPermissionRoleID = par.sID) AND (par.SVALIDSTATE = ?) AND (p.SVALIDSTATE = ?)  JOIN (APP_MANAGER.SA_OPRole r LEFT JOIN APP_MANAGER.SA_OPParentRole APP_MANAGER.SA_OPParentRole__1 ON r.sID = APP_MANAGER.SA_OPParentRole__1.sRoleID  ) ON  (par.sID = APP_MANAGER.SA_OPParentRole__1.sParentRoleID) AND ( r.sID IN (?, ?) ) , binds: [1, 1, RL-SYSTEM, RL-USER]出错
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.ksql.KSQLUtils.execSqlSelect(Unknown Source)
        ... 43 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00905: missing keyword

在这个SQL语句里，错误地把表APP_MANAGER.SA_OPParentRole的别名设置成了APP_MANAGER.SA_OPParentRole__1，貌似这种方式Oracle是不认的，所以才报这个错。

有人遇到过这个问题吗，起步的管理员有招么？还是x5系统表不能有别名。

jishuang

系统的改为 APP_MANAGER.真实表名是直接在数据库中修改的？

fzbk77

Oracle里面有不同的表空间和用户。起步系统表都建立在了APP_MANAGER用户的名下。但是由于种种原因，比如便于备份，保密等等，客户要求我们用USER用户登录。

所以，系统表就不得不写成比如说 APP_MANAGER.SP_OPPerson，这个是我在BIZ/system文件夹中，修改了data文件夹中的*.mapping.m文件中的数据库表映射关系。

结果就报错了，其实也不是大错，那个SQL语句换个普通的别名就可以了。别用"表名_1"当别名。

老哥，能否研究研究，我这个问题很急。客户那边也卡的比较死。

fzbk77

其实报错的就是这一句

LEFT JOIN   APP_MANAGER.SA_OPParentRole    APP_MANAGER.SA_OPParentRole__1

APP_MANAGER.SA_OPParentRole是表名，起步的SQL给起了个别名叫 APP_MANAGER.SA_OPParentRole__1 ，这个别名Oracle是不认的，提示为“缺少关键字”。

总之。帮帮忙吧，走投无路啊。

jishuang

把控制台详细的错误信息发一下，看一下再执行哪个action报错的

fzbk77

应该就是执行loginAction出的错。以下是全部报错信息：

2015-06-08 15:19:16 执行sql: insert into SA_Log  (sOperatingSystem, sDeviceType, sActivityName, sStatusName, sCreatorPersonName, version, SID, sProcess, sParameters, sActionName, sTypeName, sActivity, sCreateTime, sProcessName, sIP, sAction, $clientName ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, $clientValue ), binds: [WINDOWS_XP, Computer, 系统, 失败, system, 0, ADF76B1F678A480DAF31B1EB06FFFEE8, /SA/OPM/system/systemProcess, ******, 登录, 操作日志, mainActivity, 2015-06-08 15:19:16.183, 系统调用, 172.20.183.240, loginAction]失败
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3594)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3675)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
        at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStatement.java:174)
        at com.justep.system.interceptor.LogBefore.execSqlUpdate(Unknown Source)
        at com.justep.system.interceptor.LogBefore.b(Unknown Source)
        at com.justep.system.interceptor.LogBefore.c(Unknown Source)
        at com.justep.system.interceptor.LogBefore.execute(Unknown Source)
        at com.justep.system.action.EngineInterceptor.excute(Unknown Source)
        at com.justep.system.action.Engine.invokeActions(Native Method)
        at com.justep.system.action.Engine.invokeActions(Unknown Source)
        at com.justep.business.server.BusinessServer.doAction(Unknown Source)
        at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
        at com.justep.business.server.BusinessServer.excute(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:844)
        at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:844)
        at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:280)
        at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:254)
        at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:136)
        at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:341)
        at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:238)
        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3363)
        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3333)
        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
        at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
        at weblogic.servlet.provider.WlsSubjectHandle.run(WlsSubjectHandle.java:57)
        at weblogic.servlet.internal.WebAppServletContext.doSecuredExecute(WebAppServletContext.java:2220)
        at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2146)
        at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2124)
        at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1564)
        at weblogic.servlet.provider.ContainerSupportProviderImpl$WlsRequestExecutor.run(ContainerSupportProviderImpl.java:254)
        at weblogic.work.ExecuteThread.execute(ExecuteThread.java:295)
        at weblogic.work.ExecuteThread.run(ExecuteThread.java:254)
2015-06-08 15:19:16 action:<?xml version="1.0" encoding="UTF-8"?>
<action process="/SA/OPM/system/systemProcess" activity="mainActivity" name="loginAction" execute-context="" content-type="application/xml"><parameters><parameter name="pwd"><xbiz:simple xmlns:xbiz="http://www.justep.com/xbiz#" type="http://www.w3.org/2001/XMLSchema#String">e10adc3949ba59abbe56e057f20f883e</xbiz:simple></parameter><parameter name="name"><xbiz:simple xmlns:xbiz="http://www.justep.com/xbiz#" type="http://www.w3.org/2001/XMLSchema#String">system</xbiz:simple></parameter><parameter name="loginDate"><xbiz:simple xmlns:xbiz="http://www.justep.com/xbiz#" type="http://www.w3.org/2001/XMLSchema#Date">2015-06-08</xbiz:simple></parameter><parameter name="lang"><xbiz:simple xmlns:xbiz="http://www.justep.com/xbiz#" type="http://www.w3.org/2001/XMLSchema#String">zh_CN</xbiz:simple></parameter><parameter name="options"><xbiz:map xmlns:xbiz="http://www.justep.com/xbiz#"><item key="DeviceType"><xbiz:simple type="http://www.w3.org/2001/XMLSchema#String">Computer</xbiz:simple></item><item key="OperatingSystem"><xbiz:simple type="http://www.w3.org/2001/XMLSchema#String">WINDOWS_XP</xbiz:simple></item></xbiz:map></parameter><parameter name="ip"><xbiz:simple xmlns:xbiz="http://www.justep.com/xbiz#" type="http://www.w3.org/2001/XMLSchema#String">172.20.183.240</xbiz:simple></parameter></parameters></action>
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at com.justep.system.action.Engine.invokeActions(Native Method)
        at com.justep.system.action.Engine.invokeActions(Unknown Source)
        at com.justep.business.server.BusinessServer.doAction(Unknown Source)
        at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
        at com.justep.business.server.BusinessServer.excute(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:844)
        at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:844)
        at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:280)
        at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:254)
        at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:136)
        at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:341)
        at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:238)
        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3363)
        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3333)
        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
        at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
        at weblogic.servlet.provider.WlsSubjectHandle.run(WlsSubjectHandle.java:57)
        at weblogic.servlet.internal.WebAppServletContext.doSecuredExecute(WebAppServletContext.java:2220)
        at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2146)
        at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2124)
        at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1564)
        at weblogic.servlet.provider.ContainerSupportProviderImpl$WlsRequestExecutor.run(ContainerSupportProviderImpl.java:254)
        at weblogic.work.ExecuteThread.execute(ExecuteThread.java:295)
        at weblogic.work.ExecuteThread.run(ExecuteThread.java:254)

fzbk77

Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: select r,par,p.* from SA_OPRole par optional join SA_OPPermission p on p.sPermissionRoleID=par and par.sValidState = 1 and p.sValidState=1 join SA_OPRole r on par=r.sParentRole AND r IN ('RL-SYSTEM', 'RL-USER')
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.KSQL.select(Unknown Source)
        at com.justep.system.opm.Operator.a(Unknown Source)
        at com.justep.system.opm.Operator.c(Unknown Source)
        at com.justep.system.opm.Operator.a(Unknown Source)
        at com.justep.system.opm.Operator.<init>(Unknown Source)
        at com.justep.system.opm.OrgUtils.login(Native Method)
        at com.justep.system.opm.OrgUtils.login1(Unknown Source)
        at OPMProcedure.login(OPMProcedure.java:121)
        at OPMProcedure.login(OPMProcedure.java:106)
        ... 31 more
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150039; 提示: 执行sql: SELECT  r.sID, par.sID, p.sID AS p, p.sPermissionRoleID AS sPermissionRoleID, p.SPROCESS AS sProcess, p.SACTIVITYFNAME AS sActivityFName, p.SACTIVITY AS sActivity, p.SACTIONSNAMES AS sActionsNames, p.SACTIONS AS sActions, p.SSEMANTICDP AS sSemanticDP, p.SPERMISSIONKIND AS sPermissionKind, p.SDESCRIPTION AS sDescription, p.SSEQUENCE AS sSequence, p.SVALIDSTATE AS sValidState, p.VERSION AS version FROM APP_MANAGER.SA_OPRole par LEFT JOIN APP_MANAGER.SA_OPPermission p ON  (p.sPermissionRoleID = par.sID) AND (par.SVALIDSTATE = ?) AND (p.SVALIDSTATE = ?)  JOIN (APP_MANAGER.SA_OPRole r LEFT JOIN APP_MANAGER.SA_OPParentRole APP_MANAGER.SA_OPParentRole__1 ON r.sID = APP_MANAGER.SA_OPParentRole__1.sRoleID  ) ON  (par.sID = APP_MANAGER.SA_OPParentRole__1.sParentRoleID) AND ( r.sID IN (?, ?) ) , binds: [1, 1, RL-SYSTEM, RL-USER]出错
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.ksql.KSQLUtils.execSqlSelect(Unknown Source)
        ... 43 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00905: missing keyword

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3594)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3638)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
        at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:137)
        ... 44 more

fzbk77

总之，就是在Oracle数据库中，系统表对应的真实数据库表名一旦换成  “表空间名.表名” 的形式。
登录就会报错。而在Oracle数据库中，这种情况是常有的。

这个问题你们可以研究一下，看看能否加以改进。

我们则是打算用别的方法绕过去，结贴吧。

jishuang

平台在配置数据源的时候就输入了用户名和密码，所以在sql查的时候就直接找的这个用户名下的
mapping中的数据库表名不能这样修改