0

I have column that lists CLOBs.

Each CLOB is of this form :

file1.ext1
file2.ext2
file3.ext3

What I want to do is to convert all CLOBS combined to rows listed without the use of a PL/SQL program. In other words, is there a native function that will allow me to get the content of all CLOBs splitted into lines ?

devio
  • 1,147
  • 5
  • 15
  • 41
  • See 2nd answer here:http://stackoverflow.com/questions/11647041/reading-clob-line-by-line-with-pl-sql – Rene Nov 29 '16 at 12:03

1 Answers1

0

You can use the LISTAGG function in your select, something like:

SELECT LISTAGG(COL_a,',') 
  FROM MyTable
 WHERE ....

Note: I never checked how this would work with CLOBs, so...

FDavidov
  • 3,505
  • 6
  • 23
  • 59
  • `LISTAGG` requires `WITHIN`, and to use `WITHIN` I need to have a cell that will be transformed to a row. In my case, one cell (CLOB) contains many lines that should be converted to rows : 1 cell ==> many rows – devio Nov 29 '16 at 13:15
  • Ah, I misinterpreted your question. What you mean is to split each CLOB into rows? (for a single CLOB or all CLOBS combined?). If that is the case and within each CLOB there is an indication of where a line ends (say `|`), you can simply `SELECT (REPLACE(Col_a,'|',CHR(10))...` or something like this. – FDavidov Nov 29 '16 at 13:23
  • 1) a COLB contains many lines 2) I need all CLOBs combined to be splitted into rows 3) there is no indication where the line ends, there's just a back return (like in a text editor) – devio Nov 29 '16 at 13:52
  • Wait a minute... If you create a small anonymous block in SQL-Developer that would select one CLOB value into a variable, and then print the variable on screen, you are getting the text already split into lines? (you wrote _there's just a back return (like in a text editor)_) – FDavidov Nov 29 '16 at 13:54
  • If the answer is YES, I strongly suggest to test my original `SELECT` within SQL-Developer (NOT USING SELECT BUT PRINTING THE RESULT). – FDavidov Nov 29 '16 at 13:55
  • Yes, either printing or selecting... What I need is to list those rows. But how ? – devio Nov 29 '16 at 14:09
  • Could you please try the query I suggested in my answer? (you might need to use a `CHR(10)` instead of a `,` as the last parameter of `LISTAGG`). – FDavidov Nov 29 '16 at 14:14
  • As I told you before, `LISTAGG` requires `WITHIN` and I can't use it in my context. – devio Nov 29 '16 at 14:20
  • OK. Don't understand why, but... Anyway, no more ideas for the time being. – FDavidov Nov 29 '16 at 14:22