Procedure, package and ref cursors question

[sahcutie]

Is it possible to have a procedure that returns a ref cursor without
the the procedure being in a package? If it is, how should it be
passed in the parameters and also be declared?

[DA Morgan]

sahcutie wrote: Is it possible to have a procedure that returns a ref cursor without the the procedure being in a package? If it is, how should it be passed in the parameters and also be declared?

Yes. A procedure or function being in a package is irrelevant to the
parameters it can accept and/or return.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[sahcutie]

So in the procedure below, what is wrong with the ref cursor or how do
I get the cursor to be an out cursor?

PROCEDURE "WEBSERVICE".VENDORIDANDADDINFO
(
"LASTNAME" IN VARCHAR2, VendorCursor OUT REF CURSOR) IS

TYPE ref_info IS REF CURSOR RETURN VendorRecord;
C_Vendor ref_info;
TYPE vend_rec IS RECORD
(
VenNum VARCHAR2(10),
VenAdd1 VARCHAR2(35),
VenAdd2 VARCHAR2(35),
VenCity VARCHAR2(30),
VenState VARCHAR2(6),
VenPostal VARCHAR2(12),
VenCountry VARCHAR2(3)
);
VendorRecord vend_rec;

BEGIN -- executable part starts here

OPEN C_Vendor FOR select a.VENDOR_ID, c.ADDRESS1, c.ADDRESS2,

[DA Morgan]

sahcutie wrote: So in the procedure below, what is wrong with the ref cursor or how do I get the cursor to be an out cursor? PROCEDURE "WEBSERVICE".VENDORIDANDADDINFO ( "LASTNAME" IN VARCHAR2, VendorCursor OUT REF CURSOR) IS TYPE ref_info IS REF CURSOR RETURN VendorRecord; C_Vendor ref_info; TYPE vend_rec IS RECORD ( VenNum VARCHAR2(10), VenAdd1 VARCHAR2(35), VenAdd2 VARCHAR2(35), VenCity VARCHAR2(30), VenState VARCHAR2(6), VenPostal VARCHAR2(12), VenCountry VARCHAR2(3) ); VendorRecord vend_rec; BEGIN -- executable part starts here OPEN C_Vendor FOR select a.VENDOR_ID, c.ADDRESS1, c.ADDRESS2,

You have an OUT parameter that is undeclared as you define the REF
CURSOR in the procedure itself.

Either define the REF CURSOR in a package header or use SYS_REFCURSOR:
The weakly typed built-in.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[sahcutie]

Which would be the better way to do this? Define the ref cursor in a
package header or do a weakly typed ref cursor?

[DA Morgan]

sahcutie wrote: Which would be the better way to do this? Define the ref cursor in a package header or do a weakly typed ref cursor?

Define it in a package header.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[sahcutie]

Thanks, I have my package set up and I am trying to get it tested. How
can I test to make sure that it is returning the correct information?
The reason I ask is that I am trying to run this using odp.net for a
new web service that I am writing. The client is 8.1 (hope this makes
sense.) I would like to get back a dataset that we can use on various
platforms.