Associative Array help - no data found [message #571952] |
Tue, 04 December 2012 15:48 |
Lou
Messages: 6 Registered: October 2004
|
Junior Member |
|
|
I'm very new to associative arrays in Oracle 11g. I have a main stored procedure and a calling sub routine. I've declared the associative array in the sub routine as follows:
v_l_fixed_var_ind varchar2(1);
type t_fixed_var is table of varchar2(1) index by varchar2(3);
fixed_variable t_fixed_var;
I then populate the array with the following statement as I FOR loop through a table. pl_pln_code is a 3 character variable. I only want to populate the array on the very first iteration through the subroutine (controlled through a variable). The subsequent calls wouldn't populate the array, I just want to pull from it.
fixed_variable(plan_rec.pl_pln_code) := v_l_fixed_var_ind;
I then assign an output variable
output_variable := fixed_variable(o_pln_code);
The first time through the assignment works. The second time through the subroutine, I receive a No Data Found (ora 1403) error.
Ideas?
|
|
|
|
|
Re: Associative Array help - no data found [message #571961 is a reply to message #571954] |
Tue, 04 December 2012 21:36 |
Lou
Messages: 6 Registered: October 2004
|
Junior Member |
|
|
Sorry - I did not provide enough information.
T_LIPL_PLAN:
PL_PLN_CODE
001
002
003
...
020
Declares in sub routine:
v_l_fixed_var_ind varchar2(1);
type t_fixed_var is table of varchar2(1) index by varchar2(3);
fixed_variable t_fixed_var;
CURSOR c_plan IS
select pl_pln_code
from t_lipl_plan;
If i_first_time = 'Y' Then
FOR plan_rec IN c_plan LOOP
--There is logic to set i_var_ind and i_fixed_ind but that code isn't important
If i_var_ind > 0 AND i_fixed_ind > 0 then
v_l_fixed_var_ind := 'B';
Elsif i_var_ind > 0 then
v_l_fixed_var_ind := 'V';
Elsif i_fixed_ind > 0 then
v_l_fixed_var_ind := 'F';
End if;
fixed_variable(plan_rec.pl_pln_code) := v_l_fixed_var_ind;
End Loop;
i_first_time := 'N';
End If;
o_fixed_var_ind := fixed_variable(i_pln_code);
---------------------------------------------------------
Start program execution. Main_stmt routine calls sub routine. i_first_time = Y. Loop through T_LIPL and make the following assignments.
fixed_variable('001') := 'F';
fixed_variable('002') := 'V';
fixed_variable('003') := 'V';
....
fixed_variable('020') := 'B';
i_first_time := 'N'
i_pln_code = '001'
o_fixed_var_ind := fixed_variable('001')
o_fixed_var_ind = 'F'
Return to call Main_stmt
Loop
Main_stmt routine calls sub routine for the second run. i_first_time = N.
i_pln_code = '001'
o_fixed_var_ind := fixed_variable('001')
Receive Oracle No_data_found (Ora 1403) exception when trying to reference fixed_variable('001') on the second iteration.
|
|
|
|
|
Re: Associative Array help - no data found [message #571973 is a reply to message #571961] |
Wed, 05 December 2012 01:06 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
It seems like misunderstanding of scope and visibility of variables in procedural programming languages (where PL/SQL belongs).
If FIXED_VARIABLE is declared in the subroutine, it is available only there; after end of its call, it is unreachable and its content is lost. In next subroutine call, the variable is declared again, without any relationship to its content in previous calls.
By the way, you was supposed to post something like this (there was no need to copy your original code, just demonstrating your problem):
declare
v_l_fixed_var_ind varchar2(1);
i_first_time varchar2(1) := 'Y';
function subroutine ( p_index in varchar2 ) return varchar2 is
type t_fixed_var is table of varchar2(1) index by varchar2(3);
fixed_variable t_fixed_var;
begin
if i_first_time = 'Y' then
fixed_variable('001') := 'F';
fixed_variable('002') := 'V';
fixed_variable('003') := 'V';
end if;
i_first_time := 'N';
return fixed_variable( p_index );
end;
begin
v_l_fixed_var_ind := subroutine( '003' );
-- you cannot do this; FIXED_VARIABLE is not available (and hence not containing anything) here
--v_l_fixed_var_ind := fixed_variable( '001' );
-- in the next call, FIXED_VARIABLE will be initialized again
-- of course, it does not contain anything from any previous call
--v_l_fixed_var_ind := subroutine( '002' );
end;
/
|
|
|
|
Re: Associative Array help - no data found [message #572026 is a reply to message #572017] |
Wed, 05 December 2012 08:07 |
Lou
Messages: 6 Registered: October 2004
|
Junior Member |
|
|
Sorry - I'm not a programmer so I'm doing the best I can to follow all the rules of this posting.
Based on whay flyboy stated, that seemed to make sense to be. The array is available during the first call to the sub routine. The second call to the subroutine, the array is gone (declared again but not populated). I thought the solution would be to pass the array between the main and the calling sub routine.
However, Kevin Meade seems to be implying that isn't the cause...
|
|
|
Re: Associative Array help - no data found [message #572027 is a reply to message #572017] |
Wed, 05 December 2012 08:15 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Kevin Meade wrote on Wed, 05 December 2012 14:27wrong error flyboy (although I expected a different error from why I got here, still OP error is 1403).
Hm, I have two commented assignments, so the block does not fail. The first one you simplified to show that the local variable is not available in the outer block, the second one the call I assume that Lou made. And the second one, alone uncommented, does throw ORA-1403.
SQL> declare
2 v_l_fixed_var_ind varchar2(1);
3 i_first_time varchar2(1) := 'Y';
4
5 function subroutine ( p_index in varchar2 ) return varchar2 is
6 type t_fixed_var is table of varchar2(1) index by varchar2(3);
7 fixed_variable t_fixed_var;
8 begin
9 if i_first_time = 'Y' then
10 fixed_variable('001') := 'F';
11 fixed_variable('002') := 'V';
12 fixed_variable('003') := 'V';
13 end if;
14 i_first_time := 'N';
15
16 return fixed_variable( p_index );
17 end;
18 begin
19 v_l_fixed_var_ind := subroutine( '003' );
20 -- you cannot do this; FIXED_VARIABLE is not available (and hence not con
taining anything) here
21 --v_l_fixed_var_ind := fixed_variable( '001' );
22 -- in the next call, FUIXED_VARIABLE will be initialized again
23 -- of course, it does not contain anything from any previous call
24 v_l_fixed_var_ind := subroutine( '002' );
25 end;
26 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 16
ORA-06512: at line 24
|
|
|
|
Re: Associative Array help - no data found [message #572039 is a reply to message #572032] |
Wed, 05 December 2012 08:42 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Kevin Meade wrote on Wed, 05 December 2012 15:31Nope, we are both correct. It is the same issue in both cases. If you have an empty array then trying to reference any entry in the array will be referencing an entry that does not exist and will raise 1403. It is all the same thing. You are trying to reference an entry in an array that has not been initialized. But this is not a question of scoping rules.
Probably not; I just tried to show, that the local variable does not persist after local function call. I do not know shells and scripting languages, but I suspect that it is possible in some of them.
|
|
|
|
|
|