Menu de navegação

[sensuy__Participante]

Olá,
Estou fazendo uma procedure que deve retornar um cursor para uma aplicação java.
Consegui fazer com que ela retornasse um cursor e valores separados para minha aplicação.
As perguntas são as seguintes: tem como eu fazer um append dessas variáveis, no cursor, ao invés de passá-las separadamente?
aqui vai a procedure que está rodando.
CREATE OR REPLACE PROCEDURE teste_for (customer_id_in IN integer,
branch_id_in IN integer,
report_key_in IN integer,
user_id_in IN integer,
p_cursor OUT sys_refcursor,
v_customer_label OUT varchar2,
v_customer_child_label OUT varchar2,
v_vehicle_label OUT varchar2,
v_driver_label OUT varchar2,
v_user_label OUT varchar2) IS

limite integer;

v_solic_id integer;
v_param_customer_id integer;
v_param_customer_child_id integer;
v_param_vehicle_id integer;
v_param_driver_id integer;
v_param_language integer;
v_param_start_time date;
v_param_end_time date;
v_param_report_type varchar2(10);
v_user_id integer;
v_param_report_key integer;
v_param_report_keyname varchar(150);
v_status integer;
v_request_time date;
v_start_process_time date;
v_end_process_time date;
v_queue_id integer;
v_priority_id integer;
v_report_path varchar2(200);
v_param_report_name varchar2(200);

begin
open p_cursor for select solic_id, param_customer_id, param_customer_child_id,
param_vehicle_id, param_driver_id, param_language, param_start_time,
param_end_time, param_report_type, rs.user_id, param_report_key,
param_report_keyname, status, request_time, start_process_time,
end_process_time, queue_id, priority_id, report_path,
param_report_name
from
reports_solic rs
where
rs.request_time between current_timestamp-7 and current_timestamp
and
(rs.user_id = user_id_in or 1 = user_id_in)
and
(rs.param_report_key = report_key_in or -1 = report_key_in)
and
(rs.param_customer_id = customer_id_in or -1 = customer_id_in)
and
(rs.param_customer_child_id = branch_id_in or -1 = branch_id_in)
and
rownum <= 20 order by rs.request_time desc; limite := 0; loop if (limite < 20) then limite := limite + 1; FETCH p_cursor INTO v_solic_id,v_param_customer_id,v_param_customer_child_id,v_param_vehicle_id, v_param_driver_id,v_param_language,v_param_start_time,v_param_end_time, v_param_report_type,v_user_id,v_param_report_key,v_param_report_keyname, v_status,v_request_time,v_start_process_time,v_end_process_time, v_queue_id,v_priority_id,v_report_path,v_param_report_name; select cc.customer_child_name into v_customer_label from customer_child cc where cc.customer_id = v_param_customer_id and cc.is_main_office = 1; if (v_param_customer_child_id > -1) then
select cc.customer_child_name into v_customer_child_label from customer_child cc where cc.customer_child_id = v_param_customer_child_id;
end if;

if (v_param_vehicle_id > -1) then
select v.vehicle_prefix || ' - ' || v.vehicle_plates into v_vehicle_label from vehicles v where v.vehicle_id = v_param_vehicle_id;
end if;

if (v_param_driver_id > -1) then
select d.driver_firstname || ' ' || d.driver_lastname into v_driver_label from drivers d where d.driver_id = v_param_driver_id;
end if;

select user_real_name into v_user_label from user_profile where user_id = v_user_id;

else
exit;
end if;
end loop;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('CAIU NO EXCEPTION');
end;

[Autor]

Posts