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

 

[SantaMufasa]

Davism,

Could you please offer a bit more context to your need, including an example of the invoking statment you want to use for the function?

Thanks,

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 looking for something that for instance (in SQL Server means):

Create procedure mytest

as

begin

select * from mytestDB mydb, mytesthold myhld where mydb.mytestcol = myhld.thiscolumn

end

return @@error

I know Oracle SP's do not return return codes. So, I was looking at a function and having the function returning a ref_cursor (which I understand is like a recordset). So, the ref_cursor would have like the contents of the recordset from the above query.

Hope this makes a litte better sense.

 

[SantaMufasa]

Thanks, Davism, for the update.

How do you want to actually use (i.e. invoke) "mytest"? Can you offer a coded example of the invocation (not the definition) of "mytest"?

In Oracle, we usually would not use a SQL-Server-style recordset...we would typically just use an in-line query. So, if you can post some context for the invocation, it would help us provide to you a good Oracle-convention solution (which might, as you requested, end up being a function that returns a refcursor. <grin>)

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's what I'm not entirely certain of yet.

I have a strong feeling I'm going to be using like OCI or ADO in VB.NET to instantiate the object and use like the .CommandText or something along those lines to execute it. Now, I'm not entirely sure yet if VB.NET on the open of the recordset (in this case like a ref_cursor) if it's different on invocation (i.e. the CommandText to execution) with a function or SP.

I know an Oracle SP can also return the ref_cursor but it doesn't return a status. A function can. That's an important factor that I need to take into account which it why I'm looking at a function.

 

[SantaMufasa]

Davism,

Here is an extremely simplified user-defined function named, "open_cursor", that returns the contents of a sys_refcursor:

create or replace function open_cursor (query_in varchar2) return sys_refcursor is
    c sys_refcursor;
begin
    open c for query_in;
    return c;
end;
/

Function created.

Here is an invocation (from a SQL*Plus prompt) of that function:

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;
/

Biri, Ben
Catchpole, Antoinette
Chang, Eddie
Dancs, Bela
Dumas, Andre

PL/SQL procedure successfully completed.

I don't know how things would change by your invoking open_cursor from VB.NET.

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.”

 

[sem]

Another ref_cursor demonstration is an ability of sql*plus (generic Oracle database tool) to PRINT such variables:

SQL> VAR A REFCURSOR
SQL> EXEC :A := open_cursor('SELECT 1 FROM DUAL')

PL/SQL procedure successfully completed.

SQL> PRINT A

         1
----------
         1

Regards, Dima

http://seminihin.narod.ru

 

[davism]

Thanks. I have used the SQLPlus aspect.

I'm more trying to associate to a VB.NET. Again, I think it's the CommandType aspect referring to a Stored Procedure (when in actuality it is a function that is executing)...still working it.

 

[dstxaix]

Maybe someone at the VB.NET forum can give you some pointers?

 

[davism]

I was going to work that one but the biggest thing was the example of the ref_cur with a function. That's looking good, so far.

I'm working it to get to a point of testing before I cross that bridge with the VB.NET.

 

[davism]

Ok, something just hit me. Hopefully, somebody has some info related to this.

I previously in SQL Server had some something like:

EXEC mytestProc ptestval1 = lmytestval1, ptestval2 = lmytestval2, pResult = lresult OUTPUT

Basically, this is executing another SP from within an existing SP and I'm providing variation parameters to the SP I'm calling.

The mytestProc SP in this example is taking in 2 parameters (ptestval1, and ptestval2) and returning the pResult.

What is happening here is lmytestval1 is assigned to ptestval1. Does it really need to? (I don't think so but it's just for consistency and standardation sake.)

How is something like the above executed in Oracle in the function?

Any info would be greatly appreciated.

 

[SantaMufasa]

Davism,

I'm getting a bit confused by your question(s): Are you talking about Oracle user-defined stored procedures or are you talking about Oracle user-defined functions?

Above you say:

The mytestProc SP in this example is taking in 2 parameters (ptestval1, and ptestval2) and returning the pResult.

You refer to "mytestProc SP" (a stored procedure), you say that it returns "pResult" (as though it was a function), yet "pResult" does not appear in your parameter/argument list (which is the way by which you can return a value from a procedure), so I don't see how a stored procedure can be "returning the pResult" if it is not listed as an "out" parameter.


Please set me straight.

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]

SantaMufusa,

No, my apologies, this would be something within the the function that I intially was looking at.

The function would do other things and call this SP. This sounds like an SP because it's returning a value which is not a resord set hence the reason why the pResult is output on the SP.

So, the SP has those 3 parameters and one of those is output.

The function is calling the SP like what I mentioned on the statement.

Make better sense?

 

[SantaMufasa]

Davism,

Certainly, you can have functions in procedures in procedures in functions in functions...ad infinitum:

CREATE OR REPLACE FUNCTION foo (arg1 number, arg2 varchar2)
    RETURN <whatever> IS
    ptestval1 number;
    ptestval2 varchar2;
    retval1   number; 
BEGIN
    ...
    mytestProc (ptestval1, ptestval2, retval1);
    ...
END;
/

Does this answer your question? If not, please ost the code that you have so far and for which you still have 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]

Oh, I thought I had to do an EXEC for the SP. That is not correct?

If I do not have to do an EXEC then it is a bit simpler than what I expected.

Please confirm.

Thanks!

 

[SantaMufasa]

The "EXEC" statement is how you invoke a stored procedure from the SQL*Plus prompt. ("EXEC" is a SQL*Plus verb that causes invocation of an Oracle database-stored procedure.)

If you wish to invoke a stored procedure from within another stored procedure's or function's BEGIN section, then you do not need the "EXEC".

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 know this is a little off topic but it still is incorporated in the function. If it's felt that a new question can be asked please let me know. What is it that I'm doing wrong here:

lMonth := CONVERT(CHAR(2), DATEPART(MM, pdtInDate));

I'm getting a "Statement ignored" message and a message that says "PLS-00222: no function name CHAR exists in this scope"

What am I missing here?

 

[SantaMufasa]

Davism,

I'm guessing that you want IMonth to hold the 2-character Month number from pdtInDate, right? (If I'm mistaken, please correct my assumption.) To achieve that result, this is the Oracle code to use:

IMonth := to_char(pdtInDate,'MM');

Let us know if this resolves your need.

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]

Yep, that was it. Except for IMonth is a CHAR value and pdtIntDate is a DATE value. The convert in Oracle is like a COALITION is SQL Server which is a char-set aspect. Except for IMonth needs to have a different identifier in the name.

Except you have a to_char component in there. Let me give this a shot.

I'm sure I can use SYSDATE in replacement of pdtIntDate as well.

Thanks for the info!

 

[SantaMufasa]

Except for IMonth is a CHAR value and pdtIntDate is a DATE value.

I don't quite follow your use of "Except", above. Your "pdtIntDate" expression must be a DATE expression for the TO_CHAR function to derive the character equivalent of the Month number; "IMonth" should be a character data type (although "IMonth" can also be NUMBER since Oracle will perform an automatic Character-to-Number conversion from a TO_CHAR function to a NUMBER receiving data item.)

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.”