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
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!
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.
Answered by Littlefoot on November 26, 2020
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP