Best way to handle clob fields?

Stefan Hall shared this question 15 months ago
Answered

Hi,

our Oracle data source contains some CLOB fields.

Showing data in View Model Editor it is not a problem, just the first characters are displayed.


But under "Prepare" I get an ORA error message and no data is displayed.


But in the reports I can use all fields from the view and I don't get this error message.


Do you have any idea what is going wrong here or what is the best way to handle CLOB fields?


;) Stefan

/8b86348f3085b5773c45f496c545f82f

Replies (10)

photo
1

Hi Stefan,

Thanks for reaching out to Yellowfin Support.

This issue mostly occurs due to whenever You tried to perform an operation between two different datatypes, but the datatypes are not compatible(it means inconsistent) it shows the error as mentioned below.

Option 1 :

To correct this error, you can do one of the following:

  • Not use the LIKE condition in your SQL (against the LONG datatype field).
  • Consider modifying your table so that the supplier_name field is either a VARCHAR2 or CHAR field.
  • Try writing a custom PLSQL function to convert a LONG to a VARCHAR2.

Option 2 :

To correct this error, you can do one of the following:


Regards,

Sri Vamsi

photo
1

Hi Stefan,

Hope things are good on your end. Just wanted to check in to make sure you had what you needed here.

Regards,

Sri Vamsi

photo
1

Hi Stefan,

I just want to touch base to see if you had chance to read through my response. If you can let me know that would be great.

Regards,

Sri Vamsi

photo
1

Hi Sri,

of course I have read your answer. BUT

- I do not use "like" in the view

- changing the tables is not an option (3rd party software)

- for own PLSQL functions in the DB we lack the rights


I have to look for a way in YF.

What I don't understand, in reports I can use the columns without error, in view preview it comes to error message. Where is the difference?

photo
photo
1

Hi Stefan,

Thanks for your reply.

Unfortunately, there aren't any options in yellowfin. However, I've logged this Enhancement request on your behalf with the Product Team for further review.. Such requests will be reviewed by our Product Team and implemented based on feasibility, level of positive impact on the user experience, and votes from the community.

This post will be updated with any future information relevant to this process. Can I mark this ticket as completed.


Regards,

Sri Vamsi

photo
1

Thanks Sri,

even though I don't believe YF will do anything here ;) I don't see many votes for ideas on your website.


Thanks anyway for your support, it was nice.

photo
photo
1

Hi,

Sorry, but I have to ask again.

If I have a CLOB field in a view, then the content is shown to me in the data preview, the column type specified as varchar. Actually looks good (= varchar).

If I use this "Varchar (clob)" column in the report - without change, just display, I get the error message above from my question. Why actually and why then not in the view preview?
---
If I use a DB procedure in the view to change Clob to Varchar, I get the same error message already in the view and the preview shows no more data. Nevertheless the column works in the report.
Does this fit together for you?

;) Stefan

photo
1

Hi Stefan,

Sorry for the delay in response.

Could you please share the error messages/logs you are getting while using "Varchar (clob)" column in the report.

Regards,

Sri Vamsi

photo
1

Hi Vamsi,

the error message is the same and I don't understand why YF can handle it at view level - autom. display from VarChar and then displaying that column in the report suddenly doesn't work.

In the screenshot you can see the display of a clob field on view level. Looks good to me, if it was indeed a varchar.

cae537314210c5f40be90e35164ed7f3

photo
1

Hi Vanmsi,
the status has changed to "awaiting reply", but I don't see a question from you.

;) Stefan

photo
1

Hi Stefan,

Can you please share the logs that you are currently facing. We are unable to find the issue without logs.

I have changed status to "awaiting reply" because I requested for logs and I'm still waiting.

Regards,

Sri Vamsi

photo
1

Hi Vamsi,

is this thread here in a protected area? Then I can attach a log here. Otherwise I need another storage location.

;) Stefan

photo
photo
1

Hi Stefan,

Can you please upload the entire log files in https://ftp.yellowfin.bi/files/ and specify the file name , this site is protected.

Regards,

Sri Vamsi

photo
1

Hi Stefan,


The issue you are facing with CLOB fields in your Oracle data source is likely due to the large size of the data in those fields. In Oracle, a CLOB data type can store up to 4 GB of data, which is much larger than the maximum size of other data types like VARCHAR2.

When you are trying to view the data in the "Prepare" section of Yellowfin, it may be encountering issues with processing and displaying the large CLOB data.

One solution to this issue is to modify the SQL query used to retrieve the data from Oracle to only select the first few characters of the CLOB field, instead of the entire field. This will reduce the size of the data returned and may prevent the error you are encountering in the "Prepare" section of Yellowfin.

Another option is to create a view in Oracle that selects only the necessary columns and truncates the CLOB field to a smaller size. This view can then be used as the data source in Yellowfin.

Finally, if you are able to access and modify the database schema, you may consider changing the data type of the CLOB fields to a more appropriate data type if possible, such as VARCHAR2 or BLOB, depending on the data stored in the fields.

It's important to note that if you are able to view the data in reports, it means that the data is being retrieved and processed correctly, and the issue may only be related to the size of the data in the "Prepare" section of Yellowfin.

Regards,

Sri Vamsi

photo
1

Hi Stefan,

I just want to touch base to see if you had chance to read through my response. If you can let me know that would be great.

Regards,

Sri Vamsi

photo
1

Hi Stefan,

I'm going to go ahead and mark this ticket as Completed due to inactivity at this time. Feel welcome to reach out in the future.

Regards,

Sri Vamsi

Leave a Comment
 
Attach a file