Re: What is the best way to do this?

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_at_gmail.com> 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_at_gmail.com>
> 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_at_roughsea.com>
> sfaroult_at_roughsea.com> 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
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l