It has been an exhilarating day on Twitter going through all the buzz on Oracle Database Free - Developer Release. I have revisted the video, Oracle Database application development: what's new, what's next? | CloudWorld 2022 - YouTube by Gerald Venzl and have listed down few things for my better understanding and to try them out.
Bye Bye Dual?! (SELECT Without FROM):
This allows you to write a query without specifying a table or view name, which can be useful for testing expressions or generating data.
In this syntax, you can omit the FROM clause in a SELECT statement to create a single-row result set with constant values.
For example, SELECT 1 + 2 AS result; will return 3 as the result.
Annotations:
These are name-value pairs or simply a name and are a way of adding extra information to database objects, such as tables, views, columns, etc. to simplify development and improve data quality.
You can use annotations to provide metadata such as the name, description, purpose, or usage of an object.
For example, you can use an annotation to specify the display name, format, or validation rule for a column. These help to make sense of the Data Model to the users.
Schema-Level Privileges:
These privileges allow you to control access to database objects at the schema level. This means you can grant or revoke privileges on all objects in a schema at once, rather than having to grant or revoke privileges on each object individually or at the database level.
For example,
GRANT SELECT ON SCHEMA hr TO user1;
The command will grant user1 the privilege to select from any table or view in the hr schema. And also, the new tables that may get added to HR Schema at a later stage will be automatically accessed by the user.
User-friendly Error Messages:
The error messages will be providing more details and suggestions for resolving common errors which can help developers better understand and fix errors quickly.
For example, ORA-00979: not a GROUP BY expression does not tell you which column is missed out, but now the database will also show which column might be missed out as ORA-00979: <ColumnName> does not match a GROUP BY expression which can help you to get to do the correction quickly.
GROUP BY Column Alias or Position:
This feature allows you to use column aliases or positions in the GROUP BY clause instead of repeating the entire expression from the SELECT list. This can make queries easier to read and write.
For example, the below command will group by the DepartmentName column using the alias DName.
SELECT DepartmentName AS DName, COUNT(*) AS emp_count FROM emp
GROUP BY DName;
And you could do the same thing with positions as 1, 2, 3, etc.
Extension up to 4096 Columns:
This increases the maximum number of columns that a table can have from 1000 to 4096. This would be really handy with the denormalized tables with very wide number of columns.
IF [NOT] EXISTS:
This syntax allows you to check for the existence of an object before performing an action on it, such as creating or dropping a table or index. This can help prevent errors and simplify code.
For example, this command will create a table only if it does not exist already.
CREATE TABLE IF NOT EXISTS new_table (id NUMBER);
Similarly, you can make use of IF EXISTS with DROP commands.
Developer Role:
This is a special predefined role that grants common privileges (such as the ability to create tables, procedures, and sequences) for developing applications using Oracle Database instead of issuing individual privileges.
Returning Clause with OLD, NEW:
This allows you to return the old and new values of the affected rows after an INSERT, UPDATE, DELETE statements. For example,
UPDATE emp SET sal = sal * 1.1 RETURNING empno, NEW.sal AS new_sal, OLD.sal AS old_sal;
will return the employee number, new salary, and old salary of the updated rows.
UPDATE via JOIN:
This feature allows you to update rows in one table based on data from another table, using a JOIN statement.
For example, the below command will update the salaries of the employees in the sales department using a join with the dept table.
UPDATE emp e SET e.sal = e.sal * 1.1 FROM dept d WHERE e.deptno = d.deptno AND d.dname = 'SALES';
Table Value Constructor:
This allows you to specify multiple rows of values as a single expression.
For example, the below command will insert three rows into the emp table using a table value constructor.
INSERT INTO emp (empno, ename) VALUES (1001, 'Alice'), (1002, 'Bob'), (1003, 'Charlie');
SQL Domains:
These are user-defined data types that can be used to enforce constraints and validations on columns.
For example, the below command will create a domain for email addresses and check that they contain an @ sign. Later, you can use this domain in the CREATE TABLE command along with the column specifications.
CREATE DOMAIN email AS VARCHAR2(100) CHECK (value LIKE '%@%');
Boolean Data Type:
The much-awaited data type that can store TRUE or FALSE values.
For example, this command will create a table with a Boolean column.
CREATE TABLE flag_table (id NUMBER, flag BOOLEAN);
That is all for now folks!