About Oracle: Avoiding multiple logins from external Apex links Part 2

Wednesday, 28 June 2017

Avoiding multiple logins from external Apex links Part 2

A few weeks ago I wrote a post how to avoid multiple logins when using a link in an email. This solution however does not work in a batch process for two reasons:

the URL needs to be fully specified, so protocol, server, port and directory are also needed. These data are not available outside the Apex context
the function apex_util.prepare_url can only be called from within an Apex session

My first idea was to prepare the URL for every possible mail while being in an Apex session and store it in the database. The stored URL can be used when generating the email.
This would be technically complex, because a new column would have to be created and the content of this column would have to be kept up to date all the time.

Another possibility would be to create a fake Apex session within the batch process. Then the rest of the code need not be changed. Martin Giffy d'Souza provides a way to create an Apex session in this blogpost. Although this is an 5 year old post I was able to create an Apex session and generate a valid link.

The last solution was chosen because of the simplicity and the absence of need for code change.

Happy Apexing!

3 comments:

Jaydipsinh Raulji said...: Hi,

I have done steps accordingly and facing error:

Session state protection violation: This may be caused by manual alteration of a URL containing a checksum or by using a link with an incorrect or missing checksum. If you are unsure what caused this error, please contact the application administrator for assistance.

The final url prepared is :

http://www.my.com/f?p=500:50:::::P50_PARAM1,P50_PARAM2,P50_PARAM3:91,123456789,1111&cs=34BjsILecEp4NDqpiAJFLgcpBhPxvp-oP8N2g0Q3E6QouOg-HEXZApMvl3nk_Y5TJHE-QeSiZ8z9vZf0uEdogHw

I'd used this code:

BEGIN
sp_create_apex_session(
p_app_id => 500,
p_app_user => 'USER1',
p_app_page_id => 50);
END;

with lst1 as (select v('APP_SESSION') as session_id from dual)
select
'http://www.my.com/' || APEX_UTIL.PREPARE_URL (
p_url => 'f?p=500:50:'||session_id||'::::P50_PARAM1,P50_PARAM2,P50_PARAM3:971,508144928,1111' ,
p_checksum_type => 'SESSION'
) as url
from lst1;

Can you please point out where it went wrong?

I have enabled rejoin session from application settings and also set Page Access Protection = Arguments Must Have Checksum

Thanks.; 29 June 2017 at 15:29
Dick Dral said...: Hi Jaydipsinh,

I notice that the checksum in the final URL is much too long. It should be 28 characters.
So I can imagine why this is not accepted. It seems like prepare-url is called several times.
You call the prepare_url function from within a query. In PL/SQL it would be more logical to call it directly.

Thanks,
Dick; 5 July 2017 at 13:28
Jaydipsinh Raulji said...: Hi Dick,

Thanks for your prompt response.

I will try accordingly.; 9 July 2017 at 07:30