0

I have a type defined in ORACLE. Lets name it as HEADER.

create or replace TYPE "HEADER_OT"  as object ("FIELD1" VARCHAR2(25));

I am now using this object in a PL/SQL block and assigning a value to this FIELD1 as below.

HEADER_OT(some input value of length 24)

This some input value of length 24 has spaces and - in it. When i execute my PL/SQL block i get below error

Error Occured:ORA-06502: PL/SQL: numeric or value error: character string buffer too small

If I set it to null it works fine.If i set to value 123456789012345678901234 it works fine again. But if I add two more digits to the above it fails.

I used length and lengthb functions to find the size of my original input, but both give a value of 24.

Program Manager – Wfd FO is my original value

MT0
  • 143,790
  • 11
  • 59
  • 117
Sachin Kumar
  • 808
  • 3
  • 11
  • 29
  • Please re-count the number of characters. You have 29 characters, not 24. – FDavidov Mar 14 '17 at 07:05
  • I used SUBSTR to see what size it accepts. It is working fine till 23 and throwing the same error if i specify 24. – Sachin Kumar Mar 14 '17 at 07:05
  • @FDavidov: To ensure that I used length and lengthb functions in oracle which gave me 24 and not 29. I am assuming that you are talking about **Program Manager – Wfd FO** – Sachin Kumar Mar 14 '17 at 07:07
  • Are you using English characters only? If yes, VARCHAR2(n) allows you to store up to `n` characters exactly (of course, you can store less too). – FDavidov Mar 14 '17 at 07:07
  • Yeah, I did some search on that. I don't find any other characters in the input that i specified. Does **-** take more space than a single character ? – Sachin Kumar Mar 14 '17 at 07:08
  • As long as you use ASCII chars (which include `-`), one char = one space. Try something like `HEADER_TO(SUBSTR('This is a string much longer than you can store in 24 places',1,24));`. – FDavidov Mar 14 '17 at 07:11
  • 1
    try `select dump(yourString) from dual` and let's see the result – Aleksej Mar 14 '17 at 07:11
  • What is your purpose? An object type with only one single attribute seems to be quite useless. – Wernfried Domscheit Mar 14 '17 at 08:00
  • The string `123456789012345678901234` is 24 characters. Are you asking why adding two more characters prevents it being assigned to a 25-character variable? – William Robertson Mar 14 '17 at 08:31
  • 2
    Are you using that hypen-minus character you provided in the question (–), or regular hypen-minus (-)? It makes a lot of difference - first one is three byte character, and the other one is only one byte character. You said that you checked lengthb function and it returned 24 - are you absolutely sure you used the original string? For me, lengthb returns 26 characters using string you provided ('Program Manager – Wfd FO'). Edit: use dump, as suggested by Aleksej - for character '–' it will return three bytes. – Goran Stefanović Mar 14 '17 at 08:33
  • I did not copy the string separately. I used select length(column) from table format so that any modifications with the String are taken care of. – Sachin Kumar Mar 15 '17 at 03:02
  • This is the result of dump `Typ=1 Len=24: 80,114,111,103,114,97,109,32,77,97,110,97,103,101,114,32,150,32,87,102,100,32,70,79` – Sachin Kumar Mar 15 '17 at 03:03
  • @WernfriedDomscheit : I specified only one type for simplicity as I know that the problem is with that one particular field in the type. – Sachin Kumar Mar 15 '17 at 03:06
  • Please try `SELECT DUMP('Program Manager – Wfd FO', 1016) FROM dual;` Do you get correct values for `HEADER_OT('Manager – Wfd')`? Or do you get `Manager ¿ Wfd`? – Wernfried Domscheit Mar 15 '17 at 07:47
  • Does it work with `create or replace TYPE "HEADER_OT" as object ("FIELD1" VARCHAR2(25 CHAR));`? – Wernfried Domscheit Mar 15 '17 at 07:59
  • There seem to be two different versions of the string. The one in your question ("**Program Manager – Wfd FO** is my original value") contains n-dash i.e. `unistr('\2013')` which is three characters (hex `e2 80 93` or decimal 226, 128, 147), but your `dump` output shows a decimal 150 (hex 96), while what you actually want is decimal 45 (hex 2d). From [this question](http://stackoverflow.com/questions/3005515/does-the-character-96-hex-have-any-special-meaning-in-internet-protocols-formats) it seems Windows-1252 uses 150 to represent Unicode 2013 so that could just be what's displayed on screen. – William Robertson Mar 15 '17 at 08:28
  • 1
    @WilliamRobertson, that is not very precise. En Dash (U+2013) is **one character**. However, when you encode this character in UTF-8 then it uses **three bytes**. Other encoding use only one byte (e.g. Windows 1252), or two bytes (e.g. UTF-16). – Wernfried Domscheit Mar 15 '17 at 13:53
  • @WernfriedDomscheit you are quite right, and in a discussion about bytes versus characters I should take care to say what I mean :) – William Robertson Mar 15 '17 at 14:59
  • @WilliamRobertson: I should read more about encoding to understand what you have said, but i get the same error on a different OS as well. Some flavor of UNIX i believe. But I am not sure that if you would get different dump value in SQL for the same string stored in database(I am not typing the string to get the dump) – Sachin Kumar Mar 18 '17 at 05:56

1 Answers1

0

Well, I still did not figure out WHY is that behavior but i just changed the type to VARCHAR2(50) from VARCHAR2(25) which would solve the problem but still the question of WHY hangs in there.

Sachin Kumar
  • 808
  • 3
  • 11
  • 29