Executing date field with null in plsql

Question

Hello I have a procedure and questions about it. This procedure is used for extracting data then inserting them into one table. What I want to do is that when executing the procedure if pid_billdate is null, it should use previous months' last dasy as a parameter. I have already tried out in procedure definition line but didn't worked out properly. So how can I make this change? I need to add this control into procedure.

My second question is how can I test it(I mean how to enter null value?)

BEGIN
   GPU_DATA_EXTRACTOR_TEST(); -- or is it should be GPU_DATA_EXTRACTOR_TEST(null) ?
END;

I am open to any advices. Note: I have only records about 8/31/2021 so I have already tried to change value -1 to -2 in first line for testing but didn't insert to GPU_INV_TEST table. My script of procedure is below, thank you from now.

    create or replace procedure GPU_DATA_EXTRACTOR_TEST(pid_billdate DATE DEFAULT LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1))) is
c_limit   CONSTANT PLS_INTEGER DEFAULT 10000;

CURSOR c1 IS
SELECT DISTINCT intl_prod_id
      FROM apld_bill_rt abr,
           acct_bill ab
      WHERE abr.CHRG_TP = 'INSTALLMENT'
          AND abr.TAX_CATG_ID = 'NOTAX'
          AND abr.acct_bill_id = ab.acct_bill_id
          AND ab.bill_date = pid_billdate;

TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER;
l_prod_ids   prod_ids_t;
begin

   execute immediate 'truncate table GPU_INV_TEST';

  OPEN c1;

  LOOP
     FETCH c1 BULK COLLECT INTO l_prod_ids LIMIT c_limit;
     
     EXIT WHEN l_prod_ids.COUNT = 0;

    FORALL indx IN 1 .. l_prod_ids.COUNT
    
      INSERT INTO GPU_INV_TEST
         SELECT AB.ACCT_BILL_ID,
                AB.BILL_NO,
                AB.INV_ID,
                AB.BILL_DATE,
                ba2.bill_acct_id,
                ba1.bill_acct_id parent_bill_acct_id,
                AB.DUE_DATE,
                PG.CMPG_ID,
                ABR.NET_AMT,
                AB.DUE_AMT,
                P.PROD_NUM,
                pds.DST_ID,
                ABR.DESCR,
                p.intl_prod_id
           FROM apld_bill_rt abr,
                acct_bill ab,
                prod p,
                FCBSADM.PROD_DST pds,
                bill_acct_prod bap,
                bill_acct ba1,
                bill_acct ba2,
                prod_cmpg pg
          WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
                AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
                AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
                AND ba2.intl_bill_acct_id = bap.intl_bill_acct_id
                AND bap.intl_prod_id = abr.intl_prod_id
                AND ABR.CHRG_TP = 'INSTALLMENT'
                AND bap.intl_prod_id = pds.intl_prod_id
                AND bap.intl_prod_id = p.intl_prod_id
                AND p.intl_prod_id = pg.intl_prod_id(+)
                AND ABR.intl_prod_id = l_prod_ids(indx)
UNION
    SELECT AB.ACCT_BILL_ID,
                AB.BILL_NO,
                AB.INV_ID,
                AB.BILL_DATE,
                ba1.bill_acct_id,
                ba1.bill_acct_id parent_bill_acct_id,
                AB.DUE_DATE,
                PG.CMPG_ID,
                ABR.NET_AMT,
                AB.DUE_AMT,
                P.PROD_NUM,
                pds.DST_ID,
                ABR.DESCR,
                p.intl_prod_id
           FROM apld_bill_rt abr,
                acct_bill ab,
                prod p,
                FCBSADM.PROD_DST pds,
                bill_acct_prod bap,
                bill_acct ba1,
                prod_cmpg pg
          WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
                AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
                --AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
                AND ba1.intl_bill_acct_id = bap.intl_bill_acct_id
                AND bap.intl_prod_id = abr.intl_prod_id
                AND ABR.CHRG_TP = 'INSTALLMENT'
                AND bap.intl_prod_id = pds.intl_prod_id
                AND bap.intl_prod_id = p.intl_prod_id
                AND p.intl_prod_id = pg.intl_prod_id(+)
                AND ABR.intl_prod_id = l_prod_ids(indx);

      
    COMMIT;  
    
    END LOOP;
   CLOSE c1;
end;

score 1 · Accepted Answer · answered Oct 06 '21 at 07:42

1

Default value is correctly calculated:

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> SELECT LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE), -1)) result FROM DUAL;

RESULT
----------
30.09.2021

SQL>

The only place where pid_billdate is used is in cursor's select statement:

   CURSOR c1 IS
      SELECT DISTINCT intl_prod_id
        FROM apld_bill_rt abr, acct_bill ab
       WHERE     abr.CHRG_TP = 'INSTALLMENT'
             AND abr.TAX_CATG_ID = 'NOTAX'
             AND abr.acct_bill_id = ab.acct_bill_id
             AND ab.bill_date = pid_billdate;          --> here

As you said that it

didn't worked out properly

I presume that no rows were inserted into gpu_inv_test table. There are two possibilities:

either cursor's select didn't return anything (so loop didn't execute at all)
selects in forall didn't return anything (so, although there are some rows fetched by the cursor, selects didn't return any rows to be inserted into the table.

We can't really test it as we don't have your tables (and you used bunch of them in FROM clauses), so it is you who should test it.

As of your second question: both options are OK.

answered Oct 06 '21 at 07:42

Littlefoot

131,892
15
35
57

Hello, this procedure is working if I test it with-- begin GPU_DATA_EXTRACTOR_TEST(to_date('31/08/2021','DD/MM/YYYY')); end;-- script. So it doesnt work only for this null option case. – vitaminJava Oct 06 '21 at 07:45
Can we add an IF control? I tried but couldn't successfull because of syntax. I am newbie. ---IF pid_billdate IS NULL THEN USE LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), -1)) = pid_billdate; END IF; --- – vitaminJava Oct 06 '21 at 07:46
As I said: did you check whether cursor's SELECT returns any rows for 30.09.2021? As of IF: you don't really need it. NVL would do, e.g. `where ab.bill_date = nvl(pid_billdate, last_day(add_months(trunc(sysdate), -1)))`. If you really, really want to use IF, then `if pid_billdate is null then l_billdate := last_day(add_months(trunc(sysdate), -1)); end if;` but it means that you'll have to have a local variable which will be used instead of pid_billdate (I named it L_BILLDATE). – Littlefoot Oct 06 '21 at 07:58
nvl solution is worked, thank you. – vitaminJava Oct 06 '21 at 08:12

Executing date field with null in plsql

1 Answers1