• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

getting different number for LENGTH() in anon block vs sql pckg

 
Matt Kohanek
Village Idiot
Ranch Hand
Posts: 484
Java jQuery Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello

This is confusing me a bit. I set up an anonymous block to see what LENGTH would return on a String - I need to check that it will match the value of the length property from javascript

Here is the block (note there is a line break in v_var)

I run this, and it returns 21 as I would expect. I enter this into a textarea where I have some javascript returning the length property, also 21.

When I submit that text area however...
The value of the textarea is mapped to a param

Both of the values getting logged there are 22 - for each line break I have, it gets counted as two characters. Now I understand why this would be counted as such, with Kanji and other special characters out there - although, if that is the case, wouldn't all characters, even the 'normal' ones equal 2 characters there?
My question is, what is the difference between here and the anonymous block I ran earlier?
I guess it must have something to do with the difference between SQL and PL SQL (I think of this as I have typed most of this thread out...)

So what about my main problem then, which is getting the count I have in the package to be the same count returned by the javascript length property?
I do not want to pass something so insignificant into the procedure as a parameter - that is just not going to happen. Is there some SQL magic I can use to get a count I would receive from javascript in my procedure? - I don't know, something like converting to a CLOB and then getting the LENGTH (I will have to try that actually...)
Is there any way to reconcile these LENGTH values?

Thanks
 
Koen Aerts
Ranch Hand
Posts: 344
Java Linux Oracle
  • 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Line breaks are either 1 byte, or 2 bytes long. Generally on UNIX/Linux platforms it's 1 byte, while on Windows it's 2 bytes. So quite possibly if I type text in a textbox in Firefox in Linux, the line breaks are submitted as a 1 byte character. If I do the same in Firefox on Windows, the page break will be 2 bytes long. More details on the difference of carriage returns can be seen here: http://koenaerts.ca/dos2unix-equivalent/

So best thing to do on the server side, is to convert 2-byte carriage returns to a 1-byte equivalent, and then do your character count. This can be easily done in your Oracle stored procedure - i.e. replace(my_string_value, chr(13))
 
Matt Kohanek
Village Idiot
Ranch Hand
Posts: 484
Java jQuery Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yeah I am not sure why I didn't think to just replace line returns with a blank space or something (for the count sake only, not changing input values)
I think I was too focused on learning about the differences between different length functions and the like
Thanks though, I will just go with this solution.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic