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

Stack Overflow Asked by Uthpala Dl on November 26, 2020

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
     END IF;

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?


One Answer

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;

---------- ------------


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.

Answered by Littlefoot on November 26, 2020

Add your own answers!

Related Questions

How to Deserialize a list of objects from json in flutter

8  Asked on January 28, 2021 by gainz


How to pass input variable in structure directive?

1  Asked on January 27, 2021 by alice-messis


How to iterate over a list of floats in python

4  Asked on January 27, 2021 by joeyhoward988


Can I run docker system prune -a without downtime

1  Asked on January 27, 2021 by aswin-george


Calculating total Price of groceries with execptions in Python

4  Asked on January 27, 2021 by shemtheultimate


handling multiple useState inside conditional ternary

2  Asked on January 27, 2021 by kachi-cheong


react-native existing app crash on Android 4.1

0  Asked on January 27, 2021 by s-leg3ndz


HTML table widths set proportionally

1  Asked on January 27, 2021 by mayur-arora


Alsa issues on Raspberry pi

1  Asked on January 27, 2021 by suraj-hebbar-shankar


How to merge dictionaries with the same key and value in Python

1  Asked on January 26, 2021 by arbin-bulaybulay


AJAX switching HTML

1  Asked on January 26, 2021 by mimi


Using request to return a json value

1  Asked on January 26, 2021 by gta-sprx


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir