CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Paul
Paul

Reputation: 133

Using Parameters in an Oracle subquery for an SSRS Report

I have seemed to get myself in an Oracle bind (again).

I have some Oracle SQL (that works! - postable on request), that will be a base query for a bunch of other queries. So I guess this would be preferably a view with parameters that other views could join to. However I have learned that that is not possible. The results will eventually be used in SSRS. My main hurdle is that I would like to have a parameter passed to the query (which is used twice in the existing SQL). I've tried packages only to be stuck with what to do the the cursor when trying to query against other tables and getting results usable in SSRS. I've also attemted variables and I forgot what all else. In MSSQL I would just create a stored procudure but as I am learning there are more differences than similarities because Oracle seems to have more flexibility and power (for those who know how to use it). I'm sure I could figure it out eventually, but right now my head hurts from banging it on the desk to much.

Thanks for helping. Paul

Upvotes: 0

Views: 582

Answers (1)

tbone
tbone

Reputation: 15493

I'm guessing a bit about what you want, but it seems you want to have some logic wrapped inside a procedure call (passing arguments), but you can't use the OUT cursor in your client.

If this is the case, you might consider using a pipelined function, similar to what I've described here. Many other examples out there of this approach.

You can now select the data from the function as if you were selecting from a parameterized view.

Hope this helps

Upvotes: 1

Related Questions