APEX 5: Reading table into textarea

Question

I have a table which looks something like this.

 `referenceID, IP1, IP2, IP3, subnetmask`

I now need to get the IPs with specific referenceIDs into a Textarea or Displayonly field, adding some plain text before and after

For example:

*/ 
*/this goes infront of the ips  
<ip1><ip2><ip3><subnet> 
<2ip1><2ip2><2ip3><2subnet>  
*/this text comes after the ips`

The problem is, I can't figure out a way, to select all the strings and ips into the textarea (or display only).

Methods I tried so far, just cause errors like "wrong number of columns" or PL/SQL asking me pick a location to select these INTO somewhere.

Would appreciate any solution or help.

score 0 · Answer 1 · edited May 23 '17 at 12:00

You could try like this (based on this answer):

SELECT 'this goes infront of the ips' || 
       replace(SYS_CONNECT_BY_PATH (x_text, '~'), '~', CHR(10)) ||
       CHR(10) || 'this text comes after the ips' AS textarea
  FROM (SELECT IP1 || ',' || IP2 || ',' || IP3 || ',' || subnetmask AS x_text,
               ROW_NUMBER () OVER (ORDER BY referenceID) AS x_rownumber,
               COUNT (*) OVER () AS x_count
          FROM yourtable
          WHERE referenceID > 0)
WHERE x_rownumber = x_count
START WITH x_rownumber = 1
CONNECT BY x_rownumber = PRIOR x_rownumber + 1

If you're using Oracle 11.2 or higher, you should use the following (based on another answer):

SELECT 'this goes infront of the ips' || CHR(10) ||
       LISTAGG(IP1 || ',' || IP2 || ',' || IP3 || ',' || subnetmask, CHR(10)) 
       WITHIN GROUP (ORDER BY referenceID) ||
       CHR(10) || 'this text comes after the ips' AS textarea
FROM yourtable
WHERE referenceID > 0

Note: referenceID > 0 is just an example clause for restricting your query to specific referenceIDs, and yourtable should be replaced with the name of the table.

APEX 5: Reading table into textarea

1 Answers1