I need to return a recordset from a procedure

undefinedundefined
Help!!

I am a bigginer in Oracle. I am using oracle 8.1xxx version...

I need to do a procedure or package, which return a set of data. The query si:

SELECT twfl_doc_incidente.DSLIBRERIA,
twfl_doc_incidente.DSDOCID,
twfl_tipos_doctl.DSFOLDERRECHAZO

FROM twfl_doc_incidente, twfl_tipos_doctl

WHERE twfl_doc_incidente .NMRADICADO=1020
and twfl_doc_incidente.SNPASOCESA =’S’
and twfl_doc_incidente.NMTIPO_DOCUMENTO= twfl_tipos_doctl. NMTIPO_DOCUMENTO)

Input data: NMRADICADO
Output data: Recordset with DSLIBRERIA, DSDOCID, DSFOLDERRECHAZO columns for each record find it.

I heard that is bery difficult to do it..PLEASE SOME BODY HEALP ME, OR TEACH ME, OR SEND ME AN EXAMPLE OR GIVE AN IDEA.

Thanks a lot...

Hi,

not that difficult to do:

the following is an example - if you still have a problem you are more than wellcome to send a mail:

in the spec or the declare section declare the following:

1. create a type (you can also do it in the database in create or replace ... command):
TYPE driver_shifts_rec IS RECORD
(driver_id sales.driver_id%type,
start_dt date,
end_dt date,
cash_total number(12,2),
open_sum number(12,2),
cancel_total number(12,2),
bus_num shifts.bus_num%type);

this are the fields that you want to get out ...
2. create a reference cursor based on the type you've just declare:

TYPE c_shifts IS REF CURSOR RETURN driver_shifts_rec;

use in the body section:

PROCEDURE display_driver_shifts(p_driver_id IN sales.driver_id%type,
start_search IN date,
end_search IN date,
p_shifts OUT c_shifts)
IS
BEGIN
OPEN p_shifts FOR
SELECT ... FROM ... WHERE ...


hope that helps.

Does this mean that for every table that you want you have to declare all of the values you would like returned? Or can the select statement read ... "SELECT * FROM xxx" ??? I appreciate any help you can provide.

Hi,

What it means is that you need to predefine the record type that is all the columns you will return via the ref cursor (you don't have to predefine the value just the type).

Galit.

The following is my procedure. I have created a package called "Types" that sets "cursor_type" equal to ref cursor. I found this method on a different website.

I do eventually want to pass a value in to the procedure but if I wanted to do that I would just add the following, correct? p_deptno IN lookup_rank.deptno%TYPE --- and then I would add a where clause to my select statement that looks something like this... WHERE field = p_deptno... is that correct. I appreciate your help, and hope that you will provide more guidance. Thanks in advance.

CREATE OR REPLACE PROCEDURE Select_Lookup_Rank2(p_recordset OUT Types.cursor_type)
AS

BEGIN

OPEN p_recordset FOR
SELECT *
FROM LOOKUP_RANK
ORDER BY sort_order;
END Select_Lookup_Rank2;
/

I have been able to return a recordset... i found some help on the MSDN site... however this method doesn't seem very flexible... the following is the code:

CREATE OR REPLACE PACKAGE pkg_select AS
TYPE t_cursor IS REF CURSOR ;
PROCEDURE sp_select (n_test IN VARCHAR2, io_cursor OUT t_cursor);
END pkg_select;
/
CREATE OR REPLACE PACKAGE BODY pkg_select AS
PROCEDURE sp_select (n_test IN VARCHAR2, io_cursor OUT t_cursor)
IS
v_cursor t_cursor;

BEGIN
--s_table := s_strTable;
OPEN v_cursor FOR
SELECT *
FROM LOOKUP_RANK
WHERE value = n_test;
io_cursor := v_cursor;
END sp_select;
END pkg_select;
/



If this information helps please let me know.