Stored Procedure question

[sahcutie]

I am a newbie in oracle and I have written a stored procedure that
should return multiple records. I have implemented cursors and I was
wondering if anyone would point out what is wrong. I have pasted my
stored procedure below:
Any help is appreciated. Also any links to help me along the path will
also be really appreciated.

CREATE OR REPLACE PROCEDURE VINFO
(
"LASTNAME" IN VARCHAR2) IS

TYPE varVendorNumber is TABLE OF VARCHAR2(10);
TYPE varVendorAddress1 is TABLE OF VARCHAR2(35);
TYPE varVendorAddress2 is TABLE OF VARCHAR2(35);
TYPE varVendorCity is TABLE OF VARCHAR2(30);
TYPE varVendorState is TABLE OF VARCHAR2(6);
TYPE varVendorPostal is TABLE OF VARCHAR2(12);
TYPE varVendorCountry is TABLE OF VARCHAR2(3);
VenNum varVendorNumber;
VenAdd1 varVendorAddress1;
VenAdd2 varVendorAddress2;
VenCity varVendorCity;
VenState varVendorState;
VenPostal varVendorPostal;
VenCountry varVendorCountry;

CURSOR C_Vendor IS select a.VENDOR_ID, c.ADDRESS1, c.ADDRESS2,
c.CITY, c.STATE, c.POSTAL, c.COUNTRY
from PS_VENDOR a, PS_VENDOR_PAY b, PS_VENDOR_ADDR c
where a.NAME1 like '%' || LASTNAME || '%'
and a.VENDOR_ID = b.vendor_id
and a.vendor_id = c.vendor_id
and b.PYMNT_GROUP_CD = 'EM'
and a.VENDOR_STATUS = 'A'
and c.eff_status = 'A'
and c.ADDRESS_SEQ_NUM = a.PRIM_ADDR_SEQ_NUM;

BEGIN -- executable part starts here

OPEN C_Vendor;
FETCH C_Vendor BULK COLLECT INTO VenNum, VenAdd1, VenAdd2,VenCity,
VenState, VenPostal,VenCountry;
CLOSE C_Vendor;

END "VINFO";

[Peter Sylvester]

Try looking up "pipelined function" on asktom.oracle.com.

--Peter

sahcutie wrote: I am a newbie in oracle and I have written a stored procedure that should return multiple records. I have implemented cursors and I was wondering if anyone would point out what is wrong. I have pasted my stored procedure below: Any help is appreciated. Also any links to help me along the path will also be really appreciated. CREATE OR REPLACE PROCEDURE VINFO ( "LASTNAME" IN VARCHAR2) IS TYPE varVendorNumber is TABLE OF VARCHAR2(10); TYPE varVendorAddress1 is TABLE OF VARCHAR2(35); TYPE varVendorAddress2 is TABLE OF VARCHAR2(35); TYPE varVendorCity is TABLE OF VARCHAR2(30); TYPE varVendorState is TABLE OF VARCHAR2(6); TYPE varVendorPostal is TABLE OF VARCHAR2(12); TYPE varVendorCountry is TABLE OF VARCHAR2(3); VenNum varVendorNumber; VenAdd1 varVendorAddress1; VenAdd2 varVendorAddress2; VenCity varVendorCity; VenState varVendorState; VenPostal varVendorPostal; VenCountry varVendorCountry; CURSOR C_Vendor IS select a.VENDOR_ID, c.ADDRESS1, c.ADDRESS2, c.CITY, c.STATE, c.POSTAL, c.COUNTRY from PS_VENDOR a, PS_VENDOR_PAY b, PS_VENDOR_ADDR c where a.NAME1 like '%' || LASTNAME || '%' and a.VENDOR_ID = b.vendor_id and a.vendor_id = c.vendor_id and b.PYMNT_GROUP_CD = 'EM' and a.VENDOR_STATUS = 'A' and c.eff_status = 'A' and c.ADDRESS_SEQ_NUM = a.PRIM_ADDR_SEQ_NUM; BEGIN -- executable part starts here OPEN C_Vendor; FETCH C_Vendor BULK COLLECT INTO VenNum, VenAdd1, VenAdd2,VenCity, VenState, VenPostal,VenCountry; CLOSE C_Vendor; END "VINFO";

[sahcutie]

Thanks for this. I just have a quick question, so instead of doing
the work in the procedure as above, I need to do a function instead?
When does one decide when to use a function instead of a procedure? I
come from a SQLServer background and trying to make sure I do this the
right way.

Thanks again.

[sahcutie]

Also, with the above question, I am going to be running the procedure
from a web service and need to be able to get the data back for
different clients. I am using odp.net.

thanks

[Guest]

A function accepts a parameter and returns a value based on that
parameter.

A procedure can accept parameters, performs processing using those
parameters and optionally returns a value.

HTH.

[Yuri Ivanov]

sahcutie пишет: I am a newbie in oracle and I have written a stored procedure that should return multiple records. I have implemented cursors and I was wondering if anyone would point out what is wrong. I have pasted my stored procedure below: Any help is appreciated. Also any links to help me along the path will also be really appreciated.
---skip---
A procedures never RETURNs anything, against a functions.
At least, if you want to get something from procedure, you must use IN
OUT parameters. :) Or use CREATE OR REPLACE FUNCTION
Sure.

[sahcutie]

Yes, I am using the out parameter, and I am wondering how to get the
information in the out ref cursor so that I can create a dataset using
odp.net or in .Net to get the information.

[DA Morgan]

sahcutie wrote: Yes, I am using the out parameter, and I am wondering how to get the information in the out ref cursor so that I can create a dataset using odp.net or in .Net to get the information.

www.psoug.org
click on Morgan's Library
click on REF CURSOR
scroll down to "Parent Procedure"
child procedure returns a REF CURSOR ... parent show how to access it.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)