Category: Oracle-Tips and Tricks

Oracle -PL-SQL-Trigger – Old and New Column Values

Oracle, Oracle-Tips and Tricks, Oracle-Triggers, Varinder Sandhu

Within a trigger body of a row trigger, two correlation names exist for every column of the table being modified: one for the old column value, and one for the new column value. Depending on the type of triggering statement, certain correlation names might not have any meaning.  A trigger fired by an INSERT statement has meaningful access to new column values only. Because the row is being created by the INSERT, the old values are null. A trigger fired

» Read more
, , ,

Oracle – PL-SQL – ORA-04091: table name is mutating, trigger/function may not see it

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

Error: ORA-04091: table name is mutating, trigger/function may not see it Cause: Mutating trigger error occurs when a trigger references the table that owns the trigger. So the result is we receive the above error message. Solution: We cannot refer the table in the trigger that owns the trigger. If you face the similar condition or scenario then don’t use the trigger for that purpose. Use the alternative approach to handle this situation you can refer the Mutating Table Exceptions

» Read more
, ,

Oracle – PL-SQL – For Loop

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

A LOOP statement executes a sequence of statements multiple times. PL/SQL provides these loop statements: Numeric FOR_LOOP loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP. The range is evaluated when the FOR loop is first entered and is never re-evaluated. The loop body is executed once for each integer in the range defined by lower_boun..upper_bound. After each iteration, the loop index is incremented.

» Read more
,

Oracle – PLSQL – NVL Function

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

NVL lets you replace null with a string in the results of a query. Syntax NVL (string1, replace_with) If string1 is null, then NVL returns replace_with. If string1 is not null, then NVL returns string1. Note: The arguments string1and replace_with can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error.

» Read more
, ,

Oracle – SQL Developer – Anonymous Block Completed

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

Oracle SQL Developer does not display the output of DBMS_OUTPUT.PUT_LINE, it gives “anonymous block completed” in the script output tab Some time we are confused why this is giving message “anonymous block completed” To see the output of script, we need to enable DBMS_OUTPUT by clicking on plus symbol (see below in the snapshot) then we can see the output of script. Example   Note: Anonymous block An ANONYMOUS BLOCK is a PL/SQL program that has no name and not

» Read more
, ,
1 2 3