| Using Procedure/Function in a Select Statement [message #642975] |
Wed, 23 September 2015 21:18  |
Duane
Messages: 592
Registered: December 2002
|
Senior Member |
|
|
Trying to see if this can be done.
What I want to do is run a SELECT statement calling a package/procedure/function that would return a result set.
create or replace procedure get_results (emplid in varchar2,
result out sys_refcursor) is
begin
open result for
select *
from ws_grades
where student = emplid;
end get_results;
SQL Select would be:
select get_results('111') from dual;
Output would be:
I'm showing a very simple example but the actual package/procedure/function would do a lot more but still return a result set (sys_refcursor) as it's output.
Can that be done?
|
|
|
|
|
|
|
|
| Re: Using Procedure/Function in a Select Statement [message #642978 is a reply to message #642977] |
Wed, 23 September 2015 21:51  |
Duane
Messages: 592
Registered: December 2002
|
Senior Member |
|
|
This seems to work but the problem I see with this is the result is a "CURSOR". Clicking on that shows the result set I want.
create or replace function get_results (emplid in varchar2)
return sys_refcursor is
results sys_refcursor;
begin
open results for
select *
from grades
where student = emplid;
return results;
end get_results;
select get_results('0100') r from dual
Double Clicking (CURSOR) brings up:
|
|
|
|
| Re: Using Procedure/Function in a Select Statement [message #642979 is a reply to message #642978] |
Wed, 23 September 2015 22:24 |
John Watson
Messages: 9002
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
For example,orclz> create or replace type tabnum_t is table of number;
2 /
Type created.
orclz> create or replace function tabf
2 return tabnum_t pipelined
3 as
4 begin
5 for i in 1..5 loop
6 pipe row(i);
7 end loop;
8 end;
9 /
Function created.
orclz> select * from table(tabf);
COLUMN_VALUE
------------
1
2
3
4
5
orclz> |
|
|
|
| Re: Using Procedure/Function in a Select Statement [message #642980 is a reply to message #642978] |
Wed, 23 September 2015 22:25 |
Duane
Messages: 592
Registered: December 2002
|
Senior Member |
|
|
I think I got it. Please correct me if there is a better way of doing it.
create or replace type grade_type as object
(
id varchar2(20),
grade varchar2(1)
)
create or replace type grade_table as table of grade_type;
create or replace function get_results (emplid in varchar2)
return grade_table is
result grade_table;
begin
select grade_type(a.id, a.grade)
bulk collect into result
from (select id,
grade
from ws_grades
where id = emplid) a;
return result;
end;
select * from table(get_results('0100'));
|
|
|
|
| Re: Using Procedure/Function in a Select Statement [message #642981 is a reply to message #642979] |
Wed, 23 September 2015 22:30 |
Duane
Messages: 592
Registered: December 2002
|
Senior Member |
|
|
Thanks John. I wonder if your way is faster than what I came up with.
By chance, would you know if this would work with a DB Link? That's another requirement where a different campus would be calling this function.
select * from table(tabf)@DBLink;
|
|
|
|
|
|
| Re: Using Procedure/Function in a Select Statement [message #642983 is a reply to message #642982] |
Wed, 23 September 2015 22:44 |
Duane
Messages: 592
Registered: December 2002
|
Senior Member |
|
|
Can a VIEW accept an argument? I need to pass the student ID to the function.
Could you show me what this VIEW would look like using your example but your function would take one argument?
CREATE OR REPLACE FORCE VIEW GRADE (ID, GRADE) AS
select * from table(get_results('0100'));
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
