Solved

How do I parameterize a table name in SSRS against a Oracle database

Posted on 2009-07-08
11
1,242 Views
Last Modified: 2012-06-27
Hi,

I have to create a report in SSRS from an Oracle database.  The table that I have to select the data from have a year appended on the end of the table name (i.e. GL05, GL06, GL07, etc).  I can get the parameters to work in the where clause but not for the table name in the from clause.

0
Comment
Question by:barahona
11 Comments
 
LVL 16

Expert Comment

by:Richard Olutola
ID: 24989408
Please show us your query for a better understanding of what you're trying to achieve.

R.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24990493
you need to use dynamic sql if you want select dynamically from the given table name
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24992175
As nav_kum_v says, you cannot parameterize an object as a bind variable, as that would change the execution plan of the query. To use dynamic sql, you could create a function that returns a result set for SSRS

CREATE OR REPLACE FUNCTION getall(t varchar2) RETURN SYS_REFCURSOR
IS
  result SYS_REFCURSOR;
BEGIN
  open result for 'select * from '||t;
  return result;
END;
/


--Test with two tables a and b
select getall('a') from dual;
select getall('b') from dual;

0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 25059937
What does SSRS pass to Oracle?  Is it a procedure call, or simply a *.SQL query?  If it is a simply query, it should be able to dynamically construct the query without a problem.  If it expects to accept the table name though as a parameter to be used by a stored procedure, that is different.  Then you need the approach that mrjoltcola suggested, that combines "dynamic SQL" in PL\SQL with a "ref cursor" to return the results.  Oracle does not support using parameters directly for table names in PL/SQL.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 25243485
I think the question was answered adequately and recommend a split:
http:#24990493
http:#24992175
http:#25059937
Thanks.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 25243567
I agree with mrjoltcola, but he is being generous.  His suggestion was more complete, so I think his suggestion should be worth 40-50% with the other two then being 25-30% each
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 25243590
mark: On the other hand, if there is an SSRS alternative, then even my answer may not be useful, since none of us really provided any SSRS specific suggestions. We probably needed a SQL Server guru here.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 25245616
I found the ms document which I think is related to this problem:
http://msdn.microsoft.com/en-us/library/aa237477%28SQL.80%29.aspx

the parameter could be either user input, with or without a drop down, resp the drow down list filled from a query.

as simple as that :)
a3
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question