Home » SQL & PL/SQL » SQL & PL/SQL » using a package variable for a function argument (urgent) (oracle 9i)
using a package variable for a function argument (urgent) [message #329185] Tue, 24 June 2008 08:02 Go to next message
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
Member
Can any one give me an example how can I call a function which will except a package variable as an actual parameter.

say

create package p1
is
x number;
end;

create function f1(y number)
is
begin
return y;
end;

now -- execute p1.x:=10;

select f1(p1.x) from dual;

here it is showing error 'x' is not a procedure or undefined.

Then how to use the package variable as an argument for the function.
Re: using a package variable for a function argument (urgent) [message #329188 is a reply to message #329185] Tue, 24 June 2008 08:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you select from dual to call a function?
Re: using a package variable for a function argument (urgent) [message #329190 is a reply to message #329185] Tue, 24 June 2008 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is only one thing urgent for you: read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Tue, 24 June 2008 08:13]

Report message to a moderator

Re: using a package variable for a function argument (urgent) [message #329192 is a reply to message #329188] Tue, 24 June 2008 08:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is one of those places where SQL and PL/SQL behave differently.

SQL can only see procedures and functions - it can't see package variables, for reasons that I don't understand.

If you do:
BEGIN
  dbms_output.put_line(f1(pi.x));
END;
/
then it will work as you expect.

If you embed your SQL in some PL/SQL, then it will still work - because Pl/Sql gets the package value before the SQL is passed to the parser.:
declare
  v_ret  varchar2(10);
begin
  select p1.x 
  into   v_ret
  from   dual;
  
  dbms_output.put_line(v_ret);
end;
/

It's only if you try to access the package variable directly from SQL that it fails.
Re: using a package variable for a function argument (urgent) [message #329267 is a reply to message #329192] Tue, 24 June 2008 15:56 Go to previous messageGo to next message
rdebruyn
Messages: 17
Registered: June 2008
Location: Ottawa
Junior Member
Create a function get_x in the package like a java getter function. It should just return x. Use the function instaed of directly referencing the package global variable.
Re: using a package variable for a function argument (urgent) [message #329348 is a reply to message #329185] Wed, 25 June 2008 03:10 Go to previous messageGo to next message
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
Member
So SQL can only see procedures and functions - it can't see package variables. Now can anoyone expalain what is the reason behind this. It's a good anomaly.
Re: using a package variable for a function argument (urgent) [message #329350 is a reply to message #329348] Wed, 25 June 2008 03:14 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now can anoyone expalain what is the reason behind this

Because it is implemented like this.

Regards
Michel
Previous Topic: data_type as condition
Next Topic: Problem using join in sql query
Goto Forum:
  


Current Time: Thu Dec 08 03:57:40 CST 2016

Total time taken to generate the page: 0.22557 seconds