Working with REF CURSOR in PL/SQL

...

Read more »

Avoid overlapping months and years

Useful way to group data by week to avoid the quandary of weeks overlapping months and years.CREATE OR REPLACE PROCEDURE weekly_proc IS CURSOR x_cur IS SELECT DISTINCT SUBSTR(TO_CHAR(date1),4,3) m, SUBSTR(TO_CHAR(date1),1,2) w, COUNT (*) cntFROM cpad_errorsGROUP BY SUBSTR(TO_CHAR(date1),4,3), SUBSTR(TO_CHAR(date1),1,2); x_rec x_cur%ROWTYPE; week_var NUMBER; BEGIN EXECUTE IMMEDIATE 'truncate table week_test'; OPEN x_cur;...

Read more »

%TYPE vs %ROWTYPE

Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database.The %TYPE and %ROWTYPE constructs provide data independence, reduce maintenance costs, and allows programs to adapt as the database changes-- %TYPE is used to declare a field with the same type as -- that of...

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 »

Oracle Learning - 9

Oracle/PLSQL: IS NULLIn other languages, a null value is found using the = null syntax. However in PLSQL to check if a value is null, you must use the "IS NULL" syntax.To check for equality on a null value, you must use "IS NULL".For example,IF Lvalue IS NULL then.END IF;If Lvalue contains a null value, the "IF" expression will evaluate to TRUE.You can also use "IS NULL" in an SQL statement. For example:select * from supplierswhere...

Read more »