CTE stands for Common Table Expression. Another acronym is subquery factory; It’s simply a query that you can define in another query. It starts with WITH clause that lets you assign a name to a subquery block. Then you can reference this subquery block in multiple places within your main query by giving that query a name.

Oracle database traits that query name as either an inline view or as a temporary table that will be dropped automatically after the execution of your main query is finished.

Let’s take a simple example to showcase the use of CTE in the Oracle database

Let’s say we need to get the employees that are hired on the first day of each month in the current year.

WITH hiring_periods (period)
AS (SELECT to_date ('2006-01-01', 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date ('2006-02-01', 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date ('2006-03-01', 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date ('2006-04-01', 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date ('2006-05-01', 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date ('2006-06-01', 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date ('2006-07-01', 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date ('2006-08-01', 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date ('2006-09-01', 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date ('2006-10-01', 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date ('2006-11-01', 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date ('2006-12-01', 'yyyy-mm-dd') FROM dual)
SELECT e.first_name, e.last_name, e.hire_date
FROM employees e, hiring_periods hp
WHERE e.hire_date = hp.period;

Output:

| FIREST_NAME | LAST_NAME | HIRE_DATE
| ——————- | —————- | —————-|
| Samuel                 | McCain           | 01/07/2006   |

In the above query, we first start our query with a WITH clause to define a temporary table that has a date of the first day of each month and give that temp table or inline view a name which is “hiring_period” and then uses this name in the main query by joining it with the employee’s table using the hire date column.

To copy a table without copying its data, you can simply use this SQL statement:

CREATE TABLE xx_oraask_tbl_bkp AS SELECT * FROM xx_oraask_tbl WHERE 1=0;

But there is a limitation to using the above statement that you have to pay attention to:

The following database objects are not copied to the new version of your table

• Sequences
• Triggers
• Indexes
• Identity column
• Some constraints
• Partitioning

Or you can use another way to do this:

SELECT dbms_metadata.get_ddl( 'TABLE', 'xx_oraask_tbl' ) FROM DUAL;

In the above statement, you will get the DDL statement of a specified table, and then you can easily change the table name, the indexes, etc.

It’s not as simple as changing the ATTRIBUTE column size because there are a lot of dependencies that you need to pay attention to e.g.

• Changes to other tables that may be related to your table, like (interface tables).
• Check the form that is used in this ATTRIBUTE.
• Check code that may require a change like increasing or decreasing the field length like API.
• Check reports that use the same ATTRIBUTE.
• Check the value sets if any of them queried the same DFF ATTRIBUTE

That’s why it is not simple; moreover, DFF ATTRIBUTES default length of “150” characters is a coding standard used for most standard tables.