Home » SQL & PL/SQL » SQL & PL/SQL » Calling a function
Calling a function [message #231861] Wed, 18 April 2007 11:55 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member

I tried calling the function that i wrote

CREATE OR REPLACE FUNCTION FN_TEST" (p_user_sid number,p_user_role varchar2)
return types.cursor_type
AS
workload_summary_cursor types.cursor_type;

begin
If p_user_role = 'IA' Then
OPEN Workload_summary_cursor FOR
<Select statement >
ElsIf p_user_role = 'TL' Then
OPEN Workload_summary_cursor FOR
<select statement >
return Workload_summary_cursor;
end;


In the above types package that defines a ref_cursor

CREATE OR REPLACE PACKAGE PPI_UDB_RPC.Types AS
TYPE cursor_type IS REF CURSOR;
END Types;
/


I would like to do the following

SELECT FN_TEST(12024,'IA') from dual;

This gives me the error

OCI-21560 argument 3 is null,invalid or out of range

Ananthi
Re: Calling a function [message #231867 is a reply to message #231861] Wed, 18 April 2007 12:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
OK, I give up.
Exactly what did you hope/expect was going to be produced by: SELECT FN_TEST(12024,'IA') from dual;


What problem are you really trying to solve?
Re: Calling a function [message #231891 is a reply to message #231867] Wed, 18 April 2007 13:59 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
My questions are:

1. my function returns 4 values in a ref cursor.
workload_summary_cursor is defined types.cursor_type;
where types is a ref cursor.

2. How could i use the function in the select statement

like

select fn_test(12245,'IA') from dual:

I want to see the result set with the 4 values that the function returns in the ref cursor.

Hope i have explained it better.

Anu

Re: Calling a function [message #231892 is a reply to message #231861] Wed, 18 April 2007 14:27 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Let me simply say an error is returned because that is NOT how things work.
For one - a "ref cursor" is a PL/SQL object/data type; which SQL knows nothing about.
Forget about this idea & formulate an entirely new/different solution.

What business problem are you trying to solve?
Previous Topic: build formula
Next Topic: Strange output - Please Advise
Goto Forum:
  


Current Time: Fri Dec 09 17:43:42 CST 2016

Total time taken to generate the page: 0.34499 seconds