Oracle procedure passing NULL date in parameter

BeefStu

I have a procedure that takes in a start_date. I'm trying to modify the procedure to set a default end_date based on the start_date if an end_date isn't passed into the procedure. I'm getting an error (see below). 

ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to 'XXX'

Is there a way to get around this problem? Below is my TEST CASE, which also produces the error. I'm testing on live SQL so our environments can be the same.

Thanks in advance for your expertise and to all who answer.

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

Create table employees(
          employee_id NUMBER(6), 
          first_name VARCHAR2(20),
          last_name VARCHAR2(20),
         card_num VARCHAR2(10),
          work_days VARCHAR2(7)
       );

ALTER TABLE employees
        ADD (
          CONSTRAINT employees_pk PRIMARY KEY (employee_id)
              );

        INSERT INTO employees (
         employee_id,
         first_name, 
         last_name,
         card_num,
         work_days
        )
        WITH names AS   ( 
          SELECT 1, 'John',     'Doe',      'D564311','YYYYYNN' FROM dual UNION ALL
          SELECT 2, 'Justin',     'Case',      'C224311','YYYYYNN' FROM dual UNION ALL
        SELECT 3, 'Mike',     'Jones',      'J288811','YYYYYNN' FROM dual UNION ALL
         SELECT 4, 'Jane',     'Smith',      'S564661','YYYYYNN' FROM dual 
       ) SELECT * FROM names; 
    CREATE TABLE locations AS
    SELECT level AS location_id,
       'Door ' || level AS location_name,

    CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'A' 
            WHEN 2 THEN 'T' 
            WHEN 3 THEN 'T' 
         END AS location_type

    FROM   dual
    CONNECT BY level <= 5;


     ALTER TABLE locations 
         ADD ( CONSTRAINT locations_pk
       PRIMARY KEY (location_id));

create table access_history(
      seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
       employee_id NUMBER(6), 
       card_num varchar2(10),
       location_id number(4),
       access_date date,
       processed NUMBER(1) default 0
    );
CREATE OR REPLACE PROCEDURE XXX
 (
  i_start_date IN DATE,
  i_end_date  IN DATE DEFAULT NULL, 
  i_interval         IN PLS_INTEGER DEFAULT 10
) AS 
  l_end_date DATE;
BEGIN
  
SELECT nvl(i_end_date, TRUNC(i_start_date) + 1) INTO l_end_date FROM DUAL;

INSERT INTO access_history (employee_id, card_num, location_id, access_date)
WITH date_rows ( start_date, end_date ) AS (
 SELECT i_start_date,
                l_end_date
 FROM   DUAL
UNION ALL
 SELECT start_date + 
NUMTODSINTERVAL(i_interval, 'MINUTE'),
        end_date
 FROM   date_rows
 WHERE  start_date +
NUMTODSINTERVAL(i_interval, 'MINUTE') < end_date
)
SELECT     e.employee_id
,        e.card_num
,       l.location_id
,       d.start_date
FROM       employees e
CROSS JOIN locations l
CROSS JOIN date_rows d;
END;
/


EXEC XXX
(timestamp '2021-08-21 20:37:12', NULL);

Hub Tijhuis

Put the exec on 1 line or use BEGIN END;

BEGIN

 XXX(timestamp '2021-08-21 20:37:12', NULL);

END;

EXEC XXX (timestamp '2021-08-21 20:37:12', NULL);

EXEC XXX (timestamp '2021-08-21 20:37:12', TO_DATE( NULL));

BeefStu

@Hub Tijhuis thanks that worked. I didn't notice that. Problem solved

Frank Kulash

Hi, @BeefStu

... or put a single hyphen at the end of the first line, to indicate that the first line is not a complete statement, but continues on the following line.

EXEC XXX -
(timestamp '2021-08-21 20:37:12', NULL);

Frank Kulash

By the way: We don't need no stinkin' dual table here:

SELECT nvl(i_end_date, TRUNC(i_start_date) + 1) INTO l_end_date FROM DUAL;

Instead, simply say:

l_end_date := NVL (i_end_date, TRUNC (i_start_date) + 1);

Almost all of the single-row SQL functions (including NVL and TRUNC) exist in PL/SQL, too.

JackK

If problem is solved, mark your question as "solved", please.