DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: PL/SQL: ORA-06544: PL/SQL: internal error, arguments:

  1. #1
    Join Date
    Aug 2006
    Posts
    2

    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

  2. #2
    Join Date
    Sep 2005
    Posts
    278
    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.

  3. #3
    Join Date
    Aug 2006
    Posts
    2
    Hi Tabreaz,

    I have cross checked my trigger ,there was a problem in 2 insert statement.....

    Thanks mate!!!!

    -Arul

  4. #4
    Join Date
    Sep 2005
    Posts
    278
    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
  •  


Click Here to Expand Forum to Full Width