Flashback Query – The SQL statement in distress
Accidentally, important data was deleted. Of course not in a test system but during operation. The pulse and blood pressure rises. But do not worry – it is not always necessary to back up...
Category: Education
SQL Formula (Math Functions)
If you want to calculate a value with a function which is stored in the table (row or column) you can use the follwing code to execute the math formula. Replace placeholders with the...
Autonomous Transactions
PL/SQL normally treads the whole modification as a single transaction. If you want to have some part of the code as seperate transaction, you can use the functionallity of autonomous transactions. The statement PRAGMA...
SQL Aggregation
Aggregation Cube If you have dimensions, you can use the cube function. Imagine you have the dimensions time, state and product. When you use the cube function, you can get an insight of these...
APEX Office Print (AOP) – Problems Excel Text Wrap, Line Break
Problem An Excel template was created (with the variables according to AOP) including line wrapping (text wrap, line break). During the subsequent document creation, however, the line break is not taken into account (not...
Write protection of tables
Write protection can be applied to tables. This prevents DDL (Data Definition Language) and DML (Data Manipulation Language) changes during maintenance mode. The statement: ALTER TABLE tablename READ ONLY; — nur mehr lesbar ALTER...
SQL Developer – Difference Snippet VS Code Template
In principle, Snippet and Code Template serve the same purpose: Previously created code is inserted at the desired location. The only difference between the two tools is the paste option. Snippets require the Snippets...
SQL IN List – Multi-value comparison IN List
SQL IN list SQL IN Statement (IN lists) can only process and validate 1000 values. Everything about it returns an error. Here’s an example to demonstrate an IN list. Here only employees and workers...
Difference between SQL NVL and NVL2
NVL VS NVL2 The difference between the SQL functions NVL and NVL2 is, that when using the function NVL2 you can set different values for NULL and NOT NULL. NVL only checks and analyzes...
SQL Default Value if no row is returned
Sometimes default values are necassary. Normally you can use NVL or NVL2 when you get NULL values as return value. But it is also possible, that no row is returned. In this case, NVL...
