# oracle pl/sql concatenate columns & text for display **URL:** \ **Category:** Factual Questions\ **Created:** [November 12, 2009, 9:04am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299 "2009-11-12T09:04:48Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![TheGraemeGardenGoodie](https://avatars.discourse-cdn.com/v4/letter/t/e274bd/32.png) [@TheGraemeGardenGoodie](https://boards.straightdope.com/u/TheGraemeGardenGoodie)\ **Post date:** [November 12, 2009, 9:04am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/1 "2009-11-12T09:04:48Z")
I’m trying to concatenate two columns for display in an sql calendar (Application Express) _and_ insert text to describe what the two numbers are showing. The columns need to be concatenated because Apex can display a date column and one other column. This bit is cool (it shows my numbers from each column but no space between or description): select expensedate, sum(expamt) || sum(expappramt) from expenseitem group by expensedate; nothing I have tried to insert some text will work. I’m sure it’s my syntax but searching Oracle forums etc and my books on sql are not helping me here. The examples I have found aren’t trying to concatenate columns together they are just inserting text before a result like this: select expensedate, 'Exp ’ ||sum(expamt) from expenseitem group by expensedate; but continuing this path leads to failure: select expensedate, 'Exp ’ ||sum(expamt) || 'App ’ || sum(expappramt) from expenseitem group by expensedate; I’m sure there’s a simple syntax rule I’m breaking but I’m not that experienced with pl/sql. ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [November 12, 2009, 9:43am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/2 "2009-11-12T09:43:03Z")
If you are just trying yo include a space it’s just select expensedate, sum(expamt) || ’ ’ || sum(expappramt) ---
**Author:** ![Gryff](https://avatars.discourse-cdn.com/v4/letter/g/919ad9/32.png) [@Gryff](https://boards.straightdope.com/u/Gryff)\ **Post date:** [November 12, 2009, 10:13am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/3 "2009-11-12T10:13:30Z")
> [@TheGraemeGardenGoodie](#): > > …snip… > > select expensedate, 'Exp ’ ||sum(expamt) > from expenseitem > group by expensedate; > > but continuing this path leads to failure: > select expensedate, 'Exp ’ ||sum(expamt) || 'App ’ || sum(expappramt) > from expenseitem > group by expensedate; > > I’m sure there’s a simple syntax rule I’m breaking but I’m not that experienced with pl/sql. What error are you getting? I have just tried the same sort of query on my test database and it worked fine; **select businessdate, 'Exp ’ ||sum(SALESTOTAL ) || 'App ’ || sum(vattotal) from MENU\_ITEM\_DAILY\_TOTAL group by businessdate;** 05/10/2008 Exp 27240.04App 2696.37 ---
**Author:** ![TheGraemeGardenGoodie](https://avatars.discourse-cdn.com/v4/letter/t/e274bd/32.png) [@TheGraemeGardenGoodie](https://boards.straightdope.com/u/TheGraemeGardenGoodie)\ **Post date:** [November 12, 2009, 10:34am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/4 "2009-11-12T10:34:52Z")
> [@Gryff](#): > > What error are you getting? I have just tried the same sort of query on my test database and it worked fine; > > **select businessdate, 'Exp ’ ||sum(SALESTOTAL ) || 'App ’ || sum(vattotal) > from MENU\_ITEM\_DAILY\_TOTAL > group by businessdate;** > > 05/10/2008 Exp 27240.04App 2696.37 Hi Gryff, Thanks for replying. When I use a statement similar to yours : select expensedate, 'EXP '|| sum(expamt) || 'APP ’ || sum(expappramt) from expenseitem group by expensedate; The sql calendar display changes from something like e.g. 50.3050.30 (being the two amounts of 50.30 side by side) to a long string of #‘EXP’||sum(expamt)||‘APP’|| etc.etc.# i.e it’s just displaying the line of sql ---
**Author:** ![Small\_Clanger](https://avatars.discourse-cdn.com/v4/letter/s/9fc348/32.png) [@Small\_Clanger](https://boards.straightdope.com/u/Small_Clanger)\ **Post date:** [November 12, 2009, 10:42am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/5 "2009-11-12T10:42:29Z")
I have a working query pretty much the same as **Gryff** ’s (on Oracle either omit the char() function or change to to\_char()). select distribution, 'ABC ’ || char(sum(dlt1)) || 'XYZ ’ || char(sum(dlt2)) from distribution\_ids group by distribution; No PL/SQL there at all it’s straight SQL. I don’t have an Oracle database to hand so I’m not sure if the sum() result are automatically to\_char()-ed, it doesn’t hurt to explicitly to\_char the numbers and it gives you the option to format them, to a fixed length, whatever. On preview: **GraemeGG** can you post exactly what the query gives at a SQL prompt? ---
**Author:** ![Gryff](https://avatars.discourse-cdn.com/v4/letter/g/919ad9/32.png) [@Gryff](https://boards.straightdope.com/u/Gryff)\ **Post date:** [November 12, 2009, 10:54am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/6 "2009-11-12T10:54:16Z")
> [@TheGraemeGardenGoodie](#): > > Hi Gryff, > Thanks for replying. > When I use a statement similar to yours : > > select expensedate, 'EXP '|| sum(expamt) || 'APP ’ || sum(expappramt) > from expenseitem > group by expensedate; > > The sql calendar display changes from something like e.g. 50.3050.30 (being the two amounts of 50.30 side by side) to a long string of #‘EXP’||sum(expamt)||‘APP’|| etc.etc.# i.e it’s just displaying the line of sql Ahh, I completely skipped that you were using **Application Express** , which is a tool I have no experience with. The SQL you have is fine for plain old pl/sql. Could you try to place that entry into a variable then use it in Application Express? ---
**Author:** ![TheGraemeGardenGoodie](https://avatars.discourse-cdn.com/v4/letter/t/e274bd/32.png) [@TheGraemeGardenGoodie](https://boards.straightdope.com/u/TheGraemeGardenGoodie)\ **Post date:** [November 12, 2009, 10:59am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/7 "2009-11-12T10:59:16Z")
_**select expensedate, 'EXP '|| sum(expamt), 'APP ’ || sum(expappramt) from expenseitem group by expensedate;**_ Gives this table but the two columns are separate and so can’t be displayed in an sql calendar made in Application Express: Date ExpAmt ExpApprAmt 2007-08-10 00:00:00.0 EXP 10 APP 10 2007-08-09 00:00:00.0 EXP 635 APP 119 2007-09-23 00:00:00.0 EXP 73.89 APP 73.89 2007-08-13 00:00:00.0 EXP 344.64 APP 344.64 2007-08-16 00:00:00.0 EXP 225 APP 225 2007-08-15 00:00:00.0 EXP 213 APP 59.26 2007-08-14 00:00:00.0 EXP 108 APP 32.5 _**select expensedate, 'EXP '|| sum(expamt) || 'APP ’ || sum(expappramt) from expenseitem group by expensedate;**_ Gives me 2007-08-10 00:00:00.0 EXP 10, APP 10 2007-08-09 00:00:00.0 EXP 635, APP 119 2007-09-23 00:00:00.0 EXP 73.89, APP 73.89 2007-08-13 00:00:00.0 EXP 344.64, APP 344.64 2007-08-16 00:00:00.0 EXP 225, APP 225 2007-08-15 00:00:00.0 EXP 213, APP 59.26 2007-08-14 00:00:00.0 EXP 108, APP 32.5 This is actually correct output in Jdeveloper but when I put this as the sql in Application express this is the error \*\*\*ORA-06502: PL/SQL: numeric or value error: dbms\_sql.describe\_columns overflow, col\_name\_len=43. Use describe\_columns2 Return to application. \*\*\* and this is what appears in the calendar box e.g. 23 Nov _**#SUM(EXPAMT)||SUM(EXPAPPRAMT)#**_ So it looks like my column name is too long? I don’t really understand that message. ---
**Author:** ![Gryff](https://avatars.discourse-cdn.com/v4/letter/g/919ad9/32.png) [@Gryff](https://boards.straightdope.com/u/Gryff)\ **Post date:** [November 12, 2009, 11:05am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/8 "2009-11-12T11:05:50Z")
> [@TheGraemeGardenGoodie](#): > > This is actually correct output in Jdeveloper but when I put this as the sql in > Application express this is the error > \*\*\*ORA-06502: PL/SQL: numeric or value error: dbms\_sql.describe\_columns overflow, col\_name\_len=43. Use describe\_columns2 > > . try; select expensedate, 'EXP '|| sum(expamt) || 'APP ’ || sum(expappramt) as Col2 from expenseitem group by expensedate; and tell me if you get the same error? ---
**Author:** ![TheGraemeGardenGoodie](https://avatars.discourse-cdn.com/v4/letter/t/e274bd/32.png) [@TheGraemeGardenGoodie](https://boards.straightdope.com/u/TheGraemeGardenGoodie)\ **Post date:** [November 12, 2009, 11:14am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/9 "2009-11-12T11:14:09Z")
> [@Gryff](#): > > try; > > select expensedate, 'EXP '|| sum(expamt) || 'APP ’ || sum(expappramt) as Col2 > from expenseitem > group by expensedate; > > and tell me if you get the same error? That did it Gryff! So you just gave that column an alias - is that what happened? ---
**Author:** ![Gryff](https://avatars.discourse-cdn.com/v4/letter/g/919ad9/32.png) [@Gryff](https://boards.straightdope.com/u/Gryff)\ **Post date:** [November 12, 2009, 11:18am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/10 "2009-11-12T11:18:12Z")
> [@TheGraemeGardenGoodie](#): > > That did it Gryff! So you just gave that column an alias - is that what happened? Yeah, the error looked like the column name was the problem and as we hadn’t given it an explicit name it was trying to give it a name based on the calculation which was more than the length allocation to it (43). You should be able to name that column something more descriptive now, just don’t make it too long. ---
**Author:** ![TheGraemeGardenGoodie](https://avatars.discourse-cdn.com/v4/letter/t/e274bd/32.png) [@TheGraemeGardenGoodie](https://boards.straightdope.com/u/TheGraemeGardenGoodie)\ **Post date:** [November 12, 2009, 11:24am UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/11 "2009-11-12T11:24:27Z")
Thanks for that, sometimes it’s hard to see what’s happening (well it is for me:) ) ---
**Author:** ![Small\_Clanger](https://avatars.discourse-cdn.com/v4/letter/s/9fc348/32.png) [@Small\_Clanger](https://boards.straightdope.com/u/Small_Clanger)\ **Post date:** [November 12, 2009, 12:03pm UTC](https://boards.straightdope.com/t/oracle-pl-sql-concatenate-columns-text-for-display/517299/12 "2009-11-12T12:03:56Z")
[QUOTE=TheGraemeGardenGoodie] Thanks for that, sometimes it’s hard to see what’s happening… [/QUOTE] In this case the tool you’re using seems to be doing some fancy stuff and hiding it from you. Let’s see what went wrong: **ORA-06502: PL/SQL: numeric or value error:** Good ol’ 6502. Generic data error, in this case a varchar truncation. **dbms\_sql.describe\_columns** dbms\_sql is a database package for creating dynamic SQL, dunno why this is needed for a simple query but hey… Apparently the describe columns function isn’t smart enough to substr it’s output, hence the error. **Use describe\_columns2** The database is suggesting you use a different function but since it’s the tool that’s chosen it it’s quite likely that you can’t influence this. Probably more than you need or wanted to know 🙂