Home » SQL & PL/SQL » SQL & PL/SQL » ref cursors / without dynamic sql
ref cursors / without dynamic sql [message #291228] Thu, 03 January 2008 08:00 Go to next message
PORSCHITO
Messages: 5
Registered: October 2007
Junior Member
i have a senario where the field names are stored in one table, and the records are stored in a different table, how can i without using dynamic sql retrieve the column names from one table and combine them with records from the other table in a ref cursor...
Re: ref cursors / without dynamic sql [message #291229 is a reply to message #291228] Thu, 03 January 2008 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.

This is one of worst design that can exist.

Regards
Michel
Re: ref cursors / without dynamic sql [message #291232 is a reply to message #291229] Thu, 03 January 2008 08:07 Go to previous messageGo to next message
PORSCHITO
Messages: 5
Registered: October 2007
Junior Member
but we are trying to build a generic design with some sort of flexibility instead of creating a new table every time there is a need for one.
Re: ref cursors / without dynamic sql [message #291247 is a reply to message #291232] Thu, 03 January 2008 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And this is still the worst design you can imagine, not scallable.
bad, bad, bad.

Regards
Michel
Re: ref cursors / without dynamic sql [message #291292 is a reply to message #291228] Thu, 03 January 2008 12:51 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Flexible ?? If you say so .. good luck with maintenance and performance
Re: ref cursors / without dynamic sql [message #291414 is a reply to message #291232] Fri, 04 January 2008 02:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
PORSCHITO wrote on Thu, 03 January 2008 15:07

but we are trying to build a generic design with some sort of flexibility instead of creating a new table every time there is a need for one.

Wrong order.
First think what tables you will need. Then start coding.
If you use a proper design, there will be either no need for adding tables, or no problem when the need arises.
Re: ref cursors / without dynamic sql [message #291549 is a reply to message #291228] Fri, 04 January 2008 15:47 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
As you can see, all the experts are telling you, bad design.

Consider this: if this is such a great way to go, why are you here asking this question? The people who created this design for you should be able to tell you how to query without dynamic sql right? I mean they knew what they were doing when they created the design right? They must have figured people were going to be querying data from it right? What do they have to say? Use dynamic sql for everything? oh man, won't that kill your cursor cache...

Head over to ASKTOMHOME and look for his response to GENERIC DESIGNS. I think he even devoted an entire chapter in one of his books to it.

Good luck, Kevin
Previous Topic: Seperate-Comma (merged)
Next Topic: Function based index creation.
Goto Forum:
  


Current Time: Sun Dec 04 23:03:29 CST 2016

Total time taken to generate the page: 0.12186 seconds