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.

This error is thrown because there is a limit of 5 open HTTP connections per session in the Oracle database server.

Usually, the application intends to open one connection at a time. However, this error might occur due to the application not closing the connection properly after being finished.

So, it’s recommended to review your code and to make sure you are ending the response after you have finished as well as in the exception part also, ending the request like the below code.

Catch too many requests exception to handle it by closing the request and response:

EXCEPTION
WHEN UTL_HTTP.TOO_MANY_REQUESTS THEN
UTL_HTTP.END_REQUEST(req);
UTL_HTTP.END_RESPONSE(resp);

Ending the request at the end of your program

UTL_HTTP.END_REQUEST(req);

There is no global variable in Java. However, we have a static keyword that we can use to define a global variable by doing something like the below:

public class A {
public static String var1 = "Oraask";
public static int var2 = 37;
}

A.var1;

A.var2;

But be careful because if this class gets unloaded, you will end up with an undefined null error that is difficult to catch.

Find below the query to find the free space of a tablespace:

SELECT DFQ.TABLESPACE_NAME "Tablespace Name"
,DFQ.TOTALSPACE "Total Size MB"
, (DFQ.TOTALSPACE - DSQ.TOTALUSEDSPACE) "Free Space MB"
,ROUND (100 * ( (DFQ.TOTALSPACE - DSQ.TOTALUSEDSPACE) / DFQ.TOTALSPACE)) || '%' "Free Space %"
FROM (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / 1048576) TOTALSPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) DFQ
,(SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024)) TOTALUSEDSPACE
FROM DBA_SEGMENTS
GROUP BY TABLESPACE_NAME) DSQ
WHERE DFQ.TABLESPACE_NAME = DSQ.TABLESPACE_NAME(+);

Also, you can refer to this article to find a handful of detail about the tablespace:

Check Tablespace Usage in Oracle