Home » SQL & PL/SQL » SQL & PL/SQL » Associative Array help - no data found (Oracle 11g)
Associative Array help - no data found [message #571952] Tue, 04 December 2012 15:48 Go to next message
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 #571954 is a reply to message #571952] Tue, 04 December 2012 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

You have table. We don't
You have data. We don't
You have SQL. We don't
It is next to impossible to debug code that can not be seen.


>I have a main stored procedure
which I don't see
>and a calling sub routine
which I don't see
>I then populate the array with the following statement as I FOR loop through a table.
which I don't see
> I receive a No Data Found (ora 1403) error.
How can we reproduce what you report?



Re: Associative Array help - no data found [message #571960 is a reply to message #571954] Tue, 04 December 2012 21:13 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
SQL> declare
  2    v1 integer;
  3    type tarray is table of integer index by binary_integer;
  4    myarray tarray;
  5  begin
  6    v1 := myarray(1);
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6
Re: Associative Array help - no data found [message #571961 is a reply to message #571954] Tue, 04 December 2012 21:36 Go to previous messageGo to next message
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 #571964 is a reply to message #571961] Tue, 04 December 2012 22:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
How can we reproduce what you report?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Associative Array help - no data found [message #571972 is a reply to message #571964] Wed, 05 December 2012 00:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Did you spend any time thinking about the example I provided?

If so, please tell me why my example raised NO_DATA_FOUND and why this is not your problem too.

[Updated on: Wed, 05 December 2012 00:50]

Report message to a moderator

Re: Associative Array help - no data found [message #571973 is a reply to message #571961] Wed, 05 December 2012 01:06 Go to previous messageGo to next message
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 #572017 is a reply to message #571973] Wed, 05 December 2012 07:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
wrong error flyboy (although I expected a different error from why I got here, still OP error is 1403).

08:20:48 SQL> declare
08:20:58   2     procedure p1 is
08:21:02   3        v1 integer;
08:21:07   4     begin
08:21:11   5        null;
08:21:13   6     end;
08:21:15   7  begin
08:21:16   8     v1 := 1;
08:21:18   9  end;
08:21:18  10  /
   v1 := 1;
   *
ERROR at line 8:
ORA-06550: line 8, column 4:
PLS-00363: expression 'V1' cannot be used as an assignment target
ORA-06550: line 8, column 4:
PL/SQL: Statement ignored


Elapsed: 00:00:01.45
08:21:21 SQL>


Again read my original post. It is showing you how you get 1403 with arrays.

Now you have to stop being stubborn (we are all that way from time to time) and realize that what you are thinking is wrong somehow. For example you are thinking index '001' but maybe that is not the index value causing the error. Time for some basic debugging on your part. Start with a simple test case and Deskcheck your code.
Re: Associative Array help - no data found [message #572026 is a reply to message #572017] Wed, 05 December 2012 08:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Kevin Meade wrote on Wed, 05 December 2012 14:27
wrong 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 #572032 is a reply to message #572027] Wed, 05 December 2012 08:31 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Nope, 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.
Re: Associative Array help - no data found [message #572039 is a reply to message #572032] Wed, 05 December 2012 08:42 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Kevin Meade wrote on Wed, 05 December 2012 15:31
Nope, 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.
Re: Associative Array help - no data found [message #572040 is a reply to message #572039] Wed, 05 December 2012 08:44 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Well I messed up too. I did not really look that close at what you provided. Once you posted the execution of your snippet it was obvious what my confusion was too.
Re: Associative Array help - no data found [message #572045 is a reply to message #572040] Wed, 05 December 2012 09:01 Go to previous messageGo to next message
Lou
Messages: 6
Registered: October 2004
Junior Member
So if the local variable doesn't exist after the local function call - is the best way to solve this just pass the array between the routines? What's the best way to accomplish this?
Re: Associative Array help - no data found [message #572048 is a reply to message #572045] Wed, 05 December 2012 09:24 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I don't know if that is the right approach or not. But if that is where you are going, take a look at NOCOPY.
Previous Topic: Efficient way of retrieving - Joining Vs In line view
Next Topic: TYPE NUM_ARRAY as table of number
Goto Forum:
  


Current Time: Fri Apr 26 11:15:18 CDT 2024