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.