Oracle create function and return ref_cursor 1

[davism]

All,

Does anybody have any information how to create a function and have that function return a ref_cursor?

Any examples with no packages or anything would be great and very much appreciated.

Thanks

 

[davism]

SantaMufasa,

Yep, it was a date. IMonth beginning with the "I" indicated an Integer but it was defined as a CHAR. That's all I was getting at. I got everything worked out on that one (that SP).

Now, I have the function calling that SP but I'm running into a glitch getting there and this is causing some confusion for me. You may be able to help guide me here:

In the function I have in the beginning of the CREATE OR REPLACE statement:

pdtInitDate IN DATE := '01/01/1753',
.
.
.

Then further down I have:

IF (pdtInitDate = '01/01/1753') THEN

pdtInitDate := SYSDATE;

END IF;
.
.
.

Now, when I try to compile I get an error regarding "PLS-00363: expression 'PDTINITDATE' cannot be used as an assignment target.

Any idea on that?

Thanks

 

[SantaMufasa]

Davism,

Sorry, but you need to offer more context for:

pdtInitDate    IN DATE := '01/01/1753',

Is that an argument in the FUNCTION header, or is that a declared variable? (Either way, the definition has syntax issues, but I can't offer a solution until I understand better the context.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[davism]

That is an argument in the function header:

CREATE OR REPLACE FUNCTION mySchema.Myfunction
(
-- =============================================
-- declare input parametes
-- =============================================

pRESULT_CURSOR OUT SYS_REFCURSOR,
pdtInitDate IN DATE := '01/01/1753',
pvcValue1 IN VARCHAR2 := ''
)

RETURN NUMBER

AS

Make better sense? May not actually but I tried.

 

[SantaMufasa]

Davism,

There are a couple of coding issues in your function:[ul][li]If you declare "pdtInitDate" as an "IN" argument, it is read only...you cannot assign it a value. You can, however, create some other locally declared variable to which you can assign either "pdtInitDate" or SYSDATE (as I have done in the revised code, below).[/li][li]When dealing with DATE expressions for which you enter literal values, it is good form (and can avoid runtime errors such as will happen in your code) to cast explicitly character literals into DATEs using Oralce's TO_DATE function (also as I illustrate in the code, below)[/li][/ul](In this revised code, to reduce the complexity of the illustration, I have remarked out the SYS_REFCURSOR)

CREATE OR REPLACE FUNCTION davism
(
-- =============================================
-- declare input parametes
-- =============================================

     -- pRESULT_CURSOR  OUT SYS_REFCURSOR,
     pdtInitDate    IN DATE := to_date('01/01/1753','mm/dd/yyyy'),
     pvcValue1      IN VARCHAR2 default null
)

RETURN NUMBER
AS
    hold_date date;
begin
    hold_date := pdtInitDate;
    IF pdtInitDate = to_date('01/01/1753','mm/dd/yyyy') THEN
        hold_date := SYSDATE;
    END IF;
    return to_char(hold_date,'yyyy');
end;
/

Function created.

select davism(sysdate-2000,'hello') davism from dual;

    DAVISM
----------
      2003

select davism from dual;

    DAVISM
----------
      2008

Let us know if this example is helpful to you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[davism]

I have to say DDDDDOOOOHHHHH on the to_char from a DATE aspect. That never really hit me.

Are you saying that I have to declar and use a variable rather than using what is there? (i.e. hold_date instead of pdtInitDate)

 

[davism]

Ok, I've run into a little bit of a road block on the function. I thought I understood but obviously I didn't, completely.

At the end of my processing in the function I am trying to do a SELECT with an order by thinking that it was going to go into the refcursor.

I have a global temporary table that I'm using throughout the function and then doing a SELECT at the end of everything to get everything in the global temporary table.

What is happening is I get an error message on the compilation that says "an INTO clause is expected in this SELECT statement."

In addition in the example I had:

CREATE OR REPLACE FUNCTION mySchema.Myfunction
(
-- =============================================
-- declare input parametes
-- =============================================

     pRESULT_CURSOR  OUT SYS_REFCURSOR,
     pdtInitDate    IN DATE := '01/01/1753',
     pvcValue1      IN VARCHAR2     := ''
)

RETURN NUMBER

AS

But in looking at some of your examples you indicate:

RETURN <whatever> IS

What is the difference with the "AS" and the "IS". I want it to return a NUMBER but that is more the status. I really want the refcursor (which basically contains the information from the final SELECT that I do.)

 

[SantaMufasa]

Are you saying that I have to declar and use a variable rather than using what is there? (i.e. hold_date instead of pdtInitDate)

You can "use" pdtInitDate all you want, so long as the "use" is read-only. That limitation exists because you declared pdtInitDate as an IN-usage expression (which = "READ ONLY"). If you declare pdtInitDate as IN OUT (and whatever you you passed into the function as the expression for pdtInitDate from the invoking environment is a defined memory variable elsewhere, then, yes you can assign a value from within the function since now pdtInitDate is not only readable but also writable.

At the end of my processing in the function I am trying to do a SELECT with an order by thinking that it was going to go into the refcursor...I get an error message on the compilation that says "an INTO clause is expected in this SELECT statement."

Your problem is symptomatic of incorrectly specifying (and OPENing) your sys_refcursor. A working model of the syntax to use to populate a sys_refcursor (which you can then return to the invoking environment) appears in my post timestamped, "21 Nov 08 20:29", above. The "brains" of accessing a sys_refcursor lie in the code:

open <cursor_name> for <some SELECT statement>; -- Notice there is no "INTO" clause in the OPEN statement

I then access data from the open sys_refcursor with this PL/SQL code (but the syntax will be different if you are using some other language environment to "read" data from the sys_refcursor):

...
    loop
        fetch <cursor name> into <record description>; -- [B][I]Here[/I][/B] is the "INTO" clause
        exit when <cursor name>%notfound;
        <do some processing here>
    end loop;
...
end;
/

So, using the syntax constructs, above, there is no "INTO" claue in the SELECT statement...the "INTO" clause appears in the FETCH statement. (I'll bet your syntax that you were trying differs from my construct, above, right?)

What is the difference with the "AS" and the "IS".

No difference, except spelling. They have the same effect, syntactically.

I really want the refcursor (which basically contains the information from the final SELECT that I do.)

Notice in my earlier example (from timestamp "21 Nov 08 20:29") that my function returns a sys_refcursor.




Let me know your follow-on questions.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[davism]

Is this as simple as putting the

OPEN pRESULT_CURSOR FOR

Before the SELECT is it? I put that in and it compiles. BUT just because it compiled does not mean that is the right way.

Would I be able to test with a revision of the same you did of:

set serveroutput on
declare
    foo sys_refcursor;
    y s_emp%rowtype;    
begin
    foo := open_cursor('select * from s_emp order by last_name, first_name');
    loop
        fetch foo into y;
        exit when foo%notfound;
        dbms_output.put_line(y.last_name||', '||y.first_name);
    end loop;
end;
/

I'm asking before I "jump" and do that and potentially wasting time.

Please let me know.

Thanks!

 

[SantaMufasa]

Would I be able to test with a revision of the same you did...?

Absolutely!

And you can test it using your data, as well...If you have too many rows (e.g. millions of rows) to test the full data set, you can limit your query to just the first three rows by formulating your query as:

...SELECT <whatever>
     FROM <wherever>
    WHERE rownum <= 3...

Let us know how things go for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[davism]

Ok, I'm having some trouble on this one.

I am using a hacked up version of the same and trying to run it through SQLPLUS:

set serveroutput on
declare
    foo sys_refcursor;
    y gblTempTable%rowtype;    
begin
    foo := MySchema.MyFunction();
    loop
        fetch foo into y;
        exit when foo%notfound;
        dbms_output.put_line(y.GBL_LIST||', '||y.GBL_OUT);
    end loop;
end;
/

Remember, I have the function starting with:

CREATE OR REPLACE FUNCTION mySchema.Myfunction
(
-- =============================================
-- declare input parametes
-- =============================================

     pRESULT_CURSOR  OUT SYS_REFCURSOR,
     pdtInitDate    IN DATE := to_date('01/01/1753','mm/dd/yyyy'),
     pvcValue1      IN VARCHAR2     default null
)

RETURN NUMBER

AS

The global temporary table has 2 fields GBL_LIST and GBL_OUT.

So, at the end of the function I am doing a:

OPEN pRESULT_CURSOR FOR
	SELECT GBL_LIST, GBL_OUT from gblTempTable 
	
        ORDER BY GBL_LIST;
   
RETURN 0;

I'm getting errors saying that there are the wrong number of arguments for "MyFunction".

Any thoughts on this? What am I missing?

 

[SantaMufasa]

Davism,

Since your function has no arguments, in Oracle, you invoke a no-argument function this way:

    foo := MySchema.MyFunction;

Notice, there are no parentheses on a no-argument function.


Let us know if this resolves your issue.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[davism]

Ok, I will give that a try. One thing to note is that it may or may not have an argument.

Just an FYI--> I just tried and I still get the "wrong number of arguments in call to "MyFunction".

What could this be?

 

[SantaMufasa]

My bad, DavisM, Sorry...I am so used to seeing arguments immediately to the right of the function/procedure name that I spaced the fact that you have 3 function arguments (an OUT SYS_REFCURSOR, an IN DATE, and an IN VARCHAR2). In the function invocation, you must specify appropriate argument names for each. If you do not specify an argument name, then you must have a "DEFAULT" specification in the function-header definition.

So, in your invocaton, please specify a valid argument name for each of the three arguments.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[davism]

Can you clarify on that one a little, please? I do have a default null on the pvcValue1. Even though the refcursor is an out I need to assign that to a variable?

I thought that is what the "foo" would be assigned to? Or is "foo" on the invocation assigned to the RETURN value that I have in the end of the function? (i.e. the "RETURN 0").

Please let me know.

Thanks

 

[SantaMufasa]

Davism,

Typically, functions do not have OUT arguments (since the function itself represents the RETURN data). But Oracle's PL/SQL allows OUT argument in addition to RETURN data.

You can structure your function to accommodate the SYS_REFCURSOR data either as the OUT argument or as RETURN data.

But if you have an OUT argument, there is no DEFAULT option...you cannot have a zero-argument argument list.

Did I answer your question(s)? Let me know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[davism]

I'm having a little bit of problem getting the test script changed to work properly.

Do you happen to have an example with using those 3 arguments that you refer to with same code I provided?

Using this the above code:

set serveroutput on
declare
    foo sys_refcursor;
    y gblTempTable%rowtype;    
begin
    foo := MySchema.MyFunction();
    loop
        fetch foo into y;
        exit when foo%notfound;
        dbms_output.put_line(y.GBL_LIST||', '||y.GBL_OUT);
    end loop;
end;
/

Knowing SQL Server; Oracle is a little different and I'm struggling with this one a little and so far you're help has been great! and I appreciate so much so far.

Any sample using what you mentioned on the arguments with like the code above would be great!

 

[SantaMufasa]

Davism,

I'm posting, below, working code that simulates the type of thing you are trying to do, using the code fragments you have posted.

I am flying a little bit blind, however, since I have no clue as to:[ul][li]the use in your function of your two IN arguments, "pdtInitDate" and "pvcValue1", since you never refer to those arguments anywhere in your function. (They appear extraneous/useless as far as I can tell.)[/li][li]actual data values in your "gblTempTable's" two columns[/li][li]the use of the number you RETURN at the end of the function.[/li][/ul]I, personally, would RETURN the sys_refcursor instead of making it an OUT argument.


In any case, following are:[ul][li]sample data (that I made up) for the "gblTempTable"[/li][li]working code for "MyFunction()"[/li][li]working code that invokes, and uses, the "MyFunction()" function:[/li][/ul]

select * from gblTempTable;

GBL_LIST   GBL_OUT
---------- ----------
abc        def
ghi        jkl
mno        pqr

3 rows selected.

CREATE OR REPLACE FUNCTION Myfunction
(
-- =============================================
-- declare input parametes
-- =============================================
     pRESULT_CURSOR  OUT SYS_REFCURSOR,
     pdtInitDate    IN DATE := to_date('01/01/1753','mm/dd/yyyy'),
     pvcValue1      IN VARCHAR2     default null
)
RETURN NUMBER
AS
begin
    OPEN pRESULT_CURSOR FOR
        SELECT GBL_LIST, GBL_OUT from gblTempTable   
         ORDER BY GBL_LIST; 
    RETURN 0;
end;
/

Function created.

set serveroutput on
declare
    foo sys_refcursor;
    y gblTempTable%rowtype;
    some_number number;   
begin
    some_number := MyFunction(foo);
    loop
        fetch foo into y;
        exit when foo%notfound;
        dbms_output.put_line(y.GBL_LIST||', '||y.GBL_OUT);
    end loop;
end;
/
abc, def
ghi, jkl
mno, pqr

PL/SQL procedure successfully completed.

Your earlier code probably received this error:

    foo := MyFunction(foo);
           *
ERROR at line <n>:
ORA-06550: line <n>, column 12:
PLS-00382: expression is of wrong type

The reason for the error is because "foo" is a sys_refcursor, but the RETURN data type from your function is NUMBER, which are incompatible data types with one another. All I did to resolve the error was to assign the RETURNing value from MyFunction to "some_number" (a number type), to receive the RETURN value "0"...then it all works.


Let us know if all of this helps to resolve your issues.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[davism]

SantaMufasa,

I tried:

set serveroutput on
declare
    foo sys_refcursor;
    y MySchema.gblTempTable%rowtype;
    some_number number;    
begin
    some_number := MySchema.HARSP_MyFunction(foo);
    loop
        fetch foo into y;
        exit when foo%notfound;
        dbms_output.put_line(y.GBL_LIST||', '||y.GBL_OUT);
    end loop;
end;
/

When I run this in SQLPLUS I get:

SQL> @test.sql
declare
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 8

Why is it that I'm not getting what you have?

The pdtInitDate and pvcValue1 are actually used in the function. I'm using a sample function on some of this.

The parameters are going to be optionally populated and I'm trying to get the most routine done first, which is no date will be provided. The next most common situation is a date will be populated in pdtInitDate.

But in the test as mentioned above I'm getting the above error. Any idea as to why a difference?

 

[SantaMufasa]

Davism,

I cannot diagnose your error without seeing your function-definition code. (My first thought is that there is a problem with the function's opening the cursor.) Please post your function code (including the "function created" confirmation.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[davism]

Let me try a few things first before I do that.

Will get back to you soon either way.