Oracle Database Discussions

Kudos for spawning a new thread.

Check it's not file complaints first

- where your app is hosted

- where supporting files for plugin are used

ie -both that EC2 instance? Are all files on same server?

It's probably the communication itself. I note the plugin refers to an Oracle Wallet.

I can't help you with wallet setup, I have an engineer for that.

Here is a primer on ACLs

Let's Wreck This Together...with Oracle Application Express!: Application Express, Network ACLs and Oracle Database 11gR…

And an example of all you'd need

https://gist.github.com/ajin/fdd8167799f9d307537d

On wallets: I think this is from the plugin author

Apex the Smart way: making https (webservice) requests from PL/SQL without a wallet

Hi,

just to expand a bit information already provided by Scott... Please, read this about ACLs (you can safely skip the part about sacrificing a goat, it came out that it's not necessary, which is quite surprising, I must admit). Also your DB version is very important, in some older versions including XE you'll have to setup a reverse proxy as described in the link above because SHA2 based certificates are not supported (supposedly used by both Google and FB). If you're on 12c, you should be able to setup a wallet using Oracle Wallet Manager https://docs.oracle.com/database/121/DBIMI/walet.htm#DBIMI160 without the need of reverse proxy. Here https://oracle-base.com/articles/misc/utl_http-and-ssl  you can read a detailed procedure how to get server certificates, import them to a wallet and finally verify that it works. Then make sure in APEX administration that the wallet is set properly in your instance settings.

Probably the simplest way to verify if sites are reachable, ACLs and the wallet is configured properly could be a simple select like this (assuming you have execute privileges on utl_http)

select UTL_HTTP.REQUEST('https://secured.site.com',null,'file:/u01/app/oracle/wallet','wallet_password') Output from dual;

Regards,

Pavel

I was enabling 'Allow-Control-Allow-Origin' Chrome extension. And it was returning that screen I posted above. I disabled it, and now I am getting this:

From my understanding I cannot access https://accounts.google.com from http://my-host.com due to the SSL issue. Am I correct?

Following your advice, I have run the following query:

select UTL_HTTP.REQUEST('https://accounts.google.com/ ',null,'file:C:\app\ORACLE_HOME\product\12.1.0\dbhome_1\owm\wallets\oracle', 'wallet_pwd') Output from dual;

And the site is returned successfully, so I assume the wallet is set up correctly.

I also set the same wallet path and password in APEX administration -> instance settings

Also, I run the following query:

select acl, principal from dba_network_acl_privileges;

and it returned

| /sys/acls/acl_test_for_tests.xml | APEX_050100 |
| /sys/acls/acl_test_for_tests.xml | APEX_PUBLIC_USER |
| /sys/acls/acl_test_for_tests.xml | APEX_REST_PUBLIC_USER |
| /sys/acls/acl_test_for_tests.xml | APEX_LISTENER |
| /sys/acls/acl_test_for_tests.xml | TEST |

where TEST is the workspace parsing schema.

Where else do I need to check to find the cause of this error?

I am on 12c

Yes, The DB 12c, web server on the same EC2 instance. The plugin is installed on the workspace parsing schema. From @Pavel_p reply, following the validation queries, it seems to me the wallet is set up correctly (as I replied to him). I am not sure if this is on APEX, or ords side. Is there any way to validate the settings?

Since you're able to succesfully invoke the https call to the target server, we can quite safely assume that ACLs and the wallet is set properly. I took a brief look at the plugins source and (if I'm not terribly mistaken) all the communication is happening between the two servers, e.g. the Oracle database and the target Gooogle/FB...whatever server, so it has nothing in common with your browser settings. The API call is performed purely server-side, so it seems you're digging in the wrong place. The plugin creates a SEPAPEX.S4SA_SETTINGS table where are stored all the necessary settings, so I would suggest to very carefully inspect all the settings first, especially the S4SA_API_PREFIX record. Since your ACLs and the wallet is set properly, you don't need a reverse-proxy and the author recommends "All requests are prefixed with this. use http:// to bypass the reverse proxy", however I think in your case there should be "https://".

Then I suggest to enable debugging for your application (paste apex_debug.enable(); to your application's Shared Components => Security Attributes => Initialization PL/SQL code), run it and inspect the Debug trace (eventually paste it here, maybe it will give us some clues what could be possibly wrong).

Hi Pavel, Thank you very much for trying to help. Below is what I tried:

1. I checked the table S4SA_SETTINGS, and set the value of S4SA_API_PREFIX to https:// , and I got the same error above
2. I enabled the debugging. Below is the debug trace from APEX:

0.00100
0.00100
Reset NLS settings
4
8%
05-MAY-17 01.35.12.308000 PM +00:00
0.00200
0.00000
alter session set  NLS_COMP='BINARY' NLS_SORT='BINARY' NLS_CALENDAR='GREGORIAN' NLS_TERRITORY='AMERICA' NLS_LANGUAGE='AMERICAN'
4
0%
05-MAY-17 01.35.12.309000 PM +00:00
0.00200
0.00000
...NLS: Set Decimal separator="."
4
0%
05-MAY-17 01.35.12.309000 PM +00:00
0.00200
0.00000
...NLS: Set NLS Group separator=","
4
0%
05-MAY-17 01.35.12.309000 PM +00:00
0.00200
0.00000
...NLS: Set g_nls_date_format="DD-MON-RR"
4
0%
05-MAY-17 01.35.12.309000 PM +00:00
0.00200
0.00000
...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"
4
0%
05-MAY-17 01.35.12.309000 PM +00:00
0.00200
0.00100
...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"
4
8%
05-MAY-17 01.35.12.309000 PM +00:00
0.00300
0.00000
NLS of database and client differs, characterset conversion needed
4
0%
05-MAY-17 01.35.12.310000 PM +00:00
0.00300
0.00000
...Setting session time_zone to +00:00
4
0%
05-MAY-17 01.35.12.310000 PM +00:00
0.00300
0.00000
R E Q U E S T accept GGL_LOGIN
4
0%
05-MAY-17 01.35.12.310000 PM +00:00
0.00300
0.00100
Metadata: Fetch application definition and shortcuts
4
8%
05-MAY-17 01.35.12.310000 PM +00:00
0.00400
0.00000
Reset NLS settings
4
0%
05-MAY-17 01.35.12.311000 PM +00:00
0.00400
0.00000
alter session set  NLS_COMP='BINARY' NLS_SORT='BINARY' NLS_CALENDAR='GREGORIAN' NLS_TERRITORY='AMERICA' NLS_LANGUAGE='AMERICAN'
4
0%
05-MAY-17 01.35.12.311000 PM +00:00
0.00400
0.00000
...NLS: Set Decimal separator="."
4
0%
05-MAY-17 01.35.12.311000 PM +00:00
0.00400
0.00000
...NLS: Set NLS Group separator=","
4
0%
05-MAY-17 01.35.12.311000 PM +00:00
0.00400
0.00000
...NLS: Set g_nls_date_format="DD-MON-RR"
4
0%
05-MAY-17 01.35.12.311000 PM +00:00
0.00400
0.00000
...NLS: Set g_nls_timestamp_format
     
    
  
  

    
  
    

  



  

    

      

        Pavel_p
        May 5 2017 
        
      
      

          
      
    
  
  

     
     
    
Hi,
unfortunately the debug trace does not seem to be very helpful. The package code does not contain any debug messages whatsoever, however maybe you might find something interesting in the S4SA_REQUESTS table.
I really don't like to give up, but I have no idea what could be possibly wrong, not this way without the ability to "touch" it. The entire thing is really complex and really tough to reproduce the problem because even to reasonably setup the environment is quite a lot of work, moreover I don't have currently access to any 12c instance, which is actually not a big problem as there exists a rebuilt image for Oracle VM with all the necessary stuff. But then set ACLs and a wallet... I'm sending you a friend request, maybe we could find out some other way as this does not lead towards any solution.
Regards,
Pavel
     
    
  
  

    
  
    

  



  

    

      

        Hawk333
        May 7 2017 
        
      
      

          
      
    
  
  

     
     
    
Just to update you, I managed to bypass that error (OPTIONS 405), by changing some parts in the plugin code. In particular in package s4sg_auth_pck I replaced the follwing lines
owa_util.redirect_url ( t_url );
with this:
apex_util.redirect_url ( t_url );
I followed https://docs.oracle.com/database/122/AEAPI/AEAPI.pdf page 632.
I was redirected to Google account page, and after granting the account permission, I was redirected back to APEX. However, I got Forbidden (403) this time. Which I think has to do with ORDS settings. I posted a separate a question for that here
     
    
  
  

    
  
    

  



1 - 9