Oracle/Access SQL question

[Linflas]

I have a table in which one of the columns holds the names of other tables. Is there a way in SQL to use the table name from that column to select records from the table it names?

Table 1
UniqueID (indexed field)
<other columns>
ApplicationDataTableName

Table 2 (Name contained in ApplicationDataTableName in Table 1)
UniqueID (indexed field)
<other columns>

Any help will be much appreciated.

 

[Haircut]

Firstly which are you using, Oracle or Access there is quite a difference.

In Oracle you can dynamically build up a string containing the SQL statement to select from Table 2 and then get the results using EXECUTE IMMEDIATE <SQL string>

I don't know enough about Access to know if you can do something similiar, but I suspect not.

 

[Linflas]

I am doing it as a pass through query from Access to Oracle. I can also experiment with it using SQLPlus directly in Oracle if necessary.

 

[Linflas]

Bump hopefully with more clarity.

Table1
UniqueID (this is the column I use for the lookup)
TableID <----Contains a table name
Field1
Field2


Table<Name of which is contained in TableID in Table1>
UniqueID
DataItem1
DataItem2

Kind of a generic SQL of what I need to do:

Select Field1,Field2,DataItem1,DataItem2 From Table1, Table<Name of which is contained in TableID in Table1>
Where Table1.Unique = Table<Name of which is contained in TableID in Table1>.UniqueID

 

[CaffeineAndStuff]

No idea about Access and its support for Dynamic SQL but as Haircut said for Oracle Dynamic SQL is the way to do this.
It should be detailed in the PL/SQL reference manual or in Steve Feuerstein's PL/SQL book (my copy is at work so I can't say for certain). I find Feuerstein explains PL/SQL really well.

 

[Linflas]

Thanks I think I saw that book over at Borders the other day. Sounds like it might be a good one to pick up. As for Access it is just a front end to get to the Oracle tables. I can do passthrough queries which are passed by ODBC to the Oracle tables so I use standard Oracle SQL syntax. I am not sure how it will deal with PL/SQL but it is worth a try. I have access to Oracle SQL Plus but the normal user I am working with does not which is why I am doing all this through the Access frontend.

 

[Haircut]

If you are doing it through ODBC to Oracle, then your best bet might be to create a stored procedure in the Oracle DB and call this through ODBC.

You would need to know some PL/SQL to do this though, if you do go down this route then I thoroughly recommend the book by Steven Feuerstein that CaffeineAndStuff mentioned if you need a reference.

 

[ecrespol]

Interesting idea.

Sorry to highjack your thread, but I have two simple questions on oracle/sql+.

1. Is there anyway via a simple sql statment add somthing like a conditional drop when a table does or does not exist (or based on a field in a table). Something that would work in SQL+?

2. In SQL+ is there anyway to increase the amount of text that can be pasted in at one time.

Thanks

 

[bbrontosaurus]

Originally posted by: ecrespol
Interesting idea.

Sorry to highjack your thread, but I have two simple questions on oracle/sql+.

1. Is there anyway via a simple sql statment add somthing like a conditional drop when a table does or does not exist (or based on a field in a table). Something that would work in SQL+?

2. In SQL+ is there anyway to increase the amount of text that can be pasted in at one time.

Thanks

1) DROP TABLE <table name> will drop a table for you (provided that you have the correct privillages/or you are the table owner). If you have a table that does not exist, you will get the error ORA-00942: table or view does not exist. If it does exist, the table specified will be dropped. Is that what you are trying to accomplish with your conditional statement?