getting error when calling a procedure from a trigger in Oracle

Question

I have the following code excerpt which will insert data into three tables using a trigger and a stored procedure to be called by the trigger. Both the trigger and procedure are getting successfully compiled.

   CREATE OR REPLACE TRIGGER trigger_insert_attr AFTER
    INSERT ON BSCS_WORK_SYNC_INFO FOR EACH ROW BEGIN 
    bscs_rateplan_sync(:new.project_id , :new.tmcode);
   update BSCS_WORK_SYNC_INFO set comp_date=SYSDATE where 
    project_id=:new.project_id;
  END trigger_insert_attr;
  /


   CREATE OR REPLACE
  PROCEDURE bscs_rateplan_sync
  (
    tmcode_list IN VARCHAR2,
    project_id  IN VARCHAR2
  )
  AS
  BEGIN
  EXECUTE IMMEDIATE 'delete from  ecm_mpulktm1 where 
  project_id='||project_id ;
  EXECUTE IMMEDIATE 'delete from  ecm_mpulktm2 where 
  project_id='||project_id ;
  EXECUTE IMMEDIATE 'delete from  ecm_fup_tariff_work where 
  project_id='||project_id ;
  EXECUTE immediate 'insert into ecm_mpulktm1 select '||project_id||' , m1.* 
  from sysadm.mpulktm1@to_bscsprd_rpt m1 where tmcode in 
  ('||tmcode_list||')' 
   ;
  EXECUTE immediate 'insert into ecm_mpulktm2 select '||project_id||' , m1.* 
  from sysadm.mpulktm2@to_bscsprd_rpt m1 where tmcode in 
  ('||tmcode_list||')' 
  ;
  EXECUTE immediate 'insert into ecm_fup_tariff_work select '||project_id||' 
 , m1.* from sysadm.fup_tariff_work@to_bscsprd_rpt m1 where tmcode in 
 ('||tmcode_list||')' ;
  END;
  /

But while data is inserted into the table BSCS_WORK_SYNC_INFO I am getting the below error :

SQL Error: ORA-00933: SQL command not properly ended ORA-06512: at "ECMREPORT.BSCS_RATEPLAN_SYNC", line 6 ORA-06512: at "ECMREPORT.TRIGGER_INSERT_ATTR", line 2 ORA-04088: error during execution of trigger 'ECMREPORT.TRIGGER_INSERT_ATTR'

Can someone help to resolve the error? I am relatively new to PL/SQL hence would request help to resolve the above error. The data in the table BSCS_WORK_SYNC_INFO would be populated by web services. Also the data to be updated in the table after the procedure is executed.

Answer 1

A "quick and dirty" fix

CREATE OR REPLACE TRIGGER trigger_insert_attr
    AFTER INSERT ON BSCS_WORK_SYNC_INFO FOR EACH ROW
BEGIN
    bscs_rateplan_sync(:new.project_id, :new.tmcode);
    update bscs_work_sync_info set comp_date = SYSDATE where project_id = :new.project_id;
END trigger_insert_attr;
/

CREATE OR REPLACE
PROCEDURE bscs_rateplan_sync
    ( tmcode_list   IN VARCHAR2
    , project_id    IN VARCHAR2 )
AS
BEGIN
    delete from ecm_mpulktm1 where project_id = bscs_rateplan_sync.project_id;
    delete from ecm_mpulktm2 where project_id = bscs_rateplan_sync.project_id;
    delete from ecm_fup_tariff_work where project_id = bscs_rateplan_sync.project_id;

    execute immediate '
        insert into ecm_mpulktm1
        select :project_id, m1.*
        from sysadm.mpulktm1@to_bscsprd_rpt m1
        where tmcode in ('||nvl(bscs_rateplan_sync.tmcode_list,'null')||')'
    using in bscs_rateplan_sync.project_id;

    execute immediate '
        insert into ecm_mpulktm2
        select :project_id, m1.*
        from sysadm.mpulktm2@to_bscsprd_rpt m1
        where tmcode in ('||nvl(bscs_rateplan_sync.tmcode_list,'null')||')'
    using in bscs_rateplan_sync.project_id;

    execute immediate '
        insert into ecm_fup_tariff_work
        select :project_id, m1.*
        from sysadm.fup_tariff_work@to_bscsprd_rpt m1
        where tmcode in ('||nvl(bscs_rateplan_sync.tmcode_list,'null')||')'
    using in bscs_rateplan_sync.project_id;
END;
/

A proper fix

... would be

• to remove the dynamic SQL altogether by parsing the list of values in bscs_rateplan_sync.tmcode_list to a collection and binding the collection to a static SQL,
• to move the update of bscs_work_sync_info.comp_date to the update bscs_work_sync_info() stored proc and to declare the trigger to call the stored proc instead of an anonymous PLSQL block.