Solved

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

Posted on 2009-05-13
8
10,486 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 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle- set role and grant privileges 6 51
Oracle function to insert records? 15 66
Oracle Distributed Transaction Lock Error ORA-01591 8 69
error in oracle form 11 29
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

697 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