CODEWITHSUNDEEP
oracle-databaseplsql
mawia
mawia

Reputation: 9349

PL SQL oracle instr function|anomalous behaviour

I am getting a rather strange behaviour when invoking oracle instr function, or probably I'm blind enough not to see my stupid mistake.

Actually I written a procedure to split a string. For example if we have string

a,e,i,o,u

then my split method will look like 

         string_split('a,e,i,o,u',',',5);

where first parameter is the string to split while second one is the separator and third one is the number of element I know is there after splitting.

Now, of number of things , one thing my procedure do is invoke 

  start_index := instr(temp_string_to_split,',',1,(total_element-i));

But the moment it is invoked I get a 

   ORA-06512 ,numeric or value error

But if I invoke

start_index := instr(temp_string_to_split,1,(total_element-i));

the procedure runs,though not in a desirable manner. Note that in second invocation separator parameter is missing, and directly number is passed as the second parameter, which I guess should have cause big time exception. But surprisingly it goes and run fine.

Can somebody explain this anomaly...or help me see if I'm missing something.

Thanks, Mawia

Upvotes: 1

Views: 1226

Answers (2)

mawia
mawia

Reputation: 9349

Thanks a lot all for responding.

Actually as I told earlier, I was calling

 start_index := instr(temp_string_to_split,',',1,(total_element-i));

in a loop. Now as a final value of the loop 

 (total_element-i)

was getting negative. And this was the root of malady.

One thing, I'm still puzzled though is as it was a run time generated condition, that is to say everything before the final invocation was legal. Then why I dont see on console the result of few of DBMS_OUTPUT.PUT_LINE statement which I had put into to trace the execution.

Thanks, Mawia

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48111

I'm assuming that in your call to instr, temp_string_to_split is the string that was passed to string_split, and (total_element-i) is meant to be an iterator over the number of splits to make. (As an aside, it seems odd that you have ',' hardcoded in this call, when you appear to be passing it as a parameter to string_split.

I tried emulating this with the following SQL, which worked fine:

SELECT LEVEL,instr('a,e,i,o,u',',',1,LEVEL)
from dual connect by level < 5;

Do you know the exact values of temp_string_to_split, total_element, and i on the call to instr that caused the error?

Upvotes: 2

Related Questions