11 Replies Latest reply: Feb 10, 2011 9:54 AM by Pedro Lourenço RSS

    Setting context inside a query - linear and reliable?

    Pedro Lourenço
      Hello,

      I wrote a function that sets a new context to my session, which is simply something like this:

      create or replace function f_set_context (wsName varchar2) return number is
      begin
      dbms_wm.gotoworkspace(workspace => wsName);
      return 1;
      end f_set_context;

      If i use it in the where clause of any query (like: SELECT atrib FROM table WHERE f_set_context(workspace) = 1), and i'm getting results duly contextualized.

      I don't know well the workings of the Oracle query parser. Neither the order in which it evaluates and executes the query components. So, my question is:
      It's reliable to use this kind of approach? It's garanteed that in any situation, my results will reflect the desired workspace context?

      Any comments are appreciated.
      Best regards,

      Pedro Lourenço
        • 1. Re: Setting context inside a query - linear and reliable?
          NoelKhan
          Pedro,

          . . . .To ensure your query is executed within a workspace, try something like:
          CREATE OR REPLACE PROCEDURE QueryContext(dml VARCHAR2, wsName VARCHAR2) IS
              wsBefore VARCHAR2(100);
          BEGIN
             IF UPPER(SUBSTR(dml,1,5)) = 'SELECT' THEN
                  SELECT DBMS_WM.GetWorkspace INTO wsBefore FROM DUAL;
                  dbms_wm.gotoworkspace(workspace => wsName); 
                      EXECUTE IMMEDIATE dml;
                  dbms_wm.gotoworkspace(workspace => wsBefore); 
             END IF;
          END;
          Regards,
          Noel
          • 2. Re: Setting context inside a query - linear and reliable?
            Pedro Lourenço
            Hi Noel,

            Your procedure will surely work. But in my scenario i think i can't apply it.

            I use an external application that internally generates SQL queries. And I can't / shouldn't change it.
            The only input I can give this application is the filter that i want in the where clause of the select.
            I found that solution, with the function f_set_context... submit something like.. filter = 'f_set_context(ws_name) = 1'.
            In all tests i've made it worked, but i'm not sure if it's totally reliable.

            Thanks.
            Regards,

            Pedro Lourenço
            • 3. Re: Setting context inside a query - linear and reliable?
              aschilling
              hi Pedro!

              what exactly do you mean with "external application"? a web application? a java application?
              we actually do something like you are doing from a java application (we are setting the session context for the current transaction). as long as your business function doesn't use multiple sessions it should work as expected. gotoworkspace/gotosavepoint is per session. be aware of connection pools if you use one, you must be sure to set the context everytime you get a connection.

              regards,

              Andreas
              • 4. Re: Setting context inside a query - linear and reliable?
                Pedro Lourenço
                Hello,

                Yes, i'm building a web app and i set the context without any problems, just like you said.
                That's not the point. I'll try to explain my dilemma...

                The system i'm building it's a GIS - Geografic Information System - and interacts with a java server called GeoServer. Don't know if you ever heard about it.
                That server dynamically generates the sql queries that are made to the database and manages internally and automatically its connection pool.

                If i can't access and manage the connection, i can't set the context. So, i thought... it's possible to send some conditions in one of the http request parameters and get those conditions applied in the where clause of the query that is made to the DB.
                Based on this small opened window, i was trying to send the context "inside" the where clause of the query.
                It works. But i'm not confident that's a proper use.

                Any ideia to workaround this in another way?
                Thanks!

                Best regards,

                Pedro Lourenço
                • 5. Re: Setting context inside a query - linear and reliable?
                  Ben Speckhard-Oracle
                  Hi,

                  Without access to the session itself, this would be difficult to guarantee that it would work in all cases. Any function that is not based on row data, as is the case with the one you created, is typically executed as one of the first steps. However, exactly where it is positioned in regards to bind variable resolution (the workspace context with regards to the query), how it might relate to any system or session database/optimizer parameters, is difficult to say. It would also not be guaranteed from one release to the next. If you really need this functionality I would recommend either raising an SR or asking this question in the database forum for a more complete answer. This is more optimizer/database related question than anything Workspace Manager related.

                  Also, there are no other ways to work around it either that I can think of. You really do need access to the session to do it properly.

                  Regards,
                  Ben
                  • 6. Re: Setting context inside a query - linear and reliable?
                    Pedro Lourenço
                    Hi,

                    Ok... understood.
                    I think i'll try to open the server and find a way to pass the context to the place where the connections are created.
                    Thank you all for the help!

                    Best regards,
                    Pedro Lourenço
                    • 7. Re: Setting context inside a query - linear and reliable?
                      Stefan Jager
                      Pedro,

                      Why don't you have a look at Oracle Virtual private Database? That allows you to add contexts based on whatever you want. So if you give your GeoServer a specific user, you can then set the context based on that username. Oracle does this by adding a predefined statement to the where-clause (defined by you).

                      See the docs:
                      http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/vpd.htm#DBSEG007

                      This might do what it is you try to achieve?
                      • 8. Re: Setting context inside a query - linear and reliable?
                        Ben Speckhard-Oracle
                        Hi,

                        That would allow you to add a specific where clause based on an individual user, but not change workspaces at the query level. It's the changing of a workspace within a query that can be problematic.

                        Regards,
                        Ben
                        • 9. Re: Setting context inside a query - linear and reliable?
                          Stefan Jager
                          Hi Ben,

                          That is true, but seeing as he is talking about GeoServer (which is a webserver) and http-requests I assumed that this is to set up a web-GIS-app. In that case, it is not very likely that the workspace would change, I think. I have to admit that it is an assumption, and maybe the OP could answer this. Because if the workspace that his app uses is always the same, then this way you know when the Workspace is set.

                          But there may be something in GeoServer that will work. I don't know what the Workspaces are for (different scenario's? History?), but the GeoServer boys are working on something: http://suite.opengeo.org/docs/geoserver/extensions/wfsv.html
                          so maybe there is something inside GeoServer that more or less supports this?
                          • 10. Re: Setting context inside a query - linear and reliable?
                            Ben Speckhard-Oracle
                            Hi,

                            If the workspace doesn't change, then a logon trigger would be appropriate to change to the desired workspace. In the general case, you could set the workspace based on the user logging in. But when connection pooling is used with a common user that is logging into the database, this would not work and would be limited to a single workspace for every user unless some context is available to distinguish them.

                            But, as you mentioned, it would depend on exactly what the OP needed.

                            Regards,
                            Ben
                            • 11. Re: Setting context inside a query - linear and reliable?
                              Pedro Lourenço
                              Hi all,

                              Sorry for the delay in response.
                              The application i'm developing is a Geographic Information System currently used to store and view data about telecomunication networks.
                              We're trying to include a new module - a project component - that allows the creation and edition of new networks in seperate environments.
                              To accomplish this, we are trying to use Oracle Workspace.

                              So, answering to Stefan:

                              Our purpose using workspaces is to create different scenarios:
                              - one project corresponds to a specific workspace

                              There is no unitary relation between workspace and user:
                              - one project can be used by different users and different projects can be accessed by the same user
                              - i think Oracle Virtual private Database is not suited to the current requirements

                              Entering the application, any user starts in LIVE mode.
                              If someone wants to create a project or use an existing one, all new connections are contextualized in the corresponding workspace.
                              Cause we're using GeoServer, we also need to set it's conections contexts. But GeoServer automatically manages it's own connection pool.
                              So... if we don't have direct access to the session, how can we set the contex? That's my real problem...

                              GeoServer boys talk about a possible integration with Oracle Workspace for versioning purposes but i think there are no actual developments regarding this.
                              The protocol you refered (WFS-V) just works for WFS requests (we also need WMS), have very limited options and, above all, just works against PostGIS.

                              Obvsiously, we can change Geoserver to send a new parameter (like workspace_name) in the http requests, parse that parameter and set the context where the connections are managed. That's our intention, namely to share it with the community.
                              But for a fast and temporary solution, any other idea to set this scenario working?

                              Thank you all for the sugestions and ideas.
                              Best regards,

                              Pedro Lourenço