SQL Query did not show result on Data Block Oracle Forms

Question

I want show SQL Query Data into Data Block Through When-Button-Pressed.

Code:

DECLARE
 p_cnic VARCHAR2(20);
BEGIN

 p_cnic := 'SELECT cnicno FROM hof WHERE cnicno IN (SELECT cnic_no FROM we_group_hof_k)';
 :we_group_hof_k.CNIC_NO := p_cnic;
END;

The data block "CNIC_NO" Data Type is VARCHAR

When I pressed the button then I am getting error

FRM-40735: WHEN-BUTTON-PRESSED Trigger raised unhandled exception

Answer 1

You just need to use an INTO clause with a SELECT statement without quotes as :

BEGIN    
  SELECT cnicno
    INTO :we_group_hof_k.CNIC_NO 
    FROM hof 
   WHERE cnicno IN (SELECT cnic_no FROM we_group_hof_k);     
 EXCEPTION WHEN no_data_found THEN null;
END;

• In your case, no need to use a local variable ( p_cnic ), e.g. you can directly assign value to the field. If you really needed, prefer defining as p_cnic hof.cnicno%type instead, in which no matter what the data type of the column.
• Add Exception handling against the possibility to raise no_data_found exception

If you need to bring multiple records, using an SELECT .. INTO clause is not suitable, since you'd get

ORA-01422: exact fetch returns more than one requested number

in such a case.

Prefer using a cursor instead, against the situation above :

BEGIN   
     go_block('myblock'); 
     first_record;
  for c in
          (
           SELECT cnicno
             FROM hof 
            WHERE cnicno IN (SELECT cnic_no FROM we_group_hof_k)
          )     
  loop
    :we_group_hof_k.CNIC_NO := c.cnicno;
     next_record;
  end loop;
END;