Puzzled by SUBSTR.... 2

[snowcold]

OK, here is my issue:
Using an Oracle 9.1 database with some views.
One field in this view is a BLOB(4000).
I have a reporting tool that cannot use blobs in a formula.

I figured that I would use SUBSTR to return only the first 256 characters for this field. When I see the final result, it does not appear that it's working as intended.

CREATE OR REPLACE VIEW myview( field1, field2, field3) AS SELECT field1, field2, 
[b]SUBSTR(table3.field3,1,256) as VALUE,[/b]
FROM field1, field2, field3
WHERE field1.num = field2.num
AND field2.data = field3.data

but when I see the schema it tells me that field3 is of type VarChar2(768) and the reporting tool is still complaining of a BLOB.


FYI:
I used this statement to hit the target,actually sets the field size to 255..but it'll work

[b]SUBSTR(table3.field3,1,85) as VALUE,[/b]

any knowledge on why this is occuring?

thanks

 

[LKBrwnDBA]

You need to use the DBMS_LOB package:

[b]DBMS_LOB.SUBSTR[/b](table3.field3,85,1) as VALUEx

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[snowcold]

This does not appear to have done anything.

Does it matter that the original is defined as varchar2(4000)?

I've got:

DBMS_LOB.SUBSTR(table3.field3,256,1) as VALUE

It's now displaying the column as size 4000?

thanks

 

[Beilstwh]

Are you sure that TABLE3.FIELD3 is a blob? Please do a desc of the view and paste it. In one entry you say it is a blob, in the other you say it is a varchars(4000), which one is correct?



Bill
Oracle DBA/Developer
New York State, USA

 

[LKBrwnDBA]

You stated "One field in this view is a BLOB(4000)", but BLOB type does not have a size component!

If original is defined as varchar2(4000), where does BLOB come in place?






----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[snowcold]

Ok, I'm sorry.
I was using the word BLOB to describe my large datafield, which is of type varchar2(4000), this is due to my lack of knowledge of an actual BLOB and because my reporting tool is telling me that my field, which is a varchar2(4000) is a BLOB, because it's larger than 256.

So, yes its a varchar2 and I'm terribly sorry about the confusion.

 

[Beilstwh]

aHHH, by any chance Crystal Reports? Anyway, don't use VALUE. It is a reserved word in oracle. also, your view makes no sense. What columns do you want your view to return. You create view command does not specify any COLUMNS, ONLY TABLES NAMES. Is field1 the table name or the field name. the FROM portion of the select must contain the table names, not the fields.



Bill
Oracle DBA/Developer
New York State, USA

 

[snowcold]

Yes it is CR.
I only typed the create statement half-heartily, not my actual view create statement.

 

[snowcold]

If it will help, I will post the view tomorrow morning

Thanks!!

 

[karluk]

I don't know what is happening, but I would certainly consider it a very strong piece of evidence that the size of your varchar2 column seems to always turn out to be exactly three times as large as the number of characters you select in your substr. That suggests you are running into an issue with the size of your database character set. If you are using a multibyte character set that needs three bytes of storage for every character, it seems plausible that your reporting tool might see the column as three times as long as you expect.

 

[SantaMufasa]

So, as an adjunct to KarlUK's excellent observation, could you please confirm your database's character set by posting the results of this query:

select * from nls_database_parameters
where parameter like 'NLS_CHAR%';

PARAMETER                      VALUE
------------------------------ ------------
NLS_CHARACTERSET               <some value here>

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[snowcold]

The result of the query

select * from nls_database_parameters
where parameter like 'NLS_CHAR%';

produces:

PARAMETER              VALUE
----------------       -----------
NLS_CHARACTERSET       UTF8

 

[SantaMufasa]

Aha! Karl's intuition is right on the money...UTF8 is Oracle's multibyte characterset that uses from 1 to 3 bytes per character, depending upon the character. So, there you have it!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[snowcold]

Well. I learn something new everyday...

Thanks to all for the help.