tag:blogger.com,1999:blog-8677649049588007585.post4064617863857774481..comments2015-08-07T12:34:11.460+01:00Comments on PL/SQL Challenge: "Bounds" for associative arrays - correcting an ambiguity - 28 December (239)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-8677649049588007585.post-30025321422226543742010-12-29T23:35:51.585+00:002010-12-29T23:35:51.585+00:00siamnobita:
According to Oracle's documentati...siamnobita:<br /><br />According to Oracle's documentation, "numbers with precision up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point," are guaranteed to be portable. Running the following code will demonstrate that Oracle keeps a maximum of 40 significant digits:<br /><br />BEGIN<br />DBMS_OUTPUT.PUT_LINE(CAST(123456789112345678921234567893123456789412345678951234567896123456789712345678981234567899123456789012345678911234567892123456 AS VARCHAR2));<br />END;<br /><br />My description of the range of possible "integer" values that can be represented by strings of VARCHAR2 digits was intended to demonstrate that the claimed ambiguity is irrelevant to the choice's validity since even the most generous interpretation still constrains the number of possible "integer" index values (an absurdly broad range, but still a finite set of values).jhall62http://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-38683208759468760702010-12-29T23:16:24.624+00:002010-12-29T23:16:24.624+00:00siamnobita,
SQL Reference:
"A number of type...siamnobita, <br />SQL Reference:<br />"A number of type NUMBER can store a maximum of 38 digits of precision. If the literal requires more precision than provided by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, then Oracle truncates the value. If the range of the literal exceeds the range supported by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, then Oracle raises an error."<br />So it's possible to use at most 126 digits and only 38 of precision as literal._Nikotinnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-91960711998261380372010-12-29T23:03:38.303+00:002010-12-29T23:03:38.303+00:00"it's possible to have only 39 nines and ..."it's possible to have only 39 nines and (32767-39) zeros" is still not correct too.<br /><br />You can not type in the integer with more than 126 digit so the maximum should be (1e40-1)*10**(125-39)<br /><br />SQL> declare<br /> 2 type tt is table of boolean index by varchar2(200);<br /> 3 t tt;<br /> 4 begin<br /> 5 t( (1e40-1)*10**(125-39) ) := null;<br /> 6 dbms_output.put_line ( t.first ) ;<br /> 7 end;<br /> 8 /<br />9.999999999999999999999999999999999999999000000000000000000000000000000000000000000000000000000E+125<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL>siamnobitahttp://www.blogger.com/profile/02925596241710939090noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-7498034275856459552010-12-29T22:33:18.860+00:002010-12-29T22:33:18.860+00:00Spoon said that "The maximum 'integer val...Spoon said that "The maximum 'integer value' that a varchar2 string can hold is 32767 nines" and jhall62 said "the range of possible "integer" index values is -(10**32767-1) to 10**32768-1"<br /><br />I don't think it precisely correct<br />SQL> declare<br /> 2 type ty is table of number index by varchar2(100);<br /> 3 tb ty;<br /> 4 begin<br /> 5 tb(12345678901234567890123456789012345678901234567890) := 1 ;<br /> 6 dbms_output.put_line (tb.first) ;<br /> 7 end;<br /> 8 /<br />12345678901234567890123456789012345678900000000000<br /><br />PL/SQL procedure successfully completed.<br /><br />When you type in the number with precision more than 39 digit (I don't understand why it isn't 38. Could anyone else explain?), It was, then, round to the maximum precision so it's possible to have only 39 nines and (32767-39) zerossiamnobitahttp://www.blogger.com/profile/02925596241710939090noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-71918002898369143962010-12-29T20:40:12.772+00:002010-12-29T20:40:12.772+00:00I agree with jhall62 and Spoon on #1.
It shouldn&#...I agree with jhall62 and Spoon on #1.<br />It shouldn't be rescored, I think..Radoslavhttp://www.blogger.com/profile/11089830721619663885noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-51873968218194526232010-12-29T18:32:21.669+00:002010-12-29T18:32:21.669+00:00_Nikotin:
BINARY_DOUBLE_INFINITY is an IEEE-754 d..._Nikotin:<br /><br />BINARY_DOUBLE_INFINITY is an IEEE-754 derived encoding treated as positive infinity in calculations. It is not the upper bound for integer values (infinity is an unbounded quantity, thus it is not a bound at all). Oracle returns "Inf" when converting BINARY_DOUBLE_INFINITY to a character type. Your example code demonstrates that associative arrays can be indexed by VARCHAR2 with index values derived from types that can be implicitly converted to VARCHAR2 but does not address the issue of integer index ranges.jhall62http://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-39695613755048417122010-12-29T16:53:03.197+00:002010-12-29T16:53:03.197+00:00Ok, I have three points, too. ;-)
1. "If you...Ok, I have three points, too. ;-)<br /><br />1. "If you index by VARCHAR2, then the values used as index values are not integers; they are strings" - absolutely correct.<br />2. Do not re-score because of word-games and/or possible workarounds.<br />3. Try to avoid text-based quizzes as often as possible.<br /><br />Happy New Year!<br /><br />MarkusAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-64661690223149129472010-12-29T16:14:54.653+00:002010-12-29T16:14:54.653+00:00binary_double_infinity is not itself an integer va...binary_double_infinity is not itself an integer value, but it is the upper bound for the integer values and can be used as index value:<br /><br />declare<br /> type tt is table of boolean index by varchar2(3);<br /> t tt; <br /> d binary_double := binary_double_infinity;<br />begin<br /> t(d) := null;<br />end;<br />/_Nikotinnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-70061242120001469262010-12-29T15:43:28.182+00:002010-12-29T15:43:28.182+00:00I disagree with changing the scoring based on any ...I disagree with changing the scoring based on any of the arguments listed in the original post.<br /><br /> 1. Even if the all numeric VARCHAR2 workaround is accepted as being an integer (a position with which I disagree) the range of possible integer values is still constrained. A VARCHAR2 may hold a maximum of 32767 bytes; therefore, the range of possible "integer" index values is -(10**32767-1) to 10**32768-1.<br /><br />2. Regardless of the sources of any limitations, the possible integer index values of an associative array are constrained.<br /><br />3. Imprecise or incorrect descriptions outside of the quiz do not affect the validity of choices about implementation and thus should not be accepted as a basis for changing the scoring.jhall62http://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-61216334884181053772010-12-29T14:30:38.275+00:002010-12-29T14:30:38.275+00:00I'm a little confused by my score, 75% since, ...I'm a little confused by my score, 75% since, under Choices and Explanations, it shows me having all correct answers.Tom Sullivannoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-29425443834428438192010-12-29T13:52:40.654+00:002010-12-29T13:52:40.654+00:00OK, the point#1 was from me, and I really didn'...OK, the point#1 was from me, and I really didn't think re-scoring was required. I wrote it to highlight that one could use this varchar2 workaround for supplying integer values, and it can hold a higher value than a 'binary_integer' can have.<br /><br />This not really a bound of Associative Array, rather its a limitation of 'binary_integer'<br /><br /><br />SQL>declare<br /><br /> 2 n binary_integer;<br /><br /> 3 begin<br /><br /> 4 n := 2147483648;<br /><br /> 5 end;<br /><br /> 6 /<br /><br />declare<br /><br />*<br /><br />ERROR at line 1:<br /><br />ORA-01426: numeric overflow<br /><br />ORA-06512: at line 4<br /><br /><br />This way, even a varchar2 string has an upper bound on integers values. The maximum 'integer value' that a varchar2 string can hold is 32767 nines. i.e rpad('9',32767,'9')<br /><br />Since Associative array can only be indexed by binary_integer or varchar2, and both these have upper bounds on integer values, the original scoring of the quiz choice is correct, IMO.<br /><br /><br />CheersSpoonhttp://www.blogger.com/profile/04277621011196124328noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-20918034274874568292010-12-29T13:50:12.593+00:002010-12-29T13:50:12.593+00:00My thoughts are that I agree with point 2. The lim...My thoughts are that I agree with point 2. The limit is on the index type (in this case BINARY_INTEGER) and not on the associative array. There's only so much you can fit into 32 bits. Taking point 1 into account, I think we could come up with code to fill up more items in the AA than we could using integers.Patrick Barelhttp://blog.bar-solutions.comnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-857342209072248072010-12-29T13:00:03.324+00:002010-12-29T13:00:03.324+00:00I think re-scoring on point #1 was a bit of stretc...I think re-scoring on point #1 was a bit of stretch, but that's probably because the first thing I thought of was the 2**32 limitation due to the reference to "integers". Too much word play?Scott Wesleyhttp://www.blogger.com/profile/18106937181788036683noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-27867530778274443412010-12-29T12:58:50.022+00:002010-12-29T12:58:50.022+00:00My point is that the 'phrasing the question...My point is that the 'phrasing the question' wasn't quite precise and that's why I think you should accept that answer and give the points back :-)Anonymousnoreply@blogger.com