7 Replies Latest reply: Apr 2, 2013 5:44 AM by BluShadow RSS

    Reg : Memory to hold session data -

    ranit B
      Hi Experts,

      I'm designing an automation tool which will have a PSP <tt>PL/SQL Server Pages</tt> screen (assume something like some APEX) ,read the data from database and generate web-submits without actually executing the scripts on database.

      The scripts will contain only <tt>Update</tt> and <tt>Insert</tt> scripts.

      But in all the screens of the tool, I've kept a <tt>"Save to web-submit"</tt> button which stores the action performed and its data to some storage/memory. In the final screen, I've <tt>"Generate final web-submits"</tt> which generates complete thing as downloadable <tt>.txt</tt> file.

      I also have a <tt>"Reset web-submits"</tt> button which 'll clear all the work done and start the process afresh.

      Any idea guys how can I implement this storage or memory concept?
      Is there any cache concept which 'll keep storing my data?

      I'm currently reading about <tt>GTT</tt> - http://docs.oracle.com/cd/E14072_01/server.112/e10595/tables003.htm#i1006400
      Will this help ??

      Help much appreciated!!!
      - Ranit
        • 1. Re: Reg : Memory to hold session data -
          jeneesh
          How is this related to SQL or PL/SQL?

          Do you want this to be handled by database? Not very clear..
          • 2. Re: Reg : Memory to hold session data -
            ranit B
            jeneesh wrote:
            How is this related to SQL or PL/SQL?
            PSP (PL/SQL Server Pages) screens are nothing but Procedures and packages which when executed as URL in browser generate HTML pages.
            Just consider them to be like APEX screens.

            So basically I'll read data from database, make some modification (NOT in database, just as scripts) and generate those scripts as web-submits... This is my main goal.
            Do you want this to be handled by database? Not very clear..
            Yes, everything is related and about database.

            Please let me know if you have any other specific questions.
            • 3. Re: Reg : Memory to hold session data -
              BluShadow
              Global Temporary Tables work within a specific session, so at most can only hold data for that session.
              Web based applications are generally stateless, because they connect to the database with a new session each time a request is made from the browser, so will have a new database session each time.
              APEX has a clever mechanism behind it to keep track of a user's session (if you look at an APEX URL it contains a session ID), but these are virtual sessions rather than database connection sessions, so still a Global Temporary Table cannot be used.

              It sounds like you want to create some sort of virtual session like Apex does.
              • 4. Re: Reg : Memory to hold session data -
                ranit B
                Thanks Blu.
                But my tool will only be active for a specific session and that is fine. In that case, I can use GTT right???

                My screen is not APEX but like APEX.
                The procs/packages contain usage of <tt>HTP Packages</tt> and <tt>HTP.PRINT</tt> method is used to generate the HTML page.
                I know it's old and outdated but that is how things are existing.

                Can you tell me in web-based applications what marks the Start and End of a session?
                Is it the time I hit the web-app URL and the time I close the web-app?
                because they connect to the database with a new session each time a request is made from the browser, so will have a new database session each time.
                Ohh.. just read this. So you mean while navigating each redirect from page-1 to page-2 will be a new session??
                • 5. Re: Reg : Memory to hold session data -
                  BluShadow
                  ranit B wrote:
                  Thanks Blu.
                  But my tool will only be active for a specific session and that is fine. In that case, I can use GTT right???

                  My screen is not APEX but like APEX.
                  The procs/packages contain usage of <tt>HTP Packages</tt> and <tt>HTP.PRINT</tt> method is used to generate the HTML page.
                  I know it's old and outdated but that is how things are existing.

                  Can you tell me in web-based applications what marks the Start and End of a session?
                  Is it the time I hit the web-app URL and the time I close the web-app?
                  Web based applications are viewed through browsers... and browsers do not hold a connection to a database. They submit requests over the HTTP protocal and await a response... and once the response has been received the request is complete. So when you use PSP to make a request (if I recall correctly, it's been a long long time since I did PSP), it makes an HTTP request that is handled by the server, creates a database session, does whatever is needed (fetches data or whatever) and then passes back a response to the browser (including the data). The database session isn't held.

                  So, you have procedures and packages on the database that use HTP to generate HTML...

                  http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/w_htp.htm#BABDEGDG
                  >
                  The function versions do not directly generate output in your web page. Instead, they pass their output as return values to the statements that invoked them.
                  >

                  The database cannot directly "push" html to a web browser... that's not how client server architecture works. It's the browser that makes the request and receives the response. The session only lasts as long as each request takes to process. Subsequent requests will use a seperate session.
                  • 6. Re: Reg : Memory to hold session data -
                    ranit B
                    Thanks Blue, for that great explanation.
                    But then please advise some way to keep the log of what all changes I'm doing. So later, I can generate my web-submits from these changes (logged information).
                    • 7. Re: Reg : Memory to hold session data -
                      BluShadow
                      ranit B wrote:
                      Thanks Blue, for that great explanation.
                      But then please advise some way to keep the log of what all changes I'm doing. So later, I can generate my web-submits from these changes (logged information).
                      I guess you'll have to consider something similar to Apex... implement a logical session ID, specific to that browser session that is passed with each request, and use fixed tables to store the information related to that session, and then have something scheduled on the database to clear out old sessions after a period of time (or period of inactivity) so that other people cannot access them.