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!

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