Discussion Board for collaboration on QlikView Scripting.
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
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?
Solved! Go to Solution.
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.
I did not I'm afraid. I am currently running the views load from a pc. I have today raised this as a question directly to Qlickview and if I get a solution, I will share with you
I'm curious about this as well. We've been having some intermittent load failures from Oracle views. The load job hangs without error, and the log shows it's executing the Oracle load statement when it goes south.
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.
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?
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.
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.
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??