Re: Create a view using OUT values from a procedure

From: David Fitzjarrell <fitzjarrell_at_cox.net>
Date: 8 Oct 2004 11:16:56 -0700
Message-ID: <9711ade0.0410081016.6813f98e@posting.google.com>

>
> This makes it difficult, at best, for anyone to provide any reliable
> or pertinent assistance. It is also extremely frustrating to find the
> problem has been severely misstated, to the point of deception, and
> that the actual issue is nothing like the example posted. Yes, by
> your own admission this example was foolish for the reaseons you cite.
> It also makes it highly unlikely for you to receive any assistance in
> the future for any other problem you may have.
>
> Now, just WHAT is the real issue here, and why won't functions perform
> the way you'd like?
>
> David Fitzjarrell

Really, I posted a foolish example in order to keep things simple. I admited my foolishness, but since I did it once, it is not likely I will
get help in the future. Is that really the way the group works around here?

Anyway, the underlying question reamins the same.

I'm trying to create a view.
Two columns in this view will contain results from a complex function. -The results are highly correlated, so calling two functions would be inneficient.
How do I get the results of the function call into the view.

The original question was how do I get them from a procedure, but the group
said this cannong be done.

Thanks,

Mike

The fact still remains: you cannot call a procedure within a query. Functions you may call in a select list. One suggestion you've been given is to re-write your procedure as a function and return concatenated results as a single variable. You would then use substr()(and, possibly, instr()) to parse the returned value and populate your view:

create or replace mycomplicatedfunction(someval in sometype) return someotherval varchar2 as

valstring varchar2(200);
begin

    .....
    /* some complex processing here to determine val1 */     valstring:=val1;
    .....
    /* some complex processing here to determine val2 */     valstring:=valstring||':'||val2;
    return valstring;
end;
/

Then, on the view end you could:

Create view student.v_grades
(str_student, studentID, str_higrade, str_lowgrade) AS
Select studentdb.student,

          Studentdb.studentID,
          substr(mycomplicatedfunction(myval), 1,
instr(mycomplicatedfunction(myval), ':') - 1),
          substr(mycomplicatedfunction(myval),

instr(mycomplicatedfunction(myval), ':') +1) FROM studentdb
WHERE ( ¡K) thus parsing the values separated by a ':' as generated by your function.

Possibly this is what you're looking for, although it does make four calls to the function. Two separate functions would only make two calls, one for each functional value, however you're saying you cannot write two functions due to the correlation of the two returned values.

I don't know if you'll find an elegant, or efficient, solution to your problem.

David Fitzjarrell Received on Fri Oct 08 2004 - 13:16:56 CDT

Re: Create a view using OUT values from a procedure - how?