Solved

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

Posted on 2009-07-08
11
1,269 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

 
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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

719 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