Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Oracle procedure passing NULL date in parameter

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);
Answers
-
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));
-
@Hub Tijhuis thanks that worked. I didn't notice that. Problem solved
-
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);
-
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.
-
If problem is solved, mark your question as "solved", please.