[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2009-07-08
11
Medium Priority
?
1,345 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

656 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