SQL Getting missing expression when using regexp_substr

Question

my proc is:

create or replace PROCEDURE SP_TBL_COMPARE_TEST3(
    SRC_COLS     IN VARCHAR2,
    TGT_COLS     IN VARCHAR2)
IS
  CNT1     NUMBER(5);
  SQL_TEXT2 VARCHAR(4000);

BEGIN
  SQL_TEXT2:= 'select count(*) from (select regexp_substr('||src_cols||','||'[^,]+'||', +1, level) from dual connect by regexp_substr('||src_cols||','||'[^,]+'||', +1, level) is not null )'; 
  EXECUTE IMMEDIATE SQL_TEXT2 into CNT4;
END

I am trying to get the input for the src cols and separating to count them but i am getting invalid identifier error. but the columns are available in the input and it is accurate

Connecting to the database sp_test. ORA-00936: missing expression ORA-06512: at "SP_TEST.SP_TBL_COMPARE_TEST3", line 19 ORA-06512: at line 13 Process exited. Disconnecting from the database sp_test

can anyone tell me what is wrong?

If you are counting list elements you can simplify: select regexp_count('1,2,3,4', ',')+1 from dual;. Of course make sure the data does not contain the delimiter. — Gary_W
– Gary_W, Commented Sep 30, 2016 at 13:28

Aleksej · Accepted Answer · 2016-09-30 12:53:55Z

1

You are missing some quotes in dynamic SQL; this should work:

create or replace PROCEDURE SP_TBL_COMPARE_TEST3(
    SRC_COLS     IN VARCHAR2,
    TGT_COLS     IN VARCHAR2)
IS
  CNT1     NUMBER(5);
  SQL_TEXT2 VARCHAR(4000);

BEGIN
  SQL_TEXT2:= 'select count(*) from (select regexp_substr('''||src_cols||''','||'''[^,]+'||''', +1, level) from dual connect by regexp_substr('''||src_cols||''','||'''[^,]+'||''', +1, level) is not null )'; 
  dbms_output.put_line(SQL_TEXT2);
  EXECUTE IMMEDIATE SQL_TEXT2 into CNT1;
END;

Besides, notice that you never use the second parameter of your procedure.

edited Sep 30, 2016 at 12:53

answered Sep 30, 2016 at 10:04

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Getting missing expression when using regexp_substr

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related