CODEWITHSUNDEEP
sqloracle-databaseplsql
user14937341
user14937341

Reputation:

ORA-00904: "\d+$": invalid identifier

While executing the below query in PL/SQL, I am getting ORA-00904: "\d+$": invalid identifier error.

EXECUTE IMMEDIATE " select regexp_substr(table_name, "\d+$") result from user_tables where regexp_like(table_name, "\d+$") " into splitstring;

I tried using single quotes also, but still I am facing the same error.

EXECUTE IMMEDIATE ' select regexp_substr(table_name, "\d+$") result from user_tables where regexp_like(table_name, "\d+$") ' into splitstring;

Can anyone please help me with this issue

Upvotes: 0

Views: 580

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Single quotes are used for string literals; double quotes are used for identifiers.

Start with a static SQL statement that works:

select regexp_substr(table_name, '\d+$') result
from user_tables
where regexp_like(table_name, '\d+$')

Then to make it dynamic you have to make the whole statement a string; you can either escape the existing quotes by doubling them up - note that is two single-quote characters, not one double-quote character:

EXECUTE IMMEDIATE 'select regexp_substr(table_name, ''\d+$'') result
  from user_tables
  where regexp_like(table_name, ''\d+$'')'
into splitstring;

or use the alternative quoting mechanism:

EXECUTE IMMEDIATE q'^select regexp_substr(table_name, '\d+$') result
  from user_tables
  where regexp_like(table_name, '\d+$')^'
into splitstring;

You don't need dynamic SQL here though, you can just do:

select regexp_substr(table_name, '\d+$') result
into splitstring
from user_tables
where regexp_like(table_name, '\d+$');

db<>fiddle - with a single matching table; if there are none, or more than one, then your query into a scalar variable will fail. Depending on what you're doing with the numeric part of the table name(s), you might want to use a cursor and loop over all the extracted values. That's a separate issue though.

Upvotes: 2

Related Questions