Skip to Main Content
  • Questions
  • Execute immediate and passing clob as a parameter is not working.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Paul.

Asked: September 07, 2017 - 10:19 am UTC

Last updated: October 13, 2017 - 3:59 am UTC

Version: PL/SQL Release 11.2.0.4.0

Viewed 1000+ times

You Asked

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 CLOB

Please 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

and Connor said...

Request for more info:

1) Remove EXCEPTION WHEN OTHERS so we get a true error <<<<===================
2) Show us *exactly* how you are running it
3) Show us the *entire* error stack when you run this from SQL Plus <<<<===================

=============

Addenda:

Well, you didnt get

ORA-00932: inconsistent datatypes: expected - got CLOB

you got

ORA-00933: SQL command not properly ended

Your code was this:

EXECUTE IMMEDIATE 'delete from ecmreport.ecm_mpulktm1 where project_id='|| v_project_id||' and requestname='|| v_requestname;

and when you passed in your parameters, you ended up with:

delete from ecmreport.ecm_mpulktm1 where project_id=123ABC and requestname=Req_name

which is not valid SQL (unless you have some columns called "123ABC" and "REQ_NAME")

So why the dynamic SQL ? Just do:

delete from ecmreport.ecm_mpulktm1 where project_id=v_project_id and requestname=v_requestname;




Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

Paul Pranabesh, October 13, 2017 - 4:13 am UTC

Thank you for your answer. Well I have managed to resolve the issue with the clob by writing a function and also I have eliminated the redexp_substr. Here goes my code.

Function :

create or replace type my_row_value as table of varchar2(4000)
/
create or replace
function extract_clob_as_rows
(v_seqno in varchar2)
return my_row_value pipelined
as
lValue clob;
lStr varchar2(4000);
lSpos integer;
lEpos integer;
i integer;
begin
select tmcode||',' into lValue
from bscs_work_sync_info
where seq_no = v_seqno;

i := 0;
loop
i := i + 1;
lSpos := nvl(dbms_lob.instr(lValue, ',', 1, i-1), 0);
lEpos := dbms_lob.instr(lValue, ',', 1, i);
lStr := dbms_lob.substr(lValue, lEpos - lSpos -1, lSpos+1);
exit when lStr is null;
pipe row (lStr);
end loop;
return;
end;
/

Below is the procedure

create or replace
PROCEDURE bscs_rateplan_sync (
project_id IN VARCHAR2,
requestname IN VARCHAR2,
seq_no IN varchar2,
v_errm OUT VARCHAR2
)
AS
v_project_id varchar2(4000) := project_id;
v_requestname varchar2(32) := requestname;
v_seq_no varchar2(100) := seq_no;


CURSOR tm_del is
select tmcode from rateplan where shdes in(select * from table(extract_clob_as_rows(v_seq_no)));

tm tm_del%rowtype;
BEGIN

if v_requestname <> 'nonPublished'
then


for tm in tm_del
loop

delete from ecm_rep_mpulktm1 where project_id=v_project_id and tmcode=tm.tmcode;
delete from ecm_rep_mpulktm2 where project_id=v_project_id and tmcode=tm.tmcode;
delete from ecm_rep_fup_tariff_work where project_id=v_project_id and tmcode=tm.tmcode;

INSERT INTO ecm_rep_mpulktm1 SELECT v_project_id, v_seq_no, m1.* from sysadm.mpulktm1 m1 WHERE m1.tmcode=tm.tmcode;
INSERT INTO ecm_rep_mpulktm2 SELECT v_project_id, v_seq_no, m2.* from sysadm.mpulktm2 m2 WHERE m2.tmcode=tm.tmcode;
INSERT INTO ecm_rep_fup_tariff_work SELECT v_project_id, v_seq_no, m3.* from sysadm.fup_tariff_work m3 WHERE m3.tmcode=tm.tmcode;
END LOOP;



COMMIT;


UPDATE bscs_work_sync_info
SET comp_date = SYSDATE,
status = 'SUC'
WHERE project_id = v_project_id AND requestname = v_requestname and seq_no=v_seq_no;


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 and seq_no=v_seq_no;

COMMIT;
END;

Thank you

Pranabesh Paul, July 13, 2018 - 4:53 am UTC

Thank you for answering

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database