insert record error

[rk68]

while inserting a record thru form, it gives me the following error: FRM-40508-ORACLE ERROR UNABLE TO INSERT RECORD. (All fields are having data).

I am using a PRE-INSERT trigger at form level. At time of inserting a new record the following records which are "not null" are to be inserted. PRR_RD_RQ_SN is the primary key which is inserted by taking the max.
Where should I use the PRE-insert trigger - at form level or block level.
When I execute the following trigger in PL/SQL, it doesn't give any error.
Where am I making a mistake ????

PRE-INSERT Trigger----
Declare
t_prr_rd_rq_sn number(15);
BEGIN
Select max(ilclp.t_opr_prr.prr_rd_rq_sn)+1 into t_prr_rd_rq_sn from ILCLP.T_OPR_PRR ;

Insert into ILCLP.T_OPR_PRR
(prr_rd_rq_sn, prr_tt_n_be_rd, prr_tt_b_be_rd, prr_tt_s_be_rd, prr_rlm_ur_u, prr_rlm_ts, prr_rlm_tp,
prr_sno, prr_ms_sn, prr_tt_p_be_rd, prr_pe_sn_bnf, prr_aw_cd_u, prr_xm_og_ty_no, prr_xm_og_ty_no_m, prr_xm_og_ty_no_ff, prr_sh_mhd_ty_no )

Values (t_prr_rd_rq_sn, 1, 1, 1,sys_context('userenv','current_user'),
sysdate, sys_context('userenv','ip_address'),1, 1, 1, 1,'011', 10, 10, 10, 10 );

END;
---------------------

Thankx
Raj

 

[lewisp]

Use DISPLAY_ERROR to check exactly what the error is in your form. If you are getting the values for the insert from a block you should put your trigger at block level.

 

[rk68]

I'm still getting error ORA 01400. I tried PRE-INSERT/POST-INSERT/ON-INSERT trigger at block level. I checked for all all not null values & entered values for the same but still
I get the above error. The field prr_rd_rq_sn is a primary key & I want to insert max value(prr_rd_rq_sn)+1 into this field, which I have done in following trigger.
Do I create this trigger at form level or block level ?
SHOULD THE USER BE ALLOWED TO ENTER DATA IN PRR_RD_RQ_SN (primary key)AT TIME OF INSERT (AS I AM PASSING THE MAX VALUE IN THE TRIGGER) - IF NOT DO I DISBABLE THE SAME ?
pLEASE HELP & EXPLAIN IN DETAIL.

The trigger is as follows:-
-- Insert Redemption Sr. No. (max+1)
Declare
t_prr_rd_rq_sn number(15);
Begin
t_prr_rd_rq_sn := 0;

Select max(ilclp.t_opr_prr.prr_rd_rq_sn)+1 into t_prr_rd_rq_sn from ILCLP.T_OPR_PRR ;
If t_prr_rd_rq_sn>0 then
message('count greater 0');
message(t_prr_rd_rq_sn);
Insert into ILCLP.T_OPR_PRR
(prr_rd_rq_sn, prr_tt_n_be_rd,
prr_tt_b_be_rd, prr_tt_s_be_rd,
prr_rlm_ur_u, prr_rlm_ts,
prr_rlm_tp)

Values (t_prr_rd_rq_sn, 0, 0,0, sys_context('userenv','current_user'),
sysdate, sys_context('userenv','ip_address'));

commit;
display_error();
message(error_text);

Else
message('count is 0');
t_prr_rd_rq_sn := 1;

End if;

display_error();
message(error_text);
End;
---------------------

tHANKS
RAJ

 

[lewisp]

Rk, Is the ILCLP.T_OPR_PRR table the same as your query data source name on the block? If so, you are attempting to insert a record the wrong way. From your descriptions it is likely you are negelcting to populate the form fields with values, and that is the error that is being reported, not from your explicit INSERT statement.

Instead of the INSERT, try populating your forms fields directly:

Select max(ilclp.t_opr_prr.prr_rd_rq_sn)+1 into t_prr_rd_rq_sn from ILCLP.T_OPR_PRR ;

:block.prr_rd_rq_sn := t_prr_rd_rq_sn;
:block.prr_tt_n_be_rd := 0;
:block.prr_tt_b_be_rd := 0;
:block.prr_tt_s_be_rd := 0;
:block.prr_rlm_ur_u := sys_context('userenv','current_user');
:block.prr_rlm_ts := SYSDATE;
:block.prr_rlm_tp := sys_context('userenv','ip_address');

 

[rk68]

Thanks Lewisp.
I am adding a record & entering certain fields & certain other fields are being populated thru trigger which I have mentioned earlier.
1) Where do I put the code you have written ?
2) what about the field (primary key) I had mentioned in my previous thread ?
3) Is is possible to view the sql syntax/data in trigger
at runtime like as someone had mentioned that you can view the error etc. using shift+f1 key which is not possible in 9i ? what is other option ?
Lewisp, please if you can help & elaborate.
Sorry, I'm new to Oracle forms.

Thanks
Raj

 

[lewisp]

1) In your PRE-INSERT trigger.
2) PK field is populated by PRE-INSERT trigger as above.
3) View the SQL by forcing an error (as you already have ) and using Help->Show Error.

You should avoid updating or inserting to the same table as your block is based on - let forms do it using its native in-built functionality. You help it along by popluating the fields in the form with the data you want to see inserted.

 

[rk68]

Pre-Insert trigger doesn't populate the primary field at time of insert. Its still giving the same error. The Insert works fine thru PL/SQL.
The other fields which I have shown in my
pre-insert trigger are fields not in the block, as are to be updated with values shown in the trigger. I'm using the PRE-INSERT trigger at form level.
I didn't understand this:
" 3) View the SQL by forcing an error (as you already
have ) and using Help->Show Error "

Please help.
Thanks
Raj

 

[lewisp]

To force an error, create a field in the block that is a base table item, but the column does not exist in the base table. Then run the form (ignore the error if you query) and insert a new record. When the form reports an error, use Help->Show Error to display the INSERT sql the form was trying to run.

Cut and paste the text and post here.

 

[sem]

rk68, you should learn Forms basics before using it. Actually you don't need to write INSERT statement by hand, this functionality is provided by Forms itself assuming you use base-table block. Thus the only thing you should do is to populate correctly block items. Some items are to be populated manually by entering values to corresponding block elements, other may be populated progammatically from Forms triggers. In your case I suppose that all items are populated programmatically, so use Lewisp code in pre-insert trigger on block level and DON'T ADD ANYTHING ELSE to that trigger. Also it seems to be usefull to remove all other triggers that may fire during inserting records written by you to prevent further errors.

Regards, Dima

http://seminihin.narod.ru

 

[rk68]

The insert is working fine. Thanks Lewisp.
What I like to know now is at time of insert the pk should populate immediately. I'm using the PRE-Insert trigger at form level, which gets max #+1. It does get the # but populates the field when I save record.
1) How can I populate this field which is a primary key (not null) at time when I press Insert/Add button & the field is disabled to the user ? The same field at time of edit should be available to user to key in .
How can I do this ?

As I have already mentioned, I am new to Oracle forms.

Thanks
Raj

 

[lewisp]

It is not advisable to do what you are suggesting. The reason being that although a new number will appear automatically, what if another user is using the same form and also wants to create a new record? If the first user then commits, the second user will attempt to create a record with a primary key that already exists.

The best way is the way you are currently doing it with a pre-insert, and as added protection you should possibly also lock the table just before you select max + 1.

If you really must do it the way you are suggesting, look at using a WHEN-NEW-RECORD-INSTANCE trigger or possibly KEY-CREREC, and check the current record status. This is NOT recommended.