Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
oracle forms insert into database question
I have this query
BEGIN
FOR v_LoopCounter IN 1..50 LOOP
INSERT INTO dept_staff (sequential_number,departm
position,payroll_id,start_
start_hours,start_minutes,
select :dtl.sequential_number,:dt
:dtl.position_cd,:dtl.payr
:dtl.start_hours,:dtl.star
from department d,emp_unexcld e,dept_staff ds
where d.department_id = e.department_id
and e.payroll_id = ds.payroll_id
and ds.payroll_id = :dtl.payroll_id--'ZZW00235
and ds.schedule_date = :hdr.sched_date--'02-sep-2
and ds.site_code = :dtl.site_code;--'N208';
END LOOP;
END;
:system.message_level := '10';
do_key('COMMIT_FORM');
:system.message_level := '0';
-----------------
All the data gets inserted, other than site code. I tried to hardcode the value also but does not get inserted. Any Idea why? Forms experts?
BEGIN
FOR v_LoopCounter IN 1..50 LOOP
INSERT INTO dept_staff (sequential_number,departm
position,payroll_id,start_
start_hours,start_minutes,
select :dtl.sequential_number,:dt
:dtl.position_cd,:dtl.payr
:dtl.start_hours,:dtl.star
from department d,emp_unexcld e,dept_staff ds
where d.department_id = e.department_id
and e.payroll_id = ds.payroll_id
and ds.payroll_id = :dtl.payroll_id--'ZZW00235
and ds.schedule_date = :hdr.sched_date--'02-sep-2
and ds.site_code = :dtl.site_code;--'N208';
END LOOP;
END;
:system.message_level := '10';
do_key('COMMIT_FORM');
:system.message_level := '0';
-----------------
All the data gets inserted, other than site code. I tried to hardcode the value also but does not get inserted. Any Idea why? Forms experts?
Asked:
Who is Participating?
1)
:dtl.site_code Does the value of city_code get null ?
Maybe there is an on insert_trigger on dept_staf ?
2)
The loop does the same insert 50 times ? why ?
:dtl.site_code Does the value of city_code get null ?
Maybe there is an on insert_trigger on dept_staf ?
2)
The loop does the same insert 50 times ? why ?
no value of site code does not get null. How can I insert in a loop. When user wants to insert multiple lines?
1. Check if any trigger is present using
select * from all_triggers where TABLE_NAME = 'DEPT_STAFF'
2. Check if that table is part of another trigger using ALL_DEPENDENCIES and query REFERENCED_NAME column.
>> When user wants to insert multiple lines?
Do you mean that there are many records (say 50) in grid and you want to insert them one by one ?
To get insertions based on the rows in the forms block you will have to go to the next_record within the loop
so use the "first_record"- build in to get the block-pointer to row 1 before starting the loop and "next_record"-build in at the end of the loop to get the block pointer to the next records
use FORM_SUCCESS or keep track of the current recordnummer in the block to check if there exists a next-record;
However : this is not the way oracle forms was mend to be used.
Why didn't you make the dtl.block a database block linked to table dept_staff ?: oracle forms will do all the inserts/updates for you
on a commit.
More over the 'commit-form' wil not do a commit of data modified in the plsql-triggers if there is no data marked as changed within any of the blocks itself.
If you have to commit modification of data in de program-units without modifications in the forms blocks:
In an earlier version I use the "standard.commit" to get a commit on the server but in the 10g version I had to make a special my_own_standard-package to get the same behaviour.
so use the "first_record"- build in to get the block-pointer to row 1 before starting the loop and "next_record"-build in at the end of the loop to get the block pointer to the next records
use FORM_SUCCESS or keep track of the current recordnummer in the block to check if there exists a next-record;
However : this is not the way oracle forms was mend to be used.
Why didn't you make the dtl.block a database block linked to table dept_staff ?: oracle forms will do all the inserts/updates for you
on a commit.
More over the 'commit-form' wil not do a commit of data modified in the plsql-triggers if there is no data marked as changed within any of the blocks itself.
If you have to commit modification of data in de program-units without modifications in the forms blocks:
In an earlier version I use the "standard.commit" to get a commit on the server but in the 10g version I had to make a special my_own_standard-package to get the same behaviour.
Yes thats correct. The block is based on dept_staff. I removed all the code and on commit all the data gets inserted but as earlier said, site code is blank. Site code column is a database column.
select * from all_triggers where TABLE_NAME = 'DEPT_STAFF'
No rows returned. So no database triggers on the table.
Also no dependiencies.
No rows returned. So no database triggers on the table.
Also no dependiencies.
Why don't you put a "message(:dtl.site_code)" in the loop to see what Forms thinks is the value of that item?
I put the message, and the site code is populated on commit. But does not save in the table.
screen-print.JPG
screen-1.JPG
screen-print.JPG
screen-1.JPG
Where have you put this code, in what trigger? Why are the site code blank for the records at the bottom?
The bottom record are employees that may be scheduled. If not scheduled the data will be null. When they are already scheduled, I query them and they are marked yellow. The two lines I entered are normal color. Blank data for employees are yet to be scheduled, They may be scheduled or may not. So inserting data only for scheduled employees.
on commit button of the form. There is no specific insert code, as its a database block.So will automatically insert on commit
If the records will be automatically inserted on commit, why do you issue an explicit INSERT statement on the COMMIT button? Are the records new, they would be INSERTed by Forms on COMMIT and if they are UPDATEd, they would be automatically UPDATEd to the database. If you want to change this default behaviour of Forms you can do that by writing explicit ON-UPDATE and/or ON-INSERT triggers in the block DTL.
No I am not. Earlier I was doing. If you see @flow01 comment, I changed . Its now a database block. So on commit all records get i nserted except Site Code.
Sending the image of site code properties. Again the site code is null after the on-insert trigger code.
This is my code
message(:dtl.emp_name || ':' || :dtl.site_code); pause;
insert_record;
screen-print.JPG
screen-1.JPG
screen2.JPG
This is my code
message(:dtl.emp_name || ':' || :dtl.site_code); pause;
insert_record;
screen-print.JPG
screen-1.JPG
screen2.JPG
If (after the commit) you requery screen1 , is de inserted record still selected (i would not expect if site_code is null) and what is then the value of site_code ?
Where does screen2 come from ? Doesn't look like oracle forms ?
How do you know it's the inserted record ? (sorry for asking, but i'm getting out of ideas).
Where does screen2 come from ? Doesn't look like oracle forms ?
How do you know it's the inserted record ? (sorry for asking, but i'm getting out of ideas).
C204 is the value of site code that should have been inserted. Screen 2 is from the database to show that every data other than site code is inserted
Yes, I understand. But I'm trying to eliminate some options.
If the row that you insert in the form really loses the sitecode , then after a requery in het form the record should disappear (if there is a selection on C204 sitecode for the detail block), or
de sitecode should indeed show as a null value. Does it ?
If you use another tool to show one record are you sure this is the record that was just inserted and are you sure the sitecode is correctly shown (can you see other records for which the sitecode is filled ?)
If the row that you insert in the form really loses the sitecode , then after a requery in het form the record should disappear (if there is a selection on C204 sitecode for the detail block), or
de sitecode should indeed show as a null value. Does it ?
If you use another tool to show one record are you sure this is the record that was just inserted and are you sure the sitecode is correctly shown (can you see other records for which the sitecode is filled ?)
If you have placed a key-trigger on the KEY-COMMIT key at form level, you will have to issue a call to the built-in procedure COMMIT_FORM in that trigger to get the committing done. In that case you will trigger both the built-in functionality in Forms to commit the block and your own PL-code for INSERTion in the base table. I don't understand why you do both. However, if you don't make the call to COMMIT_FORM, only your own code will be executed and the most likely cause for the site code not to be inserted correctly is that the current record (which is the record the bind variables in your code implicitely refer to) is not what you think it is.
I have audit columns and I can see the insert date and inserted by. SO that confirms that its the only record inserted. What I could do is a work around, after insert, I am updating the table with site code for that schedule date, payroll_id . This is working. If there is anything else please let me know.
@linlali
I am onlu doing commit_form on the commit button. There is no insert statement as you think.
TGTK_Variables.Current_Tri
:system.message_level := '10';
do_key('COMMIT_FORM');
:system.message_level := '0';
TGTK_Variables.Current_Tri
I am onlu doing commit_form on the commit button. There is no insert statement as you think.
TGTK_Variables.Current_Tri
:system.message_level := '10';
do_key('COMMIT_FORM');
:system.message_level := '0';
TGTK_Variables.Current_Tri
If you are satisfied with the update ok,
but if you want to check more:
1. Is dept_staff the name of the real table an not a synonym ? (in that case the trigger-query should refer to the real table)
2. Do you have tools and autorities to monitor sql ( select sql_text from v$sql/enterprise manager/tkproof) : check wether the insert statement really does contains the site_code column.
3. create a copy table and in a on-insert-trigger of the original table insert a record in the copy table with the :new.values (site_code still null ?)
but if you want to check more:
1. Is dept_staff the name of the real table an not a synonym ? (in that case the trigger-query should refer to the real table)
2. Do you have tools and autorities to monitor sql ( select sql_text from v$sql/enterprise manager/tkproof) : check wether the insert statement really does contains the site_code column.
3. create a copy table and in a on-insert-trigger of the original table insert a record in the copy table with the :new.values (site_code still null ?)
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
2. Create an on insert-trigger on the dtl block
message(:dtl_empname? || ':' || :dtl.site-code); -- or an alert : add something (for example) empname to recognize the record
insert_record;
Do you get all exepected messages ?