I have a stored procedure that needs to access many tables to collect data. One of the tables accessed doesn't necessary have a matching row (which means in plain sql statement, this requires an outer join), my question is how do I enable outer join here. I tried the same way by putting a (+) here, the "NO data found" error was returned.
Thank You for the reply.
Below is a brief version of code similar to what I wanted to accomplish. Table2 either doesn't contain a matching row or contains more than 1 matching row. How should I change the code to either assign v_num1 and v_num2 an 0 to non-matched id or select only the first matched row for id that has more than one matching row?
CURSOR C_OP (I_OP_ID NUMBER) IS
SELECT DISTINCT a.id
FROM Table1 a
FETCH C_OP INTO C_OP_REC;
EXIT WHEN C_OP%NOTFOUND;
FROM table2 b
First of all, I will simply have to assume that your real code is more complex than this, since there is no reason to do something this simple with a cursor.
Given that, you are applying the wrong tool. An outer-join is used when you are combining 2 tables in a single statement. You are not. You have a second statement. You want to set 2 variables to 0 if this second statement does not return any values. Therefore:
---WHEN NO_DATA_FOUND THEN
------v_num1 := 0;
------v_num2 := 0;
---WHEN OTHERS THEN