5 Replies Latest reply: Nov 14, 2012 1:11 AM by yasser_egypt RSS

    error in fetch

    yasser_egypt
      dear sirs
      this code return only the last record and i would like that this code return all the record in table
      the code is

      PROCEDURE TRANS IS
      v_connect EXEC_SQL.CONNTYPE;
      sqlstr varchar2(1000);
      nRes PLS_INTEGER;
      curid EXEC_SQL.CURSTYPE;
      v_deptno number;
      v_dname varchar2(100);
      v_loc varchar2(100);
      BEGIN
           
      v_connect := EXEC_SQL.OPEN_CONNECTION('scott/tiger@orcl');--Defining Connection
      --Opening Cursor in Oracle
      curid := EXEC_SQL.OPEN_CURSOR(v_connect);
      --Parsing & Executing Non-Oracle Query in Oracle
      BEGIN
      EXEC_SQL.PARSE(v_connect, curID,'select to_char(deptno),dname,loc from dept');
      END;
      nRes := EXEC_SQL.EXECUTE(v_connect, curID);
      --Defining Column Sequence using Variables
      EXEC_SQL.DEFINE_COLUMN(v_connect, curID, 1, v_deptno);
      EXEC_SQL.DEFINE_COLUMN(v_connect, curID, 2, v_dname ,30 );
      EXEC_SQL.DEFINE_COLUMN(v_connect, curID, 3, v_loc ,30 );
      WHILE (EXEC_SQL.FETCH_ROWS(v_connect, curID)>0) LOOP
           --Fetching Column Values in Variables
           
           
           EXEC_SQL.column_value(v_connect,curID,1,v_deptno);
           EXEC_SQL.column_value(v_connect,curID,2,v_dname);
           EXEC_SQL.column_value(v_connect,curID,3,v_loc);
      END LOOP;
      :CONTROL.deptno:=v_deptno;
           :CONTROL.dname:=v_dname;
           :CONTROL.loc := v_loc;

      END;

      pls help
        • 1. Re: error in fetch
          MLBrown
          I'm not exactly sure what you are doing, but the way that your logic is written, you are looping through a cursor and after the looping is finished then it appears that you are saving the last value into the three items in the CONTROL block. If you want to store the values you are looping through into different items in the block then you should include that logic within your loop. Something like:
          .
          .
          .
              EXEC_SQL.column_value(v_connect,curID,1,v_deptno); 
              EXEC_SQL.column_value(v_connect,curID,2,v_dname);
              EXEC_SQL.column_value(v_connect,curID,3,v_loc); 
            
              :CONTROL.deptno := v_deptno;
              :CONTROL.dname := v_dname;
              :CONTROL.loc := v_loc;
          
              NEXT_RECORD; -- You would need to include some logic to go to the next 
                           -- record in the block in order to save the next set of values.
          
            END LOOP;
          END;
          • 2. Re: error in fetch
            ck
            hello,

            why you used code to retrieve data from tables.

            this is the strength of forms.

            just populate the block by issueing exequte_query because your only retriving simple quey;

            create a block base on this table then when new_forms_instance add code

            go_block('youblock');execute_query(no_validate); that's it.

            if more complex query create a view then follow above
            • 3. Re: error in fetch
              DanielB
              PROCEDURE TRANS IS
              v_connect EXEC_SQL.CONNTYPE;
              sqlstr varchar2(1000);
              nRes PLS_INTEGER;
              curid EXEC_SQL.CURSTYPE;
              v_deptno number;
              v_dname varchar2(100);
              v_loc varchar2(100);
              BEGIN
              
              v_connect := EXEC_SQL.OPEN_CONNECTION('scott/tiger@orcl');--Defining Connection
              --Opening Cursor in Oracle
              curid := EXEC_SQL.OPEN_CURSOR(v_connect);
              --Parsing & Executing Non-Oracle Query in Oracle
              BEGIN 
              go_block('CONTROL');
              FIRST_RECORD;
              EXEC_SQL.PARSE(v_connect, curID,'select to_char(deptno),dname,loc from dept');
              END;
              nRes := EXEC_SQL.EXECUTE(v_connect, curID);
              --Defining Column Sequence using Variables
              EXEC_SQL.DEFINE_COLUMN(v_connect, curID, 1, v_deptno);
              EXEC_SQL.DEFINE_COLUMN(v_connect, curID, 2, v_dname ,30 );
              EXEC_SQL.DEFINE_COLUMN(v_connect, curID, 3, v_loc ,30 );
              WHILE (EXEC_SQL.FETCH_ROWS(v_connect, curID)>0) LOOP
              --Fetching Column Values in Variables
              
              
              
              EXEC_SQL.column_value(v_connect,curID,1,v_deptno); 
              EXEC_SQL.column_value(v_connect,curID,2,v_dname);
              EXEC_SQL.column_value(v_connect,curID,3,v_loc); 
              :CONTROL.deptno:=v_deptno;
              :CONTROL.dname:=v_dname;
              :CONTROL.loc := v_loc;
              NEXT_RECORD;
              END LOOP;
              :CONTROL.deptno:=v_deptno;
              :CONTROL.dname:=v_dname;
              :CONTROL.loc := v_loc;
              NEXT_RECORD;
              
              END;
              • 4. Re: error in fetch
                Andreas Weiden
                Any special reason why you use exec_sql ? Is the data coming from another database?
                • 5. Re: error in fetch
                  yasser_egypt
                  thanks dear it solved