Tom,
Sorry about the test case.
My database is an Enterprise edition release 8.1.7.3.0 on a Sun solaris sever.
Thanks for any help you can give me on this.
Here you have all you need in order to reproduce my test case.
prompt Here you have a test case......Thanks......
prompt There some parameters that you have to add like: default tablespace, temporary tables..
prompt and it will ask you the users names (ctw and ssw) in order to assing quota on the tablespace...
prompt ....
conn system
create user ctw identified by ctw
default tablespace &deftbsp
temporary tablespace &temptbsp
profile default;
create user ssw identified by ssw
default tablespace &deftbsp
temporary tablespace &temptbsp
profile default;
grant create session to ctw,ssw;
grant create table, select any table to ctw,ssw;
grant create procedure, create type to ctw;
alter user &user quota unlimited on &tbsp;
conn ssw
create table ssw.orders
(ORDER_ID NUMBER(10) NOT NULL,
ACCOUNT_ID NUMBER(10) NOT NULL,
ORDER_STATUS NUMBER(3) NOT NULL)
pctfree 0;
create table ssw.order_items
(ORDER_ID NUMBER(10) NOT NULL,
ORDER_ITEM_ID NUMBER(10) NOT NULL,
SHIP_ID NUMBER(10))
pctfree 0;
insert into ssw.orders
(order_id,account_id,order_status)
values(3007,2376878,1)
/
insert into ssw.order_items
(order_id,order_item_id,ship_id)
values(3007,5113492,3254581)
/
insert into ssw.order_items
(order_id,order_item_id,ship_id)
values(3007,3004,null)
/
insert into ssw.order_items
(order_id,order_item_id,ship_id)
values(3007,692050,4534)
/
insert into ssw.order_items
(order_id,order_item_id,ship_id)
values(3007,1667964,896468)
/
insert into ssw.orders
(order_id,account_id,order_status)
values(3004,877686,1)
/
insert into ssw.order_items
(order_id,order_item_id,ship_id)
values(3004,5113293,3253170)
/
insert into ssw.order_items
(order_id,order_item_id,ship_id)
values(3004,3001,nul)
/
insert into ssw.order_items
(order_id,order_item_id,ship_id)
values(3004,690712,4105)
/
insert into ssw.order_items
(order_id,order_item_id,ship_id)
values(3004,1667848,896338)
/
commit
/
conn ctw
create or replace type array is table of varchar2(2000);
/
create or replace package types as type rc is ref cursor;
end;
/
create or replace procedure built_query_ret_result (p_colnames in
array,p_operator in array, p_values in array,
p_values_operin in array,p_selqntcol in number, p_query in varchar2,
l_ret_accid_tab out dbms_sql.Number_Table,l_ret_ordid_tab out
dbms_sql.Number_Table,
l_ret_ordstat_tab out dbms_sql.Number_Table,l_ret_orditid_tab out
dbms_sql.Number_Table,
l_ret_shipid_tab out dbms_sql.Number_Table ) as
--
l_cursor int default dbms_sql.open_cursor;
l_query long;
l_cond_oper varchar2(20) default ' ';
l_comma varchar2(1) default '';
l_status int;
l_columvalue varchar2(4000);
l_in_flag varchar2(1) default 'N';
l_nullval_flag varchar2(1) default 'N';
l_accid_tab dbms_sql.Number_Table;
l_ordid_tab dbms_sql.Number_Table;
l_ordstat_tab dbms_sql.Number_Table;
l_orditid_tab dbms_sql.Number_Table;
l_shipid_tab dbms_sql.Number_Table;
l_indx number := 10;
begin
if p_values is null then
l_nullval_flag:='Y';
end if;
if p_values_operin is not null then
l_in_flag:='Y';
end if;
l_query:=p_query;
for i in 1 ..p_colnames.count loop
if upper(p_operator(i))='IN' then
l_query:=l_query||l_cond_oper||p_colnames(i)||' '||p_operator(i)||' ( ';
for x in 1 ..p_values_operin.count loop
l_query:=l_query||l_comma||':bin_inval'||x;
if x=1 then
l_comma:=',';
end if;
end loop;
l_query:=l_query||' ) ';
else
l_query:=l_query||l_cond_oper||p_colnames(i)||' '||p_operator(i)||'
'||':bin_var'||i;
end if;
l_cond_oper:=' and ';
end loop;
dbms_sql.parse(l_cursor, l_query,dbms_sql.native);
if l_nullval_flag='N' then
for i in 1..p_values.count loop
dbms_sql.bind_variable(l_cursor,':bin_var'||i, p_values(i));
end loop;
end if;
if l_in_flag='Y' then
for i in 1..p_values_operin.count loop
dbms_sql.bind_variable(l_cursor,':bin_inval'||i, p_values_operin(i));
end loop;
end if;
--
dbms_sql.define_array(l_cursor,1,l_ret_accid_tab,p_selqntcol,l_indx);
dbms_sql.define_array(l_cursor,2,l_ret_ordid_tab,p_selqntcol,l_indx);
dbms_sql.define_array(l_cursor,3,l_ret_ordstat_tab,p_selqntcol,l_indx);
dbms_sql.define_array(l_cursor,4,l_ret_orditid_tab,p_selqntcol,l_indx);
dbms_sql.define_array(l_cursor,5,l_ret_shipid_tab,p_selqntcol,l_indx);
--
l_status:=dbms_sql.execute(l_cursor);
while (dbms_sql.fetch_rows(l_cursor)>0)
loop
dbms_sql.column_value(l_cursor,1,l_ret_accid_tab);
dbms_sql.column_value(l_cursor,2,l_ret_ordid_tab);
dbms_sql.column_value(l_cursor,3,l_ret_ordstat_tab);
dbms_sql.column_value(l_cursor,4,l_ret_orditid_tab);
dbms_sql.column_value(l_cursor,5,l_ret_shipid_tab);
end loop;
for i in 1 .. l_ret_ordid_tab.count loop
dbms_output.put_line(to_char(l_ret_accid_tab(i))||to_char(l_ret_ordid_tab(i))||to_char(l_ret_orditid_tab(i))||
to_char(l_ret_ordstat_tab(i))||to_char(l_ret_shipid_tab(i)));
end loop;
dbms_Sql.close_Cursor(l_cursor);
end;
/
create or replace procedure show_result is
v_cursor types.rc;
v_ord_id number default 3007;
v_colnames array :=array('a.order_id','b.order_item_id');
v_operator array :=array('=','in');
v_values array :=array(v_ord_id);
v_values_operin array :=array();
v_query varchar2(4000);
l_ret_accid_tab dbms_sql.Number_Table;
l_ret_ordid_tab dbms_sql.Number_Table;
l_ret_ordstat_tab dbms_sql.Number_Table;
l_ret_orditid_tab dbms_sql.Number_Table;
l_ret_shipid_tab dbms_sql.Number_Table;
v_strsql varchar2(4000);
v_pos number default 0;
i number;
rowquery ssw.order_items%rowtype;
begin
v_query:='select
a.account_id,a.order_id,a.order_status,b.order_item_id,b.SHIP_ID '||
'from ssw.orders a , ssw.order_items b '||
'where b.order_id=a.order_id and';
i:=1;
select count(*) into v_pos from ssw.order_items where order_id=v_ord_id;
v_values_operin.extend(v_pos);
v_strsql:='select * from ssw.order_items where order_id=:xord_id';
open v_cursor for v_strsql using v_ord_id;
loop
fetch v_cursor into rowquery;
exit when v_cursor%notFound;
v_values_operin(i):=rowquery.order_item_id;
i:=i+1;
end loop;
built_query_ret_result(v_colnames,v_operator,v_values,v_values_operin,v_pos,v_query,l_ret_accid_tab,l_ret_ordid_tab,l_ret_ordstat_tab,l_ret_orditid_tab,l_ret_shipid_tab);
dbms_output.put_line('l_ret_ordid_tab.count='||to_char(l_ret_ordid_tab.count));
for i in 1 .. l_ret_ordid_tab.count loop
dbms_output.put_line(to_char(l_ret_accid_tab(i))||to_char(l_ret_ordid_tab(i))||to_char(l_ret_orditid_tab(i))||
to_char(l_ret_ordstat_tab(i))||to_char(l_ret_shipid_tab(i)));
end loop;
end;
/
Here you have the results for the test:
SQL>set serveroutput on
SQL>exec show_result;
BEGIN show_result; END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "CTW.BUILT_QUERY_RET_RESULT", line 76
ORA-06512: at "CTW.SHOW_RESULT", line 34
ORA-06512: at line 1
March 27, 2003 - 7:29 am UTC
I need to write a book on how to make a simple, small, concise, to the point test case....
Anyway, add this DEBUG code:
65 l_status:=dbms_sql.execute(l_cursor);
66 while (dbms_sql.fetch_rows(l_cursor)>0)
67 loop
68 dbms_sql.column_value(l_cursor,1,l_ret_accid_tab);
69 dbms_sql.column_value(l_cursor,2,l_ret_ordid_tab);
70 dbms_sql.column_value(l_cursor,3,l_ret_ordstat_tab);
71 dbms_sql.column_value(l_cursor,4,l_ret_orditid_tab);
72 dbms_sql.column_value(l_cursor,5,l_ret_shipid_tab);
73 end loop;
74
75 dbms_output.put_line( 'accid_tab.count = ' || l_ret_accid_tab.count );
76 dbms_output.put_line( 'ordid_tab.count = ' || l_ret_ordid_tab.count );
77 dbms_output.put_line( 'ordstat_tab.count = ' || l_ret_ordstat_tab.count );
78 dbms_output.put_line( 'orditid_tab.count = ' || l_ret_orditid_tab.count );
79 dbms_output.put_line( 'shipid_tab.count = ' || l_ret_shipid_tab.count );
80
81 dbms_output.put_line( 'accid_tab.first = ' || l_ret_accid_tab.first );
82 dbms_output.put_line( 'ordid_tab.first = ' || l_ret_ordid_tab.first );
83 dbms_output.put_line( 'ordstat_tab.first = ' || l_ret_ordstat_tab.first );
84 dbms_output.put_line( 'orditid_tab.first = ' || l_ret_orditid_tab.first );
85 dbms_output.put_line( 'shipid_tab.first = ' || l_ret_shipid_tab.first );
86
87
88 dbms_output.put_line( 'accid_tab.last = ' || l_ret_accid_tab.last );
89 dbms_output.put_line( 'ordid_tab.last = ' || l_ret_ordid_tab.last );
90 dbms_output.put_line( 'ordstat_tab.last = ' || l_ret_ordstat_tab.last );
91 dbms_output.put_line( 'orditid_tab.last = ' || l_ret_orditid_tab.last );
92 dbms_output.put_line( 'shipid_tab.last = ' || l_ret_shipid_tab.last );
93
94 for i in 1 .. l_ret_ordid_tab.count loop
95 dbms_output.put_line(to_char(l_ret_accid_tab(i))||
96 to_char(l_ret_ordid_tab(i))||
97 to_char(l_ret_orditid_tab(i))||
98 to_char(l_ret_ordstat_tab(i))||
99 to_char(l_ret_shipid_tab(i)));
100 end loop;
and all will be revealed to you. you told us to start the array indexing at 10 (.first will show you that). The array subscripts run from 10..265 in your case, not 1..N....
A little debug code goes a long way...
The smaller a test case is -- the better. 99 times out of 100 in the development of a test case to demonstrate a "product issue", I find the bug in my code ;)