Programming Is Fun

control file autobackup to recover from the lost all control files -- oracle 10g

noreply@blogger.com (Anonymous) — Mon, 15 Oct 2012 20:06:00 +0000

Environment: Oracle 10gR2

Summary of the testing steps

1. First, configure RMAN to perform a control file autobackup:
2. Next, perform a backup with the control file autobackup enabled:
3. Next, simulate the missing control files by deleting all the control files.
4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount.
5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to
6. Next, restore the control file from backup:
7. Next, mount the database and begin to recover the database:
8. Finally, open the database with RESETLOGS option for normal operations:

Detailed steps:

1. First, configure RMAN to perform a control file autobackup:

RMAN> connect target
connected to target database: TOY10G (DBID=3330944552)
RMAN> configure controlfile autobackup on;
using target database control file instead of recovery catalog

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN>
RMAN> show all;
RMAN>

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_%t_%s_%p.dbf';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/snapcf_TOY10G.f';

RMAN>

2. Next, perform a backup with the control file autobackup enabled:

RMAN> run
2> {
3> backup database;
4> backup (archivelog all);
5> }

Starting backup at 06-JUL-2007 14:19:13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=35 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/system01.dbf

input datafile fno=00003 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/sysaux01.dbf

input datafile fno=00004 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/users01.dbf

input datafile fno=00005 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/indx01.dbf

input datafile fno=00002 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:15

channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:22

piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229154_17_1.dbf tag=TAG20070706T141914 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 06-JUL-2007 14:19:22
Starting backup at 06-JUL-2007 14:19:25
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=478 recid=1 stamp=627229166
channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:27
channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229166_18_1.dbf tag=TAG20070706T141926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 06-JUL-2007 14:19:28
Starting Control File Autobackup at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp comment=NONE
Finished Control File Autobackup at 06-JUL-2007 14:19:30

RMAN>

3. Next, simulate the missing control files by deleting all the control files. (The database will need to be shut down to perform this simulated failure.)

sys@TOY10G> select name from v$controlfile;

NAME
----------------------------------------------------------------------------------------------------
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf

after shutdown, do:

rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf

4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount:

idle> conn / as sysdba

Connected to an idle instance.

idle> startup nomount pfile='/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/pfile/initTOY10G.ora';

ORACLE instance started.

5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to, because the control file contains this information and failure causes the control file to be unavailable.

RMAN> connect target

connected to target database: TOY10G (not mounted)

RMAN> set dbid 3330944552

executing command: SET DBID

RMAN>

6. Next, restore the control file from backup:

RMAN> restore controlfile from autobackup;
Starting restore at 06-JUL-2007 14:35:11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=47 devtype=DISK

recovery area destination: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G

database name (or database unique name) used for search: TOY10G

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf

Finished restore at 06-JUL-2007 14:35:18

RMAN>

7. Next, mount the database and begin to recover the database:

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 06-JUL-2007 14:44:20
Starting implicit crosscheck backup at 06-JUL-2007 14:44:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 06-JUL-2007 14:44:22
Starting implicit crosscheck copy at 06-JUL-2007 14:44:22
using channel ORA_DISK_1
Crosschecked 5 objects
Finished implicit crosscheck copy at 06-JUL-2007 14:44:22

searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
File Name: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 479 is already on disk as file /ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log
archive log filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log thread=1 sequence=479
media recovery complete, elapsed time: 00:00:03
Finished recover at 06-JUL-2007 14:44:28

RMAN>

8. Finally, open the database with RESETLOGS option for normal operations:

RMAN> alter database open resetlogs;
database opened
RMAN>

Rename and drop table columns

noreply@blogger.com (Anonymous) — Sun, 14 Oct 2012 19:27:00 +0000

If I have a relatively big table and I want to modify a column from one type to another, i.e. number to varchar2(50), I may experience very long time. Note if I just change, for example, from varchar2(10) to varchar2(50), it will not be a problem, Oracle can finish in a split of second.This was what I faced last night during the code release. The column was acutally added in the previous release, but for some reason it has not been used and is containing no data. The problem was that I would run out of the maintenance window if I Ire just waiting for it to complete (obsevered it would take 4-5 hours from v$session_longops view).

In this situation, the capability of renaming a column name comes to help. I renamed the column with a suffix '_old', and added a new column with the desired data type and name. It completed in seconds.

The syntax looks like:

SQL> alter table myschema.mytab rename column MYCOL to MYCOL_old;
SQL> alter table myschema.mytab add MYCOL varchar2(50);

It is noted in the Oracle online doc that when you rename a column, Oracle updates associated data dictionary tables to ensure that function-based indexes and check constraints remain valid.

So now comes to the question what is the best way to drop those *_old columns? - There are actually 4 tables with 8 columns I addressed in this way.

Checking the online document again, there is a section in the Admin Guide (9i) that covers some options. I did not see I can do parallel drop of a column. I did notice there is an interesting option: checkpoint, which I may want to consider as our tables are very large. For example:

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

The doc describes:

" This option causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space."

Oracle PL SQL cursor For loops

noreply@blogger.com (Anonymous) — Mon, 08 Oct 2012 20:28:00 +0000

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns and opens a cursor. With each iteration the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or if an exception is raised.

The following procedure is followed in most of the situations in PL/SQL:

1. Open a cursor

2. Start a loop

3. Fetch the cursor

4. Check whether rows are returned

5. Process

6. Close the loop

7. Close the cursor

Cursor For Loop allows us to simplify this procedure by letting PL/SQL do most of the things for us.

You can simplify coding by using a Cursor For Loop instead of the OPEN, FETCH, and CLOSE statements.

A Cursor For Loop implicitly declares its loop index as a record that represents a row fetched from the database.

Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and then closes the cursor when all rows have been processed.

The cursor FOR LOOP statement lets you issue a SELECT statement and then immediately loop through the rows of the result set. This statement can use either an implicit or explicit cursor. If you use the SELECT statement only in the cursor FOR LOOP statement, then specify the SELECT statement inside the cursor FOR LOOP statement, as in. This form of the cursor FOR LOOP statement uses an implicit cursor, and is called an implicit cursor FOR LOOP statement. Because the implicit cursor is internal to the statement, you cannot reference it with the name SQL. If you use the SELECT statement more than once in the same PL/SQL unit, then define an explicit cursor for it and specify that cursor in the cursor FOR LOOP statement. This form of the cursor FOR LOOP statement is called an explicit cursor FOR LOOP statement. You can use the same explicit cursor elsewhere in the same PL/SQL unit. The cursor FOR LOOP statement implicitly declares its loop index as a %ROWTYPE record variable of the type that its cursor returns. This record is local to the loop and exists only during loop execution. Statements inside the loop can reference the record and its fields. They can reference calculated columns only by aliases. After declaring the loop index record variable, the FOR LOOP statement opens the specified cursor. With each iteration of the loop, the FOR LOOP statement fetches a row from the result set and stores it in the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or if an exception is raised.

Cursor For Loop Example

DECLARE CURSOR c1 IS

SELECT ename, sal, deptno

FROM emp;

...

BEGIN

FOR emp_rec IN c1 LOOP

...

salary_total := salary_total +

emp_rec.sal;

END LOOP;

PL/SQL Ref Cursors

noreply@blogger.com (Anonymous) — Mon, 01 Oct 2012 08:45:00 +0000

Implicit cursors. Explicit cursors. PL SQL cursors.

A REF CURSOR or cursor variable is just a reference or a handle to a static cursor. It allows a user to pass this "reference to the same cursor" among all the programs that need access to the cursor. Cursor variables give you easy access to centralized data retrieval.

There are two types of cursor variables:

Strong REF Cursor and

Weak REF Cursor

What is the difference between Cursor and REF Cursor, and when would you appropriately use each of these?

Technically, under the covers, at the most "basic level", they are the same.

A "normal" PL/SQL cursor is static in definition.

Ref cursors may be dynamically opened or opened based on logic.

Declare

type rc is ref cursor;

cursor c is select * from dual;

l_cursor rc;

begin

if (to_char(sysdate,'dd') = 30 ) then

open l_cursor for 'select * from emp';

elsif ( to_char(sysdate,'dd') = 29 ) then

open l_cursor for select * from dept;

else

open l_cursor for select * from dual;

end if;

open c;

end;

Given that block of code - you see perhaps the most "salient" difference - no matter how many times you run that block - cursor C will always be select * from dual. The ref cursor can be anything.

Another difference is a ref cursor can be returned to a client. A PL/SQL cursor cannot be returned to a client.

Another difference is a cursor can be global - a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function).

Another difference is a ref cursor can be passed from subroutine to subroutine - a cursor cannot be.

Another difference is that static SQL (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to.

returning result sets to clients

when there is NO other efficient/effective means of achieving the goal. That is, you want to use static SQL (with implicit cursors really) first and use a ref cursor only when you absolutely have to.

A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result. The advantage that a ref cursor has over a plain cursor is that is can be passed as a variable to a procedure or a function. The ref cursor can be assigned to other ref cursor variables. This is a powerful capability in that the cursor can be opened, then passed to another block for processing, then returned to the original block to be closed. The cursor variable can also be returned by a function and assigned to another variable. The ref cursor variable is not a cursor, but a variable that points to a cursor. Before assigning a cursor variable, a cursor type must be defined.

type author_cursor is ref cursor;

This "REF CURSOR" is a weak typed cursor variable because it does not define the datatype the cursor will return. Below is the same cursor that is strongly typed.

type author_cursor is ref cursor

returning author%rowtype;

By strongly typing the cursor variable, you define what the cursor can return. If a strongly typed cursor returns something other that its return type, a "ROWTYPE_MISMATCH" exception is raised. A strongly typed cursor type is less flexible but less prone to programming errors. The PL/SQL compiler will verify that the "FETCH clause" has the correct variable/record for the cursor return type at compile time.

Once the cursor type is defined, the actual variable can be defined as the cursor type.

c1 author_cursor;

Now c1 is a variable of a cursor type. It is opened using a SQL statement.

open c1 for select * from authors;

Now c1 has all the attributes of the actual cursor. As with any cursor it is important to close the cursor as soon as you have completed processing.

SQL> declare

2 type auth_cursor is ref cursor;

3 c1 auth_cursor;

4 r_c1 author%rowtype;

5 begin

6 open c1 for select * from author;

7 fetch c1 into r_c1;

8 if c1%isopen then

9 dbms_output.put_line('The Cursor is open.');

10 end if;

11 dbms_output.put_line('Row Count is '||c1%rowcount);

12 close c1;

13 if not c1%isopen then

14 dbms_output.put_line('The Cursor is closed.');

15 end if;

16 end;

17 /

The Cursor is open.

Row Count is 1

The Cursor is closed.

Here some of the cursor attributes are used to process the cursor. Notice that the record used to hold a fetched cursor row is defined as an author table "%rowtype". Even though the example cursor variable is defined, the record can not use it because the cursor variable return type is not defined. The example below attempts to create a cursor %rowtype variable for processing.

SQL> declare

2 type auth_cursor is ref cursor;

3 c1 auth_cursor;

4 r_c1 c1%rowtype;

5 begin

6 null;

7 end;

8 /

r_c1 c1%rowtype;

ERROR at line 4:

ORA-06550: line 4, column 8:

PLS-00320: the declaration of the type of this

expression is incomplete or malformed

ORA-06550: line 4, column 8:

PL/SQL: Item ignored

However, a strongly typed cursor can use the cursor variable to define the cursor record.

SQL> declare

2 type auth_cursor is ref cursor return author%rowtype;

3 c1 auth_cursor;

4 r_c1 c1%rowtype;

5 begin

6 null;

7 end;

8 /

In this example, the auth_cursor type returns an author%rowtype. Because this is defined in line 2, the record defined in line 4 can now use it.Cursor variables that are weakly typed can be defined to return any values. In the example below, the cursor variable c1 is defined as three different statements.

SQL> declare

2 type auth_cursor is ref cursor;

3 c1 auth_cursor;

4 r_c1 author%rowtype;

5 r2_c1 book%rowtype;

6 r3_c1 number;

7 begin

8 open c1 for select * from author;

9 fetch c1 into r_c1;

10 close c1;

11 open c1 for select * from book;

12 fetch c1 into r2_c1;

13 close c1;

14 open c1 for select sum(quantity)

15 from store join sales using (store_key)

16 group by store_name;

17 fetch c1 into r3_c1;

18 close c1;

19 end;

20 /

Although the block does not do anything but open and close the cursor variable, it does demonstrate that weakly typed variables can be defined differently each time the variable is opened. But what happens when a cursor variable is defined with a SQL statement but returns unexpected values? Below is an example of defining a cursor variable of one type and the record as another. The cursor variable is returning all columns in the book table, but the receiving variable is defined as a record of columns from the author table.

SQL> declare

2 type auth_cursor is ref cursor;

3 c1 auth_cursor;

4 r_c1 author%rowtype;

5 begin

6 open c1 for select * from book;

7 fetch c1 into r_c1;

8 close c1;

9 end;

10 /

declare

ERROR at line 1:

ORA-06504: PL/SQL: Return types of Result Set

variables or query do not match

ORA-06512: at line 7

Notice that the error message is pointing to the DECLARE clause. That is because the block successfully compiled and only threw the error when it was executed. The PL/SQL compiler can not catch the error because the cursor type did not define a return type. By changing the definition of the cursor type to a strongly typed definition, the compiler will catch this error when the code is compiled.

SQL> declare

2 type auth_cursor is ref cursor

3 return book%rowtype;

4 c1 auth_cursor;

5 r_c1 author%rowtype;

6 begin

7 open c1 for select * from book;

8 fetch c1 into r_c1;

9 close c1;

10 end;

11 /

fetch c1 into r_c1;

ERROR at line 8:

ORA-06550: line 8, column 5:

PLS-00394: wrong number of values in the INTO

list of a FETCH statement

ORA-06550: line 8, column 5:

PL/SQL: SQL Statement ignored

Now the compiler catches the error. So far the examples have been using the cursor variables as regular cursors. The real advantage of using a cursor variable is the ability to pass it as a parameter. In the example below a local function is used to open a cursor called c1 and return it.

The block body calls the function to assign the cursor to the cursor variable c2. The block body then processes the cursor and closes it.

SQL> declare

2 type auth_cursor is ref cursor

return author%rowtype;

3 c2 auth_cursor;

4 r_c2 c2%rowtype;

6 function get_auth return auth_cursor

7 is

8 c1 auth_cursor;

9 begin

10 open c1 for select * from author;

11 return c1;

12 end;

14 begin

15 c2 := get_auth;

16 loop

17 fetch c2 into r_c2;

18 exit when c2%notfound;

19 dbms_output.put_line(initcap(r_c2.author_last_name));

20 end loop;

21 close c2;

22 end;

23 /

Jones

Hester

Weaton

Jackie

Withers

Petty

Clark

Mee

Shagger

Smith

Line 2 defines the cursor type. Lines 3 and 4 define the cursor and return record used in the body. Line 6 declares a local function called get_auth that returns an auth_cursor type. Inside this local function, cursor c1 is defined as an auth_cursor type, opened and returned to the calling code. The function is actually executed on line 15 when c2 is assigned the return value of the get_auth function. The cursor c2 is processed and finally closed on line 21. Note that c1 opened the cursor and c2 closed it. This is an important point.

The example contains only ONE cursor. When c2 is assign the value of c1, both variables point to the same cursor. Remember that c1 and c2 are variables that point to or reference the actual cursor.

The same basic example is shown below except, the output is generated by a local procedure. Note that the procedure print_name gets passed the cursor c2 and then processes it. It then passes the cursor backup to be closed by the body of the PL/SQL block.

SQL> declare

2 type auth_cursor is ref cursor

return author%rowtype;

3 c2 auth_cursor;

4 r_c2 c2%rowtype;

6 function get_auth return auth_cursor

7 is

8 c1 auth_cursor;

9 begin

10 open c1 for select * from author;

11 return c1;

12 end;

14 procedure print_name (c3 in out auth_cursor)

15 as

16 r_c3 author%rowtype;

17 begin

18 loop

19 fetch c3 into r_c3;

20 exit when c3%notfound;

21 dbms_output.put_line(initcap(r_c3.author_last_name));

22 end loop;

23 end;

25 begin

26 c2 := get_auth;

27 print_name(c2);

28 close c2;

29 end;

30 /

Jones

Hester

Weaton

Jeckle

Withers

Petty

Clark

Mee

Shagger

Smith

There are three items you should note about this PL/SQL block. First, the body is pretty simple to understand.

Get the authors, print the names, and close the cursor. Second, you can pass cursor variables to procedures and functions, and functions can return the variables. Lastly, it can become confusing about when a cursor variable should be closed.

Source:

Fast-Track
Oracle

PL/SQL Explicit Cursors

noreply@blogger.com (Anonymous) — Mon, 01 Oct 2012 08:36:00 +0000

PL SQL ref cursors. PL SQL cursors.

Explicit Cursors

Programmers create explicit cursors, and with these you can do operations on a set of rows, which can be processed one by one. You use explicit cursors when you are sure that the SQL statement will return more than one row. You have to declare an explicit cursor in the declare section at the beginning of the PL/SQL block.

Use explicit cursors to individually process each row returned by a multiple-row SELECT statement.

Explicit cursor functions:

Can process beyond the first row returned by the query, row by row

Keep track of which row is currently being processed

Allow the programmer to manually control explicit cursors in the PL/SQL block

Once you declare your cursor, the explicit cursor will go through these steps:

Declare: This clause initializes the cursor into memory.

Open: The previously declared cursor is now open and memory is allotted.

Fetch: The previously declared and opened cursor can now access data;

Close: The previously declared, opened, and fetched cursor is closed, which also releases memory allocation.

Below is a small example of an Explicit cursor:

SQL> set serveroutput on;

DECLARE

my_emp_id NUMBER (6); -- variable for employee_id

my_job_id VARCHAR2 (10); -- variable for job_id

my_sal NUMBER (8,2); -- variable for salary

CURSOR c1 IS

SELECT employee_id, job_id, salary FROM employees

WHERE salary > 2000;

my_dept departments%ROWTYPE; -- variable for departments row

CURSOR c2 RETURN departments%ROWTYPE IS

SELECT * FROM departments

WHERE department_id = 110;

BEGIN

NULL;

END;

Explicit Cursor Attributes

Here are the main cursor attributes:

%ISOPEN It returns TRUE if cursor is open, and FALSE if it is not.

%FOUND It returns TRUE if the previous FETCH returned a row and FALSE if it did not.

%NOTFOUND It returns TRUE if the previous FETCH did not return a row and FALSE if it did.

%ROWCOUNT It gives you the number of rows the cursor fetched so far.

Few more examples of Explicit Cursors:

Example 1 of an Explicit Cursor:

An example to retrieve the first 10 employees one by one.

SET SERVEROUTPUT ON

DECLARE

v_empno employees.employee_id%TYPE;

v_ename employees.last_name%TYPE;

CURSOR emp_cursor IS

SELECT employee_id, last_name

FROM employees;

BEGIN

OPEN emp_cursor;

FOR i IN 1...10 LOOP

FETCH emp_cursor INTO v_empno, v_ename;

DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)

||' '|| v_ename);

END LOOP;

Close emp_cursor

END;

Example 2 of an Explicit Cursor:

DECLARE

CURSOR csr_ac (p_name VARCHAR2)

SELECT empno, name, sal

FROM employee

WHERE name LIKE '%p_name%';

BEGIN

FOR rec_ac IN csr_ac ('LE')

LOOP

DBMS_OUTPUT.PUT_LINE (rec_ac.empno || ' ' ||rec_ac.name || ''||v_sal);

END LOOP;

CLOSE csr_ac;

END;

Example 3 of an Explicit Cursor:

Another way of writing the above code, is to use the basic loop and the SQL%NOTFOUND cursor, as shown in the following.

DECLARE

CURSOR csr_ac (p_name VARCHAR2) IS

SELECT empno, ename, sal

FROM emp

WHERE ename LIKE '%SMITH%';

v_a emp.empno%TYPE;

v_b emp.ename%TYPE;

v_c emp.sal%TYPE;

BEGIN

OPEN csr_ac('');

LOOP

FETCH csr_ac INTO v_a, v_b, v_c;

EXIT WHEN csr_ac%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_a || ' ' || v_b || ' '||v_c);

END LOOP;

CLOSE csr_ac;

END;

see also: PL SQL ref cursors. PL SQL cursors.

PL SQL Implicit cursors

noreply@blogger.com (Anonymous) — Mon, 01 Oct 2012 08:27:00 +0000

Explicit cursors PL SQL cursors.

Implicit cursors are automatically created and used by Oracle every time you issue a Select statement in PL/SQL. If you use an implicit cursor, Oracle will perform the open, fetches, and close for you automatically. Implicit cursors are used in statements that return only one row. If the SQL statement returns more than one row, an error will occur.

The Oracle server implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor. PL/SQL allows you to refer to the most recent implicit cursor as the SQL cursor.

For a long time there have been debates over the relative merits of implicit cursors and explicit cursors. The short answer is that implicit cursors are faster and result in much neater code so there are very few cases where you need to resort to explicit cursor.

The process of an implicit cursor is as follows:

Whenever an SQL statement is executed, any given PL/SQL block issues an implicit cursor, as long as an explicit cursor does not exist for that SQL statement.

A cursor is automatically associated with every DML statement (UPDATE, DELETE, and INSERT).

All UPDATE and DELETE statements have cursors those recognize the set of rows that will be affected by the operation.

An INSERT statement requires a place to accept the data that is to be inserted in the database; the implicit cursor fulfills this need.

The most recently opened cursor is called the “SQL%” Cursor. The implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements. Oracle automatically performs the OPEN, FETCH, and CLOSE operations, during the processing of an implicit cursor.

Example 1 of an Implicit cursors

In the following PL/SQL code block, the select statement makes use of an implicit cursor:

Begin

Update emp Where 1=2;

Dbms_output.put_line (sql%rowcount ||’ ‘|| ‘ rows are affected by the update statement’);

End;

SELECT SUM (sal) INTO TOTAL

FROM emp

WHERE depno = 10;

Another Example of an Implicit cursor

The following single-row query calculates and returns the total salary for a department. PL/SQL creates an implicit cursor for this statement:

SELECT SUM (salary) INTO department_total

FROM employee

WHERE department_number = 10;

PL/SQL provides some attributes, which allow you to evaluate what happened when the implicit cursor was last used. You can use these attributes in PL/SQL statements like functions but you cannot use then within SQL statements.

%ROWCOUNT The number of rows processed by a SQL statement.

%FOUND TRUE if at least one row was processed.

%NOTFOUND TRUE if no rows were processed.

%ISOPEN TRUE if cursor is open or FALSE if cursor has not been opened or has been closed. Only used with explicit cursors.

An Example of PL/SQL Attribute

DECLARE

rows_deleted NUMBER;

BEGIN

DELETE * FROM emp;

rows_deleted := SQL%ROWCOUNT;

END;

The implicit cursor has the following drawbacks:

· It is less efficient than an explicit cursor.

· It is more vulnerable to data errors.

· It gives you less programmatic control.

Please see this link to know if Implicit cursors are fast or Explicit cursors.

Oracle PL/SQL Cursors

noreply@blogger.com (Anonymous) — Mon, 01 Oct 2012 08:22:00 +0000

We can manipulate the information within a SQL statement by means of assigning a name to a "select statement", this concept is known a cursor. A cursor is used for processing individual rows returned as a result for a query.

Oracle PL/SQL Implicit Cursors

Oracle PL/SQL Explicit Cursors

Oracle PL/SQL Cursor For Loops

Oracle PL/SQL Cursors With Parameters

Oracle PL/SQL REF Cursors

Oracle PL/SQL WHERE CURRENT OF & FOR UPDATE

Examples of Oracle PL/SQL Cursors

Difference between EXEC & EXECUTE()

noreply@blogger.com (Anonymous) — Sun, 30 Sep 2012 06:33:00 +0000

EXECUTE

The EXECUTE, or EXEC statement executes a stored procedure by following the command with the name of the stored procedure and any proper parameter arguments:

EXEC sp_Empfind @City='DE'

EXECUTE()

The EXECUTE(character_string) function executes a character string containing a Transact-SQL command.

It can be written like EXEC(character_string) also. The EXECUTE(character_string) function is not compiled until run time and the generated plan is not cached and reused. The following example executes a dynamic string that concatenates literal strings with variable values:

DECLARE @mycol varchar(30)

DECLARE @mytable varchar(30)

SELECT @mycol = 'EMPID'

SELECT @mytable = 'Employee'

EXEC('SELECT ' + @mycol + ' FROM ' + @mytable)

sp_execute of pl sql

noreply@blogger.com (Anonymous) — Sun, 30 Sep 2012 06:26:00 +0000

[sp_executesql in comparison to Execute]

If you want to have the flexibility of dynamic SQL, but the persistence of a stored query plan, consider using sp_executesql in your stored procedures instead of EXEC. The syntax for sp_executesql is as follows:

sp_executesql @SQL_commands, @parameter_definitions, param1,...paramN

sp_executesql operates just as the EXEC statement with regard to the scope of names, permissions, and database context. However, sp_executesql is more efficient when executing the same SQL commands repeatedly, and the only change is the values of the parameters. Because the SQL statement remains constant and only the parameters change, SQL Server is more likely to reuse the execution plan generated for the first execution and simply substitute the new parameter values. This saves the overhead from having to compile a new execution plan each time.

[Via:Microsoft SQL Server 2000 Unleashed By Ray Rankins, Paul Jensen, Paul Bertucci]

The sp_executesql SP provides a second method of executing dynamic sql. When used correctly, it is safer than the simple EXECUTE method for concatenating strings and executing them. Like EXECUTE, sp_executesql takes a string constant or variable as a sql statement to execute. Unlike EXECUTE, the sql statement parameter must be an nchar or nvarchar.

The sp_executesql procedure offers a distinct advantage over the EXECUTE method; you can specify your parameters seperately from the SQL statement. When you specify the parameters separately instead of concatenating them into one large string, SQL server passes the parameters to sp_executesql seperately. SQL server then substitutes the value of the parameters in the parameterized SQL statement. Because the parameter values are not concatenated into the SQL statement, sp_executesql protects against SQL injection attacks. sp_executesql parameterization also improves query execution plan cache reuse, which helps with performance.

A limitation to this approach is that you cannot use the parameters in your SQL statement in place of table, column or other object names.

Example of sp_executesql

DECLARE @product_name NVARCHAR(50) = N' Mountain%';

DECLARE @sql_stmt NVARCHAR(128) = N'SELECT ProductID, Name ' +

N'FROM Production.Product ' +

N'WHERE Name LIKE @name';

EXECUTE sp_executesql @sql_stmt,

N'@name NVARCHAR(50)',

@name = @product_name;

[Via:Pro T-SQL 2008 Programmer's Guide By Michael Coles]

The key difference in the syntax between the EXEC() and sp_executeSQL is that EXEC() will allow you to concatenate literals and variables to build up a string expression, while sp_executesql needs a string constant or a single string variable.

sp_executesql must be passed a Unicode string.

See more on difference between EXCUTE & EXECUTE().

Performance tuning - 02

noreply@blogger.com (Anonymous) — Sat, 29 Sep 2012 19:07:00 +0000

In a pre-production test, I find out that application did not use bind variables and there was no time to change code before the production rollout. So we suggested changing CURSOR_SHARING parameter from EXACT to FORCE in order to turn hard parse to soft parse. When testing this change for the two webservice functions , which mainly consists of two inserts, one update and bunch of select statements, the results are promising:

Summary of testing results from 15 min interval AWR reports from a 4-node 11.2.0.2 RAC database
2-node test (CURSOR_SHARING=EXACT)
dd1 DB Time: 23.04 (mins) 143 tx/s Parses: 440.7 per sec Hard parses: 315.2 per sec
dd4 DB Time: 23.35 (mins) 140 tx/s Parses: 414.4 per sec Hard parses: 303.5 per sec
2-node test (CURSOR_SHARING=FORCE)
dd1 DB Time: 16.68 (mins) 146 tx/s Parses: 383.9 per sec Hard parses: 2.6 per sec
dd4 DB Time: 16.01 (mins) 147 tx/s Parses: 384.2 per sec Hard parses: 2.6 per sec
-- dd1 Time Model Statistics (CURSOR_SHARING=FORCE)
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 666.6 66.6
DB CPU 350.0 35.0
parse time elapsed 80.0 8.0
hard parse elapsed time 15.1 1.5
hard parse (sharing criteria) elapsed time 1.7 .2
PL/SQL execution elapsed time 1.1 .1
hard parse (bind mismatch) elapsed time 1.0 .1
PL/SQL compilation elapsed time 0.8 .1
connection management call elapsed time 0.4 .0
sequence load elapsed time 0.4 .0
repeated bind elapsed time 0.0 .0
DB time 1,000.9
background elapsed time 200.0
background cpu time 143.9
-------------------------------------------------------------
-- dd1 Time Model Statistics (CURSOR_SHARING=EXACT)
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
DB CPU 728.2 52.7
sql execute elapsed time 632.9 45.8
parse time elapsed 481.2 34.8
hard parse elapsed time 390.0 28.2
hard parse (sharing criteria) elapsed time 3.2 .2
hard parse (bind mismatch) elapsed time 2.9 .2
PL/SQL execution elapsed time 1.4 .1
PL/SQL compilation elapsed time 1.2 .1
connection management call elapsed time 0.5 .0
sequence load elapsed time 0.5 .0
repeated bind elapsed time 0.0 .0
failed parse elapsed time 0.0 .0
DB time 1,382.4
background elapsed time 261.8
background cpu time 115.5
-------------------------------------------------------------

Bascially, I saw hard parse was greatly reduced. For the same level transaction rate, DB time was reduced from 46 min to 32 min after changing to FORCE. However, when another module was enabled and added to the two webserverice workload, things went wrong. I saw large amount of "cursor:mutex S" and "liberary cache lock" waits (>80% DB time). We identified an insert statement that has large amount of child cursors ( ~ 8000) caused the problem.

Below is a test case demonstrating large number of child cursors are created with CURSOR_SHARING=FORCE:

Test case 1

This test was carried out to repeat what we encountered in our pre-production test. Inserts into a table called MY_STB_LOG2 were performed with CUROSR_SHARING=FORCE, with 324 total number of inserts similar to the following:

insert into MY_STB_LOG2 values('0000043862420080','VHO6_W07_MTR_30243_20110504094656__.101.Zip',CURRENT_TIMESTAMP,timestamp'2011-05-04 07:51:40.000',timestamp'2011-05-04 02:20:45.000',timestamp'2011-05-04 02:35:07.000','N','N','','','','P',SYSDATE);
insert into MY_STB_LOG2 values('0000048201268050','VHO6_W07_MTR_30243_20110504094656__.101.Zip',CURRENT_TIMESTAMP,timestamp'2011-05-05 01:02:07.000',timestamp'2011-05-04 21:10:22.000','','N','N','','','','P',SYSDATE);

After run, checking v$sqlarea and v$sql, we got:

SQL> select sql_text,sql_id, executions from v$sqlarea where sql_text like 'insert into MY_STB_LOG2%';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
SQL_ID EXECUTIONS
------------- ----------
insert into MY_STB_LOG2 values(:"SYS_B_00",:"SYS_B_01",CURRENT_TIMESTAMP,timestamp:"SYS_B_02",timestamp:"SYS_B_03",:"SYS
_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",SYSDATE)
16yq35g1c433b 71
insert into MY_STB_LOG2 values(:"SYS_B_00",:"SYS_B_01",CURRENT_TIMESTAMP,timestamp:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",:"
SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",SYSDATE)
4yzz9r14n53fg 28
insert into MY_STB_LOG2 values(:"SYS_B_00",:"SYS_B_01",CURRENT_TIMESTAMP,timestamp:"SYS_B_02",timestamp:"SYS_B_03",times
tamp:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",SYSDATE)
gpb19na8zd67s 210
insert into MY_STB_LOG2 values(:"SYS_B_00",:"SYS_B_01",CURRENT_TIMESTAMP,timestamp:"SYS_B_02",:"SYS_B_03",timestamp:"SYS
_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",SYSDATE)
6ua1jndb5ud4x 15
SQL> select sql_id, count(*) from v$sql where sql_text like 'insert into MY_STB_LOG2%' group by sql_id;
SQL> select sql_id, count(*) from v$sql where sql_text like 'insert into MY_STB_LOG2%' group by sql_id;
SQL_ID COUNT(*)
------------- ----------
gpb19na8zd67s 210
16yq35g1c433b 71
4yzz9r14n53fg 28
6ua1jndb5ud4x 15

It can be seen that we have 4 versions of insert statements after literals are replaced by system-generated bind varialbes. For each version, the execution times equal to the number of its child cursors. It indicates for every insert, Oracle actually does hard parse with extra cost to search child cursors.

When using Dion's script to query v$sql_shared_cursor, I can see the reason for unshared cursor is "HASH_MATCH_FAILED"

--------------------------------------------------
SQL_TEXT = insert into MY_STB_LOG2 values(:"SYS_B_00",:"SYS_B_01",CURRENT_TIMESTAMP,timestamp:"SYS
_B_02",timestamp:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",SYSDATE)
SQL_ID = 16yq35g1c433b
ADDRESS = 0000000113DCCE88
CHILD_ADDRESS = 0000000093C96F80
CHILD_NUMBER = 68
HASH_MATCH_FAILED = Y
--------------------------------------------------
SQL_TEXT = insert into MY_STB_LOG2 values(:"SYS_B_00",:"SYS_B_01",CURRENT_TIMESTAMP,timestamp:"SYS
_B_02",timestamp:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",SYSDATE)
SQL_ID = 16yq35g1c433b
ADDRESS = 0000000113DCCE88
CHILD_ADDRESS = 00000000AB5E4300
CHILD_NUMBER = 69
HASH_MATCH_FAILED = Y
--------------------------------------------------
SQL_TEXT = insert into MY_STB_LOG2 values(:"SYS_B_00",:"SYS_B_01",CURRENT_TIMESTAMP,timestamp:"SYS
_B_02",timestamp:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",SYSDATE)
SQL_ID = 16yq35g1c433b
ADDRESS = 0000000113DCCE88
CHILD_ADDRESS = 000000013C695090
CHILD_NUMBER = 70
HASH_MATCH_FAILED = Y
--------------------------------------------------

It should be noted that even application using the bind varialbe, this problem could occur. One possible solution is to use PL/SQL procedure to do the insert:

Test case 2

I create a procedure STB_INS (see appendix) to insert into a table called MY_STB_LOG3 ( same structure as LOG2), then I run 324 exec procedure such as:

exec STB_INS('0001097562293114',

'VHO6_W07_MTR_30243_20110504094656__.101.Zip',

CURRENT_TIMESTAMP,timestamp'2011-05-05 01:30:01.000',

timestamp'2011-05-05 00:20:03.000',

timestamp'2011-05-04 23:42:57.000','N','N','','','','P',SYSDATE);

exec STB_INS('0000027988805135',

'VHO6_W07_MTR_30243_20110504094656__.101.Zip',

CURRENT_TIMESTAMP,timestamp'2011-05-05 01:46:21.000',

timestamp'2011-05-05 01:02:54.000',

timestamp'2011-05-04 22:55:10.000','N','N','','','','P',SYSDATE);

After run, check v$sqlarea and v$sql

SQL> select sql_id, sql_text, executions from v$sqlarea where sql_text like '%MY_STB_LOG3%';
SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
EXECUTIONS
----------
4fdv39rhjag5n
INSERT INTO MY_STB_LOG3 VALUES( :B13 ,:B12 ,:B11 ,:B10 ,:B9 ,:B8 ,:B7 ,:B6 ,:B5 ,:B4 ,:B3 ,:B2 ,:B1 )
324
SQL> select count(*) from v$sql where sql_id='4fdv39rhjag5n';
COUNT(*)
----------
1

We see 324 executions shared same cursor.

Appendix

create or replace procedure stb_ins(
p_UNIT_ADDRESS VARCHAR2
,p_BATCH_NUMBER VARCHAR2
,p_RECEIVED_TIME TIMESTAMP
,p_LAST_LOG_TIME TIMESTAMP
,p_POWERON_TIME TIMESTAMP
,p_POWEROFF_TIME TIMESTAMP
,p_UNAVAILABLE_FLAG VARCHAR2
,p_UNSUBSCRIBE_FLAG VARCHAR2
,p_WORK_ID VARCHAR2
,p_STATUS_CODE VARCHAR2
,p_STATUS_DESC VARCHAR2
,p_PROC_STATUS VARCHAR2
,p_CREATE_DATE DATE
)
is
begin
insert into my_stb_log3 values(
p_UNIT_ADDRESS
,p_BATCH_NUMBER
,p_RECEIVED_TIME
,p_LAST_LOG_TIME
,p_POWERON_TIME
,p_POWEROFF_TIME
,p_UNAVAILABLE_FLAG
,p_UNSUBSCRIBE_FLAG
,p_WORK_ID
,p_STATUS_CODE
,p_STATUS_DESC
,p_PROC_STATUS
,p_CREATE_DATE
);
commit;
end;
/

ASP.NET books

noreply@blogger.com (Anonymous) — Sun, 09 Sep 2012 16:01:00 +0000

ASP.NET 2.0 Unleashed by Stephen Walther
Professional ASP.NET 2.0 Special Edition (Wrox Professional Guides) by Bill evjen Scott Hansleman Farhan Muhammad Srinivasa Sivakumar
Professional ASP.NET 2.0 (Programmer to Programmer) by Bill evien Scott Hansleman Farhan Muhammad
Programming ASP.NET, 3rd Edition by jesse Liberty Dan Hurwitz
Sams Teach Yourself ASP.NET 2.0 in 24 Hours, Complete Starter Kit by Scott Mitchell
Sams Teach Yourself ASP.NET 3.5 in 24 Hours, Complete Starter Kit by Scott Mitchell
Programming Microsoft ASP.NET 2.0 Core Reference by Dini Esposito
Programming Microsoft ASP.NET 2.0 Applications: Advanced Topics by Dini Esposito
ASP.NET 2.0 Instant Results (Programmer to Programmer) by Imar Spaaniaars Paul Wilton Shawn Livermore
Beginning ASP.NET 2.0 Databases: From Novice to Professional (Beginning: From Novice to Professional) by Damien Foggon
ASP.NET 2 For Dummies (For Dummies (Computers)) by Bill Hatfield

PL SQL books

noreply@blogger.com (Anonymous) — Sun, 09 Sep 2012 07:22:00 +0000

Oracle PL/SQL Language Pocket Reference (Pocket Reference (O'Reilly)) by Steven Feuerstein and Bill Pribyl
Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 by Steven Feuerstein and Bill Pribyl
Oracle PL/SQL Programming: A Developer's Workbook (Development Workbook) by Steven Feuerstein
Oracle SQL: Jumpstart with Examples by Gavin JT Powell Carol McCullough-Dieter
Professional Oracle Programming (Programmer to Programmer)Rick Greenwald,Robert Stackowiak,Gary Dodge and David Klien
Oracle Database 10g PL/SQL 101 (Oracle Press) by Christopher Allen
Oracle PL/SQL Best Practices by steven Feuerstein
Oracle PL/SQL For Dummies by Miachel Rosenblum and Paul Dorsey

SQL books

noreply@blogger.com (Anonymous) — Sat, 08 Sep 2012 19:47:00 +0000

SQL Cookbook (Cookbooks (O'Reilly)) by Anthony Molinaro
Sams Teach Yourself SQL in 10 Minutes (3rd Edition) by Ben Forta
Learning SQL by Alan Beaulieu
SQL For Dummies by Allen G Taylor
SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL
(2nd Edition) by Miachel Hernandez and John L Viescas
SQL Pocket Guide (Pocket Guides) by Jonathan Gennick
SQL in a Nutshell (In a Nutshell (O'Reilly)) by Kevin Kline Daniel Kline Brand Hunt
SQL: The Complete Reference, 2nd Edition by James R Groff Paul N Weinberg
SQL Bible by Alex Kriegel and Boris M Trukhnoy
Microsoft SQL Server 2012 Bible by Adam Jorge Segarra Jose Chinchilla Aron Nelson
The Art of SQL by Stephane faroult and Peter Robson
Oracle SQL By Example (4th Edition) by Alice Rischert
A Guide to SQL by Philip J Pratt

SQL Basics

noreply@blogger.com (Anonymous) — Sat, 08 Sep 2012 11:55:00 +0000

SQL is a simple, yet powerful, language used to create, access, and manipulate data and structure in the database.

SQL Statements categories:

DDL - Data Definition Language.

DDL is used to define, alter, or drop database objects and their privileges. DDL statements will implicitly perform a commit.

DDL Statements:

Create It is used to create objects(tables, views) in the database.

Alter It is used to alter the structure of the database objects.

Drop Delete database objects. It will invalidate the dependent objects ,it also drops triggers and indexes, referential integrity constraints.

Truncate : Remove all records from a table, including all spaces allocated for the records are removed.It is fast as compared to Delete and does not generate undo information as Delete does. It performs an implicit commit as it is a DDL. It resets the high water mark

Grant Assigning privileges

DML - Data Manipulation Language.

DML is used to access, create, modify or delete data in the structures of the database.

DML Statements:

Select Select data from the database

Insert It is used to insert data into a table

Update It is used to update existing data within a table

Delete It removes rows from the table.

DCL - Data Control Language

Following are the examples of Data control Statements.

DCL Statements:

Commit It will end the current transaction making the changes permanent and visible to all users.

Savepoint It will identify a point(named SAVEPOINT) in a transaction to which you can later roll back

Rollback It will undo all the changes made by the current transaction.

Set-Transaction It is used to define the properties of a transaction.

SQL Syntax

noreply@blogger.com (Anonymous) — Sat, 08 Sep 2012 11:46:00 +0000

On this page, you will find SQL syntax for some of the most important SQL commands. These below SQL Syntaxes will be suitable for quick reference.

SELECT [hint][DISTINCT] select_list

FROM table_list

[WHERE conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]

[ORDER BY order_list [ASC DESC]]

[FOR UPDATE for_update_options]

SQL Select Statement

SELECT "column name" FROM "table name"

Example:

Select salary from emp;

SQL Where

SELECT "column name"

FROM "table name"

WHERE "condition"

Example:

Select salary from emp

Where salary > 2000

SQL Distinct

SELECT DISTINCT "column name"

FROM "table name"

Example:

Select DISTINCT name from emp;

SQL And/Or

SELECT "column name"

FROM "table name"

WHERE "condition"

{[ANDOR] "condition"}+

Example:

SELECT salary

FROM emp

WHERE Salary > 1000

OR (Salary <> 275)

SQL Between

SELECT "column name"

FROM "table name"

WHERE "column name" BETWEEN 'value1' AND 'value2'

Example:

SELECT *

FROM emp

WHERE Date BETWEEN 'Jan-01-1999' AND 'Jan-15-1999'

SQL In

SELECT "column name"

FROM "table name"

WHERE "column name" IN ('value1', ‘value2’ ...)

Example:

SELECT *

FROM emp

WHERE last_name IN ('Sharma', 'dhall')

SQL Like

SELECT "column name"

FROM "table name"

WHERE "column name" LIKE {PATTERN}

Example:

SELECT *

FROM emp

WHERE last_name LIKE '%EN%'

SQL Order By

SELECT "column name"

FROM "table name"

[WHERE "condition"]

ORDER BY "column name" [ASC, DESC]

Example:

SELECT name, Salary

FROM emp

ORDER BY name DESC

SQL Count

SELECT COUNT ("column name")

FROM "table name"

Example:

SELECT COUNT (salary)

FROM emp

SELECT * FROM Table;

In the above query, SELECT and FROM are SQL keywords, * is a wildcard which means “all columns”

SQL keywords related to SELECT include:

FROM is used to indicate from which tables the data is to be taken, as well as how the tables JOIN to each other.

WHERE is used to identify which rows to be retrieved, or applied to GROUP BY. WHERE is evaluated before the GROUP BY.

GROUP BY is used to combine rows with related values into elements of a smaller set of rows.

HAVING is used to identify which of the "combined rows" (combined rows are produced when the query has a GROUP BY keyword or when the SELECT part contains aggregates), are to be retrieved.

HAVING acts much like a WHERE, but it operates on the results of the GROUP BY and hence can use aggregate functions.

ORDER BY is used to identify which columns are used to sort the resulting data.

Returning a Single Column from a table

The syntax is as follows:

SELECT Column FROM Table;

Oracle uses the ANSI standard concatenation operator, . Because this operator is reserved for string concatenation, the operands don't need to be cast to a string type—they'll be converted automatically:

SELECT FirstName ' ' LastName AS Name FROM EMP_Database;

Sorting with the ORDER BY Clause

The ORDER BY clause is always applied after all other clauses are applied, such as the WHERE and GROUP BY clauses. Without an ORDER BY clause in an SQL statement, rows will often be retrieved in the physical order in which they were added to the table. The default behavior is to sort rows in ascending order.

SELECT ColumnA, ColumnB FROM Table

ORDER BY ColumnA ASC;

Although this is the default behavior so you don't need to explicitly specify the ASC keyword, it's a better idea to include it to ensure that your queries are as easy to read as possible.

Also, you may want to sort your rows in descending order, in which case you use the DESC keyword:

SELECT ColumnA, ColumnB FROM Table

ORDER BY ColumnA DESC;

Filtering Data

DISTINCT always retrieves the first value from a repeating group. If there are multiple repeating groups DISTINCT will retrieve the first row from each group.

Hence, DISTINCT will always require a sort. DISTINCT can operate on a single or multiple columns.

SELECT DISTINCT ColumnA FROM Table;

In order to filter out duplicate rows, you use the DISTINCT keyword.

If you want to retrieve multiple columns, you can guarantee that every row you obtain contains unique data for the specified set of columns.For example, the following query will return only unique combinations of customer names and debit card details:

SELECT DISTINCT CustomerName, DebitCard FROM Customers;

This doesn't mean that you won't have duplicates in either column, only that each combination is unique. You could, for example, have several customers with the same name but with different debit card numbers.

Using WHERE Clause

We can use the WHERE clause to restrict the rows returned by a query.

SELECT ColumnA, ColumnB, ColumnC FROM Table

WHERE Condition;

Condition is very flexible, allowing you to test for equalities and inequalities in column data, ranges of values to look for etc. You achieve all this using a simple syntax that includes various operators and keywords that, when combined, allow you to search for pretty much anything.

We can use following comparison operators with the WHERE clause:

Operator	Meaning
=	Equal
<>, !=, ^=	Not Equal
>	Greater Than
<	Less Than
>=	Greater Than Or Equal
<=	Less Than Or Equal

And many more for example BETWEEN ...AND..., LIKE, IS NULL etc.

Some examples of where

1) SELECT name FROM bedroom

WHERE bedcolor = 'BLACK'

OR ceilingcolor = 'GREEN'

OR wallcolor = 'YELLOW'

2) SELECT z

FROM t

WHERE x = 6 AND y > 7 ;

3) Find the average price of Bud. Sells (bar, beer, price)

SELECT AVG (price)

FROM Sells

WHERE beer = 'Bud';

SQL - The language

noreply@blogger.com (Anonymous) — Sat, 08 Sep 2012 11:44:00 +0000

SQL is a tool for organizing managing and retrieving data stored by a computer database. The acronym SQL is an abbreviation for structured Query Language for historical reasons SQL is usually pronounced sequel but the alternate pronunciation S.Q.L is also used. As the name implies SQL is a computer language that you use to interact with a database SQL works with one specific type of database, called the relational database. The database is stored in the computer system. If the computer system is in a business, the database might store inventory, production, sales or payroll data. On personal computers (Pc’s) the database might store data about the checks you written, lists of people and their phone numbers, or data extracted from a larger computer system. The computer program that controls the database is called a database management system, or DBMS. When you need to retrieve data from the database, you use the SQL language to make the request the DBMS processes the SQL request, retrieves the requested data, and returns it to you. This process of requesting data from a database and receiving back the results is called database query----- hence the name structured query language. The name “Structured Query Language” is actually somewhat of a misnomer. First of all, SQL is far more than a query tool although that was its original purpose, and retrieving data is still one of its most important functions. SQL is used to control all of the functions that a DBMS provides for its users including:

Data definition: SQL lets a user define the structure and organization of the stored data and relationships among the stored data items.
Data retrieval: SQL allows a user or an application program to retrieve stored data from the database and use it.
Data manipulation: SQL allows a user or an application program to update the database by adding new data, removing old data, and modifying previously stored data.
Access control: SQL can be used to restrict a user’s ability to retrieve, add, and modify data, protecting stored data against unauthorized access.
Data sharing: SQL id used to coordinate data sharing by concurrent users, ensuring that they do not interfere with one another.
Data integrity: SQL defines integrity constraints in the database, protecting it from corruption due to inconsistent updates or system failures.

SQL is thus a comprehensive language for controlling and interacting with a database management system. SQL is not a complete computer language like COBOL, C, C++, or java.SQL contains no if statement for testing conditions, and no go to, do or for statements for program flow control. Instead, SQL is a database sublanguage, consisting of about 40 statements specialized for database management tasks.SQL has emerged as the standard language for using relational databases. SQL is both a powerful language and one that is relatively easy to learn.

The SELECT Statement

noreply@blogger.com (Anonymous) — Sat, 08 Sep 2012 11:39:00 +0000

The SELECT statement retrieves data from a database and returns it you in the form of query results. For simple queries, the English language request and the SQL SELECT statement are very similar. When the requests become more complex, more features of the select statement must be used to specify the query precisely.

List eastern region sales offices whose sales exceed their targets, sorted in alphabetical order by city.

SELECT CITY, TARGET, SALES

FROM OFFICES

WHERE REGION = ‘Eastern’

AND SALES > TARGET

ORDER BY CITY

CITY TARGET SALES

------------- ------------- --------------

Atlanta $350,000.00 $367,911.00

New York $575,000.00 $692,637.00

What are the average target and sales for Eastern region offices?

SELECT AVG (TARGET), AVG (SALES)

FROM OFFICES

WHERE REGION = ‘Eastern’

AVG (TARGET) AVG (SALES)

-------------- -------------

$575,000.00 $598,530.00

The figure shows the full form of the SELECT statement, which consists of six clauses. The SELECT and FROM clauses of the statement are required. The remaining four clauses are optional. You include them in a SELECT statement only when you want to use the functions they provide. The following list summarizes the function of each clause:

· The SELECT clause lists the data items to be retrieved by the SELECT statement. The items may be columns from the database, or columns to be calculated by SQL as it performs the query.

The FROM clause lists the tables that contain the data to be retrieved by the query. Queries that draw their data from a single table are described here.

· The WHERE clause tells the SQL to include only certain rows of data in the query results. A search condition is used to specify the desired rows.

· The GROUP BY clause specifies a summary query. Instead of producing one row of query results for each row of query results for each row of data in the database, a summary query groups form from similar rows and then produces one summary row of query results for each group.

· The HAVING clause tells SQL to include only certain groups produced by the GROUP BY clause in the query results. Like the WHERE clause, it uses a search condition to specify the desired groups.

· The ORDER BY clause sorts the query results based on the data in one or more columns. If it is omitted, the query results are not sorted.

The select clause:

The select clause that begins each SELECT statement specifies the data items to be retrieved by the query. The items are usually specified by a select list, a list of select items separated by commas. Each select item in the list generates a single column of query results, in left-to-right order. A select item can be one of the following:

· A column name, identifying a column from the table(s) named in the FROM clause. When a column name appears as a select item, SQL simply takes the value of that column from each row of the database table and places it in the corresponding row of query results.

· A constant, specifying that the same constant value is to appear in every row of the query results.

· A SQL expression, indicating that SQL must calculate the value to be placed into the query results, in the style specified by the expression.

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SQL SELECT Syntax

SELECT column_name(s)

FROM table_name

The SQL statement below shows a simple usage of the SQL SELECT command:

SELECT FirstName, LastName, DateOfBirth

FROM Employees

The SELECT statement has many optional clauses:

WHERE specifies which rows to retrieve.

GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.

HAVING selects among the groups defined by the GROUP BY clause.

ORDER BY specifies an order in which to return the rows.

SQL Insert

noreply@blogger.com (Anonymous) — Fri, 07 Sep 2012 21:04:00 +0000

Inserting a new row into the dept table

Insert into dept (deptno, dname, loc)

values (10,'Apple','Boston')

You can omit the column list in the insert statement but then you have to enter those in the same order as they appear in the table and you have o include all the columns in the values.

The following example illustrates the use of the "default" keyword while inserting the records.

insert into table_name values(default);

insert into table_name(column1,column2..) values(default)

How to create a new table having the same structure as some other table?

create table new_table

select *

from old_table

How to insert data into multiple tables?

There are two terms used INSERT ALL AND INSERT FIRST

EXAMPLE:
Suppose there are three tables emp, emp_1 and emp_2 with the same structure and columns.

insert all

when sal in (800, 1600) then

into emp (empno, ename,job) values (empno, ename,job)

when sal = 3200 then

into emp_1 (empno, ename, job) values (empno,ename,job)

else

into emp_2 (empno, ename,job) values (empno,ename,job)

select empno, ename, job

from emp

INSERT FIRST will breakout as soon as it sees a condition that evaluates to true.

INSERT ALL will evaluate al the conditions even if the previous condition evluates to true.

Insert Default Values

Some RDBMSs, including SQL Server, allow you to create a new row based on default values that are defined in the table specification using the DEFAULT VALUES method in place of the VALUES section. Suppose you have a table like this:

CREATE TABLE A (

A_ID int IDENTITY PRIMARY KEY,

Name varchar (100) DEFAULT 'Anonymous',

Dates varchar (20) DEFAULT 'Unknown);

And you if want to insert a new row with just the default values for each column and the auto-generated ID field. You can’t do it using the standard INSERT...VALUES syntax, so instead you'd use this:

INSERT INTO A DEFAULT VALUES;

And that does the trick. Using DEFAULT VALUES instead of the standard VALUES clause inserts the default value for every column (or an auto-generated value for identity columns). If there isn't a default value for a non-identity column, the value is treated as NULL. If any column in the table is NOT NULL, isn't an identity column, and doesn't have a default value, then an error will be raised.

Performing a Multi-Row INSERT

INSERT INTO S_Backup SELECT EveryName, EveryCost FROM S;

Alternatively, you can easily reshape data and rename columns of rows using a statement such as this:

INSERT INTO S_1 (TheName, TheCost)

SELECT EveryName, EveryCost FROM S;

SQL tuning advisor

noreply@blogger.com (Anonymous) — Fri, 07 Sep 2012 17:47:00 +0000

Purpose

This tutorial shows you how to use the SQL Tuning Advisor feature in Oracle SQL Developer 3.0.

Complete time

Approximately 20 minutes.

Overview

The SQL Tuning Advisor analyzes high-volume SQL statements and offers tuning recommendations. It takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. It can run against any given SQL statement. The SQL Tuning Advisor provides advice in the form of precise SQL actions for tuning the SQL statements along with their expected performance benefits. The recommendation or advice provided relates to the collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

Oracle Database can automatically tune SQL statements by identifying problematic SQL statements and implementing tuning recommendations using the SQL Tuning Advisor. You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic.

In this tutorial, you learn how to run and review the recommendations of the SQL Tuning Advisor.

Note: Tuning Advisor is part of the Tuning Pack, one of the Oracle management packs and is available for purchase with Enterprise Edition. For more information see The Oracle Technology Network or the online documentation

Software and Hardware Requirements

The following is a list of software requirements:

Oracle Database 11g Enterprise Edition with access to the Tuning and Diagnostic management packs and with the sample schema installed.
Oracle SQL Developer 3.0.

Prerequisites

Before starting this tutorial, you should:

1 .	Install Oracle SQL Developer 3.0 from OTN. Follow the readme instructions here.
2 .	Install Oracle Database 11g with the Sample schema.

Creating a Database Connection

The first step to managing database objects using Oracle SQL Developer 3.0 is to create a database connection.

Perform the following steps to create a database connection:

Note: If you already have database connections for SCOTT and SYSTEM, you do not need to perform the following steps. You can move to Providing Privileges to the Scott User topic.

If you have installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer and move to Step 4. If you do not have the icon located on your desktop, perform the following steps to create a shortcut to launch SQL Developer 3.0 from your desktop.

Open the directory where the SQL Developer 3.0 is located,right-click sqldeveloper.exe(on Windows) or sqldeveloper.sh (on Linux) and select Send to > Desktop (create shortcut).

On the desktop, you will find an icon named Shortcut to sqldeveloper.exe. Double-click the icon to open SQL Developer 3.0.

Note: To rename it, select the icon and then press F2 and enter a new name.

SQL Having

noreply@blogger.com (Anonymous) — Thu, 06 Sep 2012 16:43:00 +0000

SQL Group By Examples of SQL Group By and SQL Having Null in SQL Group By

The SQL HAVING clause allows us to restrict the data that is sent to the GROUP BY clause.

Group functions cannot be used in the WHERE clause. SQL statement can have both a WHERE clause and an HAVING clause. WHERE filters data before grouping and HAVING filters the data after grouping.

A WHERE clause is useful in both grouped and ungrouped queries, while a HAVING clause should appear only immediately after the GROUP BY clause in a grouped query.

According to Wikipedia (http://en.wikipedia.org) HAVING statement in SQL specifies that a SQL SELECT statement should only return rows where aggregate values meet the specified conditions.

An SQL statement with the HAVING clause may or may not include the GROUP BY clause.

HAVING allows a user to perform conditional tests on aggregate values. It is often used in combination with GROUP BY. With HAVING, you can include or exclude groups based on the aggregate value for that group.

Example 1 of SQL HAVING

Find the average salary of for each department that has either more than 1 employee or starts with a “To”:

SELECT Dept, AvgSal=(AVG(Salary))

FROM Employee

GROUP BY Dept

HAVING COUNT(Name) > 1

OR

Dept LIKE “To”;

Example 2 of SQL HAVING

Workforce (workforceno, name, position, salary, email, dcenterno)

For each distribution center with more than one member of workforce, find the number of workforce working in each of the centers and the sum of their salaries.

SELECT dCenterNo, COUNT (workforceNo) AS totalworkforce,

SUM(salary) AS totalSalary

FROM workforce

GROUP BY dCenterNo

HAVING COUNT (workforceNo) > 1

ORDER BY dCenterNo;

Important points about SQL HAVING:

Aggregates cannot be used in a WHERE clause; they are used only inside HAVING.

Similar to the WHERE clause, the HAVING clause requires that the column names that appear in the clause must also appear as column names in the GROUP BY clause.

Similar to the WHERE clause, it is ok for column names not appearing in the GROUP BY clause to appear as arguments to aggregate functions.

SQL Group By

noreply@blogger.com (Anonymous) — Thu, 06 Sep 2012 16:24:00 +0000

SQL Having Examples of SQL Group By and SQL Having Null in SQL Group By

It is a clause in SQL, which specifies how to report the output of the query. Allows one to define a subset of the values of a particular field and to apply an aggregate function to the subsets.

We normally use a GROUP BY clause in conjunction with an aggregate expression (like SUM, COUNT etc).

Example 1 of SQL Group BY

Calculate the total sales for each store in the following table

Store_name	Sales	Date
London	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
London	$300	Jan-08-1999
Boston	$700	Jan-08-1999

First, we need to make sure we select the store name as well as total sales.

SELECT store_name,SUM (Sales)

FROM Store_Information;

Second, we need to make sure that all the sales figures are grouped by stores.

SELECT store_name, SUM (Sales)

FROM Store_Information

GROUP BY store_name;

So the final query is:

SELECT store_name, SUM (Sales)

FROM Store_Information

GROUP BY store_name;

The result is:

Store_name	SUM (Sales)
London	$1800
San Diego	$250
Boston	$700

Example 2 of SQL Group BY

SELECT COUNT (*) FROM t_state

The above statement returns the total number of rows in the table. We can use GROUP BY to count the number of offices in each state.

With GROUP BY, the table is split into groups by state, and COUNT (*) is applied to each group in turn.

SELECT state, COUNT (*)

FROM t_state

GROUP BY state;

Important points to remember:

Group by cannot use column aliasing. A GROUP BY clause must contain the column or expressions on which to perform the grouping operation. For example:

Incorrect way:

Select deptno as department,count(*) as cnt

From emp

Group by department;

Correct way is:

Select deptno as department, count (*) as cnt

From emp

Group by deptno;

What is the difference between the outputs of the following two queries?

Statement 1:

SELECT COUNT (*),SUM (comm)

FROM hr.employees;

Statement 2:

SELECT COUNT (comm), SUM (comm)

FROM hr.employees;

The COUNT (*) will count all rows in the table.

The COUNT (comm) will count only the number commission values that appear in the table. If there are any rows with a NULL commission, statement 2 will not count them.

Restriction on SELECT Lists with Aggregation

If any aggregation is used, then each element of a SELECT clause must either be aggregated or appear in a group-by clause. i.e. as a rule, when using GROUP BY and aggregate functions, any items in the SELECT list not used as an argument to an aggregate function must be included in the GROUP BY clause.

Examples of SQL Having & SQL Group By

noreply@blogger.com (Anonymous) — Thu, 06 Sep 2012 15:58:00 +0000

SQL Having SQL Group By Null in SQL Group By

Some of the examples found on the Internet:

Find the average sales price for each beer.

Sells (bar, beer, price)

SELECT beer, AVG (price)

FROM Sells

GROUP BY beer;

Find, for each drinker, the average price of Bud at the bars they frequent.

Sells (bar, beer, price)

Frequents (drinker, bar)

SELECT drinker, AVG (price)

FROM Frequents, Sells

WHERE beer = 'Bud' AND

Frequents.bar = Sells.bar

GROUP BY drinker;

Find the average price of those beers that are either served in at least 3 bars or manufactured by Anheuser-Busch.

Beers (name, manf)

Sells (bar, beer, price)

SELECT beer, AVG (price)

FROM Sells

GROUP BY beer

HAVING

COUNT (*) > = 3 OR

beer IN(

SELECT name

FROM Beers

WHERE manf = 'Anheuser-Busch');

Examples of Joins

noreply@blogger.com (Anonymous) — Thu, 06 Sep 2012 11:30:00 +0000

Examples of Inner Join, Left Outer Join, Right Outer Join & Full Join

An Example of INNER JOIN

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons with any orders.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

INNER JOIN Orders

ON Persons.P_Id=Orders.P_Id

ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

[Via]

An Example of LEFT OUTER JOIN

XXTable:

XX	ID
xx1	1
xx2	2
xx3	3
xx4	4
xx5	5
xx7	7

YYTable:

ID	YY
1	yy1
2	yy2
3	yy3
4	yy4
6	yy6
7	yy7

SELECT xx, xxtable.id, yytable.id, yy

FROM xxtable

LEFT OUTER JOIN yytable

ON xxtable.id=yytable.id;

XX	XXtable.id	YYtable.id	YY
xx1	1	1	yy1
xx2	2	2	yy2
xx3	3	3	yy3
xx4	4	4	yy4
xx5	5	-
xx7	7	7	yy7

[Via]

An Example of RIGHT OUTER JOIN

(taking same XXtable & YYtable tables as above)

Now, let's say we wanted all of the rows in the table 2 (yy table, the "right" table) in our result, regardless of whether they matched with rows in table 1 (the xx table, the "left" table), we could perform a RIGHT OUTER JOIN between tables xxtable and yytable.

The following SQL syntax:

SELECT xx, xxtable.id, yytable.id, yy

FROM xxtable

RIGHT OUTER JOIN yytable

ON xxtable.id=yytable.id;

would give the following result:

XX	XXtable.id	YYtable.id	YY
xx1	1	1	yy1
xx2	2	2	yy2
xx3	3	3	yy3
xx4	4	4	yy4
-	-	6	yy6
xx7	7	7	yy7

In this example, there is a NULL value for the fields xxtable.id and xx where yytable.id=6 since xxtable does not have a record with xxtable.id=6. Note that all of the records of the right table in a RIGHT OUTER JOIN get displayed, regardless of whether or not they match rows in the left table.

An Example of FULL JOIN

Table P

p_id	LastName	FirstName	City
1	Dhall	Sachin	Delhi
2	Gupta	Pankaj	Bangalore
3	Kumar	Sanjeev	Chandigarh

Table O

o_id	OrderNo	p_id
1	111	3
2	222	3
3	333	14
4	444	2
5	555	2

SELECT P.LastName, P.FirstName, O.OrderNo

FROM P

FULL JOIN O

ON P.P_Id=O.P_Id

ORDER BY P.LastName

LastName	FirstName	OrderNo
NULL	NULL	333
Dhall	Sachin	NULL
Gupta	Pankaj	444
Gupta	Pankaj	555
Kumar	Sanjeev	111
Kumar	Sanjeev	222

Null Values in SQL Group By

noreply@blogger.com (Anonymous) — Thu, 06 Sep 2012 11:22:00 +0000

SQL Having SQL Group By Examples of SQL Group By and SQL Having

It is known that Null <> Null. When two different NULL values are compared, the result is NULL (not TRUE), i.e. the two NULL values are not considered to be equal. Applying the same rule to the GROUP BY clause would force SQL to place each row with a NULL grouping column into a separate group by itself.

But creating a separate group for every row with a NULL in a grouping column is confusing and of no useful value, so designers wrote the SQL standard such that NULL values are considered equal for the purposes of a GROUP BY clause. Therefore, if two rows have NULL values in the same grouping columns and matching values in the remaining non-NULL grouping columns, the DBMS will group the rows together.

Simply put if the grouping column contains more than one null value, the null values are put into a single group.

For example, the grouped query:

SELECT A, B, SUM(amount_purchased) AS 'C'

FROM customers

GROUP BY A, B

ORDER BY A, B

will display a results table similar to

A	B	C
NULL	NULL	61438.0000
NULL	101	196156.0000
AZ	NULL	75815.0000
AZ	103	36958.0000
CA	101	78252.0000
LA	NULL	181632.0000

for CUSTOMERS that contain the following rows.

A	B	Amount purchased
NULL	NULL	45612.00000
NULL	NULL	15826.00000
NULL	101	45852.0000
NULL	101	74815.0000
NULL	101	75489.0000
AZ	NULL	75815.0000
AZ	103	36958.0000
CA	101	78252.0000
LA	NULL	96385.0000
LA	NULL	85247.0000

Explicit v/s. Implicit SQL Joins

noreply@blogger.com (Anonymous) — Thu, 06 Sep 2012 09:45:00 +0000

The explicit join is easier to read and the implicit syntax is difficult to understand and more prone to errors. Moreover implicit syntax is now a day’s outdated.

SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation":

The "explicit join notation" uses the JOIN keyword to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:

SELECT *

FROM employee INNER JOIN department

ON employee.DepartmentID = department.DepartmentID;

The "implicit join notation" simply lists the tables for joining (in the FROM clause of the SELECT statement), using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates (which function comparably to the join-predicates in the explicit notation).

The following example shows a query which is equivalent to the one from the previous example, but this time written using the implicit join notation:

SELECT *

FROM employee, department

WHERE employee.DepartmentID = department.DepartmentID;

[Via]

Programming Is Fun

control file autobackup to recover from the lost all control files -- oracle 10g

Summary of the testing steps

Detailed steps:

Rename and drop table columns

Oracle PL SQL cursor For loops

PL/SQL Ref Cursors

PL/SQL Explicit Cursors

PL SQL Implicit cursors

Oracle PL/SQL Cursors

Difference between EXEC & EXECUTE()

sp_execute of pl sql

[sp_executesql in comparison to Execute]

Performance tuning - 02

ASP.NET books

PL SQL books

SQL books

SQL Basics

DDL - Data Definition Language.

DML - Data Manipulation Language.

DCL - Data Control Language

SQL Syntax

SQL - The language

The SELECT Statement

SQL Insert

SQL tuning advisor

SQL Having

SQL Group By Examples of SQL Group By and SQL Having Null in SQL Group By

The SQL HAVING clause allows us to restrict the data that is sent to the GROUP BY clause.

Group functions cannot be used in the WHERE clause. SQL statement can have both a WHERE clause and an HAVING clause. WHERE filters data before grouping and HAVING filters the data after grouping.

A WHERE clause is useful in both grouped and ungrouped queries, while a HAVING clause should appear only immediately after the GROUP BY clause in a grouped query.

According to Wikipedia (http://en.wikipedia.org) HAVING statement in SQL specifies that a SQL SELECT statement should only return rows where aggregate values meet the specified conditions.

An SQL statement with the HAVING clause may or may not include the GROUP BY clause.

HAVING allows a user to perform conditional tests on aggregate values. It is often used in combination with GROUP BY. With HAVING, you can include or exclude groups based on the aggregate value for that group.

Example 1 of SQL HAVING

Find the average salary of for each department that has either more than 1 employee or starts with a “To”:

SELECT Dept, AvgSal=(AVG(Salary))

FROM Employee

GROUP BY Dept

HAVING COUNT(Name) > 1

OR

Dept LIKE “To”;

Example 2 of SQL HAVING

Workforce (workforceno, name, position, salary, email, dcenterno)

For each distribution center with more than one member of workforce, find the number of workforce working in each of the centers and the sum of their salaries.

SELECT dCenterNo, COUNT (workforceNo) AS totalworkforce,

SUM(salary) AS totalSalary

FROM workforce

GROUP BY dCenterNo

HAVING COUNT (workforceNo) > 1

ORDER BY dCenterNo;

Important points about SQL HAVING:

Aggregates cannot be used in a WHERE clause; they are used only inside HAVING.

Similar to the WHERE clause, the HAVING clause requires that the column names that appear in the clause must also appear as column names in the GROUP BY clause.

Similar to the WHERE clause, it is ok for column names not appearing in the GROUP BY clause to appear as arguments to aggregate functions.

SQL Group By

SQL Having Examples of SQL Group By and SQL Having Null in SQL Group By

It is a clause in SQL, which specifies how to report the output of the query. Allows one to define a subset of the values of a particular field and to apply an aggregate function to the subsets.

We normally use a GROUP BY clause in conjunction with an aggregate expression (like SUM, COUNT etc).

Example 1 of SQL Group BY

Calculate the total sales for each store in the following table

Store_name

Sales

Date

London

$1500

Jan-05-1999

San Diego

$250

Jan-07-1999

London

$300

Jan-08-1999

Boston

$700

Jan-08-1999

First, we need to make sure we select the store name as well as total sales.

SELECT store_name,SUM (Sales)

FROM Store_Information;

Second, we need to make sure that all the sales figures are grouped by stores.