t [message #539846] |
Wed, 18 January 2012 21:57 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ora1980
Messages: 251 Registered: May 2008
|
Senior Member |
|
|
CREATE OR REPLACE TYPE crtr_array IS VARRAY(200) OF VARCHAR2(2000)
PROCEDURE myproc (p_varray_s IN strg_array,
p_behv_fea_c IN varchar2,
p_txlt_crtr IN crtr_array,
p_is_avl_i OUT VARCHAR2,
p_avail_rc OUT SYS_REFCURSOR,
p_res_rc OUT VARCHAR2 )
IS
TYPE av_set_rectype IS RECORD
(id VARCHAR2(50),
av_flag VARCHAR2(1));
TYPE av_set_tabtype IS TABLE OF av_set_rectype
INDEX BY BINARY_INTEGER;
av_set_rec av_set_tabtype;
v_query VARCHAR2(1000);
l_select_string VARCHAR2(4000);
v_view varchar2(50);
v_query_begin varchar2(100);
v_query_end varchar2(50);
v_rslt_set SYS_REFCURSOR;
l_count number;
BEGIN
IF v_query IS NOT NULL
THEN
v_query := ' ( ' || v_query || ') and';
END IF;
IF p_varray_s.COUNT = 0
THEN
l_select_string :=
' select a.loan_n N, ''Y'' AVL_I from '
|| v_view
|| ' a where'
|| v_query
|| ' 1 = 1 ';
OPEN p_avail_rc FOR l_select_string;
OPEN v_rslt_set FOR l_select_string;
ELSE
v_query_begin :=
'select id N , decode(code, '''',''N'',''Y'') AVL_I ' || ' from (';
v_query_end := ' ) ';
l_select_string :=
v_query_begin
|| ' select a.loan_n code, t.column_value id from '
|| v_view
|| ' a, '
|| ' TABLE(:p_varray_s) t '
|| ' where a.loan_n (+)= t.column_value and '
|| v_query
|| ' 1 = 1 '
|| v_query_end;
OPEN p_avail_rc FOR l_select_string USING p_varray_s;
OPEN v_rslt_set FOR l_select_string USING p_varray_s;
END IF;
l_count := 1;
p_is_avl_i := 'Y';
LOOP
FETCH v_rslt_set INTO av_set_rec (l_count);
EXIT WHEN v_rslt_set%NOTFOUND;
IF av_set_rec (l_count).av_flag = 'N'
THEN
p_is_avl_i := 'N';
EXIT;
END IF;
END LOOP;
CLOSE v_rslt_set;
p_res_rc := ' Success. ';
END IF;
EXCEPTION
-------------
END myproc;
|
|
|
Re: t [message #539855 is a reply to message #539846] |
Wed, 18 January 2012 23:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Nice formatted post, you got it!
Regards
Michel
|
|
|