On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,529 Users
  • 2,269,755 Discussions
  • 7,916,772 Comments

Discussions

convert clob to long varchar

User_AC3QE
User_AC3QE Member Posts: 34 Red Ribbon
edited Jun 15, 2020 5:06PM in SQL & PL/SQL

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

Answers

  • Tubby
    Tubby Oracle Consultant Member Posts: 6,990 Bronze Crown
    edited Jun 15, 2020 11:03AM
    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
    User_AC3QE Member Posts: 34 Red Ribbon
    edited Jun 15, 2020 11:55AM

    Thanks,

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

  • User_AC3QE
    User_AC3QE Member Posts: 34 Red Ribbon
    edited Jun 15, 2020 4:40PM

    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
    Tubby Oracle Consultant Member Posts: 6,990 Bronze Crown
    edited Jun 15, 2020 4:54PM
    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
    User_AC3QE Member Posts: 34 Red Ribbon
    edited Jun 15, 2020 5:03PM

    concatenating displays the error below:

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

    need to display in a column only

  • Tubby
    Tubby Oracle Consultant Member Posts: 6,990 Bronze Crown
    edited Jun 15, 2020 5:06PM
    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,