CODEWITHSUNDEEP
sqloracle-database
gumpi
gumpi

Reputation: 281

Oracle concatenation issue

SELECT
  tbl1.PYU_EMAIL,
  COUNT(tbl1.PYU_EMAIL) as TOTAL,
  ( SELECT ROWCONCAT( 'SELECT pyu_id FROM p_survey_invite WHERE pyu_email=' || tbl1.pyu_email ) FROM dual )
FROM p_survey_invite tbl1
GROUP BY tbl1.pyu_id, tbl1.pyu_email
ORDER BY total DESC;

hi all,

I keep getting ORA-04054: database link DIGITALVIDEOSYSTEMS.NET does not exist error

I suspect that when I concat the tbl1.pyu_email within the rowconcat function, the tbl1.pyu_email has @ char on it, which reflect to DB-LINK on oracle.

How do I tell oracle not to think @ is db link?

Upvotes: 1

Views: 487

Answers (1)

John Doyle
John Doyle

Reputation: 7803

You should put quotes around your e-mail address:

SELECT
  tbl1.PYU_EMAIL,
  COUNT(tbl1.PYU_EMAIL) as TOTAL,
  ( SELECT ROWCONCAT( 'SELECT pyu_id FROM p_survey_invite WHERE pyu_email=''' || tbl1.pyu_email ||'''') FROM dual )
FROM p_survey_invite tbl1
GROUP BY tbl1.pyu_id, tbl1.pyu_email
ORDER BY total DESC;

You are passing a string to the ROWCONCAT() function which executes that string dynamically. Your code concatenates a varchar2 column to the DML string. Unless you include escaped quotes in the string what you pass (and the function tries to execute) is something like this:

SELECT pyu_id FROM p_survey_invite WHERE [email protected]

And that's why it fails.

Upvotes: 4

Related Questions