Different results when using a CURSOR and direct SQL execute? - PL/SQL

Question

While debugging, I came across a strange situation today with a query that has a join condition on a column that could be NULL.

I have data in two tables like this:

emp_payment_trans_tab:

emp_payment_tab:

When I execute this statement, I get no result:

SELECT emp_no, payment_date, expense_id, a.company_id
    FROM   emp_payment_trans_tab a, emp_payment_tab b
    WHERE  a.emp_pay_trans_id = '201906'
    AND    a.payment_id = b.payment_id
    AND    a.emp_payment_status != 'C';

I have written a procedure where this query is run using a cursor. In the real scenario, emp_pay_trans_id will be a variable passed on to the procedure as a parameter. But for testing I just add the same value I used in the above query. As you can see, I added a trace line to see if the values are getting printed correctly as well.

PROCEDURE Create_Header__ (   
   emp_pay_trans_id_ IN NUMBER )
IS      
      emp_no_           VARCHAR2(11);
      pay_date_         DATE;
      expense_id_       NUMBER;
      company_id_       VARCHAR2(20);
   
      CURSOR get_emp_details IS
        SELECT emp_no, payment_date, expense_id, a.company_id
        FROM   emp_payment_trans_tab a, emp_payment_tab b
        WHERE  emp_pay_trans_id = '201906' -- emp_pay_trans_id_ will be used here in the real scenario
        AND    a.payment_id = b.payment_id
        AND    a.emp_payment_status != 'C';  
   
   BEGIN
      OPEN get_emp_details;
      FETCH get_emp_details INTO emp_no_, pay_date_, expense_id_, company_id_;
      CLOSE get_emp_details;
   
      -- commenting this as I won't share the trace procedure because the question will by lengthy if I add everything. You can use a dbms output line instead.
      -- Autonomous_Trace_dinllk('values passed0', 'emp_no_: ' || emp_no_ || ', company_id_: ' || company_id_  || ', pay_date_: ' || pay_date_ || ', expense_id_: ' || expense_id_ );     
      
END Create_Header__;

For the trace line, I'm getting all the values printed. I'm confused. Can someone explain the reason for this different behavior? If there are no rows when the same query is executed independently, why does the cursor fetch values?

I'm using Oracle 12c (Enterprise Edition Release 12.2.0.1.0 if it helps).

Edit: Adding queries and code for reference:

create table EMP_PAYMENT_TRANS_TAB
(
  emp_pay_trans_id   NUMBER not null,
  emp_payment_status VARCHAR2(20) not null,
  company_id         VARCHAR2(20) not null,
  expense_id         NUMBER,
  emp_no             VARCHAR2(11) not null,
  payment_id         NUMBER,
  constraint EMP_PAYMENT_TRANS_PK primary key (EMP_PAY_TRANS_ID)
)

insert into emp_payment_trans_tab (COMPANY_ID, EMP_PAY_TRANS_ID, EMP_NO, EMP_PAYMENT_STATUS, EXPENSE_ID, PAYMENT_ID)
values ('10', 201906, '1002', 'A', 203184, null);

insert into emp_payment_trans_tab (COMPANY_ID, EMP_PAY_TRANS_ID, EMP_NO, EMP_PAYMENT_STATUS, EXPENSE_ID, PAYMENT_ID)
values ('10', 201960, '10100', 'T', 202795, 201314);

insert into emp_payment_trans_tab (COMPANY_ID, EMP_PAY_TRANS_ID, EMP_NO, EMP_PAYMENT_STATUS, EXPENSE_ID, PAYMENT_ID)
values ('10', 201962, '10113', 'T', 203089, 201314);


create table EMP_PAYMENT_TAB
(
  payment_id   NUMBER not null,
  payment_date DATE,
  company_id   VARCHAR2(20),
  method_id    VARCHAR2(25),
  constraint EMP_PAYMENT_PK primary key (PAYMENT_ID)
)

insert into emp_payment_tab (COMPANY_ID, PAYMENT_ID, PAYMENT_DATE, METHOD_ID)
values ('10', 201314, to_date('25-11-2020', 'dd-mm-yyyy'), 'BACS');

insert into emp_payment_tab (COMPANY_ID, PAYMENT_ID, PAYMENT_DATE, METHOD_ID)
values ('10', 202011, to_date('01-07-2021', 'dd-mm-yyyy'), 'BANK2');

insert into emp_payment_tab (COMPANY_ID, PAYMENT_ID, PAYMENT_DATE, METHOD_ID)
values ('10', 202151, to_date('01-11-2021', 'dd-mm-yyyy'), 'CASH');

insert into emp_payment_tab (COMPANY_ID, PAYMENT_ID, PAYMENT_DATE, METHOD_ID)
values ('10', 202392, to_date('25-01-2022', 'dd-mm-yyyy'), 'CASH');

Please do **NOT** include data and code as images as they cannot be copy/pasted into an editor and are not accessible to users with screen readers. Please [edit] your question to include the code and data as text (preferably data as `CREATE TABLE` and `INSERT` statements that can be copy/pasted and executed to recreate your environment). Also, when you include code, make sure your code can be executed; which for PL/SQL code needs a `DECLARE` before the variables and a trailing `END;/` — MT0, Apr 03 '22 at 18:18

score 1 · Answer 1 · answered Apr 03 '22 at 19:44

To me, it seems that you're misinterpreting reality, because BOTH SQL and PL/SQL option return nothing.

SQL:

SQL> SELECT emp_no, payment_date, expense_id, a.company_id
  2      FROM   emp_payment_trans_tab a, emp_payment_tab b
  3      WHERE  a.emp_pay_trans_id = '201906'
  4      AND    a.payment_id = b.payment_id
  5      AND    a.emp_payment_status != 'C';

no rows selected

PL/SQL:

SQL> set serveroutput on;
SQL> create or replace
  2  PROCEDURE Create_Header__ (
  3     emp_pay_trans_id_ IN NUMBER )
  4  IS
  5        emp_no_           VARCHAR2(11);
  6        pay_date_         DATE;
  7        expense_id_       NUMBER;
  8        company_id_       VARCHAR2(20);
  9
 10        CURSOR get_emp_details IS
 11          SELECT emp_no, payment_date, expense_id, a.company_id
 12          FROM   emp_payment_trans_tab a, emp_payment_tab b
 13          WHERE  emp_pay_trans_id = emp_pay_trans_id_ --'201906' -- emp_pay_trans_id_ will be used here in the real scenario
 14          AND    a.payment_id = b.payment_id
 15          AND    a.emp_payment_status != 'C';
 16
 17     BEGIN
 18        OPEN get_emp_details;
 19        FETCH get_emp_details INTO emp_no_, pay_date_, expense_id_, company_id_;
 20        CLOSE get_emp_details;
 21
 22        -- commenting this as I won't share the trace procedure because the question will by lengthy if I add everything. You can use a dbms output line instead.
 23        -- Autonomous_Trace_dinllk('values passed0', 'emp_no_: ' || emp_no_ || ', company_id_: ' || company_id_  || ', pay_date_: ' || pay_date_ || ', expense_id_: ' || expense_id_ );
 24        dbms_output.put_line('emp_no_: ' || emp_no_ || ', company_id_: ' || company_id_  || ', pay_date_: ' || pay_date_ || ', expense_id_: ' || expense_id_ );
 25  END Create_Header__;
 26  /

Procedure created.

SQL> exec create_header__(201906);
emp_no_: , company_id_: , pay_date_: , expense_id_:

PL/SQL procedure successfully completed.

SQL>

You'd get something if you used outer join (see line #2 below); also, try to switch to JOINs (instead of comma-separated tables in FROM clause and joining in WHERE clause which should be used to apply filter to the result.

SQL> select emp_no, payment_date, expense_id, a.company_id
  2  from emp_payment_trans_tab a left join emp_payment_tab b on a.payment_id = b.payment_id
  3  where a.emp_pay_trans_id = '201906'
  4    and a.emp_payment_status != 'C';

EMP_NO      PAYMENT_D EXPENSE_ID COMPANY_ID
----------- --------- ---------- --------------------
1002                      203184 10

SQL>

Hello, thank you for the comment! Just discovered that this is actually due to my idiocy! I'm debugging an enterprise code, and there is a dynamic query that I totally missed from another API that inserts a new record to emp_payment_tab and adds that payment_id to emp_payment_trans_tab. By the time this cursor is run, there is a record matching the conditions, but due to an error that pops up in the middle, it reverts the change! Sorry for wasting your time! — Uthpala Dl, Apr 04 '22 at 16:13

Different results when using a CURSOR and direct SQL execute? - PL/SQL

1 Answers1