tag:blogger.com,1999:blog-8677649049588007585.post6643926149594487608..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Questions about the INSTR quiz of 11 August (1328)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-8677649049588007585.post-83893345529933741262010-08-13T15:00:30.026+01:002010-08-13T15:00:30.026+01:00Sometimes with potential answers like that, I just...Sometimes with potential answers like that, I just think Steven is testing us to ensure unless we have some elite speed readers/thinkers, there should be at least a few seconds delay until the selection of answers ;-)<br /><br />Both where accurate - perhaps liken them to the occasional business requirement descriptions that come from different places describing the same request.<br /><br />Perhaps.Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-76308672239395907812010-08-13T07:58:58.139+01:002010-08-13T07:58:58.139+01:00Now I took a look on "Past quizes" and i...Now I took a look on "Past quizes" and it looks as if I misread the exact wording of the quiz in question - it says "correctly" and by taking quiz I misread it as "accurately".al0https://www.blogger.com/profile/15743792964167204705noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-85079653241933352092010-08-13T06:35:10.487+01:002010-08-13T06:35:10.487+01:00@Kim Berg Hansen:
Even replicating the sections fr...@Kim Berg Hansen:<br />Even replicating the sections from the Oracle's documentation may not serve to <i>completely</i> describe a function. For example, the <i>pos</i> argument to <i>instr</i> is specified to be a non-zero integer; however, Oracle does not raise an exception if a 0 is passed, instead <i>instr</i> returns 0. Neither does the documentation specify that NULL is returned if any of the passed arguments is NULL (although such behavior is common to most Oracle supplied functions).<br /><br />@Jay:<br />The following code includes "my_instr" which is functionally equivalent to INSTR (at least sufficiently so to demonstrate <b>backward</b> searching using your second case):<br /><br />DECLARE<br /> FUNCTION my_instr(<br /> str1 VARCHAR2,<br /> str2 VARCHAR2,<br /> pos PLS_INTEGER DEFAULT 1,<br /> nth POSITIVE DEFAULT 1<br /> )<br /> RETURN PLS_INTEGER IS<br /> found_position PLS_INTEGER;<br /> max_position PLS_INTEGER;<br /> occurrence NATURAL := 0;<br /> search_increment PLS_INTEGER := 1;<br /> search_position PLS_INTEGER;<br /> str2_length PLS_INTEGER;<br /> BEGIN<br /> IF str1 IS NULL OR str2 IS NULL OR pos IS NULL OR nth IS NULL THEN<br /> RETURN NULL;<br /> END IF;<br /><br /> str2_length := LENGTH(str2);<br /> max_position := LENGTH(str1) + 1 - str2_length;<br /> search_increment := SIGN(pos);<br /> search_position :=<br /> CASE search_increment<br /> WHEN -1 THEN LENGTH(str1) + 1 + LEAST(pos, 0 - str2_length)<br /> WHEN 1 THEN pos<br /> ELSE 0<br /> END;<br /> -- Value to be returned if search is unsuccessful.<br /> found_position := 0;<br /><br /> <<search>><br /> WHILE found_position = 0 AND search_position BETWEEN 1 AND max_position LOOP<br /> sys.DBMS_OUTPUT.<br /> put_line('search_position: ' || TO_CHAR(search_position, 'TM'));<br /><br /> IF SUBSTR(str1, search_position, str2_length) = str2 THEN<br /> -- An occurrence of str2 has been found in str1.<br /> occurrence := occurrence + 1;<br /><br /> IF occurrence = nth THEN<br /> -- The nth occurrence has been found.<br /> found_position := search_position;<br /> END IF;<br /> END IF;<br /><br /> -- Even if the nth occurrence has been found, there are neither logical<br /> -- nor performance reasons not to increment the search_position.<br /> search_position := search_position + search_increment;<br /> END LOOP search;<br /><br /> RETURN found_position;<br /> END my_instr;<br />BEGIN<br /> sys.DBMS_OUTPUT.put_line('my_instr return: '<br /> || TO_CHAR(my_instr(<br /> 'dad went to see his dad',<br /> 'ad',<br /> -2,<br /> 1<br /> ), 'TM'));<br />END;<br /><br /><br />Whenever <i>pos</i> is negative, <i>instr</i> searches by moving the position for the <b>start</b> of the comparison backwards until either the <i>nth</i> match is found or all of the candidate positions have been tested. When <i>str2</i> is a multi-character string, then the end of the comparison may occur after <i>pos</i>.<br /><br />The values in your second case are insufficient for ascertaining the behavior of <i>instr</i> because the output will be the same regardless of which direction the comparison start position moves during the search (in this specific case it never moves since the value in <i>str2</i> occurs at <i>pos</i> in <i>str1</i>).jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-18532109826352625582010-08-13T01:45:46.078+01:002010-08-13T01:45:46.078+01:00I do agree that if position is negative the search...I do agree that if position is negative the search proceeds backwards but that was not always. Thats is what I am trying to say.<br />See the optoins:<br />1. "Oracle searches for the NTH occurrence of the STR2 value backwards through STR1 (towards index position 1)" <br />2. "The search for STR2 begins from the end of STR1, instead of from the beginning. That is, a value for POS of -1 starts the search from the last character in STR1. A value for POS of -2 starts the search from the second-to-last character in STR1"<br /><br />Both these options are correct, provided I have taken a string such that it satisfies the above two options like my 1st example in previous post.<br /><br />What if a novice person tries to check and unfortunately chooses my 2nd kind of example? I am not saying that the optoins are wrong but the options might turn wrong based on the example we are trying to confirm that.Jaydeep Cherukuhttps://www.blogger.com/profile/17533836153440189719noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-63803946035869739392010-08-12T21:14:16.532+01:002010-08-12T21:14:16.532+01:00@Jay:
When position is negative the search proceed...@Jay:<br />When <i>position</i> is negative the <b>search</b> proceeds backwards but <b>comparisons</b> are still performed in the forward direction. In your second case the portion of the string beginning at offset -2 is "ad" which matches the search criteria.jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-53816857475999816042010-08-12T17:42:11.277+01:002010-08-12T17:42:11.277+01:00Hi steven, I remember when reading your book "...Hi steven, I remember when reading your book "Programming PL/SQL" you mentioned a special characteristic of INSTR with -ve POS.<br /><br />SELECT INSTR('dad went to see his dad','ad',-1,2) FROM DUAL;<br />This gives 2 as output which searched backward.<br /><br />If we change it to<br />SELECT INSTR('dad went to see his dad','ad',-2,1) FROM DUAL;<br />This won't give 2 as output since it won't search backwords at all in this case but it searches fwd from that location and gives output as 22<br /><br />So if I take my second example none of the options were correct. But if i take my first example 2 options which were scored correct will be correct.<br /><br />I too got this ambiguity while answering the quiz but selected the more generic answer and was awarded 100% score. Please do let me know if my understanding is wrong.Jaydeep Cherukuhttps://www.blogger.com/profile/17533836153440189719noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-87125769633838579382010-08-12T13:55:49.661+01:002010-08-12T13:55:49.661+01:00It's really a matter of how you interpret this...It's really a matter of how you interpret this phrase:<br /><br />"Which of the following statements correctly describes the impact of passing a negative integer for the POS parameter?"<br /><br />At first I also (maybe because I'm a non-native English speaker?) interpreted "correctly describes" as meaning "a correct and complete description."<br /><br />But when I read the answers I concluded that it only made sense if I interpreted it as "which statements are true (but not necessarily complete by themselves.)"<br /><br />I think the question and answers are OK, as the answers seem to make it obvious how "correctly describes" should be interpreted. But it can be confusing at first.<br /><br />Also I considered, that if it had been meant that the answers were false due to "incompleteness", that would have been a really tricky question then with no truly accurate answers. There have been devious answers before, but not that tricky ;-) Besides, if an answer had to be absolutely completely complete - it would have to be as long as the relevant section of the manual...<br /><br />Just my 2 bits...Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-23061428722645249202010-08-12T12:52:39.224+01:002010-08-12T12:52:39.224+01:00Hello,
you have written 'that choice itself i...Hello,<br /><br />you have written 'that choice itself is correct as a "self-contained"', unfortunately neither of supposedly correct choices are correct as "self-contained". <br /><br />Moreover, you have asked not for the "correct" description but for the "accurate" description and latter may be understood as "correct and complete".al0https://www.blogger.com/profile/15743792964167204705noreply@blogger.com