CLOB - Oracle 9i

building semantic layer
1

Hi!

Has someone tried to create an object of a CLOB field in Oracle 9i, using BO 6.1b?

I went through the various articles posted in the forum for CLOB. I tried to create a ‘Long Text’ object. I didn’t use the substr function. My select clause for the object has only the schema.tablename.fieldname
It parses ok. But when I try to bring that object in the report, it displays only first 99 characters (including spaces). The length of the CLOB at the database side is set to 4000.

Any idea on why is it pulling only 99 characters rather than the entire field content?

Thanks,

Shobha

SB8 (BOB member since 2005-05-02)

2

I’ve never worked with CLOB nor long text objects – but have you checked your universe parameters to make sure the limit isn’t happening there?

From Designer ==>File ==> Parameters ==> go to the Controls tab. Look at “Limit size of long text objects to:” – is that just 99 characters?

Be aware, too, that Supervisor can override Designer settings such as this, and you should double-check with Supervisor to see if there are overrides in effect. Right-click the universe ==> Properties ==> go to the Controls tab.

Anita Craig :us: (BOB member since 2002-06-17)

3

Thanks Anita!

But I checked at both places. The ‘Limit size of long text objects to’ check box is not selected at either places.

Any other suggestions?

Thanks,

Shobha

SB8 (BOB member since 2005-05-02)

4

Didn’t your search on BOB lead you to the dbms_lob function? Check https://bobj-board.org/t/3170.

jobjoris :netherlands: (BOB member since 2004-06-30)

5

Indeed; I have implemented it in a universe as follows:

dbms_lob.SUBSTR(TABLE.CLOB_COLUMN,4000,1)

regards
Oscar

ohuibers :netherlands: (BOB member since 2004-03-05)

6

when I use dbms_lob.SUBSTR(DWPETWARE.MRH.MRHNT,4000,1), I get the following parsing error:

‘The expression type is not compatible with the object type’

Do I have to set any options before I use dbms_lob.substr?

If I run the following statement in SQL Navigator it runs perfectly fine.
SELECT
DBMS_LOB.SUBSTR(DWPETWARE.MRH.MRHNT,4000,1)
FROM dwpetware.mrh

Thanks for the help!

Shobha

SB8 (BOB member since 2005-05-02)

7

Just change the type to Char or some other value until it parses…it may be that there isn’t a valid type to make it parse…but don’t worry, it should work within a report.

Nick Daniels :uk: (BOB member since 2002-08-15)

8

Thanks Nick! That works! Appreciate it!

Shobha

SB8 (BOB member since 2005-05-02)