This discussion is archived
5 Replies Latest reply: Nov 13, 2012 11:11 PM by yasser_egypt RSS

error in fetch

yasser_egypt Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Any special reason why you use exec_sql ? Is the data coming from another database?
  • 5. Re: error in fetch
    yasser_egypt Newbie
    Currently Being Moderated
    thanks dear it solved

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points