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
Solved

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

Posted on 2009-07-08
11
1,229 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

789 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