Home » Developer & Programmer » Forms » Problem with insertion of a new record (Oracle 10g Forms)
Problem with insertion of a new record [message #334650] Thu, 17 July 2008 09:58 Go to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Friends,
I have a data block in which I can display a number of records. In this data block the user will be able to edit the fields if no child records are found in another table. I have used when-new-record-instance to attain this scenario. All are text items. One item licensee_id which is made invisible by setting the property in property palette and required=no ( as this is the primary key of the table). Also the audit columns are made invisible.
The code for it is as follows:
DECLARE
	v_alert_button  NUMBER;
	v_cnt 					NUMBER;
BEGIN
	   SELECT COUNT (*)
     INTO   v_cnt
     FROM   id_rev_contracts
     WHERE  licensee_id = :ID_REV_LICENSEES.licensee_id;
   
   IF v_cnt > 0 THEN
       set_item_property('ID_REV_LICENSEES.LICENSEE_NAME', UPDATE_ALLOWED, PROPERTY_FALSE);
   ELSE
   	   set_item_property('ID_REV_LICENSEES.LICENSEE_NAME', UPDATE_ALLOWED, PROPERTY_TRUE);
   	  -- set_item_property('ID_REV_LICENSEES.LICENSEE_NAME', INSERT_ALLOWED, PROPERTY_TRUE);
   END IF;
END;


Now in this data block I should also be able to insert new records and for the same I have used PRE-INSERT trigger and the code for it is as follows:
DECLARE 
	v_alert_button NUMBER;
  CURSOR v_licensee_id IS SELECT id_rev_licensees_s.NEXTVAL FROM dual; 
BEGIN 

OPEN v_licensee_id; 
FETCH v_licensee_id INTO :id_rev_licensees.licensee_id; 
CLOSE v_licensee_id; 

IF :id_rev_licensees.licensee_id IS NULL THEN 
Message('Error Generating Next v_licensee_id'); 
RAISE Form_Trigger_Failure; 
END IF; 

:ID_REV_LICENSEES.created_by := :GLOBAL.g_login_name;
   :ID_REV_LICENSEES.last_updated_by := :GLOBAL.g_login_name;
   :ID_REV_LICENSEES.create_date := SYSDATE;
   :ID_REV_LICENSEES.last_update_date := SYSDATE;
EXCEPTION
   WHEN form_trigger_failure
   THEN
      RAISE form_trigger_failure;
   WHEN OTHERS
   THEN
      v_alert_button :=
          msgbox ('ERROR in Pre-Insert - ' || SQLERRM, 'STOP', 'Contact IST');
      RAISE form_trigger_failure;
END;



Every thing is compiling fine but at the run time when I am trying to insert a new record I am receiving the following error:
FRM-40508:ORACLE error:unable to insert record

I also think the pre-insert record is not firing at the time of inserting a new record and saving it. So I request you to please delve into this problem and suggest me how to overcome this problem. Code snippets would do more help for me. If you need any other things from me please let me know. I will see if I could be of any help in that concern because I may not be able to send the entire form as it is. I also request senior member like David to look into this problem.

Thanks and regards,
Vamsi K Gummadi.




Re: Problem with insertion of a new record [message #334729 is a reply to message #334650] Thu, 17 July 2008 15:22 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
FRM-40508:ORACLE error:unable to insert record
Go to Help menu, click Display Error and let us know what's in there.

Quote:
I also think the pre-insert record is not firing
So put MESSAGE('I am executing the PRE-INSERT trigger') into it and you'll know whether it is executed or not.
Re: Problem with insertion of a new record [message #334755 is a reply to message #334729] Thu, 17 July 2008 16:57 Go to previous messageGo to next message
vamsikgummadi
Messages: 62
Registered: April 2006
Location: United States, MI
Member
Hi Little Foot,
Thank you for your suggestion and it helped me.
I have modified the code in Pre-Insert trigger as follows:
DECLARE
   v_alert_button   NUMBER;
   
BEGIN
	   SELECT id_rev_licensees_s.NEXTVAL
     INTO :ID_REV_LICENSEES.LICENSEE_ID
     FROM DUAL;
   
   :ID_REV_LICENSEES.created_by := :GLOBAL.g_login_name;
   :ID_REV_LICENSEES.last_updated_by := :GLOBAL.g_login_name;
   :ID_REV_LICENSEES.create_date := SYSDATE;
   :ID_REV_LICENSEES.last_update_date := SYSDATE;
EXCEPTION
   WHEN form_trigger_failure
   THEN
      RAISE form_trigger_failure;
   WHEN OTHERS
   THEN
      v_alert_button :=
          msgbox ('ERROR in Pre-Insert - ' || SQLERRM, 'STOP', 'Contact IST');
          --GO_ITEM('ID_REV_LICENSEES.COUNTRY_CODE');
      RAISE form_trigger_failure;
      
END;


I think the problem is with
Quote:
:ID_REV_LICENSEES.LICENSEE_ID
.

Because if I am using
Quote:
:id_rev_licensees.licensee_id
PRE-INSERT trigger is not firing but when I use
Quote:
:ID_REV_LICENSEES.LICENSEE_ID
the trigger is firing.

I don't know if it is case sensitive or...? But now it is working.

Thanks you for your suggestions.

Vamsi K Gummadi.
Re: Problem with insertion of a new record [message #334791 is a reply to message #334755] Fri, 18 July 2008 00:50 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Case sensitive? No, as far as I can tell. The problem must have had been somewhere else.
Previous Topic: change the default status bar of oracle forms ? (merged threads)
Next Topic: Send Email with PDF attachment
Goto Forum:
  


Current Time: Sun Dec 04 20:47:44 CST 2016

Total time taken to generate the page: 0.16158 seconds