html column handling in oracle

Question

I have a requirement to send email from oracle code.

Email id , subject and body of the email is formatted in the oracle query. The body of the email is a simple hyperlink as follows.

https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID=011

caseId is dynamic and the where clause in the query accepts dynamic case id's

My query looks like,

select
'[email protected]' email,
'Welcome back !' subject,
'<a href="https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID='||c.case_id||'">https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID='||c.case_id||'</a>' body
from cases c where c.case_id = 011;

Output is:

<a href="https://www.example.net/ind/case/viewIndCase.action?submitType=0011="011>"https://www.example.net/ind/case/viewIndCase.action?submitType=0011="011</a>

Expected Output is:

<a href="https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID=011">https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID=011</a>

Can someone help find out why the caseId is replaced with number (011="011) instead of caseID=011 I am messing with quotes I guess but unable to find out.

Some tools will treat &caseID as a substitution variable named caseID (perhaps that's your caseId is dynamic and the where clause in the query accepts dynamic case ids) so if that's the case it would be helpful to include the value provided. — William Robertson
– William Robertson, Commented Jun 18, 2020 at 21:21

Geek · Accepted Answer · 2020-06-18 19:56:37Z

1

Output you claim to be is ... wrong. Where did the first double quotes go?

SQL> with cases (case_id) as
  2    (select '011' from dual)
  3  select
  4  '<a href="https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID='||c.case_id||'">https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID='||c.case_id||'</a>' body
  5  from cases c where c.case_id = '011';

BODY
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
<a href="https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID=011">https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID=011</a>

SQL>

Your "expected output" (above) vs. my output (below): there's no difference, so - why do you think that there's something wrong?

<a href="https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID=011">https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID=011</a>
<a href="https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID=011">https://www.example.net/ind/case/viewIndCase.action?submitType=0&caseID=011</a>

This is what I see after executing your sql: BTW i am using sqlDeveloper

<a href="https://www.example.net/ind/case/viewIndCase.action?submitType=0011=011">https://www.example.net/ind/case/viewIndCase.action?submitType=0011=011</a>

edited Jun 18, 2020 at 19:56

Geek

3,32716 gold badges82 silver badges123 bronze badges

answered Jun 18, 2020 at 19:17

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

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

9 Comments

Geek Over a year ago

I am still getting the same

Littlefoot Over a year ago

The same ... what? I'm not sure I understand what you want, because your "expected output" seems to be wrong, while mine seems to be OK. At least, I don't see what's wrong with it.

Geek Over a year ago

I have made couple edits. I had replaced with your query but still see 0011=011"

Littlefoot Over a year ago

I edited my answer as well, adding comparison between your expected output and my result. No difference.

Geek Over a year ago

OK this is what helps : '||'&'||' - Thank you so much

|

Collectives™ on Stack Overflow

html column handling in oracle

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related