I am concatenating all columns with applying NVL() function to each column.

I am using python to connect to oracle db so I am able to get the column names easily and then applying some join with the list get the desired select query like :

select NVL(col1,'?')||NVL(col2,'?')...

It was working fine until the aggregated value crossed 4k characters.

I tried to search and found about listagg but the problem with that is it is being used vertically and I want to concatenate rowwise.

This concatenated value I am using to generate an MD5.

CodePudding user response：

Concatenate them as a CLOB. To do that, either start with EMPTY_CLOB():

SELECT EMPTY_CLOB()||NVL(col1,'?')||NVL(col2,'?')...

or use TO_CLOB on everything:

SELECT TO_CLOB(NVL(col1,'?'))||TO_CLOB(NVL(col2,'?'))...

or just TO_CLOB on the first column:

SELECT TO_CLOB(NVL(col1,'?'))||NVL(col2,'?')...

db<>fiddle here

CodePudding user response：

You're selecting that long string into something, right? A variable? At Oracle's SQL level, it is limited to VARCHAR2(4000), but in PL/SQL it becomes 32K so - one option is to switch to PL/SQL.

Otherwise, use CLOB datatype instead of VARCHAR2.

Unrelated to what you're doing, but - NVL function is to be used to get some value in case of COL1 is NULL. What you're doing is useless, as you're "converting" possible NULL value in COL1 into an empty string (two consecutive single quotes, '') which is - in Oracle - equivalent to NULL so you could've just

select col1 || col2 || ...

It would make sense if it were e.g.

select nvl(col1, 'x') || nvl(col2, 'x') || ...