Home » SQL & PL/SQL » SQL & PL/SQL » Error in execution of Trigger (oracle 10g)
Error in execution of Trigger [message #637608] Thu, 21 May 2015 02:17 Go to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

conn hr/hr

select * from employees

select * from departments

create table emp as select * from employees
where 1=2

create table temp
partition by list(department_id)
(partition pnull values(null))
as select * from emp

create table dept as select * from departments
where 1=2


CREATE OR REPLACE TRIGGER trg_emp_hr
   AFTER INSERT
   ON emp
   FOR EACH ROW
DECLARE
   v_count   NUMBER;
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   IF :NEW.department_id IS NOT NULL
   THEN
      SELECT COUNT (*)
        INTO v_count
        FROM dept
       WHERE department_id = :NEW.department_id;

      IF v_count = 0
      THEN
         EXECUTE IMMEDIATE    'Alter table temp add partition p'
                           || :NEW.department_id
                           || ' values '''
                           || :NEW.department_id
                           || ''')';
      END IF;

      INSERT INTO temp
                  (employee_id, first_name, last_name,
                   email, phone_number, hire_date,
                   job_id, salary, commission_pct,
                   manager_id, department_id
                  )
           VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name,
                   :NEW.email, :NEW.phone_number, :NEW.hire_date,
                   :NEW.job_id, :NEW.salary, :NEW.commission_pct,
                   :NEW.manager_id, :NEW.department_id
                  );

      COMMIT;
   ELSE
      INSERT INTO temp
                  (employee_id, first_name, last_name,
                   email, phone_number, hire_date,
                   job_id, salary, commission_pct,
                   manager_id, department_id
                  )
           VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name,
                   :NEW.email, :NEW.phone_number, :NEW.hire_date,
                   :NEW.job_id, :NEW.salary, :NEW.commission_pct,
                   :NEW.manager_id, :NEW.department_id
                  );

      COMMIT;
   END IF;
END;


insert into emp
select * from employees

ORA-14007: missing LESS keyword
ORA-06512: at "HR.TRG_EMP_HR", line 11
ORA-04088: error during execution of trigger 'HR.TRG_EMP_HR'


I am using same trigger in one of my applications but don't know i am getting same error again n again. Please help thanks.
Re: Error in execution of Trigger [message #637614 is a reply to message #637608] Thu, 21 May 2015 02:44 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Error means what it says - missing keywors at line 11.
Line 11 is execute immediate.
Your alter table is wrong.

That said, I don't think adding partitions in a trigger is a particularly good idea.
Suppose the transaction that ran the trigger is rolled back - the rows inserted into temp will remain.
Re: Error in execution of Trigger [message #637623 is a reply to message #637608] Thu, 21 May 2015 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
CREATE OR REPLACE TRIGGER ...
PRAGMA AUTONOMOUS_TRANSACTION;


99.9999% of the time this is a bug.

Re: Error in execution of Trigger [message #637626 is a reply to message #637608] Thu, 21 May 2015 02:59 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
I think you have a single bracket in your EXECUTE IMMEDIATE.

But I do not think this is sensible.
If you yout partition pruning, it would be better to create a fixed number of hash partitions, sufficient for the maximum number of deartments you are ever likely to have. But even then, why are you doing this? What is your indexing strategy going to be? WHat about constraints?
Re: Error in execution of Trigger [message #637633 is a reply to message #637626] Thu, 21 May 2015 03:53 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

SO Finally made few changes inside trigger as per seniors suggestion, successfully inserted 77 rows,partition get created for that. but now get new error "partition key doesn't match to any partition". I know the reason behind this error. it is trying to create a partition for value for which the paertition has already been created but it is not able to find that value in user_tab_partitions table and returning 0 in count.
The code which i am using in application is creating partition for the varchar type column, n here i am using numeric column to create partition. maybe this is the reaon i am having trouble. Now what seniors have to say ?

CREATE OR REPLACE TRIGGER trg_emp_hr
after insert on emp
for each row
declare
v_count number;
pragma autonomous_transaction;
begin
if :new.department_id is not null then

select count(*) into v_count from user_tab_partitions
where partition_name='P'||:new.department_id
and table_name='TEMP';

if v_count=0 then
EXECUTE IMMEDIATE'Alter table temp add partition p'||:new.department_id||' values ('''||:new.department_id||''')';
commit;
end if;
rollback;
insert into temp(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,
                 HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
          values(:new.EMPLOYEE_ID,:new.FIRST_NAME,:new.LAST_NAME,:new.EMAIL,:new.PHONE_NUMBER,
                 :new.HIRE_DATE,:new.JOB_ID,:new.SALARY,:new.COMMISSION_PCT,:new.MANAGER_ID,:new.DEPARTMENT_ID);
                 commit;
                 
else
insert into temp(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,
                 HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
          values(:new.EMPLOYEE_ID,:new.FIRST_NAME,:new.LAST_NAME,:new.EMAIL,:new.PHONE_NUMBER,
                 :new.HIRE_DATE,:new.JOB_ID,:new.SALARY,:new.COMMISSION_PCT,:new.MANAGER_ID,:new.DEPARTMENT_ID);
                 commit;
 end if;
end;
/
Re: Error in execution of Trigger [message #637634 is a reply to message #637614] Thu, 21 May 2015 03:55 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

@cm i have handled this situation in actual code , i have used rollback after alter statement -->end if -->rollback-->insert into ......

thanks all for the reply.
Re: Error in execution of Trigger [message #637642 is a reply to message #637634] Thu, 21 May 2015 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is completely silly and will NEVER work whatever you want to do.

Re: Error in execution of Trigger [message #637646 is a reply to message #637642] Thu, 21 May 2015 05:27 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

It is working let me show you the original code:-

CREATE OR REPLACE TRIGGER trg_gtemp_data_upd_dtl
   AFTER INSERT
   ON gtemp_data_upd_dtl
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   vcount_stvcd   stv_mst.stv_cd%TYPE;
   v_stvid        VARCHAR2 (255);
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   SELECT NVL (MAX (stv_id), 0) + 1
     INTO v_stvid
     FROM stv_mst
    WHERE stv_cd IS NOT NULL;

   IF :NEW.cv12_stv IS NOT NULL
   THEN
--select COUNT(*) INTO VCOUNT_STVCD from user_tab_partitions
--WHERE TABLE_NAME='PRT_DATA_UPD_DTL'
--AND partition_name=:NEW.CV12_STV;
      SELECT COUNT (1)
        INTO vcount_stvcd
        FROM stv_mst
       WHERE stv_cd = :NEW.cv12_stv;

--AND :NEW.CV12_STV IS NOT NULL;
      IF vcount_stvcd = 0
      THEN
         INSERT INTO stv_mst
                     (stv_id, stv_cd, stv_dtl
                     )
              VALUES (v_stvid, :NEW.cv12_stv, 'NEW STV'
                     );

         EXECUTE IMMEDIATE    'ALTER TABLE PRT_DATA_UPD_DTL
ADD PARTITION '
                           || :NEW.cv12_stv
                           || ' VALUES('''
                           || :NEW.cv12_stv
                           || ''')';

         COMMIT;
      END IF;

      ROLLBACK;

      INSERT INTO prt_data_upd_dtl
                  (data_upd_id, data_upd_dtl_id, servicekey,
                   nodelabel, inserttimestamp,
                   cv2_calldatetime, cv6a_calledparty,
                   cv6b_calltype, cv9_duration,
                   cv11_1a_dedbuckname, cv11_1b_dedbuckstrt,
                   cv11_1c_dedbuckend, cv11_1d_dedbuckrate,
                   cv11_2a_dedbuckname, cv11_2b_dedbuckstrt,
                   cv11_2c_dedbuckend, cv11_2d_dedbuckrate,
                   cv11_3a_dedbuckname, cv11_3b_dedbuckstrt,
                   cv11_3c_dedbuckend, cv11_3d_dedbuckrate,
                   cv11_4a_dedbuckname, cv11_4b_dedbuckstrt,
                   cv11_4c_dedbuckend, cv11_4d_dedbuckrate,
                   cv12_baseplan, cv12_stv,
                   cv13_1a_rembalname, cv13_1b_rembalstrt,
                   cv13_1c_rembalend, cv13_1d_rembalrate,
                   cv13_2a_rembalname, cv13_2b_rembalstrt,
                   cv13_2c_rembalend, cv13_2d_rembalrate,
                   cv13_3a_rembalname, cv13_3b_rembalstrt,
                   cv13_3c_rembalend, cv13_3d_rembalrate,
                   cv13_4a_rembalname, cv13_4b_rembalstrt,
                   cv13_4c_rembalend, cv13_4d_rembalrate,
                   cv13_5a_rembalname, cv13_5b_rembalstrt,
                   cv13_5c_rembalend, cv13_5d_rembalrate,
                   cv13_6a_rembalname, cv13_6b_rembalstrt,
                   cv13_6c_rembalend, cv13_6d_rembalrate,
                   cv13_7a_rembalname, cv13_7b_rembalstrt,
                   cv13_7c_rembalend, cv13_7d_rembalrate,
                   cv13_8a_rembalname, cv13_8b_rembalstrt,
                   cv13_8c_rembalend, cv13_8d_rembalrate
                  )
           VALUES (:NEW.data_upd_id, :NEW.data_upd_dtl_id, :NEW.servicekey,
                   :NEW.nodelabel, :NEW.inserttimestamp,
                   :NEW.cv2_calldatetime, :NEW.cv6a_calledparty,
                   :NEW.cv6b_calltype, :NEW.cv9_duration,
                   :NEW.cv11_1a_dedbuckname, :NEW.cv11_1b_dedbuckstrt,
                   :NEW.cv11_1c_dedbuckend, :NEW.cv11_1d_dedbuckrate,
                   :NEW.cv11_2a_dedbuckname, :NEW.cv11_2b_dedbuckstrt,
                   :NEW.cv11_2c_dedbuckend, :NEW.cv11_2d_dedbuckrate,
                   :NEW.cv11_3a_dedbuckname, :NEW.cv11_3b_dedbuckstrt,
                   :NEW.cv11_3c_dedbuckend, :NEW.cv11_3d_dedbuckrate,
                   :NEW.cv11_4a_dedbuckname, :NEW.cv11_4b_dedbuckstrt,
                   :NEW.cv11_4c_dedbuckend, :NEW.cv11_4d_dedbuckrate,
                   :NEW.cv12_baseplan, :NEW.cv12_stv,
                   :NEW.cv13_1a_rembalname, :NEW.cv13_1b_rembalstrt,
                   :NEW.cv13_1c_rembalend, :NEW.cv13_1d_rembalrate,
                   :NEW.cv13_2a_rembalname, :NEW.cv13_2b_rembalstrt,
                   :NEW.cv13_2c_rembalend, :NEW.cv13_2d_rembalrate,
                   :NEW.cv13_3a_rembalname, :NEW.cv13_3b_rembalstrt,
                   :NEW.cv13_3c_rembalend, :NEW.cv13_3d_rembalrate,
                   :NEW.cv13_4a_rembalname, :NEW.cv13_4b_rembalstrt,
                   :NEW.cv13_4c_rembalend, :NEW.cv13_4d_rembalrate,
                   :NEW.cv13_5a_rembalname, :NEW.cv13_5b_rembalstrt,
                   :NEW.cv13_5c_rembalend, :NEW.cv13_5d_rembalrate,
                   :NEW.cv13_6a_rembalname, :NEW.cv13_6b_rembalstrt,
                   :NEW.cv13_6c_rembalend, :NEW.cv13_6d_rembalrate,
                   :NEW.cv13_7a_rembalname, :NEW.cv13_7b_rembalstrt,
                   :NEW.cv13_7c_rembalend, :NEW.cv13_7d_rembalrate,
                   :NEW.cv13_8a_rembalname, :NEW.cv13_8b_rembalstrt,
                   :NEW.cv13_8c_rembalend, :NEW.cv13_8d_rembalrate
                  );

      COMMIT;
   ELSE
      INSERT INTO prt_data_upd_dtl
                  (data_upd_id, data_upd_dtl_id, servicekey,
                   nodelabel, inserttimestamp,
                   cv2_calldatetime, cv6a_calledparty,
                   cv6b_calltype, cv9_duration,
                   cv11_1a_dedbuckname, cv11_1b_dedbuckstrt,
                   cv11_1c_dedbuckend, cv11_1d_dedbuckrate,
                   cv11_2a_dedbuckname, cv11_2b_dedbuckstrt,
                   cv11_2c_dedbuckend, cv11_2d_dedbuckrate,
                   cv11_3a_dedbuckname, cv11_3b_dedbuckstrt,
                   cv11_3c_dedbuckend, cv11_3d_dedbuckrate,
                   cv11_4a_dedbuckname, cv11_4b_dedbuckstrt,
                   cv11_4c_dedbuckend, cv11_4d_dedbuckrate,
                   cv12_baseplan, cv12_stv,
                   cv13_1a_rembalname, cv13_1b_rembalstrt,
                   cv13_1c_rembalend, cv13_1d_rembalrate,
                   cv13_2a_rembalname, cv13_2b_rembalstrt,
                   cv13_2c_rembalend, cv13_2d_rembalrate,
                   cv13_3a_rembalname, cv13_3b_rembalstrt,
                   cv13_3c_rembalend, cv13_3d_rembalrate,
                   cv13_4a_rembalname, cv13_4b_rembalstrt,
                   cv13_4c_rembalend, cv13_4d_rembalrate,
                   cv13_5a_rembalname, cv13_5b_rembalstrt,
                   cv13_5c_rembalend, cv13_5d_rembalrate,
                   cv13_6a_rembalname, cv13_6b_rembalstrt,
                   cv13_6c_rembalend, cv13_6d_rembalrate,
                   cv13_7a_rembalname, cv13_7b_rembalstrt,
                   cv13_7c_rembalend, cv13_7d_rembalrate,
                   cv13_8a_rembalname, cv13_8b_rembalstrt,
                   cv13_8c_rembalend, cv13_8d_rembalrate
                  )
           VALUES (:NEW.data_upd_id, :NEW.data_upd_dtl_id, :NEW.servicekey,
                   :NEW.nodelabel, :NEW.inserttimestamp,
                   :NEW.cv2_calldatetime, :NEW.cv6a_calledparty,
                   :NEW.cv6b_calltype, :NEW.cv9_duration,
                   :NEW.cv11_1a_dedbuckname, :NEW.cv11_1b_dedbuckstrt,
                   :NEW.cv11_1c_dedbuckend, :NEW.cv11_1d_dedbuckrate,
                   :NEW.cv11_2a_dedbuckname, :NEW.cv11_2b_dedbuckstrt,
                   :NEW.cv11_2c_dedbuckend, :NEW.cv11_2d_dedbuckrate,
                   :NEW.cv11_3a_dedbuckname, :NEW.cv11_3b_dedbuckstrt,
                   :NEW.cv11_3c_dedbuckend, :NEW.cv11_3d_dedbuckrate,
                   :NEW.cv11_4a_dedbuckname, :NEW.cv11_4b_dedbuckstrt,
                   :NEW.cv11_4c_dedbuckend, :NEW.cv11_4d_dedbuckrate,
                   :NEW.cv12_baseplan, :NEW.cv12_stv,
                   :NEW.cv13_1a_rembalname, :NEW.cv13_1b_rembalstrt,
                   :NEW.cv13_1c_rembalend, :NEW.cv13_1d_rembalrate,
                   :NEW.cv13_2a_rembalname, :NEW.cv13_2b_rembalstrt,
                   :NEW.cv13_2c_rembalend, :NEW.cv13_2d_rembalrate,
                   :NEW.cv13_3a_rembalname, :NEW.cv13_3b_rembalstrt,
                   :NEW.cv13_3c_rembalend, :NEW.cv13_3d_rembalrate,
                   :NEW.cv13_4a_rembalname, :NEW.cv13_4b_rembalstrt,
                   :NEW.cv13_4c_rembalend, :NEW.cv13_4d_rembalrate,
                   :NEW.cv13_5a_rembalname, :NEW.cv13_5b_rembalstrt,
                   :NEW.cv13_5c_rembalend, :NEW.cv13_5d_rembalrate,
                   :NEW.cv13_6a_rembalname, :NEW.cv13_6b_rembalstrt,
                   :NEW.cv13_6c_rembalend, :NEW.cv13_6d_rembalrate,
                   :NEW.cv13_7a_rembalname, :NEW.cv13_7b_rembalstrt,
                   :NEW.cv13_7c_rembalend, :NEW.cv13_7d_rembalrate,
                   :NEW.cv13_8a_rembalname, :NEW.cv13_8b_rembalstrt,
                   :NEW.cv13_8c_rembalend, :NEW.cv13_8d_rembalrate
                  );

      COMMIT;
   END IF;
END;
/

One of our client sends us Call Data Records in csv or excel file. Our task is to find overcharged and undercharged records. telling them which plan arrived in which quarter n several things.
for ex. Caller has bought plan "ISAV6" then his call should be charged at 1paisa/second .
I have made partition on table using Plan name.
We get CDRs quarterwise. As a developer my task is to upload CDRs and priniting Undercharge overcharge observations in a report. To achieve this task i have made a software which does all these activities.
Let me tell you I am the only Developer in my company . so i had to do each n every thing individually. In last 1.5 years i have learnt a lot through this forum.
@michel Maybe I am wrong and the code is silly but still it fulfills the purpose.
I have processed data for last 4 quarters successfully.

[Updated on: Thu, 21 May 2015 05:29]

Report message to a moderator

Re: Error in execution of Trigger [message #637650 is a reply to message #637646] Thu, 21 May 2015 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, it is not working you were just lucky.
And if the code worked last year why had you to post a question here?

[Updated on: Thu, 21 May 2015 06:39]

Report message to a moderator

Re: Error in execution of Trigger [message #637663 is a reply to message #637650] Thu, 21 May 2015 07:25 Go to previous messageGo to next message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member

i have interview and i was revising triggers , when i tried to create same trigger on default tables. i was getting error.
and i have already said in application i made partition usinh char type column , n here the column is numeric.
Re: Error in execution of Trigger [message #637668 is a reply to message #637663] Thu, 21 May 2015 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

One example.
BEGIN
  IF <condition> THEN
     <do something>
     COMMIT;
  END IF;
  ROLLBACK;
END;

What do you think the ROLLBACK rolls back?

Re: Error in execution of Trigger [message #637674 is a reply to message #637668] Thu, 21 May 2015 08:49 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
why don't you simply have oracle automatically create the partitions? Since oracle 11, it can do that if you define the partition range.
Previous Topic: does any one use oracle v12?
Next Topic: date Format error
Goto Forum:
  


Current Time: Tue Mar 19 05:54:18 CDT 2024