Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

inserting data into a temp table for some validations

select count(*) into v_count from dept_staff
            where schedule_date = :dept_staff.schedule_date
              and payroll_id = :emp_unexcld.payroll_id
              and (
              (
                  to_date(lpad(:dept_staff.start_time,4,'0') || :dept_staff.start_ampm || 'M','HHMIAM') between
                        to_date(lpad(start_time,4,'0')||start_ampm||'M','HHMIAM') and
                              to_date(lpad(end_time,4,'0') || end_ampm || 'M','HHMIAM')
            )
            or
            (
                  to_date(lpad(:dept_staff.end_time,4,'0') || :dept_staff.end_ampm || 'M','HHMIAM') between
                        to_date(lpad(start_time,4,'0')||start_ampm||'M','HHMIAM') and
                        to_date(lpad(end_time,4,'0')||end_ampm||'M','HHMIAM')
            ));
--------------------------------------------
If v_count = 0 then
            insert into temp_dept_staff(payroll_id,schedule_date,start_time,start_ampm,total_hours,end_time,end_ampm)
                             values(:dept_staff.payroll_id,:dept_staff.schedule_date,:dept_staff.start_time,
                                    :dept_staff.start_ampm,:dept_staff.total_hours,
                                    :dept_staff.end_time,:dept_staff.end_ampm);
 
              :system.message_level := '10';
                   commit;
              :system.message_level := '0';  
End if;

-----------------------

      select count(*) into v_count1 from temp_dept_staff
            where schedule_date = :dept_staff.schedule_date
              and payroll_id = :emp_unexcld.payroll_id
              and (
              (
                  to_date(lpad(:dept_staff.start_time,4,'0') || :dept_staff.start_ampm || 'M','HHMIAM') between
                        to_date(lpad(start_time,4,'0')||start_ampm||'M','HHMIAM') and
                              to_date(lpad(end_time,4,'0') || end_ampm || 'M','HHMIAM')
            )
            or
            (
                  to_date(lpad(:dept_staff.end_time,4,'0') || :dept_staff.end_ampm || 'M','HHMIAM') between
                        to_date(lpad(start_time,4,'0')||start_ampm||'M','HHMIAM') and
                        to_date(lpad(end_time,4,'0')||end_ampm||'M','HHMIAM')
            ));
-----------------------------------

if v_count > 0 then
msgbox.show('Conflicting Schedule ', 'You entered conflicting schedule information.'||chr(10)||' The new schedule runs from '||:dept_staff.start_time||:dept_staff.start_ampm ||' to '||:dept_staff.end_time||:dept_staff.end_ampm||'.' ||chr(10)||'The previous schedule runs from '||v_start_time||v_start_ampm||' to '||v_end_time||v_end_ampm, 'ERROR');
          go_item('reason_code');
     raise form_trigger_failure;
Elsif v_count1 > 1 then            
msgbox.show('Conflicting Schedule ', 'You entered conflicting schedule information.'||chr(10)||' The new schedule runs from '||:dept_staff.start_time||:dept_staff.start_ampm ||' to '||:dept_staff.end_time||:dept_staff.end_ampm||'.' ||chr(10)||'The previous schedule runs from '||v_start_time||v_start_ampm||' to '||v_end_time||v_end_ampm, 'ERROR');
     go_item('reason_code');
     raise form_trigger_failure;
End if;

The reason I am inserting into a temp table is the user enters the data and on the 2nd line by mistake they may type in the same schedule date and same start time, and at this point I have to show the conflicting message.

The problem is I am committing as I insert into the temp table which also does a commit on the databasae table dept_staff. I do not want the data to get inserted in dept_staff table until the user commits. Any solutions?
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or use autonomous transaction.
An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions do SQL operations and commit or roll back, without committing or rolling back the main transaction.
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS00609
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Even with autonomous transactions there is an issue.  If temp_dept_staff is not a global temp table, all users will see all committed rows.

This can cause people to step on each other it two people are working at the same time.
You are right, there must be some column for identifying users in such temp table.
Avatar of anumoses

ASKER

CREATE GLOBAL TEMPORARY TABLE temp_dept_staff (
  SCHEDULE_DATE  DATE,
  START_TIME     NUMBER(9),
  START_AMPM     CHAR(1 BYTE),
  TOTAL_HOURS    NUMBER(9),
  END_TIME       NUMBER(9),
  END_AMPM       CHAR(1 BYTE),
  PAYROLL_ID     VARCHAR2(10 BYTE)
) ON COMMIT PRESERVE ROWS;

I am using this. When I enter a schedule date that is not in the database, then my v_count = 0
so this gets inserted in the temp global table.
But give me conflicing error. This conflicting error has to copme when v_count > 1. But I am getting that error when v_count1 = 1
I'm not able to debug your logic issue.

I can say that you should not have to commit the insert into the temp table as long as everything is done in the same transaction.
thanks