1 Reply Latest reply: Jun 23, 2014 6:59 AM by 974415 RSS

    Unable to connect SQL Developer 4.0.2 to Oracle REST Data Services 2.0.7 (Apex Listener)

    974415

      Background

       

      1. Weblogic 10.3.6.0 running on Oracle Linux 6.5 with Oracle JRockit(R) (build R28.2.3-13-149708-1.6.0_31-20120327-1523-linux-x86_64, compiled mode)

      2. Oracle Apex 4.2.1

      3. Apex Listener 1.1.3

      4. Apex and Apex Listener running on the Weblogic box above

      5. SQL Developer 4.0.2 running on Windows 7 64 bit / Java 1.7.0_55

       

      Updated Apex to 4.2.5 via p17966818_425_Generic.zip patchset. Apex environment running fine as do all applications via Listener 1.1.3

      Updated Oracle Listener to the latest version of Oracle REST Data Services 2.0.7


      Current Status

       

      Apex Applications and RESTful services are running fine.


      Problem

       

      Unable to connect SQL Developer 4.0.2 to Oracle REST Data Services 2.0.7

       

      Error message in SQL Developer - Cannot connect to DEV. HTTP/1.1 403 Target service not allowed

       

      Additional Information

       

      Steps followed -

       

      1. The oracle users APEX_LISTENER and APEX_REST_PUBLIC_USER did not exist, so I ran apex_rest_config.sql from the APEX 2.4.1 patchset whilst connected sys as sysdba and provided the same password as APEX_PUBLIC_USER for both users.

       

      2. I also ran

       

      grant insert, delete on APEX_040200.wwv_flow_rt$privilege_groups to APEX_LISTENER;

      alter session set current_schema = APEX_LISTENER;

      create or replace synonym wwv_flow_rt$privilege_groups for APEX_040200.wwv_flow_rt$privilege_groups;

       

      as per Oracle SQL Developer User's Guide Release 4.0

       

      3. copied ords.war to apex.war

      4. java -jar apex.war configdir /u01/app/oracle/admin/apex/devdomain1/

      5. java -jar apex.war

       

      Jun 5, 2014 5:15:31 PM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder

      INFO: Using configuration folder: /u01/app/oracle/admin/apex/devdomain1/apex

      Enter the name of the database server [localhost]:dbservername

      Enter the database listen port [1521]:

      Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1

      Enter the database service name:dbservicename

      Enter the database user name [APEX_PUBLIC_USER]:

      Enter the database password for APEX_PUBLIC_USER:

      Confirm password:

      Enter 1 to enter passwords for the RESTful Services database users (APEX_LISTENER,APEX_REST_PUBLIC_USER), 2 to use the same password as used for APEX_PUBLIC_USER or, 3 to skip this step [1]:2

      Jun 5, 2014 5:16:52 PM oracle.dbtools.common.config.file.ConfigurationFiles update

      INFO: Updated configurations: defaults, apex, apex_al, apex_rt

      Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2

       

      6. java -jar apex.war user adminlistener "Listener Administrator"

       

      Jun 5, 2014 5:18:52 PM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder

      INFO: Using configuration folder: /u01/app/oracle/admin/apex/devdomain1/apex

      Enter a password for user adminlistener:

      Confirm password for user adminlistener:

      Jun 6, 2014 5:19:12 PM AM oracle.dbtools.standalone.ModifyUser execute

      INFO: Created user: adminlistener in file: /u01/app/oracle/admin/apex/devdomain1/apex/credentials

       

      7. Updated the defaults.xml file

       

      Added

      <entry key="apex.security.user.roles">RESTful Services</entry>

      <entry key="apex.security.developer.roles">OAuth2 Client Developer, SQL Developer</entry>

       

      as per Oracle SQL Developer User's Guide Release 4.0

       

      Changed

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

      to

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

       

      for RESTful debugging

       

      Added

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

       

      to use OAuth2 in Non HTTPS Environment as per Oracle® REST Data Services Installation and Configuration Guide, Release 2.0

       

      8. Deleted the Admin and Manager Roles within the apex application deployment which were part of the Apex Listener 1.1.3 install

       

      9. Deployed the apex.war in weblogic

       

      10. Stopped and started the weblogic server to which apex.war was deployed

       

      11. In SQL Developer, View, RESTful Services. In the RESTful services windows hit connect, Create a new connection,

      Connection Name DEV

      Username adminlistener

      selected http protocol

      Hostname weblogicservername

      Port 7250

      Server Path /apex

      Workspace (blank)

       

      On the Authentication dialog

      Username adminlistener

      Password passwordsuppliedabove in step 6

       

      Response is a dialog box titled Authentication Failed

      message - Cannot connect to DEV. HTTP/1.1 403 Target service not allowed

       

      NOTHING UNUSUAL IN ANY OF THE WEBLOGIC LOG FILES, EXCEPT THAT IT STATES

      'Oracle REST Data Services version : 2.0.6.27.18.06' when I've installed 2.0.7.

       

      Any help much appreciated

        • 1. Re: Unable to connect SQL Developer 4.0.2 to Oracle REST Data Services 2.0.7 (Apex Listener)
          974415

          Further to the above post, I thought I'd simplify my Apex Listener install just to see if I can connect to it via SQL Developer

           

          So downloaded the latest version 2.0.8 and decided to configure and run the Listener on my pc whilst still connecting to the same database.

           

          Operating system : Window 7 Pro 64 bit

          Oracle Apex Listener 2.0.8

          Java(TM) SE Runtime Environment (build 1.7.0_25-b17)

          Java HotSpot(TM) 64-Bit Server VM (build 23.25-b01, mixed mode)

           

          Went through the configuration as above, once started I tried again to access the listener via SQL Developer. This time I received this output from the listener:

           

          INFO: Error: ORA-02291: integrity constraint (APEX_040200.WWV_FLOW_RT$APPROVALS_SGID_FK) violated - parent key not found, occurred during execution of: [CALL, insert into wwv_flow_rt$approvals (user_name,status,client_id,security_group_id,created_by,created_on,updated_by,updated_on) values(/*in:user_id*/?,/*in:status*/?,/*in:client_id*/?,/*in:tenant_id*/?,upper(/*in:user_id*/?),/*in:created*/?,upper(/*in:user_id*/?),/*in:created*/?), [tenant_id, in, class java.math.BigInteger], [client_id, in, class java.math.BigInteger], [user_id, in, class java.lang.String], [status, in, class oracle.dbtools.rt.oauth.ApprovalRequest$Status], [created, in, class java.sql.Timestamp]]with values: [adminlistener, APPROVED, 3858401374580004, -1, adminlistener, 2014-06-23 12:19:18.785, adminlistener, 2014-06-23 12:19:18.785]

           

          SQL Developer responded with an Authentication Failed titled dialog with the following displayed in the body of the dialog

           

          Cannot connect to DEV.

          <!DOCTYPE html>

          <!--[if lt IE 7 ]> <html class="ie6"> <![endif]-->

          <!--[if IE 7 ]> <html class="ie7 no-css3"> <![endif]-->

          <!--[if IE 8 ]> <html class="ie8 no-css3"> <![endif]-->

          <!--[if IE 9 ]> <html class="ie9"> <![endif]-->

          <!--[if (gt IE 9)|!(IE)]><!-->

          <html>

          <!--<![endif]-->

          <html lang="en">

          <head>

          <meta http-equiv="Content-Type" content="text/html; charset=utf-8">

          <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">

          <style type="text/css" media="screen">html,body,div,span,h3,p,ol,ul,li,header,hgroup{margin:0;padding:0;border:0;font-size:100%;font:inherit;vertical-align:baseline}header,hgroup{display:block}body{font:normal 12px/16px Arial,sans-serif;margin:0 auto;background:#6a9cda}header#xHeader{border-bottom:1px solid #8fa4c0;position:relative;z-index:10;background:none #000}header#xHeader hgroup{width:974px;margin:0 auto;position:relative;height:36px;background:none #000}header#xHeader a#uLogo{margin:8px 0;display:inline-block;font:bold 14px/20px Arial,sans-serif;color:#AAA;text-decoration:none}header#xHeader a#uLogo span.logo{color:#F00}.no-css3 div#xContentContainer div.xContent{padding-top:14px}.no-css3 div#xContentContainer div.xContent div.xMainLeft h2{margin-top:0}div#xWhiteContentContainer{margin-bottom:30px}div#xWhiteContentContainer.xContentWide{background:#FFF;margin-bottom:0}div#xWhiteContentContainer.xContentWide div.xWhiteContent{-moz-border-radius:0;-webkit-border-radius:0;border-radius:0;-moz-box-shadow:none;-webkit-box-shadow:none;box-shadow:none}div#xWhiteContentContainer div.xWhiteContent{width:974px;margin:0 auto;padding:0 0 20px 0;background:#FFF;min-height:500px;-moz-border-radius:0 4px 4px 4px;-webkit-border-radius:0 4px 4px 4px;border-radius:0 4px 4px 4px;-moz-box-shadow:0 1px 2px rgba(0,0,0,0.15);-webkit-box-shadow:0 1px 2px rgba(0,0,0,0.15);box-shadow:0 1px 2px rgba(0,0,0,0.15)}div#xContentHeaderContainer{background:#6a9cda;-moz-box-shadow:0 -1px 0 rgba(0,0,0,0.15) inset;-webkit-box-shadow:0 -1px 0 rgba(0,0,0,0.15) inset;box-shadow:0 -1px 0 rgba(0,0,0,0.15) inset}div#xContentHeaderContainer div.xContentHeader{width:974px;margin:0 auto;padding:30px 0 32px 0;position:relative;min-height:60px}div#xContentHeaderContainer div.xContentHeader h3{font:bold 24px/24px Arial,sans-serif;color:#fff;text-shadow:0 2px 1px rgba(0,0,0,0.25);margin:0 0 20px 0}div#xFooterContainer{min-height:200px;border-top:1px solid rgba(0,0,0,0.15);background:#6a9cda}body.errorPage div#xContentHeaderContainer div.xContentHeader{min-height:30px}body.errorPage div#xContentHeaderContainer div.xContentHeader h3{font:bold 30px/30px Arial,sans-serif;color:#FFF;text-shadow:0 1px 2px rgba(0,0,0,0.25);margin:0}div.errorPage p{font:normal 14px/20px Arial,sans-seri;color:#666;padding:0 0 10px 0}div.errorPage ul{list-style:disc outside;padding:0 10px 0;margin:0 0 20px 0}div.errorPage ul li{font:normal 12px/16px Arial,sans-serif;color:#666;margin:0 0 8px 10px}pre{font-family:Consolas,"Lucida Console","Courier New",Courier,monospace}

          </style>

          <script type="text/javascript" charset="utf-8">

            'header hgroup'.replace(/\w+/g,

            function(n) {

            document.createElement(n)

            })

          </script>

          <title>Internal Server Error</title>

          </head>

          <body class="errorPage">

            <header id="xHeader">

            <hgroup>

            <a id="uLogo" href="./"><span class="logo">ORACLE</span>

            REST DATA SERVICES</a>

            </hgroup>

            </header>

            <div id="xContentHeaderContainer">

            <div class="xContentHeader">

            <h3>

            <span class="statusCode">500</span> - <span

            class="statusMessage">Internal Server Error</span>

            </h3>

            </div>

            </div>

            <div id="xWhiteContentContainer" class="xContentWide">

            <div class="xWhiteContent">

            <div class="errorPage">

            <p>

            <ul class="reasons">

            </ul>

            </p>

            <p>

            <pre>Request Path passes syntax validation

          Mapping request to database pool: PoolMap [_failed=false, _lastUpdate=-1, _pattern=null, _poolName=apex, _regex=null, _type=REGEX, _workspaceIdentifier=null, _serviceName=null]

          Applied database connection info

          Attempting to process with PL&#x2F;SQL Gateway

          Not processed as PL&#x2F;SQL Gateway request

          Attempting to process as a RESTful Service

          Choosing: oracle.dbtools.rt.oauth.TokenHandler as current candidate with score: Score [handle=null, score=0, scope=SecurityConfig [constraint=secure, realm=OAUTH_CLIENT, logonConfig=NONE], originsAllowed=[], corsEnabled=true]

          Determining if request can be dispatched as a Tenanted RESTful Service

          Request path has one path segment, continuing processing

          No Tenant Principal established yet, continuing processing

          APEX_LISTENER pool exists, continuing processing

          No matching tenant found for: oauth2, cannot dispatch

          Chose oracle.dbtools.rt.oauth.TokenHandler as the final candidate with score: Score [handle=null, score=0, scope=SecurityConfig [constraint=secure, realm=OAUTH_CLIENT, logonConfig=NONE], originsAllowed=[], corsEnabled=true] for: POST oauth2&#x2F;token

          oauth2&#x2F;token authorized as: p6xycV-2QceFnFHkWyJlnA..

          </pre>

            </p>

            <p>

            <pre>WebException [statusCode=500]

            at oracle.dbtools.rt.web.WebException.webException(WebException.java:343)

            at oracle.dbtools.rt.web.WebException.internalError(WebException.java:262)

            at oracle.dbtools.rt.oauth.jdbc.JDBCOAuthDataAccess.createApproval(JDBCOAuthDataAccess.java:514)

            at oracle.dbtools.rt.oauth.jdbc.JDBCOAuthDataAccess.createApprovedRequest(JDBCOAuthDataAccess.java:181)

            at oracle.dbtools.rt.oauth.OAuthAuthorization.resourceOwnerCredentials(OAuthAuthorization.java:654)

            at oracle.dbtools.rt.oauth.OAuthAuthorization.tokenRequest(OAuthAuthorization.java:273)

            at oracle.dbtools.rt.oauth.TokenHandler.post(TokenHandler.java:44)

            at oracle.dbtools.rt.web.RequestHandler.response(RequestHandler.java:268)

            at oracle.dbtools.rt.web.RequestHandler.dispatch(RequestHandler.java:361)

            at oracle.dbtools.rt.web.RequestHandler.dispatch(RequestHandler.java:85)

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

            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(Unknown Source)

          Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (APEX_040200.WWV_FLOW_RT$APPROVALS_SGID_FK) violated - parent key not found

           

           

            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.executeForRows(T4CPreparedStatement.java:1046)

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

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

            at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3713)

            at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)

            at sun.reflect.GeneratedMethodAccessor28.invoke(Unknown Source)

            at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

            at java.lang.reflect.Method.invoke(Unknown Source)

            at oracle.ucp.jdbc.proxy.StatementProxyFactory.invoke(StatementProxyFactory.java:230)

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

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

            at oracle.dbtools.common.jdbc.JDBCInsert.execute(JDBCInsert.java:58)

            at oracle.dbtools.rt.oauth.jdbc.JDBCOAuthDataAccess.createApproval(JDBCOAuthDataAccess.java:487)

            ... 34 more

          </pre>

            </p>

            </div>

            </div>

            </div>

            <div id="xFooterContainer">

            </div>

          </body>

          </html>

           

           

          I am assuming this must be part of the problem I am seeing this the Weblogic deployment in my original post.

           

          Does anyone have any suggestions on how best to tackle this?

           

          Regards,

           

          Derek