Question

How to Query Data from an Oracle view

Workflow Version 8

Updated July 23, 2015

asked on July 21, 2015

I'm using Workflow 8.3.

I've created a Data Source that links to an Oracle instance - OracleOdbc(Direct). The test function in "Manage Data Sources..." utility says that I'm "Successfully connected..." My Oracle tools agree that the login I'm using has select permissions on the views that I want to select from.

I'm trying to create a Query Data activity that uses the Data Source and selects from a view, but the view does not appear in the "Select the table..." list in the Query Criteria panel. The list appears to show only actual tables, not views.

How can I select from a view?

0 0

Replies

replied on July 21, 2015

Hi Brett,

I reference a View in SQL, Which queries a Linked server reference to an oracle database. This was all done in SQL. I use the View for a DB lookup in laserfiche forms

I hope this helps

Kind Regards

Chris

0 0

replied on July 22, 2015

I've been doing that for a while as well, but something about a Query Data activity that selects from a SQL view that reaches through a Linked Server and selects from an Oracle view (about 400,000 records) that selects from some tables is so slow that Workflow registers timeout errors. Some days it works fine, other times it errors on more than half of the documents.

Note: To the best of my knowledge, Workflow 8 does not have the ability to extend the timeout period.

My real purpose here is to solve those timeout errors. My hope is that by directly querying Oracle, I can remove any overhead in the SQL view and Linked Server object. More importantly, if I can send my parameters through to Oracle, the Oracle server will filter my results down to one record instead of sending 400,000 records across the link.

0 0

replied on July 22, 2015

You could try a stored procedure and Pass a parameter into the select statement

0 0

replied on July 23, 2015

Perhaps. But if I can't reference the Oracle view that I created, I'm skeptical that I'll be able to reference a stored procedure.

It seems like the Query Data activity is almost able to see my view. I wonder if I'm just a click or two away... In the meantime I've had some success using a Custom Query (and For Each) activity.

0 0

You are not allowed to follow up in this post.

Question

Question

How to Query Data from an Oracle view

Replies

Sign in to reply to this post.