1

I have a procedure which performs a SELECT INTO and stores the returned result in a VARCHAR2 variable called account_:

SELECT DISTINCT NVL(XYZ_API.Get_Ref(company, currency, pay_type, order_id), 
                    XYZ_API.Get_Id(company, currency, pay_type, order_id)) account
INTO account_
FROM some_table
WHERE company                     = company_
     AND payment_type             = 'SUPP'
     AND order_id                 = order_id_
     AND payment_date             = pay_date_;

company_, order_id_, and pay_date_ are all VARCHAR2 variables I am using to filter out the records.

The result taken into account_ is then formatted as follows and stored in another VARCHAR2 variable named giro_:

giro_ := LPAD( TRANSLATE( account_, '1234567890- ','1234567890' ), 16, '0' );

Then I check whether giro_ is a number, as follows with a FOR loop.

FOR i_ IN 1..LENGTH( giro_ ) LOOP
     c_ := ASCII( SUBSTR( giro_ , i_, 1 ) );
     IF ( c_ < ASCII( '0' ) OR c_ > ASCII( '9' ) ) THEN
        RETURN FALSE;
     END IF;
  END LOOP;

I have a scenario where the SELECT query shown above does not pick up any records.

This eventually introduces an exception in the FOR loop with errors ORA-06502 and ORA-06512.

As per my understanding the cause is LENGTH( giro_ ).

1..LENGTH( giro_ ) is failing as the LENGTH of giro_ value (LENGTH being a NULL) cannot be converted into a NUMBER.

My question is, by this time in this scenario, is giro_ an empty string or a NULL? What exactly happens here?

Thanks!

Uthpala Dl
  • 45
  • 8
  • 1
    While I've come to appreciate that the empty string and NULL are the same in Oracle, `LENGTH` is one of very few drawbacks. It returns NULL rather than zero in case of an empty string. It is strange by the way that you see the necessity of using `DISTINCT`, but see it guaranteed to get thus exactly one result row. This may indicate a poor data model or a not so robust query. – Thorsten Kettner Oct 11 '20 at 10:13
  • Yes I agree to what you are saying. I will try to make amends to my data/query :) thank you for the input! – Uthpala Dl Oct 11 '20 at 17:17

1 Answers1

1

Your understanding is wrong. LENGTH will always return a number (string length), it doesn't check whether its argument is a number.

Exception from the rule is if its (LENGTH's) argument is an empty string (in Oracle, it is equal to NULL) - then the length is also unknown (NULL):

SQL> select length(''), length(null) from dual;

LENGTH('') LENGTH(NULL)
---------- ------------


SQL>

In that case, modify your code so that it includes the NVL function, e.g.

FOR i_ IN 1 .. NVL(LENGTH( giro_ ), 0) LOOP

For empty strings, it won't do anything.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Hi, Thank you for the answer. I think you are agreeing to my point in the question? when I said, "1..LENGTH( giro_ ) is failing as the LENGTH of giro_ value cannot be converted into a NUMBER" I meant that the code is failing because the return value from LENGTH is a NULL here. So, the loop can't go from 1 to NULL, it should be 1 to some number. – Uthpala Dl Oct 11 '20 at 17:15
  • 1
    You're welcome. Exactly :) Sorry if I misunderstood what you were saying. – Littlefoot Oct 11 '20 at 18:15