convert clob to long varchar

User_AC3QE

Hi

I changed a field that was varchar to Clob, but, I will have to change the Clob field to type Long with 4000 more for report export,

how to make?

SELECT       rtrim(xmlcast(xmlagg(xmlelement(e,'column > 4000','  ')) as clob),',')  dispositivo from dual

Tubby

3746359 wrote:HiI changed a field that was varchar to Clob, but, I will have to change the Clob field to type Long with 4000 more for report export,how to make?SELECT rtrim(xmlcast(xmlagg(xmlelement(e,'column > 4000',' ')) as clob),',') dispositivo from dual 

If the value exceeds 4000 characters what's the value of returning an incomplete set of data to the client? I would recommend revisiting the requirements for displaying this on a report.

That aside, dbms_lob.substr should accomplish what you've asked for.

https://docs.oracle.com/database/121/ARPLS/d_lob.htm#ARPLS66748

Cheers,

User_AC3QE

Thanks,

when it exceeds 4000 characters can i use dbms_lob how? to bring all the complete information

User_AC3QE

select

      DBMS_LOB.SUBSTR(column,4000,1) part1,

      DBMS_LOB.SUBSTR(column,4000,4001) part2 from dual

What is the way to join a column only?

Tubby

3746359 wrote:select DBMS_LOB.SUBSTR(column,4000,1) part1, DBMS_LOB.SUBSTR(column,4000,4001) part2 from dualWhat is the way to join a column only?

Do you mean concatenate?

select 'hello' || 'world' from dual

If not please elaborate on what you're after.

Cheers,

User_AC3QE

concatenating displays the error below:

01489. 00000 -  "result of string concatenation is too long"

need to display in a column only

Tubby

3746359 wrote:concatenating displays the error below:01489. 00000 - "result of string concatenation is too long"

Sounds about right, could it be time to put your thinking cap on?

I mentioned originally haphazardly displaying a portion of the clob seemed like a bad idea, that statement didn't seem to get any traction.

Now you're in a position where you have technical issues to deal with, so you'll have to figure out what makes the most sense for your situation. I will still recommend that you revisit the business value of what you are doing though, if you need all the data you probably need to work with whatever is presenting the data so that it can handle a CLOB value.

Cheers,