11 Replies Latest reply: Oct 25, 2012 8:43 AM by 845109 RSS

    create cookie using pl sql and retrieve values

    845109
      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
          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
            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.
              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
                I am really sorry :(
                • 5. Re: create cookie using pl sql and retrieve values
                  845109
                  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.
                    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
                      Billy~Verreynne
                      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
                        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
                          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
                            Billy~Verreynne
                            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
                              Thanks a lot Billy for your help :)
                              owa environment really solved my need . I believe this session and cookie is not stored anywhere .