I have the following procedure
create or replace
PROCEDURE bscs_rateplan_sync (
tmcode_list IN CLOB,
project_id IN VARCHAR2,
requestname IN VARCHAR2,
v_errm OUT VARCHAR2
)
AS
v_tmcode_list CLOB;
v_project_id VARCHAR2 (4000) := project_id;
v_requestname VARCHAR2 (32) := requestname;
BEGIN
DBMS_OUTPUT.put_line ('GOOD');
SELECT tmcode
INTO v_tmcode_list
FROM bscs_work_sync_info
WHERE project_id = v_project_id AND requestname = v_requestname;
EXECUTE IMMEDIATE 'delete from ecmreport.ecm_mpulktm1 where project_id='|| v_project_id||' and requestname='|| v_requestname;
EXECUTE IMMEDIATE 'delete from ecmreport.ecm_mpulktm2 where project_id='|| v_project_id||' and requestname='|| v_requestname;
EXECUTE IMMEDIATE 'delete from ecmreport.ecm_fup_tariff_work where project_id='|| v_project_id||' requestname='|| v_requestname;
INSERT INTO ecm_mpulktm1
SELECT v_project_id, v_requestname, m1.*
FROM sysadm.mpulktm1@to_bscsprd_rpt m1
WHERE tmcode IN (
SELECT tmcode
FROM sysadm.rateplan@to_bscsprd_rpt
WHERE shdes IN (
SELECT REGEXP_SUBSTR (v_tmcode_list,
'[^,]+',
1,
LEVEL
)
FROM DUAL
CONNECT BY REGEXP_SUBSTR (v_tmcode_list,
'[^,]+',
1,
LEVEL
) IS NOT NULL));
INSERT INTO ecm_mpulktm2
SELECT v_project_id, v_requestname, m1.*
FROM sysadm.mpulktm2@to_bscsprd_rpt m1
WHERE tmcode IN (
SELECT tmcode
FROM sysadm.rateplan@to_bscsprd_rpt
WHERE shdes IN (
SELECT REGEXP_SUBSTR (v_tmcode_list,
'[^,]+',
1,
LEVEL
)
FROM DUAL
CONNECT BY REGEXP_SUBSTR (v_tmcode_list,
'[^,]+',
1,
LEVEL
) IS NOT NULL));
INSERT INTO ecm_fup_tariff_work
SELECT v_project_id, v_requestname, m1.*
FROM sysadm.fup_tariff_work@to_bscsprd_rpt m1
WHERE tmcode IN (
SELECT tmcode
FROM sysadm.rateplan@to_bscsprd_rpt
WHERE shdes IN (
SELECT REGEXP_SUBSTR (v_tmcode_list,
'[^,]+',
1,
LEVEL
)
FROM DUAL
CONNECT BY REGEXP_SUBSTR (v_tmcode_list,
'[^,]+',
1,
LEVEL
) IS NOT NULL));
UPDATE bscs_work_sync_info
SET comp_date = SYSDATE,
status = 'SUC'
WHERE project_id = v_project_id AND requestname = v_requestname;
UPDATE ecmcatalog.ideaecm_publish_lock
SET status = 'Unlocked'
WHERE projectcode = v_project_id
AND shdes IN (
SELECT REGEXP_SUBSTR (v_tmcode_list, '[^,]+', 1, LEVEL)
FROM DUAL
CONNECT BY REGEXP_SUBSTR (v_tmcode_list, '[^,]+', 1, LEVEL) IS NOT NULL);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_errm := SUBSTR(SQLERRM, 1, 64);
ROLLBACK;
DBMS_OUTPUT.put_line (v_errm);
UPDATE bscs_work_sync_info
SET err_text = v_errm,
comp_date = SYSDATE,
status = 'FAIL'
WHERE project_id = v_project_id AND requestname = v_requestname;
COMMIT;
END;
When I execute the above I get the delete section is not working and when i comment the section then the error i get is :
ORA-00932: inconsistent datatypes: expected - got CLOBPlease help to rectify my mistakes in the above procedure. The procedure will be called from a Java code and the v_tmcode_list will contain a list of 10000 records.
I am trying to execute the procedure as below
DECLARE
PROJECT_ID VARCHAR2(200);
REQUESTNAME VARCHAR2(200);
V_ERRM VARCHAR2(200);
BEGIN
PROJECT_ID := '123ABC';
REQUESTNAME := 'Req_name';
V_ERRM := NULL;
ECMREPORT.BSCS_RATEPLAN_SYNC ( PROJECT_ID, REQUESTNAME, V_ERRM );
END;
sending the tmcode_list as 'T1003,T1004' (there may be more than 12000 entries in the clob).I am executing from sql developer and the errors mentioned above are the errors that i get.
Update
---------------------------
As suggested run the below. Please help.
SQL> DECLARE
2 PROJECT_ID VARCHAR2(200);
3 REQUESTNAME VARCHAR2(200);
4 V_ERRM VARCHAR2(200);
tmcode_list clob;
5 6 BEGIN
7
8 tmcode_list := '640,641,973';
9 PROJECT_ID := '123ABC';
10 REQUESTNAME := 'Req_name';
11 V_ERRM := NULL;
12
13 ECMREPORT.BSCS_RATEPLAN_SYNC ( tmcode_list,PROJECT_ID, REQUESTNAME, V_ERRM );
14
15 END;
16 /
DECLARE
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "ECMREPORT.BSCS_RATEPLAN_SYNC_TEST", line 19
ORA-06512: at line 13
Regards,
Paul