[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Embeded subquery for table name Pl SQL

Posted on 2008-11-10
16
Medium Priority
?
763 Views
Last Modified: 2013-12-19
I'm trying to write query that gets it's table names from a sub query.

I tried Select * from TABLE(Select tablename from tablename where x=foo)
 and I get and error: [Oracle][ODBC][Ora]ORA-22905:cannot access rows from a non-nested table item

I tried Select * from (Select tablename from tablename where x=foo)  T
 and get no error but there are not fields avaiable
0
Comment
Question by:txbtech
  • 9
  • 7
16 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22926347
you cannot do that without dynamic sql.
and as you want some SELECT to be executed, that will be a dynamic cursor:
http://www.adp-gmbh.ch/ora/plsql/cursors/dynamic.html
0
 

Author Comment

by:txbtech
ID: 22926659
Appearently you can do this as long as you cast the table query. http://www.error-code.org.uk/view.asp?e=ORACLE-ORA-22905
But I don't know what to cast as. I tried "(Cast(select ....) as Table)" and that didn't work
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22926728
the TABLE() construct can only take a cursor or an array/collection as input, and not a "single value that represents the table name". CAST() as such is of no help here.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:txbtech
ID: 22963561
So since I can only send oracle a select statement via Crystal Reports to get my data, I'm hosed? Do you have any suggestions?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22963980
>So since I can only send oracle a select statement via Crystal Reports to get my data
can you clarify, please? not sure what you mean?
0
 

Author Comment

by:txbtech
ID: 22964467
I'm using crystal reports and entering this select statement in the SQL Command window.  So, I can't write any PL SQL procedures with variables, if that is what you mean by Dynamic SQL.  therefore, I'm stuck.

I guess I should ask you to clearify "Dynamic SQL".
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22964487
I have only done a couple of reports in CR, so bare with me on that part.

however, I remember that indeed you enter the sql to be executed in the CR interface, so where is the problem?

from what I guess, you don't want to write the actual sql in the report itself, but have it (the sql) stored in a table , and just refer to the key information to identify which SQL to run for that particular report.
is that correct?
0
 

Author Comment

by:txbtech
ID: 22975594
That is correct. There is a command window where you enter the SQL to be executed and entered the following query which resulted in an error:
Select * from (Select tablename from tablename where x=foo)  T

I don't know which table I need to pull from. The correct table name is the result of the subquery in paranthasis.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22975639
so I have to repeat:
you cannot do that without dynamic sql.

you really should create a view for each of those reports, and base your report on the view (select * from view_name).

hence, when a report's SQL has to be changed (ie the table it comes from), all you have to do is to change the view.

now, you might want to clarify what exactly is the "table name changing requirement", as eventually there are some other solutions?¨
0
 

Author Comment

by:txbtech
ID: 22975717
The problem with that is I have readonly access to the DB.

There are over 300 structurally identical tables that this could be pulling from, so it would not be very feasible to create a view for each.

Again, what do you mean by "Dynamic SQL"? Stored Proc perhapse?

If that is the only solution, then I'm hosed.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 22975811
>Again, what do you mean by "Dynamic SQL"? Stored Proc perhapse?
yes, well a function actually, but that's indeed the only solution (using either a function returning data PIPELINEd , or via a REF CURSOR)...
0
 

Author Comment

by:txbtech
ID: 22976135
I don't suppose there is any way of using either in a Select statement is there?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22976451
at least not that I know. the table (and column) names cannot get put dynamically.
0
 

Author Comment

by:txbtech
ID: 22979050
Is that Business Objects offcial stance? I ask because the report works fine on my desktop.
0
 

Author Comment

by:txbtech
ID: 22984930
This should work, but it seems that the server isn't executing this properly.

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes/sdn_oss_boj_erq/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/scn_bosap/notes{6163636573733d36393736354636443646363436353344333933393338323636393736354637333631373036453646373436353733354636453735364436323635373233443330333033303331333233313335333133383336}.do

This subreport runs fine even on the server as a stand alone report, but as a subreport it fails.
0
 

Author Closing Comment

by:txbtech
ID: 31515303
It took a lot of prompting for the expert to finally give the specifics.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
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 shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

834 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