Working with REF CURSOR in PL/SQL

...

Read more »

Oracle By Example (OBE)

Oracle By Example (OBE)The Oracle by Example (OBE) series provides hands-on, step-by-step instructions on how to implement various technology solutions to business problems. OBE solutions are built for practical real-world situations, allowing you to gain valuable hands-on experience as well as use the presented solutions as the foundation for production implementation, dramatically reducing time to deployment. In addition to...

Read more »

Oracle Learning - 13

Oracle/PLSQL: Set TransactionThere are three transaction control functions. These are:1. SET TRANSACTION READ ONLY;2. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;3. SET TRANSACTION USE ROLLBACK SEGMENT name;Oracle/PLSQL: Lock TableThe syntax for a Lock table is:LOCK TABLE tables IN lock_mode MODE [NOWAIT];Tables is a comma-delimited list of tables.Lock_mode is one of:ROW SHAREROW EXCLUSIVESHARE UPDATESHARESHARE ROW EXCLUSIVEEXCLUSIVE.NoWait...

Read more »

Oracle Learning - 12

Oracle/PLSQL: Sequences (Autonumber)In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.The syntax for a sequence is:CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value;For example:CREATE...

Read more »

Oracle Learning - 11

Oracle/PLSQL: FOR LoopThe syntax for the FOR Loop is:FOR loop_counter IN [REVERSE] lowest_number..highest_numberLOOP {.statements.}END LOOP;You would use a FOR Loop when you want to execute the loop body a fixed number of times.Let's take a look at an example.FOR Lcntr IN 1..20LOOP LCalc := Lcntr * 31;END LOOP;This example will loop 20 times. The counter will start at 1 and end at 20.The FOR Loop can also loop in reverse....

Read more »

Oracle Learning - 10

Oracle/PLSQL: Case StatementIn Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.The syntax for the case statement is:CASE expressionWHEN condition_1 THEN result_1WHEN condition_2 THEN result_2...WHEN condition_n THEN result_nELSE result ENDexpression is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)condition_1...

Read more »