Solved

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

Posted on 2009-07-08
11
1,210 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
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 recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now