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 the following OBE tutorials, you can also access more product training at the Oracle University Knowledge Center.

Expand the folder of the product area or component you are interested in.

Oracle by Example

Read more »

How to Add to the List of Existing Connections

Read more »

Interactive Viewing with BusinessObjects Web XI

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 (*) cnt
FROM cpad_errors
GROUP 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;

LOOP

FETCH x_cur INTO x_rec;
EXIT WHEN x_cur%notfound;

IF TO_NUMBER(x_rec.w) < 8
THEN week_var := 1;
ELSIF TO_NUMBER(x_rec.w) < 15 AND TO_NUMBER(x_rec.w) > 7
THEN week_var := 2;
ELSIF TO_NUMBER(x_rec.w) < 22 AND TO_NUMBER(x_rec.w) > 16
THEN week_var := 3;
ELSE week_var := 4;
END IF;


INSERT INTO week_test (WEEK_NUM, TTL, MNTH)
VALUES (week_var, x_rec.cnt, x_rec.m);

END LOOP;

CLOSE x_cur;

COMMIT;


END weekly_proc;


*********************************
SELECT mnth|| ' week '|| week_num, SUM(ttl)
FROM week_test
GROUP BY mnth|| ' week '|| week_num;
*********************************

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 a specified table's column:

DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/



-- %ROWTYPE is used to declare a record with the same types as
-- found in the specified database table, view or cursor:

DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/

Read more »