use case data

View All Public Scripts Login to Run Script

Script Name use case data
Visibility Unlisted - anyone with the share link can access
Description use case data
Area SQL General
Contributor Oracle
Created Thursday April 10, 2025

Statement 1
```
DROP TABLE test PURGE
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

CREATE TABLE test
(
	id 				VARCHAR2(256) NOT NULL,
	t_id   			VARCHAR2(256) NOT NULL,
	code  			VARCHAR2(128) NOT NULL,
	status 			VARCHAR2(64) NOT NULL,
	upd_date		TIMESTAMP(6),
	CONSTRAINT test_id_pk PRIMARY KEY(id)
)

Table created.

Statement 3

INSERT INTO test VALUES(SYS_GUID(), 't1', 'Sync', 'Enable', SYSTIMESTAMP-40)

1 row(s) inserted.

Statement 4

INSERT INTO test VALUES(SYS_GUID(), 't1', 'Sync', 'Disable', SYSTIMESTAMP-30)

1 row(s) inserted.

Statement 5

INSERT INTO test VALUES(SYS_GUID(), 't1', 'Sync', 'Enable', SYSTIMESTAMP-30)

1 row(s) inserted.

Statement 6

INSERT INTO test VALUES(SYS_GUID(), 't1', 'Sync', 'Disable', SYSTIMESTAMP-10)

1 row(s) inserted.

Statement 7

INSERT INTO test VALUES(SYS_GUID(), 't1', 'Lookup', 'Enable', SYSTIMESTAMP-40)

1 row(s) inserted.

Statement 8

INSERT INTO test VALUES(SYS_GUID(), 't1', 'Lookup', 'Disable', SYSTIMESTAMP-20)

1 row(s) inserted.

Statement 9

INSERT INTO test VALUES(SYS_GUID(), 't1', 'Lookup', 'Enable', SYSTIMESTAMP-30)

1 row(s) inserted.

Statement 10

INSERT INTO test VALUES(SYS_GUID(), 't1', 'Lookup', 'Disable', SYSTIMESTAMP-10)

1 row(s) inserted.

Statement 11

INSERT INTO test VALUES(SYS_GUID(), 't1', 'Disc', 'Disable', SYSTIMESTAMP-10)

1 row(s) inserted.

Statement 12

INSERT INTO test VALUES(SYS_GUID(), 't2', 'Sync', 'Enable', SYSTIMESTAMP-40)

1 row(s) inserted.

Statement 13

INSERT INTO test VALUES(SYS_GUID(), 't2', 'Sync', 'Disable', SYSTIMESTAMP-30)

1 row(s) inserted.

Statement 14

INSERT INTO test VALUES(SYS_GUID(), 't2', 'Search', 'Enable', SYSTIMESTAMP-30)

1 row(s) inserted.

Statement 15

INSERT INTO test VALUES(SYS_GUID(), 't2', 'Search', 'Disable', SYSTIMESTAMP-10)

1 row(s) inserted.

Statement 16

INSERT INTO test VALUES(SYS_GUID(), 't2', 'Lookup', 'Enable', SYSTIMESTAMP-40)

1 row(s) inserted.

Statement 17

INSERT INTO test VALUES(SYS_GUID(), 't2', 'Lookup', 'Disable', SYSTIMESTAMP-20)

1 row(s) inserted.

Statement 18

INSERT INTO test VALUES(SYS_GUID(), 't2', 'Lookup', 'Enable', SYSTIMESTAMP-30)

1 row(s) inserted.

Statement 19

INSERT INTO test VALUES(SYS_GUID(), 't2', 'Lookup', 'Disable', SYSTIMESTAMP-10)

1 row(s) inserted.

Statement 20

INSERT INTO test VALUES(SYS_GUID(), 't2', 'Disc', 'Enable', SYSTIMESTAMP-10)

1 row(s) inserted.

Statement 21

SELECT * FROM test ORDER BY t_id, code


ID T_ID CODE STATUS UPD_DATE
32700ABA8AFD410EE063410EA8C07643 t1 Disc Disable 31-MAR-25 04.43.55.000000 PM
32700ABA8AFB410EE063410EA8C07643 t1 Lookup Disable 31-MAR-25 04.43.55.000000 PM
32700ABA8AF9410EE063410EA8C07643 t1 Lookup Enable 11-MAR-25 04.43.55.000000 PM
32700ABA8AF7410EE063410EA8C07643 t1 Lookup Disable 21-MAR-25 04.43.55.000000 PM
32700ABA8AF5410EE063410EA8C07643 t1 Lookup Enable 01-MAR-25 04.43.55.000000 PM
32700ABA8AF3410EE063410EA8C07643 t1 Sync Disable 31-MAR-25 04.43.55.000000 PM
32700ABA8AF1410EE063410EA8C07643 t1 Sync Enable 11-MAR-25 04.43.55.000000 PM
32700ABA8AED410EE063410EA8C07643 t1 Sync Enable 01-MAR-25 04.43.55.000000 PM
32700ABA8AEF410EE063410EA8C07643 t1 Sync Disable 11-MAR-25 04.43.55.000000 PM
32700ABA8B0F410EE063410EA8C07643 t2 Disc Enable 31-MAR-25 04.43.55.000000 PM
32700ABA8B07410EE063410EA8C07643 t2 Lookup Enable 01-MAR-25 04.43.55.000000 PM
32700ABA8B0D410EE063410EA8C07643 t2 Lookup Disable 31-MAR-25 04.43.55.000000 PM
32700ABA8B0B410EE063410EA8C07643 t2 Lookup Enable 11-MAR-25 04.43.55.000000 PM
32700ABA8B09410EE063410EA8C07643 t2 Lookup Disable 21-MAR-25 04.43.55.000000 PM
32700ABA8B05410EE063410EA8C07643 t2 Search Disable 31-MAR-25 04.43.55.000000 PM
32700ABA8B03410EE063410EA8C07643 t2 Search Enable 11-MAR-25 04.43.55.000000 PM
32700ABA8AFF410EE063410EA8C07643 t2 Sync Enable 01-MAR-25 04.43.55.000000 PM
32700ABA8B01410EE063410EA8C07643 t2 Sync Disable 11-MAR-25 04.43.55.000000 PM

18 rows selected.

ID	T_ID	CODE	STATUS	UPD_DATE
32700ABA8AFD410EE063410EA8C07643	t1	Disc	Disable	31-MAR-25 04.43.55.000000 PM
32700ABA8AFB410EE063410EA8C07643	t1	Lookup	Disable	31-MAR-25 04.43.55.000000 PM
32700ABA8AF9410EE063410EA8C07643	t1	Lookup	Enable	11-MAR-25 04.43.55.000000 PM
32700ABA8AF7410EE063410EA8C07643	t1	Lookup	Disable	21-MAR-25 04.43.55.000000 PM
32700ABA8AF5410EE063410EA8C07643	t1	Lookup	Enable	01-MAR-25 04.43.55.000000 PM
32700ABA8AF3410EE063410EA8C07643	t1	Sync	Disable	31-MAR-25 04.43.55.000000 PM
32700ABA8AF1410EE063410EA8C07643	t1	Sync	Enable	11-MAR-25 04.43.55.000000 PM
32700ABA8AED410EE063410EA8C07643	t1	Sync	Enable	01-MAR-25 04.43.55.000000 PM
32700ABA8AEF410EE063410EA8C07643	t1	Sync	Disable	11-MAR-25 04.43.55.000000 PM
32700ABA8B0F410EE063410EA8C07643	t2	Disc	Enable	31-MAR-25 04.43.55.000000 PM
32700ABA8B07410EE063410EA8C07643	t2	Lookup	Enable	01-MAR-25 04.43.55.000000 PM
32700ABA8B0D410EE063410EA8C07643	t2	Lookup	Disable	31-MAR-25 04.43.55.000000 PM
32700ABA8B0B410EE063410EA8C07643	t2	Lookup	Enable	11-MAR-25 04.43.55.000000 PM
32700ABA8B09410EE063410EA8C07643	t2	Lookup	Disable	21-MAR-25 04.43.55.000000 PM
32700ABA8B05410EE063410EA8C07643	t2	Search	Disable	31-MAR-25 04.43.55.000000 PM
32700ABA8B03410EE063410EA8C07643	t2	Search	Enable	11-MAR-25 04.43.55.000000 PM
32700ABA8AFF410EE063410EA8C07643	t2	Sync	Enable	01-MAR-25 04.43.55.000000 PM
32700ABA8B01410EE063410EA8C07643	t2	Sync	Disable	11-MAR-25 04.43.55.000000 PM

Statement 22

SELECT COUNT(1), t_id, code, status
FROM test
WHERE status in ('Enable','Disable')
AND TRUNC(upd_date) <= TO_DATE('20250404', 'YYYYMMDD')
AND code IN ('Lookup','Sync','Search')
GROUP BY t_id, code, status
ORDER BY t_id


COUNT(1) T_ID CODE STATUS
2 t1 Lookup Disable
2 t1 Lookup Enable
2 t1 Sync Disable
2 t1 Sync Enable
2 t2 Lookup Disable
2 t2 Lookup Enable
1 t2 Search Disable
1 t2 Search Enable
1 t2 Sync Disable
1 t2 Sync Enable

10 rows selected.

COUNT(1)	T_ID	CODE	STATUS
2	t1	Lookup	Disable
2	t1	Lookup	Enable
2	t1	Sync	Disable
2	t1	Sync	Enable
2	t2	Lookup	Disable
2	t2	Lookup	Enable
1	t2	Search	Disable
1	t2	Search	Enable
1	t2	Sync	Disable
1	t2	Sync	Enable

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database