Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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 Smiley Happy

P

1 Solution

Accepted Solutions
Not applicable

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! Smiley Happy


Thanks for all your advice and pointers.

13 Replies
Not applicable

Extracting views from Oracle

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

Kind regards, Jane

Not applicable

Extracting views from Oracle

Hi Jane

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 Smiley Happy

Kind regards

P

Not applicable

Extracting views from Oracle

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.

~Mark

Not applicable

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.

Not applicable

Extracting views from Oracle

Thanks Sweetpea, I look forward to the answer. I wish I knew more about Oracle but I am more of a MS SQL person.

Cheers, Jane

Not applicable

Extracting views from Oracle

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

Not applicable

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

Not applicable

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.

Not applicable

Extracting views from Oracle

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