WHEN-MOUSE-DOUBLECLICK with more than 1 result row 1

[hudo]

Hello

I've displayed scott.emp on canvas-tab1. By doubleclicking on a row I'd like to have the result (depending on the condition) on canvas-tab2.
Therefore I use the WHEN-MOUSE-DOUBLECLICK Trigger:

-------------------------------------------------------------
DECLARE
the_button_pressed VARCHAR2(1);
my_current_value_emp VARCHAR(10);

cur_blk VARCHAR2(40) := :SYSTEM.CURSOR_BLOCK;
cur_rec NUMBER;
--top_rec NUMBER;
bk_id BLOCK;

BEGIN

bk_id := FIND_BLOCK( cur_blk);
cur_rec := GET_BLOCK_PROPERTY( bk_id ,CURRENT_RECORD);

my_current_value_emp := :EMP.SAL;

the_button_pressed := :SYSTEM.MOUSE_BUTTON_PRESSED;

GO_BLOCK('EMP1');
CLEAR_BLOCK(NO_VALIDATE);

SELECT empno ,ename ,job ,mgr ,sal ,comm ,deptno
INTO :EMP1.EMPNO, :EMP1.ENAME, :EMP1.JOB ,:EMP1.MGR ,:EMP1.SAL,:EMP1.COMM ,:EMP1.DEPTNO
FROM EMP
WHERE sal = TO_NUMBER(my_current_value_emp);


--END IF;
END;
------------------------------------------------------------

If there is just 1 row as result everything is fine, if there are more result-rows ORA-01422 is raised.
Why is there a problem with more then 1 result-row ??
How can I solve this Problem ??

 

[HimanB]

Hi,
ORA-01422 states that exact fetch returns more than requested number of rows.
In your coding you are trying to fetch mutiple records into variables which is not possible.

What I suggest you is that , you make the Block of second Tab page as base table block & modify your code as follows:

 DECLARE
the_button_pressed VARCHAR2(1);
my_current_value_emp VARCHAR(10);

cur_blk VARCHAR2(40) := :SYSTEM.CURSOR_BLOCK;
cur_rec NUMBER;
--top_rec NUMBER;
bk_id BLOCK;
L_Str Varchar2(2000):=' where 1=1';

BEGIN

bk_id := FIND_BLOCK( cur_blk);
cur_rec := GET_BLOCK_PROPERTY( bk_id ,CURRENT_RECORD);

my_current_value_emp := :EMP.SAL;
    
    the_button_pressed := :SYSTEM.MOUSE_BUTTON_PRESSED;
    L_str:=L_str||' AND SAL='||to_number(my_current_value_emp);
    GO_BLOCK('EMP1');
    CLEAR_BLOCK(NO_VALIDATE);
    
    set_block_property('EMP1',default_where,L_Str);
    
    execute_query;
END;

HTH
Himanshu