Solved

oracle forms insert into database question

Posted on 2011-09-09
26
385 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:anumoses
  • 14
  • 6
  • 5
  • +1
26 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 36513636
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
 
LVL 6

Author Comment

by:anumoses
ID: 36513799
no value of site code does not get null. How can I insert in a loop. When user wants to insert multiple lines?
0
 
LVL 6

Author Comment

by:anumoses
ID: 36513808
there is no on insert trigger on dept_staff tsble
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36514143

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
 
LVL 6

Author Comment

by:anumoses
ID: 36514282
Yes insert all
0
 
LVL 20

Expert Comment

by:flow01
ID: 36516218
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
 
LVL 6

Author Comment

by:anumoses
ID: 36522168
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
 
LVL 6

Author Comment

by:anumoses
ID: 36522195
select * from all_triggers where TABLE_NAME = 'DEPT_STAFF'

No rows returned. So no database triggers on the table.
Also no dependiencies.
0
 

Expert Comment

by:linlali
ID: 36522377
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
 
LVL 6

Author Comment

by:anumoses
ID: 36522433
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
 

Expert Comment

by:linlali
ID: 36522848
Where have you put this code, in what trigger? Why are the site code blank for the records at the bottom?
0
 
LVL 6

Author Comment

by:anumoses
ID: 36522882
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
 
LVL 6

Author Comment

by:anumoses
ID: 36522891
on commit button of the form. There is no specific insert code, as its a database block.So will automatically insert on commit
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:linlali
ID: 36523007
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
 
LVL 6

Author Comment

by:anumoses
ID: 36523090
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
 
LVL 20

Accepted Solution

by:
flow01 earned 250 total points
ID: 36527484
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
 
LVL 6

Author Comment

by:anumoses
ID: 36528771
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
 
LVL 20

Expert Comment

by:flow01
ID: 36532648
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
 
LVL 6

Author Comment

by:anumoses
ID: 36533622
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
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 250 total points
ID: 36539437
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
 

Expert Comment

by:linlali
ID: 36541046
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
 
LVL 6

Author Comment

by:anumoses
ID: 36543768
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
 
LVL 6

Author Comment

by:anumoses
ID: 36543838
@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
 
LVL 20

Expert Comment

by:flow01
ID: 36552128
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
 

Expert Comment

by:linlali
ID: 36558543
Could it be that the SITE_CODE item has the "Query only" propertry?
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 36562955
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Left Justify field in Oracle 6 70
levels for reporting 5 51
T-SQL Convert to PL/SQL 23 62
query in Oracle forms Builder 2 27
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now