Oracle Apex get cookie from an ajax request

user_3133418 0
Viewed 898

How can I get the value of a cookie in oracle from a request that was originated with ajax from a non-apex page (inside an apex server)?

I wanted to start by creating a function that returns the value of the login cookie and use that function to return the value to the browser to see that it can be done.

So I created a resource template and handler with the following source: select test() from dual. The test function is declared as:

create or replace function test return varchar2
is
   c owa_cookie.cookie;
begin
   c := owa_cookie.get('LOGIN_USERNAME_COOKIE');
   return c.vals(1);
end;

When I run select test() from dual from an sql commands window I get the value of the cookie, but when I send a request to the handler with ajax I get the following error:

An error occurred when evaluating the SQL statement associated with this resource. SQL Error Code 6502, Error Message: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 354
ORA-06512: at "SYS.OWA_COOKIE", line 59
ORA-06512: at "SYS.OWA_COOKIE", line 179
ORA-06512: at "<no need to see this>.TEST", line 5

I've been using the following call (checking the result through the network dev tools) $.ajax({url: "<handler url here>"})

Update: The reason I'm using a rest service is because the page that originates the request is not an apex application but it is on the same domain (so it has the same cookies).

oracle-apex plsql oracle ajax javascript
2 Answers

This is what I think is happening: The cookie is sent as part of the http request header of your apex application call. The rest call is another http request, unrelated to your apex http request and I don’t believe the header of that request includes the cookie. So in the database session that is created as part of your rest request the cookie will not exist. You could probably set the header of the rest request yourself to fix this.

--UPDATE-- To check which cookies can be read, create a rest handler of source type pl/sql with the following source:

DECLARE
  l_names owa_cookie.vc_arr;
  l_vals owa_cookie.vc_arr;
  l_num_vals INTEGER;
BEGIN
    owa_cookie.get_all(
        names => l_names,
        vals => l_vals,
        num_vals => l_num_vals);
    APEX_JSON.open_array('cookies');        
    FOR r IN 1 .. l_names.COUNT LOOP
    APEX_JSON.open_object; -- {
    APEX_JSON.write('name', l_names(r));
    APEX_JSON.write('value', l_vals(r));
    APEX_JSON.close_object; -- } cookie
    END LOOP;
     APEX_JSON.close_array; -- ] cookies
END;

The apex username cookie is not in that list of cookies. I suggest you create your own custom cookie in an after-login process in apex. I verified that a cookie set with the following code in an apex process is picked up by the rest handler (based on blog https://www.apex-at-work.com/2012/05/apex-simple-cookie-example.html)

begin
owa_util.mime_header('text/html', FALSE);
owa_cookie.send(
name => 'CUSTOM_USER_COOKIE',
value => :APP_USER,
expires => sysdate + 30 );

apex_util.redirect_url (
p_url => 'f?p=&APP_ID.:1:' || :SESSION,
p_reset_htp_buffer => false );
end;
user_41898140

To get a cookie from an AJAX request within APEX, you can use the OWA_COOKIE package, but you do not need to define any templates or handlers. It can all be done from within the page (or calling an external procedure from within the page). Below are the steps I used to get the JSESSIONID cookie.

I have built an example application on apex.oracle.com that you can check out.

Page Setup

The page is very simple with just one text box and one button. The button's action is set to Redirect to URL and this is the target:

javascript: apex.server.process("AJAX_TEST", { }, { success: function (pData) { console.log(pData); apex.item("P10_JSESSIONID").setValue(pData); }, dataType: "text", error: function (jqXHR, textStatus, errorThrown) { } }).always(function () {  });

The javascript is calling the AJAX Callback (which is shown below) and storing the output in the P10_JSESSIONID page item as well as logging it to the browser console.

Page setup

AJAX Callback Setup

The code in the AJAX Callback is very similar to the code you shared. I am getting the cookie then printing it out with HTP.P. This is all that is needed to return the value of the cookie to the page to be used for whatever you'd like. You can also make the code in the AJAX callback call a standalone procedure compiled into the database if you so choose.

Ajax Callback setup

Update

I updated my example application so that there will be a dropdown showing all of the cookies, then you can select from the list and click the button to send an AJAX request to get the value of that cookie.

user_71100990
Related