clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36307 in the last week).

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';
 hidden batch(es)


CREATE OR REPLACE TYPE access_history_obj AS OBJECT( employee_id NUMBER(6), first_name VARCHAR2(20), last_name VARCHAR2(20), card_num VARCHAR2(10), location_id NUMBER(6), location_name VARCHAR2(30), access_date DATE );
 hidden batch(es)


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


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;
4 rows affected
 hidden batch(es)


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;
5 rows affected
 hidden batch(es)


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 );
 hidden batch(es)


INSERT INTO access_history( employee_id, card_num, location_id, access_date ) WITH rws AS ( SELECT 1,'J11111',2,TO_DATE('2021/08/15 08:30:25', 'YYYY/MM/DD HH24:MI:SS') FROM dual UNION ALL SELECT 1,'J11111',3,TO_DATE('2021/08/15 18:30:35', 'YYYY/MM/DD HH24:MI:SS') FROM dual UNION ALL SELECT 2,'E11111',2,TO_DATE('2021/08/15 11:20:35', 'YYYY/MM/DD HH24:MI:SS') FROM dual) SELECT * FROM rws;
3 rows affected
 hidden batch(es)


CREATE PROCEDURE LAST_EMPLOYEE_HISTORY( i_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE, i_rws IN PLS_INTEGER DEFAULT 20, o_cursor OUT SYS_REFCURSOR ) AS BEGIN OPEN o_cursor FOR SELECT e.employee_id, e.first_name, e.last_name, e.card_num, l.location_id, l.location_name, a.access_date FROM employees e INNER JOIN access_history a ON a.employee_id = e.employee_id INNER JOIN locations l ON l.location_id = a.location_id WHERE e.employee_id = i_employee_id ORDER BY access_date DESC FETCH FIRST i_rws ROWS ONLY; END; /
 hidden batch(es)


SELECT * FROM USER_ERRORS;
NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTE MESSAGE_NUMBER
 hidden batch(es)


DECLARE cur SYS_REFCURSOR; v_eid EMPLOYEES.EMPLOYEE_ID%TYPE; v_fname EMPLOYEES.FIRST_NAME%TYPE; v_lname EMPLOYEES.LAST_NAME%TYPE; v_card EMPLOYEES.CARD_NUM%TYPE; v_lid LOCATIONS.LOCATION_ID%TYPE; v_loc LOCATIONS.LOCATION_NAME%TYPE; v_dt ACCESS_HISTORY.ACCESS_DATE%TYPE; BEGIN LAST_EMPLOYEE_HISTORY(1, 1, cur); LOOP FETCH cur INTO v_eid, v_fname, v_lname, v_card, v_lid, v_loc, v_dt; EXIT WHEN cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_fname || ' ' || v_lname || ': ' || v_loc || ' @ ' || v_dt); END LOOP; END; /
1 rows affected dbms_output: John Doe: Door 3 @ 2021-08-15T18:30:35
 hidden batch(es)