ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

WARNING: The pool_config view or table was not found in schema: APEX_LISTENER

Brad537152Aug 14 2014 — edited Aug 15 2014

Hi we are getting this error in an ORDS Standalone on Grizzly set up.

Aug 14, 2014 9:59:05 AM

oracle.dbtools.common.config.db.DatabasePoolConfig readPoolConfig

WARNING: The pool_config view or table was not found in schema:

APEX_LISTENER

Aug 14, 2014 9:59:05 AM oracle.dbtools.rt.web.WebErrorResponse

internalError

SEVERE: JDBCException [kind=NO_DATA]

JDBCException [kind=NO_DATA]

defaults.xml "XXXXX" denote removed for security, before post

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">

<comment>Saved on Mon Aug 04 16:25:09 EDT 2014</comment>

<entry key="cache.caching">false</entry>

<entry key="cache.directory">/tmp/apex/cache</entry>

<entry key="db.hostname">XXXXXXXXX</entry>

<entry key="db.password">XXXXXXXXXXX</entry>

<entry key="db.sid">XXXXXX</entry>

<entry key="debug.debugger">false</entry>

<entry key="debug.printDebugToScreen">false</entry>

<entry key="log.logging">false</entry>

<entry key="security.disableDefaultExclusionList">false</entry>

</properties>

Full Log

Aug 14, 2014 11:28:13 AM oracle.dbtools.standalone.Standalone execute

INFO: NOTE:

Standalone mode is designed for use in development and test environments. It is not supported for use in production environments.

Aug 14, 2014 11:28:13 AM oracle.dbtools.standalone.Standalone execute

INFO: Starting standalone Web Container in: /u00/home/oracle/ords/ords

Aug 14, 2014 11:28:14 AM oracle.dbtools.standalone.Deployer deploy

INFO: Will deploy application path = /u00/home/oracle/ords/ords/ords/WEB-INF/web.xml

Aug 14, 2014 11:28:17 AM oracle.dbtools.standalone.Deployer deploy

INFO: Deployed application path = /u00/home/oracle/ords/ords/ords/WEB-INF/web.xml

Aug 14, 2014 11:28:18 AM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder

INFO: Using configuration folder: /u00/home/oracle/ords/ords

Configuration properties for: apex

cache.caching=false

cache.directory=/tmp/apex/cache

cache.duration=days

cache.expiration=7

cache.maxEntries=500

cache.monitorInterval=60

cache.procedureNameList=

cache.type=lru

db.hostname=XXXXXXXXXXXXXXXXXXX

db.password=******

db.port=XXXXXXXXXXXXXXXXXXX

db.sid=XXXXXXXXXXXXXXXXXXX

debug.debugger=false

debug.printDebugToScreen=false

error.keepErrorMessages=true

error.maxEntries=50

jdbc.DriverType=thin

jdbc.InactivityTimeout=1800

jdbc.InitialLimit=3

jdbc.MaxConnectionReuseCount=1000

jdbc.MaxLimit=10

jdbc.MaxStatementsLimit=10

jdbc.MinLimit=1

jdbc.statementTimeout=900

log.logging=false

log.maxEntries=50

misc.compress=

misc.defaultPage=apex

misc.enableOldFOP=true

security.disableDefaultExclusionList=false

security.maxEntries=2000

db.username=APEX_PUBLIC_USER

Aug 14, 2014 11:28:26 AM oracle.dbtools.common.config.db.ConfigurationValues intValue

WARNING: *** jdbc.MaxLimit in configuration apex is using a value of 10, this setting may not be sized adequately for a production environment ***

Aug 14, 2014 11:28:26 AM oracle.dbtools.common.config.db.ConfigurationValues intValue

WARNING: *** jdbc.InitialLimit in configuration apex is using a value of 3, this setting may not be sized adequately for a production environment ***

Using JDBC driver: Oracle JDBC driver version: 11.2.0.3.0

Aug 14, 2014 11:28:28 AM oracle.dbtools.rt.web.SCListener contextInitialized

INFO: Oracle REST Data Services initialized

Oracle REST Data Services version : 2.0.8.163.10.40

Oracle REST Data Services server info: Grizzly/1.9.49

Aug 14, 2014 11:28:28 AM com.sun.grizzly.Controller logVersion

INFO: GRIZZLY0001: Starting Grizzly Framework 1.9.49 - 8/14/14 11:28 AM

Aug 14, 2014 11:28:28 AM oracle.dbtools.standalone.Standalone execute

INFO: http://localhost:8080/ords/ started.

Configuration properties for: apex_al

cache.caching=false

cache.directory=/tmp/apex/cache

cache.duration=days

cache.expiration=7

cache.maxEntries=500

cache.monitorInterval=60

cache.procedureNameList=

cache.type=lru

db.hostname=XXXXXXXXXXXXXXXXXXX

db.password=******

db.port=XXXXXXXXXXXXXXXXXXX

db.sid=BXXXXXXXXXXXXXXXXXXX

debug.debugger=false

debug.printDebugToScreen=false

error.keepErrorMessages=true

error.maxEntries=50

jdbc.DriverType=thin

jdbc.InactivityTimeout=1800

jdbc.InitialLimit=3

jdbc.MaxConnectionReuseCount=1000

jdbc.MaxLimit=10

jdbc.MaxStatementsLimit=10

jdbc.MinLimit=1

jdbc.statementTimeout=900

log.logging=false

log.maxEntries=50

misc.compress=

misc.defaultPage=apex

misc.enableOldFOP=true

security.disableDefaultExclusionList=false

security.maxEntries=2000

db.username=APEX_LISTENER

Aug 14, 2014 11:32:01 AM oracle.dbtools.common.config.db.ConfigurationValues intValue

WARNING: *** jdbc.MaxLimit in configuration apex_al is using a value of 10, this setting may not be sized adequately for a production environment ***

Aug 14, 2014 11:32:01 AM oracle.dbtools.common.config.db.ConfigurationValues intValue

WARNING: *** jdbc.InitialLimit in configuration apex_al is using a value of 3, this setting may not be sized adequately for a production environment ***

Aug 14, 2014 11:32:03 AM oracle.dbtools.common.config.db.DatabasePoolConfig readPoolConfig

WARNING: The pool_config view or table was not found in schema: APEX_LISTENER

Aug 14, 2014 11:40:49 AM oracle.dbtools.common.config.db.DatabasePoolConfig readPoolConfig

WARNING: The pool_config view or table was not found in schema: APEX_LISTENER

Aug 14, 2014 11:40:50 AM oracle.dbtools.rt.web.WebErrorResponse internalError

SEVERE: JDBCException [kind=NO_DATA]

JDBCException [kind=NO_DATA]

at oracle.dbtools.common.jdbc.JDBCException.wrap(JDBCException.java:88)

at oracle.dbtools.common.jdbc.JDBCQueryProvider.query(JDBCQueryProvider.java:63)

at oracle.dbtools.common.jdbc.JDBCQueryProvider.query(JDBCQueryProvider.java:38)

at oracle.dbtools.rt.jdbc.entity.JDBCTenantDispatcher.tenant(JDBCTenantDispatcher.java:98)

at oracle.dbtools.rt.jdbc.entity.TenantDispatcherBase.target(TenantDispatcherBase.java:71)

at oracle.dbtools.rt.jdbc.entity.TenantDispatcherBase.target(TenantDispatcherBase.java:37)

at oracle.dbtools.rt.web.ReTargetingDispatcher.canDispatch(ReTargetingDispatcher.java:45)

at oracle.dbtools.rt.web.RequestDispatchers.choose(RequestDispatchers.java:160)

at oracle.dbtools.rt.web.RequestDispatchers.dispatch(RequestDispatchers.java:75)

at oracle.dbtools.rt.web.ETags.checkPrecondition(ETags.java:93)

at oracle.dbtools.rt.web.HttpEndpointBase.restfulServices(HttpEndpointBase.java:426)

at oracle.dbtools.rt.web.HttpEndpointBase.service(HttpEndpointBase.java:164)

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

at com.sun.grizzly.http.servlet.ServletAdapter$FilterChainImpl.doFilter(ServletAdapter.java:1059)

at com.sun.grizzly.http.servlet.ServletAdapter$FilterChainImpl.invokeFilterChain(ServletAdapter.java:999)

at com.sun.grizzly.http.servlet.ServletAdapter.doService(ServletAdapter.java:434)

at oracle.dbtools.standalone.SecureServletAdapter.doService(SecureServletAdapter.java:91)

at com.sun.grizzly.http.servlet.ServletAdapter.service(ServletAdapter.java:379)

at com.sun.grizzly.tcp.http11.GrizzlyAdapter.service(GrizzlyAdapter.java:179)

at com.sun.grizzly.tcp.http11.GrizzlyAdapterChain.service(GrizzlyAdapterChain.java:196)

at com.sun.grizzly.tcp.http11.GrizzlyAdapter.service(GrizzlyAdapter.java:179)

at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849)

at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746)

at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045)

at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228)

at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)

at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)

at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)

at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)

at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)

at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)

at com.sun.grizzly.ContextTask.run(ContextTask.java:71)

at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)

at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)

at java.lang.Thread.run(Thread.java:662)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)

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:505)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:223)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)

at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1288)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3612)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3656)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at oracle.ucp.jdbc.proxy.PreparedStatementProxyFactory.invoke(PreparedStatementProxyFactory.java:111)

at com.sun.proxy.$Proxy44.executeQuery(Unknown Source)

at oracle.dbtools.common.jdbc.JDBCQueryImpl.resultSet(JDBCQueryImpl.java:92)

at oracle.dbtools.common.jdbc.JDBCResultRowIterator.<init>(JDBCResultRowIterator.java:29)

at oracle.dbtools.common.jdbc.JDBCQueryImpl.execute(JDBCQueryImpl.java:52)

at oracle.dbtools.common.jdbc.JDBCQueryProvider.query(JDBCQueryProvider.java:60)

... 33 more

This post has been answered by Brad537152 on Aug 15 2014

Jump to Answer

Vadim Tropashko-Oracle

Is this RDBMS bug? Because, the prompted expansion is just a dbms_utility.expand_sql_text call. And it is always ctrl-Z away if unsatisfied with the amended statement.

Jeffrey Kemp

If SQL Developer is merely calling dbms_utility.expand_sql_text that explains the behaviour.

I don't think it's an RDBMS bug, dbms_utility.expand_sql_text seems to do what it says on the tin including the VPD policy, according to the docs (https://docs.oracle.com/database/121/ARPLS/d_util.htm#ARPLS73240 ):

"The resulting query text only contains references to underlying tables and is semantically equivalent with some caveats:
If there are invoker rights functions called from any of the views, they may be called as a different user in the resulting query text if the view owner is different from the user who will eventually compile/run the expanded SQL text.
The VPD policy expands differently if there is a function supplied to generate the dynamic WHERE clause. This function would return differently, for example, if the userid caused the expansion to be different."

I think it's more likely that SQL Developer is using it inappropriately, or else this feature is not quite as useful as it might appear. When I hovered over the select statement in SQL Developer and it offered to "expand the SQL statement" the developer might expect to get a suggestion that actually makes sense for development purposes

Ideally, the dbms_utility.expand_sql_text function would accept parameters to customise its behaviour (e.g. to suppress the expansion of VPD policies) and this would then become a useful feature of SQL Developer.

Thanks

1 person found this helpful

Glen Conway

Personally, I think it is wonderful that SQL Developer shows you exactly what the database is doing with the SQL statement you send to it. I imagine that will help avoid confusion in many cases.

Jeffrey Kemp

Part of the feature is that you can click on the expansion and SQL Developer copies it into your code editor - which is almost always exactly what you don't want to do.

1 person found this helpful

Glen Conway

To each his own. Thanks for bringing up this interesting topic. I haven't used the feature (in existence for a long while: https://www.thatjeffsmith.com/archive/2014/12/sql-developer-and-a-12c-magic-trick/ ), except accidentally when mousing over statements in an editor.

My preference would be a more fully developed feature, probably explicitly available in a context menu, with an option to control whether the expansion continues through the VPD layer. But as Vadim notes, that would require changes on the RDBMS side of the house to enhance dbms_utility.expand_sql_text

Mike Kutz

Jeffrey Kemp wrote:

Part of the feature is that you can click on the expansion and SQL Developer copies it into your code editor - which is almost always exactly what you don't want to do.

It seems like the only time I would want to replace the SQL is if I'm want to expand only the SELECT * section into SELECT <list of columns>

Beyond that, I consider it a potential security risk to allow anyone to see the applied VPD/RAS rules.

Not only should the caller specifically tell EXPAND_SQL_TEXT that he/she wants to see those rule, the DBA/Security person should tell the database "yes, that person is allowed to see those rules".

But, those are my thoughts and opinions.

1 - 6

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 12 2014

Added on Aug 14 2014

1 comment

1,538 views

ORDS, SODA & JSON in the Database

WARNING: The pool_config view or table was not found in schema: APEX_LISTENER

Comments

Post Details