I have looked for a while now for a solution to this issue, and all the NO_DATA_FOUND handling tutorials I found only show how to do it when doing a Select Into with only one column into one variable.
First, here is my code:
BEGIN
OPEN c_no_source;
LOOP
DECLARE
l_price_code VARCHAR2(20) := '';
l_price_level VARCHAR(10) := '';
l_code_date DATE := p_effective_date;
BEGIN
FETCH c_no_source INTO c_no_source_row;
exit WHEN c_no_source%NOTFOUND;
BEGIN
WITH codeList AS /* liste des dates ayant une donnée avant la date effective incluse. */
(
SELECT distinct
NVL(effective_date,p_effective_date) as effective_date,
NVL(user_group2,'') as price_code,
NVL(user_group3,'') as price_level
FROM DGA_Service.Hjvsecmaster_Hist
WHERE effective_date <= p_effective_date
AND user_group2 IS NOT NULL
AND security_alias = c_no_source_row.security_alias
ORDER BY 1 DESC
)
SELECT price_code, price_level, effective_date
INTO l_price_code, l_price_level, l_code_date
FROM codelist
WHERE ROWNUM = 1;
EXCEPTION WHEN no_data_found THEN NULL;
...
[UPDATE statement using the variables]
...
END;
END;
END LOOP;
CLOSE c_no_source;
END;
What I'm trying to do is take the top 1 result of my codeList
temp result set into three separate variables.
However one of the three columns in codeList
will often be NULL
. And none of my 3 variables will be set.
I tried to handle the exception but I can't get my code to insert the columns that DO have a value into their respective variables. If even one of the columns is NULL then none of them are inserted into their variable.
I would like the Select Into statement to set the variables that it CAN set.
I tried to handle them all separately using something like this:
EXCEPTION WHEN NO_DATA_FOUND THEN
BEGIN
IF price_code IS NOT NULL THEN l_price_code := price_code; END IF;
IF price_level IS NOT NULL THEN l_price_level := price_level; END IF;
IF effective_date IS NOT NULL THEN l_code_date := effective_date; END IF;
END;
But I got the following error message:
ORA-06550: line 294, column 18:
PLS-00201: identifier 'PRICE_CODE' must be declared
ORA-06550: line 294, column 15:
PL/SQL: Statement ignored
ORA-06550: line 295, column 18:
PLS-00201: identifier 'PRICE_LEVEL' must be declared
ORA-06550: line 295, column 15:
PL/SQL: Statement ignored
ORA-06550: line 296, column 18:
PLS-00201: identifier 'EFFECTIVE_DATE' must be declared
ORA-06550: line 296, column 15:
PL/SQL: Statement ignored
So I ran out of ideas. I tried specifying the temp table name, to no avail, like this:
IF codeList.price_code IS NOT NULL
I would love any help on this issue. The package this piece of code runs in is already heavy and I would prefer not to have to get each column with a separate With ... As () Select Into
clause.
Best Answer
Okay, I think I get you; most of your problem is caused by you nesting PL/SQL blocks incorrectly.
Your update statement is contained within the EXCEPTION block, which means it'll only get executed if the exception is thrown. Secondly, you're referencing the columns directly in the following:
This is the cause of your compilation error.
Lastly, if there is a single row in your
select into
then every variable will be set, so there's no need to try to deal with this.This is normally a highly inefficient way of doing this. If you can fit everything into a single UPDATE or MERGE then I would do so. You don't appear to have any additional logic so it should be possible.