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 |
|
Unclefool
Messages: 85 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 |
Solomon Yakobson
Messages: 3275 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.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:40:49 CDT 2024
|