Home » SQL & PL/SQL » SQL & PL/SQL » Is this a good way of using table function? (PL/SQL)
Is this a good way of using table function? [message #296976] Tue, 29 January 2008 15:43 Go to next message
jzhou2005@hotmail.com
Messages: 6
Registered: January 2008
Junior Member
Could somebody take look at following code and provide any feed back on pipelined table function usage?
I will pass these records from this table function to another table function.
Is this a good way of using it? Or there are better way of doing it.

Thank you so much!

Joyce

FUNCTION get_all_pat_brdg(p_supp_proc_id in number, p_eval_cls_cd in number)
RETURN pat_eligy_table_type PIPELINED
AS
TYPE refcurtyp IS REF CURSOR;
cur refcurtyp;

v_supplier_proc_id number(15) := null;
v_evalutn_clas_cd number(5):= null;
v_btch_nbr number(5):= null;
v_base_dt date:= null;
v_rx_btch_supplier_seq_nbr number(9):= null;
v_rx_chnl_cd number(2):= null;
v_long_pat_tracker varchar2(30 byte):= null;
v_ims_outlet_ctry_cd varchar2(3 byte):= null;
v_ims_outlet_pstl_cd varchar2(15 byte):= null;
v_ims_outlet_seq_nbr varchar2(3 byte):= null;
v_ims_pat_nbr number:= null;
v_encptd_pat_brth_dt varchar2(44 byte):= null;
v_encptd_pat_frst_nm varchar2(44 byte):= null;
v_encptd_pat_last_nm varchar2(44 byte):= null;
v_supplier_eligy_strt_dt date := null;
v_rx_dspnsd_dt date := null;
v_eligy_pd_start_dt date := null;
v_pat_gender_cd number := null;
v_rx_pat_gender_cd number := null;
v_rx_pat_dob char(6 byte) := null;
v_pat_dob char(6 byte) := null;
v_eligy_dt_upd_ind number := null;
v_pat_gender_upd_ind number := null;
v_pat_dob_upd_ind number := null;

v_cr_tbl_stmt varchar2(4000) := '';
v_dp_tbl_stmt varchar2(1000) := '';
v_stmt varchar2(4000) := '';
v_where_clause varchar2(1000) := '';
v_eval_cls_ind number(1) := 0;
v_outlet_ind number(1) :=0;
v_encypt_frst_nm_ind number(1) := 0;
v_encypt_last_nm_ind number(1) := 0;
v_encypt_dob_ind number(1) := 0;
v_supp_start_dt date;

v_tab pat_eligy_row_type := pat_eligy_row_type(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null ,null);
BEGIN

select EVALUTN_CLAS_CD_REQ_IND,
IMS_OUTLET_ID_IND,
ENCPTD_PAT_BRTH_DT_IND,
ENCPTD_PAT_LAST_NM_IND,
ENCPTD_PAT_FRST_NM_IND,
SUPPLIER_ELIGY_STRT_DT
into v_eval_cls_ind, v_outlet_ind, v_encypt_dob_ind, v_encypt_last_nm_ind, v_encypt_frst_nm_ind, v_supp_start_dt
from uda_pat_eligy_key_rule
where SUPPLIER_PROC_ID = p_supp_proc_id
and EVALUTN_CLAS_CD = p_eval_cls_cd;

if v_outlet_ind = 1
then
v_where_clause := ' e.IMS_OUTLET_PSTL_CD = a.IMS_OUTLET_PSTL_CD (+) and ';
v_where_clause := v_where_clause || ' e.IMS_OUTLET_SEQ_NBR=a.IMS_OUTLET_SEQ_NBR (+) and ';
end if;

if v_encypt_dob_ind = 1
then
v_where_clause := v_where_clause || ' e.ENCPTD_PAT_BRTH_DT = a.ENCPTD_PAT_BRTH_DT (+) and ';
end if;

if v_encypt_last_nm_ind = 1
then
v_where_clause := v_where_clause || ' e.ENCPTD_PAT_LAST_NM = a.ENCPTD_PAT_LAST_NM (+) and ';
end if;

if v_encypt_frst_nm_ind = 1
then
v_where_clause := v_where_clause || ' e.ENCPTD_PAT_FRST_NM = a.ENCPTD_PAT_FRST_NM (+) and ';
end if;

v_where_clause := v_where_clause || ' e.supplier_proc_id = a.supplier_proc_id (+) ';


open cur for 'Select e.SUPPLIER_PROC_ID,e.EVALUTN_CLAS_CD,e.BTCH_NBR, e.BASE_DT,e.RX_BTCH_SUPPLIER_SEQ_NBR, ' ||
' e.RX_CHNL_CD, e.LONG_PAT_TRACKER,e.IMS_OUTLET_CTRY_CD,e.IMS_OUTLET_PSTL_CD,e.IMS_OUTLET_SEQ_NBR, ' ||
' a.IMS_PAT_NBR, e.ENCPTD_PAT_BRTH_DT,e.ENCPTD_PAT_FRST_NM,e.ENCPTD_PAT_LAST_NM, e.SUPPLIER_ELIGY_STRT_DT, ' ||
' e.RX_DSPNSD_DT, null ELIGY_PD_START_DT, null PAT_GENDER_CD, e.RX_PAT_GENDER_CD, e.RX_PAT_DOB, ' ||
' null PAT_DOB, e.ELIGY_DT_UPD_IND, e.PAT_GENDER_UPD_IND, e.PAT_DOB_UPD_IND from pat_eligy_key a, pat_test e ' ||
' where e.supplier_proc_id = a.supplier_proc_id (+) and e.long_pat_tracker = a.LONG_PAT_TRACKER (+) and ' || v_where_clause;

LOOP
FETCH cur INTO v_supplier_proc_id,
v_evalutn_clas_cd,
v_btch_nbr,
v_base_dt,
v_rx_btch_supplier_seq_nbr,
v_rx_chnl_cd,
v_long_pat_tracker,
v_ims_outlet_ctry_cd,
v_ims_outlet_pstl_cd,
v_ims_outlet_seq_nbr,
v_ims_pat_nbr,
v_encptd_pat_brth_dt,
v_encptd_pat_frst_nm,
v_encptd_pat_last_nm,
v_supplier_eligy_strt_dt,
v_rx_dspnsd_dt,
v_eligy_pd_start_dt,
v_rx_pat_gender_cd,
v_pat_gender_cd,
v_rx_pat_dob,
v_pat_dob,
v_eligy_dt_upd_ind,
v_pat_gender_upd_ind,
v_pat_dob_upd_ind;

EXIT WHEN cur%NOTFOUND;
-- v_tab.extend;

v_tab := pat_eligy_row_type(v_supplier_proc_id,
v_evalutn_clas_cd,
v_btch_nbr,
v_base_dt,
v_rx_btch_supplier_seq_nbr,
v_rx_chnl_cd,
v_long_pat_tracker,
v_ims_outlet_ctry_cd,
v_ims_outlet_pstl_cd,
v_ims_outlet_seq_nbr,
v_ims_pat_nbr,
v_encptd_pat_brth_dt,
v_encptd_pat_frst_nm,
v_encptd_pat_last_nm,
v_supplier_eligy_strt_dt,
v_rx_dspnsd_dt,
v_eligy_pd_start_dt,
v_rx_pat_gender_cd,
v_pat_gender_cd,
v_rx_pat_dob,
v_pat_dob,
v_eligy_dt_upd_ind,
v_pat_gender_upd_ind,
v_pat_dob_upd_ind);
PIPE ROW(v_tab);
END LOOP;
CLOSE cur;
RETURN;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT(TO_CHAR(SQLCODE) || '-' || SQLERRM(SQLCODE));
END get_all_pat_brdg;
Re: Is this a good way of using table function? [message #296977 is a reply to message #296976] Tue, 29 January 2008 15:46 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Could somebody take look at following code

Could you have a look at OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

One thing I can say is that:
Quote:
WHEN others THEN
DBMS_OUTPUT.PUT(TO_CHAR(SQLCODE) || '-' || SQLERRM(SQLCODE));

is the biggest PL/SQL error you can made.

Regards
Michel
Previous Topic: Display column values in the header
Next Topic: Doubt in parsing expression using REGEXP_REPLACE
Goto Forum:
  


Current Time: Sat Dec 03 05:58:43 CST 2016

Total time taken to generate the page: 0.09884 seconds