-
PL/SQL: ORA-06544: PL/SQL: internal error, arguments:
Hi All,
I have executed the following trigger
CREATE OR REPLACE TRIGGER delta_ps_rc_case_qry_trig
AFTER
INSERT OR UPDATE
ON ps_rc_case_qry
FOR EACH ROW
DECLARE
flag VARCHAR2(10) := 'TRUE';
v_oprid VARCHAR2(90);
v_rc_case_query VARCHAR2(120);
v_rc_case_mode VARCHAR2(3);
v_rc_vertical VARCHAR2(12);
v_market VARCHAR2(9);
CURSOR c1 IS SELECT oprid,rc_case_query,rc_case_mode,rc_vertical,market FROM ps_rc_case_qry_jcm;
BEGIN
IF INSERTING THEN
INSERT INTO ps_rc_case_qry_jcm
(oprid,rc_case_query,rc_case_mode,rc_vertical,market,dttm_stamp,case_id,business_unit,
rc_pin_sin_flg,person_pin,sin,cust_id,name1,case_contact,name_search,bo_id_cust,
role_type_id_cust,bo_name,bo_id_contact,role_type_id_cntct,bo_name_2,emplid,deptid,
rc_dept_descr,location,rc_loc_descr,physical_location,rc_cm_cust_rep_flg,rc_phone_cust,
extension_cust,email_addr_cust,rc_phone,extension,email_addr,provider_grp_name,
name_assigned_to,case_type,rc_source,contact_id,rc_status,rc_severity,rc_priority,
problem_type,competency,quick_cd,rc_category,rc_type,rc_detail,closed_date_from,
closed_date_to,creation_date_from,creation_date_to,inst_prod_id,serial_id,product_id,
assettag,provider_grp_id,assigned_to,rc_match_case,rc_tracking_nbr,issue_subtype,
issue_visibility,fin_account_id,target_close_date,target_close_date2,compl_cause,
compl_cause_entity,claim_date,claim_date_to,claim_submitted_by,claim_paid_date,
claim_paid_date_to,compl_close_reason,compl_received_via,rbtacctno,rbtacctid,
rctinstphoneno)
VALUES(:new.oprid,:new.rc_case_query,:new.rc_case_mode,:new.rc_vertical,:new.market,
:new.dttm_stamp,:new.case_id,:new.business_unit,:new.rc_pin_sin_flg,:new.person_pin,
:new.sin,:new.cust_id,:new.name1,:new.case_contact,:new.name_search,:new.bo_id_cust,
:new.role_type_id_cust,:new.bo_name,:new.bo_id_contact,:new.role_type_id_cntct,
:new.bo_name_2,:new.emplid,:new.deptid,:new.rc_dept_descr,:new.location,
:new.rc_loc_descr,:new.physical_location,:new.rc_cm_cust_rep_flg,:new.rc_phone_cust,
:new.extension_cust,:new.email_addr_cust,:new.rc_phone,:new.extension,:new.email_addr,
:new.provider_grp_name,:new.name_assigned_to,:new.case_type,:new.rc_source,
:new.contact_id,:new.rc_status,:new.rc_severity,:new.rc_priority,:new.problem_type,
:new.competency,:new.quick_cd,:new.rc_category,:new.rc_type,:new.rc_detail,
:new.closed_date_from,:new.closed_date_to,:new.creation_date_from,:new.creation_date_to,
:new.inst_prod_id,:new.serial_id,:new.product_id,:new.assettag,:new.provider_grp_id,
:new.assigned_to,:new.rc_match_case,:new.rc_tracking_nbr,:new.issue_subtype,
:new.issue_visibility,:new.fin_account_id,:new.target_close_date,
:new.target_close_date2,:new.compl_cause,:new.compl_cause_entity,:new.claim_date,
:new.claim_date_to,:new.claim_submitted_by,:new.claim_paid_date,:new.claim_paid_date_to,
:new.compl_close_reason,:new.compl_received_via,:new.rbtacctno,:new.rbtacctid,
:new.rctinstphoneno);
END IF;
IF UPDATING THEN
FOR i IN c1 LOOP
v_oprid:=i.oprid;
v_rc_case_query:=i.rc_case_query;
v_rc_case_mode:=i.rc_case_mode;
v_rc_vertical:=i.rc_vertical;
v_market:=i.market;
IF (:new.oprid = v_oprid AND :new.rc_case_query = v_rc_case_query AND
:new.rc_case_mode = v_rc_case_mode AND :new.rc_vertical = v_rc_vertical AND
:new.market = v_market) THEN
DELETE FROM ps_rc_case_qry_jcm
WHERE oprid = :new.oprid
AND rc_case_query = :new.rc_case_query
AND rc_case_mode = :new.rc_case_mode
AND rc_vertical = :new.rc_vertical
AND market = :new.market;
INSERT INTO ps_rc_case_qry_jcm
(oprid,rc_case_query,rc_case_mode,rc_vertical,market,dttm_stamp,case_id,
business_unit,rc_pin_sin_flg,person_pin,sin,cust_id,name1,case_contact,
name_search,bo_id_cust,role_type_id_cust,bo_name,bo_id_contact,
role_type_id_cntct,bo_name_2,emplid,deptid,rc_dept_descr,location,
rc_loc_descr,physical_location,rc_cm_cust_rep_flg,rc_phone_cust,
extension_cust,email_addr_cust,rc_phone,extension,email_addr,
provider_grp_name,name_assigned_to,case_type,rc_source,contact_id,
rc_status,rc_severity,rc_priority,problem_type,competency,
quick_cd,rc_category,rc_type,rc_detail,closed_date_from,closed_date_to,
creation_date_from,creation_date_to,inst_prod_id,serial_id,product_id,
assettag,provider_grp_id,assigned_to,rc_match_case,rc_tracking_nbr,
issue_subtype,issue_visibility,fin_account_id,target_close_date,
target_close_date2,compl_cause,compl_cause_entity,claim_date,
claim_date_to,claim_submitted_by,claim_paid_date,claim_paid_date_to,
compl_close_reason,compl_received_via,rbtacctno,rbtacctid,
rctinstphoneno)
VALUES(:new.oprid,:new.rc_case_query,:new.rc_case_mode,:new.rc_vertical,
:new.market,:new.dttm_stamp,:new.case_id,:new.business_unit,
:new.rc_pin_sin_flg,:new.person_pin,:new.sin,:new.cust_id,:new.name1,
:new.case_contact,:new.name_search,:new.bo_id_cust,
:new.role_type_id_cust,:new.bo_name,:new.bo_id_contact,
:new.role_type_id_cntct,:new.bo_name_2,:new.emplid,:new.deptid,
:new.rc_dept_descr,:new.location,:new.rc_loc_descr,
:new.physical_location,:new.rc_cm_cust_rep_flg,:new.rc_phone_cust,
:new.extension_cust,:new.email_addr_cust,:new.rc_phone,:new.extension,
:new.email_addr,:new.provider_grp_name,:new.name_assigned_to,
:new.case_type,:new.rc_source,:new.contact_id,:new.rc_status,
:new.rc_severity,:new.rc_priority,:new.problem_type,:new.competency,
:new.quick_cd,:new.rc_category,:new.rc_type,:new.rc_detail,
:new.closed_date_from,:new.closed_date_to,:new.creation_date_from,:
new.creation_date_to,:new.inst_prod_id,:new.serial_id,:new.product_id,
:new.assettag,:new.provider_grp_id,:new.assigned_to,:new.rc_match_case,
:new.rc_tracking_nbr,:new.issue_subtype,:new.issue_visibility,
:new.fin_account_id,:new.target_close_date,:new.target_close_date2,
:new.compl_cause,:new.compl_cause_entity,:new.claim_date,
:new.claim_date_to,:new.claim_submitted_by,:new.claim_paid_date,
:new.claim_paid_date_to,:new.compl_close_reason,:new.compl_received_via,
:new.rbtacctno,:new.rbtacctid,:new.rctinstphoneno);
flag:='FALSE';
END IF;
END LOOP;
IF flag='TRUE' THEN
INSERT INTO ps_rc_case_qry_jcm
(oprid,rc_case_query,rc_case_mode,rc_vertical,market,dttm_stamp,case_id,
business_unit,rc_pin_sin_flg,person_pin,sin,cust_id,name1,case_contact,
name_search,bo_id_cust,role_type_id_cust,bo_name,bo_id_contact,
role_type_id_cntct,bo_name_2,emplid,deptid,rc_dept_descr,location,rc_loc_descr,
physical_location,rc_cm_cust_rep_flg,rc_phone_cust,extension_cust,
email_addr_cust,rc_phone,extension,email_addr,provider_grp_name,
name_assigned_to,case_type,rc_source,contact_id,rc_status,rc_severity,
rc_priority,problem_type,competency,quick_cd,rc_category,rc_type,rc_detail,
closed_date_from,closed_date_to,creation_date_from,creation_date_to,
inst_prod_id,serial_id,product_id,assettag,provider_grp_id,assigned_to,
rc_match_case,rc_tracking_nbr,issue_subtype,issue_visibility,fin_account_id,
target_close_date,target_close_date2,compl_cause,compl_cause_entity,claim_date,
claim_date_to,claim_submitted_by,claim_paid_date,claim_paid_date_to,
compl_close_reason,compl_received_via,rbtacctno,rbtacctid,rctinstphoneno)
VALUES(:new.oprid,:new.rc_case_query,:new.rc_case_mode,:new.rc_vertical,
:new.market,:new.dttm_stamp,:new.case_id,:new.business_unit,:new.rc_pin_sin_flg,
:new.person_pin,:new.sin,:new.cust_id,:new.name1,:new.case_contact,
:new.name_search,:new.bo_id_cust,:new.role_type_id_cust,:new.bo_name,
:new.bo_id_contact,:new.role_type_id_cntct,:new.bo_name_2,:new.emplid,
:new.deptid,:new.rc_dept_descr,:new.location,:new.rc_loc_descr,
:new.physical_location,:new.rc_cm_cust_rep_flg,:new.rc_phone_cust,
:new.extension_cust,:new.email_addr_cust,:new.rc_phone,:new.extension,
:new.email_addr,:new.provider_grp_name,:new.name_assigned_to,:new.case_type,
:new.rc_source,:new.contact_id,:new.rc_status,:new.rc_severity,:new.rc_priority,
:new.problem_type,:new.competency,:new.quick_cd,:new.rc_category,:new.rc_type,
:new.rc_detail,:new.closed_date_from,:new.closed_date_to,
:new.creation_date_from,:new.creation_date_to,:new.inst_prod_id,:new.serial_id,
:new.product_id,:new.assettag,:new.provider_grp_id,:new.assigned_to,
:new.rc_match_case,:new.rc_tracking_nbr,:new.issue_subtype,
:new.issue_visibility,:new.fin_account_id,:new.target_close_date,
:new.target_close_date2,:new.compl_cause,:new.compl_cause_entity,
:new.claim_date,:new.claim_date_to,:new.claim_submitted_by,:new.claim_paid_date,
:new.claim_paid_date_to,:new.compl_close_reason,:new.compl_received_via,
:new.rbtacctno,:new.rbtacctid,:new.rctinstphoneno);
END IF;
END IF;
END delta_ps_rc_case_qry_trig;
The following error was throwing up......
(1): PLS-00801: internal error [ph2csql_strdef_to_diana: bind]
(2): PL/SQL: ORA-06544: PL/SQL: internal error, arguments: [ph2csql_strdef_to_diana: bind], [], [], [], [], [], [], []
(3): PL/SQL: SQL Statement ignored
I'm not sure of this error .....
i'm using the oracle version--
oracle 9i enterprise edition -9.2.0.6.0
Please help me out
Thanks
Arul
-
Seems there is some problem in bind variables,
My suggestion is you split the code into multiple procedures or single package and test it. You may find the error in specific procedure then you can debug it easily.
-
Hi Tabreaz,
I have cross checked my trigger ,there was a problem in 2 insert statement.....
Thanks mate!!!!
-Arul
-
One query, is there bulk load been done on ps_rc_case_qry tabel?
If you do bulk load then its not good to have code in triggers it slows down the overall performance of ur application.
Even for single row inserts the query
(
SELECT oprid,rc_case_query,rc_case_mode,rc_vertical,market
FROM ps_rc_case_qry_jcm
) will get parsed each time ur executing inside a trigge.
Consider having that code into some package, or procedure which will parse the code.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|