0

How can i use this select query in my code to get special characters for each columns dynamically ? this select query works fine in sql but in this code how can i pass this select query by using dynamic sql?

DECLARE
  r_emp   SYS.ODCINUMBERLIST   := SYS.ODCINUMBERLIST();
  v_array SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
     'cust_name',
     'BILL_DELIVER_METHOD'
  );
BEGIN
  DBMS_OUTPUT.ENABLE;
  FOR i IN 1..v_array.COUNT LOOP
    r_emp.EXTEND;
    EXECUTE IMMEDIATE
      'SELECT COUNT(1) FROM customer_profile WHERE NOT REGEXP_LIKE('||v_array(i)||','[A-Za-z0-9.]')' 
         INTO r_emp(i);
    DBMS_OUTPUT.PUT_LINE(v_array(i) || ': ' || r_emp(i));
  END LOOP;
END;
/

Error report -

ORA-06550: line 12, column 86:
PLS-00103: Encountered the symbol "[" when expecting one of the following:
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Aman
  • 19
  • 3

1 Answers1

0

I see big problem with the string

'select count(1) from customer_profile where not regexp_like('||v_array(i)||','[A-Za-z0-9.]')'

It looks like that you want to use regexp_like(something,'[A-Za-z0-9.]') dynamically, but the ' breaks the string. Use escape for it: '' inside the string translates to '

'Anna''s diary' ==> Anna's diary

Try

'select count(1) from customer_profile where not regexp_like('||v_array(i)||',''[A-Za-z0-9.]'')'
Evgeny Petrov
  • 1