No result from a query stored in a VARCHAR2 variable is treated as an empty string or a NULL? - Oracle PL/SQL - Oracle 11g

Question

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!

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
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

Littlefoot Littlefoot · Accepted Answer · 2020-10-11T10:03:41

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.

No result from a query stored in a VARCHAR2 variable is treated as an empty string or a NULL? - Oracle PL/SQL - Oracle 11g

1 Answers