This discussion is archived
11 Replies Latest reply: Oct 25, 2012 6:43 AM by 845109 RSS

create cookie using pl sql and retrieve values

845109 Newbie
Currently Being Moderated
Hi ,
I am currently in an urgent need to create cookie from oracle BE and also read values from it . I have tried to use below code . its showing error
code
DECLARE
ourcookie owa_cookie.cookie;
BEGIN
ourCookie := owa_cookie.get('SESSION2');
END;
/

BEGIN

owa_util.mime_header('text/html', FALSE);

-- Create a cookie
owa_cookie.send(
name=>'SESSION',
value=>'344444',
expires=> sysdate+2,
path=>'/');


owa_util.http_header_close;

EXCEPTION
WHEN OTHERS THEN NULL;

END;


But when I am trying to read from the cookie I have created I am unable to perform

--- this is for reading cookies
DECLARE
ourcookie owa_cookie.cookie;
BEGIN
ourCookie := owa_cookie.get('SESSION2');
END;
/

error :
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 325
ORA-06512: at "SYS.OWA_COOKIE", line 36
ORA-06512: at "SYS.OWA_COOKIE", line 140
ORA-06512: at line 4
  • 1. Re: create cookie using pl sql and retrieve values
    BluShadow Guru Moderator
    Currently Being Moderated
    Firstly, remove the stupid exception handling...
    EXCEPTION
    WHEN OTHERS THEN NULL;
    As that is basically saying "if I get an error, just ignore it"
  • 2. Re: create cookie using pl sql and retrieve values
    EdStevens Guru
    Currently Being Moderated
    842106 wrote:
    Hi ,
    I am currently in an urgent need
    This is a forum of peer volunteers. There is no "urgent" here.


    <snip>
  • 3. Re: create cookie using pl sql and retrieve values
    Sven W. Guru
    Currently Being Moderated
    You write a cookie named SESSION. Then you read a cookie named SESSION2. And then you wonder why cookie SESSION2 does not exist. Think about it once again.

    Btw. You can use your browser and check if the cookie is there. In firefox this under preferences=>Single cookies.
    Not sure if I translated the menu option correctly. but since this was so urgent I didn't take the time to look it up.

    Also this code needs to be run during the page load (onload page header).

    Edited by: Sven W. on Oct 23, 2012 2:51 PM
  • 4. Re: create cookie using pl sql and retrieve values
    845109 Newbie
    Currently Being Moderated
    I am really sorry :(
  • 5. Re: create cookie using pl sql and retrieve values
    845109 Newbie
    Currently Being Moderated
    Thanks all for your reply . Actually I need to create a fake cookie from oracle BE and try to retrieve values from it. I have modified my query and removed exceptions
    Code :

    declare
    ourcookie owa_cookie.cookie;
    v_session number;
    BEGIN

    owa_util.mime_header('text/html', FALSE);

    owa_cookie.send(
    name=>'SESSION1',
    value=>'344433',
    expires=> sysdate+2,
    path=>'/');

    owa_util.http_header_close;

    dbms_output.put_line('cookie created');

    ourCookie := owa_cookie.get('SESSION1');
    v_session := v_cookie.vals(1);
    dbms_output.put_line('session created '||v_session);


    END;


    Still its showing
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.OWA_UTIL", line 325
    ORA-06512: at "SYS.OWA_UTIL", line 369
    ORA-06512: at line 5


    I am very new to cookie related stuff in oracle .
  • 6. Re: create cookie using pl sql and retrieve values
    Sven W. Guru
    Currently Being Moderated
    842106 wrote:
    Thanks all for your reply . Actually I need to create a fake cookie from oracle BE and try to retrieve values from it. I have modified my query and removed exceptions
    Try to move the header_close procedure to the end of your routine and see if that changes anything.
      owa_util.http_header_close;
    If the cookie is not there you should get a no_data_found error.
    But you got an numeric or value error. This indicates that something else went wrong.
    I use the get function in apex from time to time. But only during the load of page headers. Not so sure if that is needed for the get function. It is necessary for the send however.

    The problem could also be the path. Not sure if '/' is a valid path. Try to send without any path setting.
    owa_cookie.send(
      name=>'SESSION1',
      value=>'344433',
      expires=> sysdate+2
      );
    Edited by: Sven W. on Oct 23, 2012 3:02 PM
  • 7. Re: create cookie using pl sql and retrieve values
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Some basics first.

    Cookies are send to the web browser, kept by the web browser, and supplied by the web browser.

    In Apex, you can send a cookie to the web browser. And you can also read a cookie value from the web browser.

    However, the Apex PL/SQL code that does this needs a valid web server CGI (Common Gateway Interface) environment. Details explained yesterday by me in posting {message:id=10649027}.

    If you want to test your PL/SQL web enabled code outside of an Apex or mod_plsql environment, you need to create a valid OWA environment and manually create the cookies in this environment.

    The details of how to write a web browser call (using PL/SQL) to a web enabled procedure is detailed in {message:id=2251131}.

    Note that this approach requires that you create a PL/SQL call environment that supplies all the web browser details. This approach cannot be used to act like a real browser - and for example call a PL/SQL proc that sets a cookie, traps that call, and set the cookie. Or respond to an Ajax call. As a real web browser would do.
  • 8. Re: create cookie using pl sql and retrieve values
    845109 Newbie
    Currently Being Moderated
    Thanks Billy and Sven for your help .

    Billy, you are correct I am executing this in normal PL SQL environment for that it was giving numeric or value error.

    Now as per your comment I have created a simple OWA environment and executed the below code . Now I am getting no data found error

    ORA-01403: no data found
    ORA-06512: at line 23

    Code:

    declare
    ourcookie owa_cookie.cookie;
    v_session number;
    BEGIN
    owa_util.mime_header('text/html', FALSE);

    owa_cookie.send(
    name=>'SESSION1',
    value=>'344433',
    expires=> sysdate+2,
    path=>'/'
    );
    owa_util.http_header_close;
    /** Initialize */
    owa.cgi_var_name (1) := 'something';
    owa.cgi_var_val (1) := 'else';
    owa.init_cgi_env (1, owa.cgi_var_name, owa.cgi_var_val);
    /** End Initialize */

    dbms_output.put_line('cookie created');

    ourCookie := owa_cookie.get('SESSION1');
    v_session := ourCookie.vals(1);
    dbms_output.put_line('session created '||v_session);


    END;

    So is it not possible to create a real cookie and retrieve value from non apex or CGI environment ?
  • 9. Re: create cookie using pl sql and retrieve values
    BluShadow Guru Moderator
    Currently Being Moderated
    842106 wrote:
    So is it not possible to create a real cookie and retrieve value from non apex or CGI environment ?
    As Billy indicated.. cookies are related to web browsers. PL/SQL code within a database is not a web browser, so it has no concept of "cookie" storage. The cookie functionality provided by PL/SQL is to, in principle, communicate with the web browser to get it to create the cookie.
  • 10. Re: create cookie using pl sql and retrieve values
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    842106 wrote:

    Now as per your comment I have created a simple OWA environment and executed the below code . Now I am getting no data found error

    ORA-01403: no data found
    ORA-06512: at line 23
    The error is due to the cookie not being found.

    The following is not valid - as there is no browser to receive the cookie:
    owa_cookie.send(
    name=>'SESSION1',
    value=>'344433',
    expires=> sysdate+2,
    path=>'/'
    );
     

    The following code needs to set the CGI environment - and add the cookie to the environment for the subsequent code to read (as shown in the sample code I posted in the 2nd thread I referred you to):
    /** Initialize */
    owa.cgi_var_name (1) := 'something';
    owa.cgi_var_val (1) := 'else';
    owa.init_cgi_env (1, owa.cgi_var_name, owa.cgi_var_val);
    /** End Initialize */
     

    Of course, the following code will fail with a NO_DATA_FOUND when there is no cookie.
    ourCookie := owa_cookie.get('SESSION1');
    v_session := ourCookie.vals(1);
     
    So is it not possible to create a real cookie and retrieve value from non apex or CGI environment ?
    Yes it is. But the web browser is non-existant. So you need to create the CGI environment that the web server would have created, prior to calling your web enabled PL/SQL code.

    So in other words, your CGI initialisation code needs to pretend it is the web server, it has received the web browser call, it has received the cookie(s) from the web browser - and now it needs to create the CGI environment (cookies included) for this web browser call, and then call the PL/SQL procedure that the web browser references in the URL it submitted.
  • 11. Re: create cookie using pl sql and retrieve values
    845109 Newbie
    Currently Being Moderated
    Thanks a lot Billy for your help :)
    owa environment really solved my need . I believe this session and cookie is not stored anywhere .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points