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

From: David Fitzjarrell <fitzjarrell_at_cox.net>
Date: 8 Oct 2004 08:00:03 -0700
Message-ID: <9711ade0.0410080700.743763b4@posting.google.com>

"Mike" <none_at_nospam.com> wrote in message news:4syhgrbsywpd.90wdk4tuxmp1$.dlg_at_40tude.net...

> On 8 Oct 2004 05:21:27 -0700, David Fitzjarrell wrote:
> 
>> "Mike" < none_at_nospan.com> wrote in message news:25tcm0p4mridsv9urf7mejkr8ri4g3vpb4_at_4ax.com...
>>> On Thu, 07 Oct 2004 18:29:15 -0700, Daniel Morgan
>>> <damorgan_at_x.washington.edu> wrote:
>>> 

>>>>Mike wrote:
>>>>
>>>>> Hi, I¡¦m creating a view and would like to have two of the
columns

>> to

>>>>> contain data obtained from a procedure which has two out values.

>> I'm using


>>>>> Oracle 9.2

>>>>> 

>>>>> For example 

>>>>> 

>>>>> Create view student.v_grades

>>>>> (str_student, studentID, str_higrade, str_lowgrade)

>>>>> AS

>>>>> Select studentdb.student,

>>>>>           Studentdb.studentID,

>>>>> /*  How do I get student grades from the procedure */

>>>>> FROM studentdb

>>>>> WHERE ( ¡K)

>>>>> 

>>>>> 

>>>>> e.g. procedure:

>>>>> 

>>>>> Create Procedure PR_getgrades(

>>>>> P_higrade OUT VARCHAR2,

>>>>> P_lowgrade OUT VARCHAR2

>>>>> P_studentID IN Number)

>>>>> IS

>>>>> BEGIN

>>>>> 

>>>>> /*process data*/

>>>>> 

>>>>> P_higrade := result1;

>>>>> P_lowgrade :=result2;

>>>>> 

>>>>> End;

>>>>> END PR_getgrades;

>>>>> /

>>>>> 

>>>>> Thanks,

>>>>>
>>>>> Mike
>>>>
>>>>It can not be done.
>>>>
>>>>Hasn't your instructor taught you anything about how Oracle works?
>>>>
>>>>There are solutions ... but as this is clearly classwork you need
>>>>to figure them out for yourself. So what you get is a hint.
>>>>
>>>>Hint: Use functions.

>>> 
>>> 
>>> Hi, I am not a student, although I am a noob to Oracle PL/SQL.

Using
>>> functions does not solve my problem, because a function can only >>> return one value, and I need two values. I could create two functions

>>> and do double the processing, but this seems inefficient, but
>>> according to this group this is the only way. True?
>>> 
>>> Thanks,
>>> 
>>> Mike
>>> 
>> 
>> Why do you see a two functions as 'double the procesing'?  Yes, it
>> would generally require two functions to return the data, but how
>> difficult can it be to return the max() and min() grades for a

given

>> student?  Especially when all you're using is the student id to
>> retrieve them?
>> 
>> create or replace function highgrade(p_StudentId in number) return
>> number is
>>    higrd number;
>> begin
>>    select max(grade) into higrd from studentdb where studentid =
>> p_StudentId;
>>    return higrd;
>> end;
>> /
>> 
>> create or replace function lowgrade(p_StudentId in number) return
>> number is
>>    logrd number;
>> begin
>>    select min(grade) into logrd from studentdb where studentid =
>> p_StudentId;
>>    return logrd;
>> end;
>> /
>> 
>> Create view student.v_grades
>> (str_student, studentID, str_higrade, str_lowgrade)
>> AS
>> Select studentdb.student,
>>        Studentdb.studentID,
>>        highgrade(studentdb.studentID),
>>        lowgrade(studentdb.studentID)
>> FROM studentdb
>> WHERE ...
>> 
>> I can't see the problem, or the issue.
>> 
>> David Fitzjarrell
> 
> 
> Thanks for the reponse, but the problem is, the real world problem I am
> working on is much more complex, and I now realize the example I used was a
> foolish one because. 1) people think I'm doing a class assignment, and 2)
> the result appears easy to accomplish. The reality is, I will be calling a
> a function/procedure that is much more complex, yet the results are higly
> correlated.
> 
> Mike 
> 

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 Received on Fri Oct 08 2004 - 10:00:03 CDT