Home » SQL & PL/SQL » SQL & PL/SQL » loop problem
loop problem [message #198304] Mon, 16 October 2006 09:04 Go to next message
Messages: 4
Registered: July 2006
Location: poona
Junior Member
in this example i m checking one plsql table values with other plsql table.(in this case m_appln_values and m_param_values both are plsql table).
The problem i m getting is for the first value it executes properly, but for second value(i.e csr1_rec second value )
it comes out of the loop even though csr1_rec has second value is still there.
i dont know what is the problem, just look into it.

FOR csr1_rec IN 1..avl_pkg_availibility.m_appln_values.LAST-- m_appln_values is plsql table
   IF m_appln_values(m_appln_row_count).sz_compare_value IS NOT NULL
  IF(m_param_values(m_appln_row_count).sz_data_comp_source = m_appln_values(m_appln_row_count).sz_data_comp_source  
                           AND m_param_values(m_appln_row_count).sz_data_comp_source_col = m_appln_values(m_appln_row_count).sz_data_comp_source_col)
                           IF  m_param_values(m_appln_row_count).sz_control_type = 'XREF'
                               --generate the sql statement which is used to execute the api
                               m_sql_statement := 'SELECT '
                                                   || m_param_values(m_appln_row_count).sz_api_name
                                                   || '(:p_availability_srno,:p_id) FROM DUAL ';
                               --get values from xref api
                               EXECUTE IMMEDIATE   m_sql_statement
                               INTO                m_check_yn
                               USING               p_availability_srno
                                                 , p_id;
                               IF m_check_yn = 'Y'
                                   m_value_avail_yn    := TRUE;
                                   m_value_avail_yn    := FALSE;
                               END IF;
                               --compare the values
                               --compare those values which are available in transaction
                               m_value_avail_yn := avl_pkg_availibility.fun_compare_values( p_base_value1  => m_param_values(m_appln_row_count).sz_param_value1
                                                                                          , p_base_value2  => m_param_values(m_appln_row_count).sz_param_value2
                                                                                          , p_comp_value   => m_appln_values(m_appln_row_count).sz_compare_value
                                                                                          , p_data_type    => m_param_values(m_appln_row_count).sz_datatype
                                                                                          , p_operator     => m_param_values(m_appln_row_count).sz_operator );
                           END IF;
                           IF m_value_avail_yn = FALSE
                               --compare transaction value with the variation permitted value
                               IF m_param_values(m_param_row_count).c_flag2 = 'Y'
                                   m_value_avail_yn := avl_pkg_availibility.fun_compare_values( p_base_value1  => m_param_values(m_param_row_count).sz_param_value1 + NVL(m_param_values(m_param_row_count).sz_param_value2,0)
                                                                                              , p_base_value2  => m_param_values(m_param_row_count).sz_param_value2
                                                                                              , p_comp_value   => m_appln_values(m_appln_row_count).sz_compare_value
                                                                                              , p_data_type    => m_param_values(m_param_row_count).sz_datatype
                                                                                              , p_operator     => m_param_values(m_param_row_count).sz_operator );
                                   m_value_avail_yn    := FALSE;
                                   m_validity_status(m_validity_row_count).sz_param_code   := m_appln_values(m_param_row_count).sz_data_comp_source_col;
                                   m_validity_status(m_validity_row_count).c_status        := 'N';
                                   m_validity_row_count    :=  m_validity_row_count + 1;
                               END IF;
                           END IF;
                          --removed count
                       END IF;    
                   IF m_value_avail_yn = TRUE
                       m_available_yn  := TRUE;
                       m_available_yn  := FALSE;
                   END IF;
                   --removed count
               END IF;
               m_appln_row_count   := m_appln_row_count + 1;
 END LOOP loop_application_details;

[Moderator - added code tags]

[Updated on: Mon, 16 October 2006 21:21] by Moderator

Report message to a moderator

Re: loop problem [message #198334 is a reply to message #198304] Mon, 16 October 2006 12:08 Go to previous messageGo to next message
Messages: 4727
Registered: February 2005
Location: East Coast USA
Senior Member
Please use code tags around your code to make it legible to others besides yourself.
Re: loop problem [message #198409 is a reply to message #198334] Tue, 17 October 2006 02:09 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Theres some odd code there.
Why do you loop using the value CSR1_REC, but then never actually use that value?

If I were you, I'd put a load of debug code into that loop, and output the values involved in each IF statement before it happens, so you can see what's happening.
Previous Topic: Too many Commits
Next Topic: Creating a table with a constraint.
Goto Forum:

Current Time: Fri Apr 28 22:53:59 CDT 2017

Total time taken to generate the page: 0.14161 seconds