13 Replies Latest reply: Sep 7, 2010 8:04 AM by paula gabriel RSS

    Extracting views from Oracle

      Hi guys

      I am running some qvw scripts from a server using odbc to extract tables and views from the Oracle Application. The tables are extracting without any problems, but the views do not.

      I did raise an SR with oracle who have advised that the following pl/sql has to be run in Oracle App 11i to be able to extract from the views

      begin fnd_client_info.set_org_context('&v_org_id');

      When I run this in sqlplus on the server, i am able to select * from the views successfully.

      Is there anyway to run this pl/sql in my qvw extract query so that I can retrieve data from the views?

      Thank you :)

      P

        • Extracting views from Oracle
          Jane Jackman

          Hi Sweetpea, did you ever get an answer for this. I have the same problem?

          Kind regards, Jane

          • Extracting views from Oracle

            Instead of using ODBC to pull the data from Oracle, I use the native oracle drivers. I use exclusively views to pull semi-pre-processed data into my QlikView applications, and I've never had any problem with it.

              • Extracting views from Oracle
                Jane Jackman

                Hi Does anyone have an update on this. Fry are you sure that using the Oracle native driver will solve this issue, how do you set the context for the view which i believe is the problem? Could you post an example please?

                TIA, Jane

                  • Extracting views from Oracle

                    I assume by native drivers you're referring to OLE DB vs. ODBC? We never use ODBC, always OLE.

                    We use a third party job scheduler, so we've just been running qv.exe with the /r switch and then the path the QVW that loads our QVDs. We've been thinking of switching to using publisher just to see if we can get some better error logging.

                    Thanks,

                    ~Mark

                • Extracting views from Oracle

                  You're correct, I am referring to OLE DB.

                  @Jane: I'm not really an oracle expert, I'm not sure what you're referring to by context. I connect to the database via OLE DB, and then pull data from my views using a simple SQL SELECT.

                  Here's an example of my connect string, if that helps
                  CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=<Schema Name>;Data Source=<Data Source Name>;Extended Properties=""] (XPassword is aLWZdRBPHKOKGRdLVJQMC);

                  @mgertgen: I use publisher for my reload tasks; I've not encountered that particular issue. I have had issues that sound similar, but without logging to back up the theory it's tough to say if it's the same problem. In my case, the reload task pushed the query to the oracle database, and as a result of an optimization issue, the query would 'never' return. (It might eventually, but not before my QV Server gave up on it) and the process was automatically killed.

                    • Extracting views from Oracle
                      Jane Jackman

                      Hi Fry,

                      I know how I would do what my customer wants if it was SQL server, I would create a stored procedure and within that I wpould set the context and select the rows from the view and return the lot via a

                      SQL Execute storedprocname @parn1='xxx', @parm2 = 123;

                      statement. But I don't know oracle either. It seems that the context setting command changes the data seen by the view, so as the conetxt command is a package perhaps a new package could be written which calls the context and then the view, but I don't know. Problem i have is that the customers IT people don't seem the be the most helpful in the world. They just throw it back and hold their hands up.

                      Surely there must be and oracle expert somewhere in Qliktech??

                      Cheers,

                      Jane

                    • Extracting views from Oracle

                      Well! You learn something new every day. I just gave myself a little crash course on parameterized views using oracle contexts. So now I have some idea what you're referring to.

                      I've never used contexts in an oracle environment. Whenever I've needed a parameterized query, I've written a stored procedure. Is it not possible, in your QV script to call:

                      SQL begin fnd_client_info.set_org_context('&v_org_id');

                      And then SQL SELECT * FROM ;

                      The 'SQL' keyword in the QV scripting language just passes the following string to the database as a query. Any query or command you can issue the database with a read-only connection, you should be able to execute using the SQL keyword.

                        • Extracting views from Oracle
                          Jane Jackman

                          Hi Fry,

                           

                          I don't know if that would work because they would be viewed as 2 separate connections. I'll see if I can get on the clients system and have a fiddle around but I don't want to upset the oracle bods. I'll let you know if I get anywhere.

                           

                          Cheers,

                          Jane

                            • Extracting views from Oracle

                              I was wondering precicely that. Again, lacking contexts, I can't really give it a shot myself. My thinking was that as the connect string is defined once, and the actual connection appears to be a single command that QV might retain a session in the DB for all of the following queries, but it may not. It may well create a new connection each time.

                          • Extracting views from Oracle

                            Ok, I think I found it!

                            The package fnd_client_info.set_org_context was already installed in our database, so in the qlikview extract script (just below the odbc connection settings) I entered the following:

                             

                            SQL call fnd_client_info.set_org_context('103');


                            The views now extract fully! Yay! :)


                            Thanks for all your advice and pointers.