Home » SQL & PL/SQL » SQL & PL/SQL » Oracle procedure passing NULL in DATE parameters (19c)
Oracle procedure passing NULL in DATE parameters [message #684776] Wed, 18 August 2021 11:14 Go to next message
Unclefool
Messages: 82
Registered: August 2021
Member
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);

Re: Oracle procedure passing NULL in DATE parameters [message #684777 is a reply to message #684776] Wed, 18 August 2021 11:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
EXEC is SQL*Plus command so if you want to split it to multiple lines you need to put SQL*Plus line continuation character (minus sign). Or put complete command on a single line:

SQL> CREATE OR REPLACE PROCEDURE XXX
  2   (
  3    i_start_date IN DATE,
  4    i_end_date  IN DATE DEFAULT NULL,
  5    i_interval         IN PLS_INTEGER DEFAULT 10
  6  )
  7  IS
  8  BEGIN
  9      NULL;
 10  END;
 11  /

Procedure created.

SQL> EXEC XXX
BEGIN XXX; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'XXX'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> (timestamp '2021-08-21 20:37:12', NULL);
(timestamp '2021-08-21 20:37:12', NULL)
 *
ERROR at line 1:
ORA-00928: missing SELECT keyword


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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL>
SY.
Re: Oracle procedure passing NULL in DATE parameters [message #684778 is a reply to message #684777] Wed, 18 August 2021 13:18 Go to previous message
Unclefool
Messages: 82
Registered: August 2021
Member
Thanks I should have noticed that. Time to go get my eyes checked
Previous Topic: Creating time rows that doesn't cross midnight
Next Topic: Oracle cte multiple rows (3 merged)
Goto Forum:
  


Current Time: Thu Mar 28 18:26:16 CDT 2024