I have a procedure which performs a SELECT INTO and stores the returned result in a VARCHAR2 variable called
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_;
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_ := 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?
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 (
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.
Answered by Littlefoot on November 26, 2020
8 Asked on January 28, 2021 by gainz
0 Asked on January 27, 2021 by m0bbin
1 Asked on January 27, 2021 by alice-messis
4 Asked on January 27, 2021 by shemtheultimate
2 Asked on January 27, 2021 by kachi-cheong
0 Asked on January 27, 2021 by s-leg3ndz
3 Asked on January 27, 2021
1 Asked on January 27, 2021 by micim987
1 Asked on January 27, 2021 by user13888307
1 Asked on January 27, 2021 by heaven
2 Asked on January 27, 2021 by youtube
1 Asked on January 26, 2021
1 Asked on January 26, 2021 by arbin-bulaybulay
Get help from others!