Solved

FRM 40735 when button presses trigger raised unhandled exception ORA-06502!!! Urgent!!!

Posted on 2009-05-13
8
9,929 Views
Last Modified: 2013-12-19
I have a form and I need to add a search functionality as shown in the screenshot which I have uploaded. In the Find box when we type AQ then the pointer goes and shows the item which has the word starting with AQ, and IF I press the go button again it shows me the next word starting with the letters AQ and so on.

This code is already in the current form, but I have recently added few new modal windows and I need to add this functionality in them. I tried to put the same logic behind it for the new window, but it does not work. Gives me the error
'FRM 40735 when button presses trigger raised unhandled exception ORA-06502'

I have attached the current working code and screenshot of how it looks earlier with correct working functionality and how it looks after I have added the new window with find (functionality).

Please help me out urgently
screen1.JPG
screen2.JPG
0
Comment
Question by:vishal_singh
  • 4
  • 3
8 Comments
 
LVL 12

Expert Comment

by:jwahl
ID: 24382585
this error typically occurs at data mismatch (converting string to date or numbers) or on a data overflow (check the length of variables in your code). please post the code of the when-button-pressed trigger ("go") also!
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 24384949
I agree with jwahl.  The ORA-06502 error usually means either:
1. A database column, screen field or variable is not long enough for the data that the program unit or trigger attempts to put into it.
2. A database column, screen field or variable is declared as a "number" or "date" datatype, and the program unit or trigger is attempting to put character data into it that is not valid for that datatype (or isn't in the correct format to be implicitly converted if the target datatype is a date).
0
 

Author Comment

by:vishal_singh
ID: 24397237
Hi Jwahl and markgreer!!

I have attached/pasted my code below. Please let me know about it.

Thanks,
 Vishal
declare

	

	in_block varchar2(5) := 'REGION_LOV_BLK';

	in_srch_field varchar2(50);

	

	curr_rec  number;

	not_found BOOLEAN := TRUE;
 

begin
 

	in_srch_field := in_block || '.REGION';

	

	if :B01.TI_FIND_REGION is not null then
 

		go_block(in_block);

		curr_rec := :system.cursor_record;

		

    -- loop rest of records		

    if :system.last_record != 'TRUE' then

    	

    	next_record;

    	

  		loop
 

    		if upper(name_in(in_srch_field)) like '%' || upper(:b01.ti_find_region) || '%' then

    			go_item(in_srch_field);

    			synchronize;

		  		not_found := FALSE;
 

				  exit;

  			elsif :system.last_record = 'TRUE' then

	  			exit;

  			else

  				next_record;

  			end if;

 			

  		end loop;

  		

  	end if;
 

    -- if not found, start from top

    if not_found then

    	

    	first_Record;
 

  		loop
 

    		if upper(name_in(in_srch_field)) like '%' || upper(:b01.ti_find_region) || '%' then

    			go_item(in_srch_field);

    			synchronize;

		  		not_found       := FALSE;
 

				  exit;

  			elsif :system.cursor_record = curr_rec then

	  			exit;

  			else

  				next_record;

  			end if;

 			

  		end loop;

  		

    end if;

    			

		if not_found then

			debug_help('No Match Found');

			go_record(curr_rec);

		end if;

		

	end if;
 

  go_item('b01.pb_find_region');

  

end;

Open in new window

0
 

Author Comment

by:vishal_singh
ID: 24397302
Guys,

I think I got it, but the only thing remaining is the highlighting of the current record. As I have shown in the screenshot, it is not highlighting (OR) may be I need to set some property to highlight the records chosen.
Please could you let me know about.
Thanks,
 Vishal
(I have used varchar2(5) instead of varchar2(50) in my code in the declaration part.)
Looks like it is solved now.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 12

Expert Comment

by:jwahl
ID: 24397579
there is a block property "current record visual attribute group", but this changes only the record where the cursor is currently in. if you want all checked records in another color, you have to do this manually, e.g. in the when-checkbox-changed trigger:

BEGIN
    IF :CHECKBOX_VALUE = 'Y' THEN
       SET_ITEM_INSTANCE_PROPERTY('FIELD1', .... <visual_attribute1>);
       SET_ITEM_INSTANCE_PROPERTY('FIELD2', .... <visual_attribute1>);
       SET_ITEM_INSTANCE_PROPERTY('FIELD3', .... <visual_attribute1>);
    ELSE
       SET_ITEM_INSTANCE_PROPERTY('FIELD1', .... <visual_attribute2>);
       SET_ITEM_INSTANCE_PROPERTY('FIELD2', .... <visual_attribute2>);
       SET_ITEM_INSTANCE_PROPERTY('FIELD3', .... <visual_attribute2>);
    END IF;
END;

(look into forms builder help for correct syntax)

0
 

Author Comment

by:vishal_singh
ID: 24397829
Jwahl,

That is what I wanted. I want only the current record to be shown in a color. And when I press the 'GO' button, it points to next record starting with letter 'AQ' (whatever I have typed in it). So, now that one should get highlighted.(And the previous one as normal)

I dont know how to write the code, and where to write, and in which block to write it.
I am  new to ORACLE FORMS. Please help me out.

I have serched this property "current record visual attribute group", but I am not getting any idea about how to proceed.

Thanks,
 Vishal
0
 
LVL 12

Accepted Solution

by:
jwahl earned 500 total points
ID: 24400511
after pressing 'GO' your procedure moves the cursor to the next record matching the search criteria.
so the block property 'current record ...' is the right option:

1) create a visual attribute (in forms builder open the form and navigate to the node "visual attributes"): choose a name and the desired attributes like color, background color, font, ...

2) move to node 'blocks' and double-click on block REGION_LOV_BLK. the property palette opens, now you can choose your visual attribute at 'current record visual attribute group'.

that's it!
0
 

Author Closing Comment

by:vishal_singh
ID: 31581109
Thank You very much
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now