| SQL [message #620401] |
Thu, 31 July 2014 23:59  |
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
|
Senior Member |
|
|
select a.c1,a.c2,b.c3,b,c4,
pkg_1.function1(a.c3,trunc(b.c5_dt))
from tabl1 a, tabl2 b
where a.c1=b.c2
In the above sample query i need to call a function which is inside a package.
Is it possible to call a function which is inside a package . i did for standalone function before.
I have tried but got an error.
Apologize that currently my database is down cant able to give the exact error .
If any other way suggest me.
i cant go away with select statement as its single refcursor retruning to java , moreover the input values to the function will be
based on select values.
|
|
|
|
|
|
| Re: SQL [message #620403 is a reply to message #620401] |
Fri, 01 August 2014 00:13  |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
Create a package with a function:
create or replace package oef as
function oeff(a int) return char;
end;
/
create or replace package body oef as
function oeff(a int) return char is
begin
return ('a');
end;
end;
/
Try if we can call it in sql:
(2014-08-01 07:10:50) SYSTEM@ozprep >select oef.oeff(3) from dual;
OEF.OEFF(3)
---------------------------------------------------------------------------------------
a
Verstreken: 00:00:00.01
(2014-08-01 07:11:06) SYSTEM@ozprep >
So ... I would say : "Yes we can".
|
|
|
|
|
|
| Re: SQL [message #620406 is a reply to message #620403] |
Fri, 01 August 2014 00:51 |
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
|
Senior Member |
|
|
In Your examples you are passing values as hardcoded but my need is to take values from that select query column itself
select a.c1,a.c2,b.c3,b,c4,
pkg_1.function1(a.c3,trunc(b.c5_dt))
from tabl1 a, tabl2 b
where a.c1=b.c2 [Updated on: Fri, 01 August 2014 00:51] Report message to a moderator |
|
|
|
| Re: SQL [message #620408 is a reply to message #620406] |
Fri, 01 August 2014 01:04 |
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
|
Senior Member |
|
|
ORAGENASHOK wrote on Fri, 01 August 2014 07:51In Your examples you are passing values as hardcoded but my need is to take values from that select query column itself
select a.c1,a.c2,b.c3,b,c4,
pkg_1.function1(a.c3,trunc(b.c5_dt))
from tabl1 a, tabl2 b
where a.c1=b.c2
Not a problem:
create or replace package oef as
function oeff(a int) return char;
end;
/
create or replace package body oef as
function oeff(a int) return char is
begin
return ('a');
end;
end;
/
drop table ints;
create table ints(a int);
insert into ints values (1);
insert into ints values (2);
commit;
Results in :
(2014-08-01 08:02:38) SYSTEM@ozprep >@ff
Package is aangemaakt.
Verstreken: 00:00:00.01
Package-body is aangemaakt.
Verstreken: 00:00:00.03
Tabel is verwijderd.
Verstreken: 00:00:00.03
Tabel is aangemaakt.
Verstreken: 00:00:00.03
1 rij is aangemaakt.
Verstreken: 00:00:00.01
1 rij is aangemaakt.
Verstreken: 00:00:00.03
Commit is voltooid.
Verstreken: 00:00:00.01
(2014-08-01 08:02:42) SYSTEM@ozprep >
Now we have a working testcase.
Now let's try:
(2014-08-01 08:02:42) SYSTEM@ozprep >select a, oef.oeff(a) func from ints;
A FUNC
---------- --------------------
1 a
2 a
Verstreken: 00:00:00.06
(2014-08-01 08:03:42) SYSTEM@ozprep >
So, I would still say "Yes we can".
|
|
|
|
| Re: SQL [message #620414 is a reply to message #620408] |
Fri, 01 August 2014 02:38 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
Is the function you're trying to call declared in the package spec? It needs to be.
|
|
|
|
|
|
|
|
| Re: SQL [message #620588 is a reply to message #620584] |
Mon, 04 August 2014 08:45 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
ORAGENASHOK wrote on Mon, 04 August 2014 14:09No its from different package's funciton
I have no idea what that means
|
|
|
|
| Re: SQL [message #620589 is a reply to message #620584] |
Mon, 04 August 2014 08:54 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ORAGENASHOK wrote on Mon, 04 August 2014 18:39No its from different package's funciton
Different_package.function doesn't work for you?
Did you look at the test case posted by Martijn? That code snippet has a package, function, SQL. And the logic used is what you are looking for.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
