BusinessObjects Topics

Search This Blog

BusinessObjects Topics
Showing posts with label PLSQL. Show all posts
Showing posts with label PLSQL. Show all posts

Wednesday, November 18, 2009

Working with REF CURSOR in PL/SQL

...

Read more »

Monday, October 19, 2009

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 »

Tuesday, November 11, 2008

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 »

Tags

Oracle Data warehouse Business Objects Business Objects XI R2 Business Objects XI 3 PLSQL BOBJ BI News Business Objects FAQs bo DataModeling Concepts Databases Server Administration BOCP-BOE Business Intelligence (BI) MS SQL Sever Database Universe Designer Certification Designer REPOSITORY SAP BI SAP Education Supervisor BO Reporter/ functions BO XI 4.0 BOXI 3.1 Business Objects Designer Downloads Fan / Chasm Trap Functions Integration and Certification Loops MS SQL Sever Administration OLAP QaaWS SAP Business Objects SAP BusinessObjects Business Intelligence 4.0 Star Schema Universe Design Web Intelligence WebIntelligence