"We need to make sure that the new PL/SQL version that runs on Oracle will work exactly as it does on SQL Server," stated the directive given to Andy by his supervisor.

Andy had been tasked to port over a T-SQL function which, when passed valid string, would convert it into a data format that could then be returned back to the calling application and submitted to a label printer to produce a bar code.

Though experienced as an Oracle Developer by position, and coming into the task with zero SQL Server experience, Andy found that his task didn't seem nearly as difficult as he had imagined. The code was very well commented and the formatting of the T-SQL logic was close enough to the style of PL/SQL that he was most familiar with that he soon felt right at home and was able to get straight to work.

Things were going long quite smoothly until he hit part of function responsible for data validation - the section of the code that ensured the passed value to convert into barcode data format was a whole number.

While @QuickCounter <= @Length
    BEGIN
            Set @CheckDigit = Null
            Begin Try
                  Set @CheckDigit = Cast (Substring(@barcodeValueSequence, @QuickCounter, 1) as Int)
            End Try
            Begin Catch
                  If @CheckDigit is null
                  Begin
                        Select 'Non-Numeric Sequence Provided'
                        Return
                  End
            End Catch
            Set @QuickCounter = @QuickCounter + 1
    END

Though Andy's level of T-SQL experience didn't extend much further beyond that of the code that he was handed, Andy had a strong feeling that there was something fishy about how this particular piece code behaved and, after a little research, his hunch was confirmed. Rather than evaluate the entire value stored in the barcodeValueSequence variable as a single value, the logic would step through the string and evaluate if each character could be converted to an integer, character by character, for the entire length of the string. While this approach didn't seem to be much of a performance hog, this approach bothered him. Andy knew there must be a better way and had to pursue it, if for no other reason than to make his conversion to PL/SQL task easier.

So, after a little more research, Andy discovered SQL Server's built in 'ISNUMERIC' function which could, as the name implies, evaluate the entire string and determine if the value is a number and could replace the While...BEGIN...END block with a single line of code.

Quite pleased with the fruits of his research, Andy figured that he would share his findings to his manager, however, the reply wasn't entirely as he had hoped.

"This is a great catch! Thanks for thinking outside of the box on this one, but I do have to ask one favor of you," replied Andy's boss,"for now, I'll need you to replicate the validation logic in your new procedure."

"We just can't run the risk of the new Oracle code behaving any differently than the SQL Server version does."

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!