Hello Y'all,
I am and have been maintain an application (w/ Oracle 10g and forms 6i) which has been developed and maintained by various Analyst/Programmers since 2000. I recently modified one of the forms including removing one of the displayed fields which is defined as a dB constrainted column(it is neither PK nor FK). The modified form works fine for updating an existing record, but it generates the following error while I tried to save a newly created record:
‘‘FRM-40508: ORACLE error: unable to INSERT record" error:
I received the following detailed error by clicking 'Display Error' in the Help menu on top of the form:
The SQL statement in error is as following:
INSERT INTO PROJECT_NEED(PROJECT_NEED_ID,NAME,MODE_CODE,REGION_CODE,STATUS_CODE,BOROUGH,GOV_INITIATIVE_PROGRAM,HOUSE_DISTRICT,
HD2012,HD2013,MPO,PRIMARY_PURPOSE,CORE_SERVICE,PRIMARY_WORK,ESTIMATE_AMT,PLACE_NAME,HIGHWAY,DESCRIPTION,
SECONDARY_WORK,SPECIAL_FINANCIAL,fn_class,NEED_CATEGORY,CRITERIA_TYPE,PEB_SCORE,PEB_SCORE_DATE,SPONSOR,STIP_LOCKED,
TEN_YEAR_PLAN,THEME,TY_CONTROL_CTR,TY_FUND_MODE,TY_TRANS_MODE,TY_PROGRAM,TY_TRANSFER_TO,TY_PROJECT_SOURCE,
CREATED_BY,CREATION_DATE,SAVED_BY,SAVED_DATE)
VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39)
The PRIMARY_PURPOSE field is to be removed from the form which I hid the field from being displayed on the form. I resolved the dB error by disabling **constraint (**PRODUCTION.PRIMARY_PURP). I tried to locate the 'insert' statement where error was generated by converting the form to a txt file and then using Crimson Editor to do the search but failed. I also queried the SYS.DBA_Source table for the 'insert' statement but all tries failed again. In addition, I reviewed the dB Triggers, procedures, functions and packages, but still out of luck...
Could any of you help me find the 'insert' statement where error was generated due to Oracle check constraint (PRODUCTION.PRIMARY_PURP) violated?
Any/all the help on this would be greatly appreciated.
Thanks millions in advance!