| Dynamic Sql [message #38888] |
Thu, 23 May 2002 23:06  |
Devendra
Messages: 2
Registered: May 2001
|
Junior Member |
|
|
I want to use the code like this :--->
for i in 1..fld.count
loop
DBMS_SQL.DEFINE_COLUMN(v_cid,i,fld(i));
htp.p(i);
end loop;
where "fld" is table of numbers.Then at the time of execution i am getting the result "Data not found".
But this code is working fine :--->
DBMS_SQL.DEFINE_COLUMN(v_cid,1,fld1);
DBMS_SQL.DEFINE_COLUMN(v_cid,2,fld2);
DBMS_SQL.DEFINE_COLUMN(v_cid,3,fld3);
How can i manage a loop ?
|
|
|
|
| Re: Dynamic Sql [message #38896 is a reply to message #38888] |
Fri, 24 May 2002 02:49  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Are you sure every row between 1 and fld.count is filled. PL/SQL tables don't need to be filled sequentially, it is possible that you don't fill them all.
Look at the following I've ran:
1 declare
2 type my_tab_type is table of number index by binary_integer;
3 my_tab my_tab_type;
4 i number := 1;
5 j number := 0;
6 begin
7 while i < 30
8 loop
9 my_tab(i) := i;
10 i:=i+7;
11 end loop;
12 for j in 1..my_tab.count loop
13 dbms_output.put_line(my_tab(j)||'<--'||j);
14 end loop;
15* end;
SQL> /
1<--1
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 13
As you can see: The first record is filled, but the next is record with index 8. The record with index 2 hasn't been created yet so I'll get a no_data_found.
Look at the second example:
1 declare
2 type my_tab_type is table of number index by binary_integer;
3 my_tab my_tab_type;
4 i number := 1;
5 j number := 0;
6 begin
7 while i < 30
8 loop
9 my_tab(i) := i;
10 i:=i+7;
11 end loop;
12 for j in 1..my_tab.count loop
13 begin
14 dbms_output.put_line(my_tab(j)||'<--'||j);
15 exception
16 when no_data_found then
17 dbms_output.put_line('Index '||j||' is missing from the table!');
18 end;
19 end loop;
20* end;
SQL> /
1<--1
Index 2 is missing from the table!
Index 3 is missing from the table!
Index 4 is missing from the table!
Index 5 is missing from the table!
This is not yet the desired result. Because you counted the number of created records, you'll get 5 (records 1,8,15,22 and 29).
The third variant is what one would expect:
1 declare
2 type my_tab_type is table of number index by binary_integer;
3 my_tab my_tab_type;
4 i number := 1;
5 j number := 0;
6 begin
7 while i < 30
8 loop
9 my_tab(i) := i;
10 i:=i+7;
11 end loop;
12 for j in my_tab.first..my_tab.last loop
13 begin
14 dbms_output.put_line(my_tab(j)||'<--'||j);
15 exception
16 when no_data_found then
17 dbms_output.put_line('Index '||j||' is missing from the table!');
18 end;
19 end loop;
20* end;
SQL> /
1<--1
Index 2 is missing from the table!
Index 3 is missing from the table!
Index 4 is missing from the table!
Index 5 is missing from the table!
Index 6 is missing from the table!
Index 7 is missing from the table!
8<--8
Index 9 is missing from the table!
Index 10 is missing from the table!
Index 11 is missing from the table!
Index 12 is missing from the table!
Index 13 is missing from the table!
Index 14 is missing from the table!
15<--15
Index 16 is missing from the table!
Index 17 is missing from the table!
Index 18 is missing from the table!
Index 19 is missing from the table!
Index 20 is missing from the table!
Index 21 is missing from the table!
22<--22
Index 23 is missing from the table!
Index 24 is missing from the table!
Index 25 is missing from the table!
Index 26 is missing from the table!
Index 27 is missing from the table!
Index 28 is missing from the table!
29<--29
PL/SQL procedure successfully completed.
HTH,
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
