asked on

APEX: Accessing the the page URL in PL/SQL

Hi,
I have an APEX app that I am wanting to have a procedure that handles the process errors, PL/SQL errors.
In the proc I am using OWA_UTIL.GET_CGI_ENV() to get env values.
I would like to get the URL of the page that has the process error.
How do I do that? or do you have error handling that may help.

Also, I take it I will have to use a PRAGMA AUTONOMOUS_TRANSACTION; to be able to send the email and then call raise_application_error() which rollsback.. doesn't it?

ls_errmsg :='Get Browser Type';
ls_browser_type:=OWA_UTIL.GET_CGI_ENV('HTTP_USER_AGENT');
IF ls_browser_type LIKE '%Chrome%' THEN
ln_start:= instr(ls_browser_type,'Chrome/');
ln_end := instr(ls_browser_type,'Safari/');
ls_browser_type := substr(ls_browser_type,ln_start,ln_end -ln_start);
ELSIF ls_browser_type LIKE '%MSIE 9.0%' THEN
ls_browser_type := 'Internet Explorer 9.0';
ELSIF ls_browser_type LIKE '%MSIE 8.0%' THEN
ls_browser_type := 'Internet Explorer 8.0';
ELSIF ls_browser_type LIKE '%MSIE 7.0%' THEN
ls_browser_type := 'Internet Explorer 7.0';
ELSIF ls_browser_type LIKE '%MSIE 6.0%' THEN
ls_browser_type := 'Internet Explorer 6.0';
ELSIF ls_browser_type LIKE '%Firefox/3%' THEN
ls_browser_type := 'Firefox v3.0';
ELSIF ls_browser_type LIKE '%Firefox/2%' THEN
ls_browser_type := 'Firefox v2.0';
ELSIF ls_browser_type LIKE '%Firefox/1%' THEN
ls_browser_type := 'Firefox v1.0';
ELSE
ls_browser_type := 'UNKNOWN['||ls_browser_type||']';
END IF;

ls_errmsg :='Get HTTP_REFERER';
ls_HTTP_REFERER:=OWA_UTIL.GET_CGI_ENV('HTTP_REFERER');

ls_errmsg :='Get HTTP_CONTENT_LENGTH';
ls_HTTP_CONTENT_LENGTH:=OWA_UTIL.GET_CGI_ENV('HTTP_CONTENT_LENGTH');

ls_errmsg :='Get HTTP_HOST';
ls_HTTP_HOST := OWA_UTIL.GET_CGI_ENV('HTTP_HOST');

lc_clob_loc := 'Passed ErrCode['||p_sqlcode||']'
||ls_newline||'ERROR: ['||p_errmsg||']'||ls_newline
||ls_newline||'APP['||v('APP_ID')||']'
||ls_newline||'PAGE['||v('APP_PAGE')||']'
||ls_newline||'SQLCODE['||nvl(ls_SQLCODE,'NULL')||']'
||ls_newline||'SQLERRM['||nvl(ls_SQLERRM,'NULL')||']'
||ls_newline||'BROWSER['||nvl(ls_browser_type,'NULL')||']'
||ls_newline||'REFERER['||nvl(ls_HTTP_REFERER,'NULL')||']'
||ls_newline||'HOST['||nvl(ls_HTTP_HOST,'NULL')||']'
||ls_newline||'CONTENT_LENGTH['||nvl(ls_HTTP_CONTENT_LENGTH,'NULL')||']';

lb_continue:= APP_UTIL.SEND_MAIL(
ls_calling_prog,
ls_to_email,
'','',
ls_from_email,
ls_email_subj,
lc_clob_loc,
'');
apex_mail.push_queue;

skahlert2010

Is this process part of the login page or page zero?

I guess you just want to make sure that IE lower 8 is not used, since it most likely causes problems with the UI, right?

If so, then couldn't you just make your login button, password and user item conditional depending on the value of another page item (lets call it P101_Browser_Check), which applies your OWA_UTIL.GET_CGI_ENV() routine as source?

You could add a html region that is displayed or hidden depending on P101_Browser_Check.
If the browser version is detected as IE 7, IE 6 and so on then hide the login area and display the info area. Otherwise do it vice versa.

Maybe it's after all not at all what you want. But Apex offers quite good error handling at least in version 4.1.1. Thus, I don't know why you want to send emails with your sql error codes and respective URL.

BILL Carlisle

ASKER

Hi,
no, this is a procedure in db schema that all others use for error handling.

RE: I don't know why you want to send emails with your sql error codes and respective URL.
At the time of error I want to receive an email with all the info possible... say can I access all the debug info at that time?
the url would give me app, page, args, etc

ASKER CERTIFIED SOLUTION

skahlert2010

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial