?
Solved

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

Posted on 2009-05-13
8
Medium Priority
?
10,903 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 35

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

800 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