Parse base domain name using HTTP_HOST in MOD_PLSQL

                Parse base domain name using HTTP_HOST in MOD_PLSQL

SOLUTION
---------

HTTP_HOST can be obtained via owa_util.GET_CGI_ENV to be later parsed using
INSTR/SUBSTR such as follows:

SQL> create or replace function base_domain return varchar2
  2  is
  3     l_http_host varchar2(4000) := owa_util.GET_CGI_ENV('HTTP_HOST');
  4  begin
  5     return substr(l_http_host,instr(substr(l_http_host,1,instr(l_http_host,'.',-1)-1),'.',-1)+1);
  6  end base_domain;
  7  /

Function created.


You could then use base_domain() function to set cookies for the base level domain:

       owa_cookie.send(name => 'cookie_name',
                       value => 'cookie_value',
                       path => '/',
                       domain => base_domain);


TEST CASE
----------
SQL> create or replace function base_domain_test(p_full_domain in varchar2) return varchar2
  2  is
  3     l_http_host varchar2(4000) := p_full_domain;
  4  begin
  5     return substr(l_http_host,instr(substr(l_http_host,1,instr(l_http_host,'.',-1)-1),'.',-1)+1);
  6  end base_domain_test;
  7  /

Function created.


SQL> select base_domain_test('server-name.sub-sub.sub.domain.com') from dual;

BASE_DOMAIN_TEST('SERVER-NAME.SUB-SUB.SUB.DOMAIN.COM')
--------------------------------------------------------------------------------

domain.com

SQL> select base_domain_test('sub.sub.domain.com') from dual;

BASE_DOMAIN_TEST('SUB.SUB.DOMAIN.COM')
--------------------------------------------------------------------------------

domain.com

SQL> select base_domain_test('sub.domain.com') from dual;

BASE_DOMAIN_TEST('SUB.DOMAIN.COM')
--------------------------------------------------------------------------------

domain.com

SQL> select base_domain_test('domain.com') from dual;

BASE_DOMAIN_TEST('DOMAIN.COM')
--------------------------------------------------------------------------------

domain.com

SQL>