title: PL/SQL Fetching Next and Previous records with EXEC_SQL Oracle forms 6i
body: <p>I am using oracle forms 6i and i am have created a dynamic sql using EXEC_SQL. I am using a single block record however i am unable to navigate between records when i say next_record i am not moving. No errors just the record is not going to the next record;</p>
  
  <p>This code fires every time the user clicks on the <em>'first','previous','next' and 'last'</em> buttons on the toolbar. The query is returning the records i am just having problems accessing it thru navigation buttons in a single record block.</p>
  
  <p><strong>Code</strong> </p>
  
  <pre><code>BEGIN   
      cursor_number := Exec_SQL.Open_cursor;  
      recordcount_cursor_number := Exec_SQL.Open_cursor;
  
    --build SQL
    Sql_Stmt := 'SELECT .......   FROM table WHERE 1 = 1 '||where_clause ||' order by surname';--dynamic where
  
      --cannot use the dynamic where since this changes based on rowid                                
      Sql_CountStmt := 'SELECT COUNT(DISTINCT(ins_reg_no))FROM table WHERE 1 = 1 '||:global.ins_last_where_clause;--dynamic where
  
  --  Show_Message(where_clause);                                                         
      --Parse SQL statement
      EXEC_SQL.PARSE(cursor_number, Sql_Stmt);
      EXEC_SQL.PARSE(recordcount_cursor_number, Sql_CountStmt);
  
      --Define the cloums for the data to be returned
      EXEC_SQL.DEFINE_COLUMN(cursor_number,1, ins_reg_no);
      ......
      ......
      ......
  
      EXEC_SQL.DEFINE_COLUMN(recordcount_cursor_number,1,totalRecords);
  
      --Execute the cursor
      Count := EXEC_SQL.EXECUTE(cursor_number);
  
      CountRec := EXEC_SQL.EXECUTE(recordcount_cursor_number);    
  
          WHILE EXEC_SQL.FETCH_ROWS(recordcount_cursor_number)&gt; 0 LOOP    
              --SET_ITEM_PROPERTY(CURSOR_STYLE,'BUSY');               
                  EXEC_SQL.COLUMN_VALUE(recordcount_cursor_number,1,totalRecords);            
                  :global.ins_last_record_click := totalRecords;
          END LOOP;
  
      EXEC_SQL.CLOSE_CURSOR(recordcount_cursor_number);--very important to close cursor since system can freeze when navigating records
  
      IF(to_number(:global.ins_last_record_click) != 0)THEN
          :global.ins_last_record_click := totalRecords;
  
      WHILE EXEC_SQL.FETCH_ROWS(cursor_number)&gt; 0 LOOP
  
  
              EXEC_SQL.COLUMN_VALUE(cursor_number,1,ins_reg_no);
              .......
              .......
              ........
  
              --For each operation here that request data to get to the record
  
                  IF(request = 'GET_ALL')THEN
                      --LAST_RECORD;  
                      FIRST_RECORD;       
                      --EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';                                           
                  ELSIF(request = 'NEXT') THEN
                      --LAST_RECORD;
                      NEXT_RECORD;                
                  ELSIF(request = 'PREVIOUS')THEN
                  --LAST_RECORD;
                  PREVIOUS_RECORD;
                  ELSIF(request = 'FIRST')THEN
                      FIRST_RECORD;               
                  END IF;
  
              END LOOP;       
  
  
              EXEC_SQL.CLOSE_CURSOR(cursor_number);--very important to close cursor since system can freeze when navigating records   
              --set form fields qual to the cursor data
              :ins_reg_no             := ins_reg_no;
              :ins_mar_stat           := ins_mar_stat;
              :ins_oth_name           := ins_oth_name ;
              :ins_sex                := ins_sex;
              :ins_dob                := ins_dob;
              :ins_pob                := ins_pob;
              ...............
              ...............
  
        GO_BLOCK('TOOLBAR-BLK');  
      ELSE
          Message('Query Caused No Records To Be Retrived',NO_ACKNOWLEDGE);
      END IF;
  
  END;
  </code></pre>