Extracting views from Oracle

[#1]

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

[#2]

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.

[#3]

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

[#4]

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.

[#5 · Accepted 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.