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 |
|
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 |
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 #637626 is a reply to message #637608] |
Thu, 21 May 2015 02:59 |
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 |
|
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 #637646 is a reply to message #637642] |
Thu, 21 May 2015 05:27 |
|
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
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Mar 19 05:54:18 CDT 2024
|