oracle forms insert into database question

I have this query
BEGIN
       FOR v_LoopCounter IN 1..50 LOOP

INSERT INTO dept_staff (sequential_number,department_id,schedule_date,
                        position,payroll_id,start_time,end_time,total_hours,
                        start_hours,start_minutes,end_hours,end_minutes,site_code)
         select  :dtl.sequential_number,:dtl.department_id1,:dtl.schedule_date,
                 :dtl.position_cd,:dtl.payroll_id,:dtl.start_time1,:dtl.end_time2,:dtl.total_hours,
                 :dtl.start_hours,:dtl.start_minutes,:dtl.end_hours,:dtl.end_minutes,:dtl.site_code
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--'ZZW0023516'
  and ds.schedule_date = :hdr.sched_date--'02-sep-2011'
  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?
LVL 6
anumosesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
flow01Connect With a Mentor Commented:
1.  Can you show us the properties of the :dtl.site-code item ?
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 ?

0
 
flow01Commented:
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 ?
0
 
anumosesAuthor Commented:
no value of site code does not get null. How can I insert in a loop. When user wants to insert multiple lines?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
anumosesAuthor Commented:
there is no on insert trigger on dept_staff tsble
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:

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 ?
0
 
anumosesAuthor Commented:
Yes insert all
0
 
flow01Commented:
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.
 
0
 
anumosesAuthor Commented:
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.
0
 
anumosesAuthor Commented:
select * from all_triggers where TABLE_NAME = 'DEPT_STAFF'

No rows returned. So no database triggers on the table.
Also no dependiencies.
0
 
linlaliCommented:
Why don't you put a "message(:dtl.site_code)" in the loop to see what Forms thinks is the value of that item?
0
 
anumosesAuthor Commented:
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
0
 
linlaliCommented:
Where have you put this code, in what trigger? Why are the site code blank for the records at the bottom?
0
 
anumosesAuthor Commented:
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.
0
 
anumosesAuthor Commented:
on commit button of the form. There is no specific insert code, as its a database block.So will automatically insert on commit
0
 
linlaliCommented:
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.
0
 
anumosesAuthor Commented:
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.
0
 
anumosesAuthor Commented:
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
0
 
flow01Commented:
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).
0
 
anumosesAuthor Commented:
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
0
 
flow01Connect With a Mentor Commented:
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 ?)
0
 
linlaliCommented:
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.
0
 
anumosesAuthor Commented:
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.
0
 
anumosesAuthor Commented:
@linlali

I am onlu doing commit_form on the commit button. There is no insert statement as you think.

TGTK_Variables.Current_Trigger := get_application_property(CURRENT_FORM_NAME)||'.Toolbar.Button_Commit.When-Button-Pressed';

      :system.message_level := '10';
         do_key('COMMIT_FORM');
      :system.message_level := '0';  
   
TGTK_Variables.Current_Trigger := NULL;
0
 
flow01Commented:
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 ?)
0
 
linlaliCommented:
Could it be that the SITE_CODE item has the "Query only" propertry?
0
 
anumosesAuthor Commented:
thanks
0
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.