Issue in creating a Function returning a SQL Query

Question

I am currently trying to create a Function in oracle Apex to return SQL Query, and there seems to be some errors in my code. Can someone look over it and see what's wrong.

  RETURN
'SELECT 1, LAN_ID label,'||
'   '#'              target,'||
'    NULL          is_current_list_entry,'||
'   'http://orig03.deviantart.net/efa6/f/2015/200/5/6/anon_icon_thinger_by_arofexdracona-d920vda.png'    image,'|| 
'    NULL             image_attribute,'||
'    NULL             image_alt_attribute,'||
'    'left'           attribute1,'||
'    'fa-clock-o'     attribute2,'||
'    DATE_POSTED      attribute3,'||
'    RESPONSE         attribute4'||
'from CHAT_RESPONSE'||
'where LAN_ID IS NOT NULL'||
'order by DATE_POSTED DESC;'||

I think if you look at the formatting, it should tell you the problems. You are not escaping the single quotes. — roymustang86
– roymustang86, Commented Aug 20, 2015 at 17:43
I am still new to this, do you know how I can fix it so it still uses the value without a single quote? — Digi
– Digi, Commented Aug 20, 2015 at 17:46
Single quotes are escaped with an extra single quote. Check out the answer provided for reference. — roymustang86
– roymustang86, Commented Aug 20, 2015 at 19:35

Trinimon · Accepted Answer · 2015-08-20 17:57:22Z

2

Quote the ' in the right way and replace the trailing || by ;:

RETURN
'SELECT 1, LAN_ID label,'||
'  ''#''              target,'||
'    NULL          is_current_list_entry,'||
'  ''http://orig03.deviantart.net/efa6/f/2015/200/5/6/anon_icon_thinger_by_arofexdracona-d920vda.png''    image,' || 
'    NULL             image_attribute,'||
'    NULL             image_alt_attribute,'||
'  ''left''           attribute1,'||
'  ''fa-clock-o''     attribute2,'||
'    DATE_POSTED      attribute3,'||
'    RESPONSE         attribute4'||
'  from CHAT_RESPONSE'||
' where LAN_ID IS NOT NULL'||
' order by DATE_POSTED DESC;';

... and add some spaces before from and where.

edited Aug 20, 2015 at 17:57

answered Aug 20, 2015 at 17:50

Trinimon

14k9 gold badges46 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

Issue in creating a Function returning a SQL Query

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