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?
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.
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.
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.
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:
