PL SQL Code Snippets

05 October, 2016   

This code accumulates 100 units of part number 9999 from various storage bins

07 January, 2016   

The INNER JOIN keyword return rows when there is at least one match in both tables.

26 November, 2015   

Show the currently active processes.

09 August, 2015   

Handy routine to display the database's SGA statistics.

24 June, 2015   

Create a table with the results from a SELECT statement.

29 March, 2015   

When creating an index, the default method Oracle uses to sort the index is in ascending order (SORT). However, if the rows are already stored in the database in ascending order you can use the NOSORT clause to skip the sorting process. If neither SORT or NOSORT is provided, Oracle will use SORT as the clause.

You can define parameters with three types of parameters. 1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function. 2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the...

18 December, 2014   

Example code for locking table. when a user modifies data through a transaction, that data is locked by that transaction until the transaction is committed or rolled back.

07 December, 2014   

Note that hit ratio based tuning is not recommended.

This example code calculates the ratio between the X and Y columns of the RATIO table.

02 November, 2014   

The RETURNING clause was implemented as part of the Oracle 10g release and is used to return information about the effected rows after issuing Data Manipulation Language (DML) statements.

27 September, 2014   

How to measure the Buffer Cache Hit Ratio. Note that hit ratio based tuning is not recommended.

07 September, 2014   

The "WITH CHECK OPTION" is used to indicate that Oracle will prevent any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.

25 August, 2014   

The function NVL replaces null values with a stated value. In this example the rc.customer is assigned the number '0' if it is null.

12 July, 2014   

This block finds all employees whose monthly wages (salary plus commission) are higher than $2000.

28 June, 2014   

Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.

Oracle PL/SQL example for avoiding overlapping months and years

27 May, 2014   

PL/SQL Code for a searched CASE statement

15 January, 2014   

Basic definition for a cursor in MSSQL

30 October, 2013   

Find a key from a list of values​​. parameters: sbValues​​: List of Values 'A;B;C;D' sbToken: Key 'B' sbSeparator: ',' or any defined sbValues