Home » Other » Training & Certification » UDF question (oracle 10g)
UDF question [message #315817] Tue, 22 April 2008 20:12 Go to next message
Michael GIllespie
Messages: 8
Registered: January 2004
Junior Member
I have a udf question. To give background i'm trying to create a udf to retrieve a value. The function will be used within an application. Here's the catch. I can only query one table within the application. The table has a primary key column of id. Which is an input variable i'm passing to the function. The function is bolean returning 0 or 1. The value I need to check is in a secondary table which I can access via a join on the id column in the function. The issue is there is a two column key in the second table of prof,id. So the id can exist for each prof. If I could pass the function the prof as input it would be easy however that is not an option. I need the function to return the proper value for each prof,id combination.
Re: UDF question [message #315819 is a reply to message #315817] Tue, 22 April 2008 20:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
With all the seemingly arbitrary "restriction", this smells to me to be nothing more than a home work assignment posted to the incorrect sub-forum.
Re: UDF question [message #315845 is a reply to message #315817] Tue, 22 April 2008 23:41 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
> The function is bolean returning 0 or 1.
> I need the function to return the proper value for each prof,id combination.

Are not these two statements contradictory?
You may "return" the desired value in an OUT paramater in proper data type (collection or cursor).
Re: UDF question [message #315942 is a reply to message #315819] Wed, 23 April 2008 05:25 Go to previous messageGo to next message
Michael GIllespie
Messages: 8
Registered: January 2004
Junior Member
Your opinion but your wrong. It's a real application.
Re: UDF question [message #315945 is a reply to message #315845] Wed, 23 April 2008 05:27 Go to previous messageGo to next message
Michael GIllespie
Messages: 8
Registered: January 2004
Junior Member
AFAIK u cant create a function that have more than one out variable
a function is that it gets any number of inputs and returns only one value
A procedure can have any number of in (input) variables and any number of out(return variable)variables.
A procedure can have variables that acts as both in and out variables

This is a function.
Re: UDF question [message #315948 is a reply to message #315945] Wed, 23 April 2008 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
AFAIK u cant create a function that have more than one out variable

Wrong.

Quote:
a function is that it gets any number of inputs and returns only one value

Wrong.

Quote:
A procedure can have any number of in (input) variables and any number of out(return variable)variables.
A procedure can have variables that acts as both in and out variables

As well as function.

SQL> create or replace function f (v1 out number, v2 out number) return number
  2  is
  3  begin
  4    v1 := 1;
  5    v2 := 2;
  6    return 0;
  7  end;
  8  /

Function created.

SQL> declare
  2    l0 number;
  3    l1 number;
  4    l2 number;
  5  begin
  6    l0 := f (l1, l2);
  7    dbms_output.put_line ('l0='||l0||', l1='||l1||', l2='||l2);
  8  end;
  9  /
l0=0, l1=1, l2=2

PL/SQL procedure successfully completed.

Regards
Michel

[Updated on: Wed, 23 April 2008 05:39]

Report message to a moderator

Re: UDF question [message #315950 is a reply to message #315817] Wed, 23 April 2008 05:47 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Maybe "u" cannot create more OUT parameters, but I see no reason why anybody else could not. Function may have same number and types of parameters as procedure; function additionally returns a value.

In both, there is a problem using dynamic number of parameters (not known in compile time); in that case you shall use collection or cursor - it depends on your preferences.
Previous Topic: Difference bet Trunc,delete,drop
Next Topic: triggers help
Goto Forum:
  


Current Time: Thu Nov 21 14:21:59 CST 2024