Hi RJamya, I think that a pipelined function would be appropriate in a situation where you would normally want to return multiple rows of data. In my case, I only want to return a single string value. Thanks for your reply, Regards, Mike On Mon, Apr 4, 2011 at 8:33 AM, rjamya <rjamya@xxxxxxxxx> wrote: > Mike since you need to do lookups perhaps pipelined function can help. It > will give you the flexibility of plsql while still allow you to use SQL > interface to retrieve data. > > Sent from my iPhone > > On Apr 3, 2011, at 12:03 PM, Michael Moore <michaeljmoore@xxxxxxxxx> > wrote: > > Lol Stephane, > > You are preaching to the choir here! > However, ultimately this is about making my company more profitable. There > are probably over a million lines of code (plsql java, > hibernate,struts,php,etc etc) that depend on this ... ahem, how shall I say > it ... sub-optimal database structure. The cost of making deep architectural > changes would be astronomical. Would it be worth it in the long run? But > that's the wrong question. The right question is, How would an expensive > re-archetecting project affect next quarter's EBIDTA and ultimately our > stock price? > > My original thinking on asking this question is that maybe there was some > feature of Oracle of which I was not aware. For example, maybe there is a > hint that allows 210 varchar2(4000) columns to be context-switched directly > into an array more quickly. Probably not, but I figured it couldn't hurt to > ask. > > Regards, > Mike > > > > On Sun, Apr 3, 2011 at 8:32 AM, Stephane Faroult < <sfaroult@xxxxxxxxxxxx> > sfaroult@xxxxxxxxxxxx> wrote: > >> Mike, >> >> No need to apologize, I didn't spend hours on it. But the problem is >> precisely the scope of what you are allowed to do, and that is a problem >> that is very common. >> >> <rant> >> I have seen umpteen cases of queries (or processes) that, when seen >> under a magnifier, are absolutely untunable - OK, let's say you can gain 10% >> after hours of testing of obscure parameters or features. But when you see >> that the query that takes the most time on a machine is some kind of "select >> attribute from mega_table where sequence_generated_pk=:1" you know that >> something is seriously wrong with the program and that no amount of "SQL >> tuning" can save you. Many people on this list will tell you the same thing. >> I am flabbergasted by the number of developers who before writing a join >> need to search the web for "SQL tips", and the number of managers who seem >> to believe that "SQL tuning" is something analogous to copy editing. It >> isn't. >> Why not as blatant as the join-in-a-loop, your case is in my eyes very >> similar and made worse by a totally twisted data repository (I cannot >> decently call THAT a database). But if you walk up the stack of function >> calls, perhaps one level up, perhaps two level ups, perhaps even more, you >> probably have a big loop (you mentioned a batch) that returns customer ids. >> This is where you need to hit, and hit hard, rethinking what you are given >> as input and what you need to produce in the end. This looks like a case >> where you can have performance gains of one order of magnitude. >> Needless to say, what you'd really need to have is a properly designed >> database, because the SQL that is required to make any sense of what you >> have to work with is probably slightly above the means of a developer with a >> couple of years of experience. >> </rant> >> >> If I were you I'd renegotiate the scope. >> >> >> Stephane Faroult >> RoughSea Ltd <http://www.roughsea.com> >> Konagora <http://www.konagora.com> >> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> >> >> On 04/03/2011 04:52 PM, Michael Moore wrote: >> >> Response to Stephane's post which starts: >> " Hmm, looks like one of the hidden reasons ..." >> >> Stephane, >> I feel a bit guilty that you made such a huge effort. While you solution >> is very clever, it addresses the problem at a level that is beyond the scope >> of what I am allowed to do. Specifically, I am not trying to resolve the >> virtual column names in a given 'where clause'. Instead, my task is to >> receive a single operand (virtual_column_name) and resolve it and then >> return it's value which is stored in the ABC table. I think my April 2nd >> post should make this clear. My apologies for not being more clear from, the >> beginning. >> >> Regards, >> Mike >> >> >> >