Avatar of anumoses
anumoses
Flag 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?
Oracle Database

Avatar of undefined
Last Comment
anumoses

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Helena Marková

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
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.
Helena Marková

You are right, there must be some column for identifying users in such temp table.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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
slightwv (䄆 Netminder)

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.
anumoses

ASKER
thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.