The Magic Of ROWNUM

The “ROWNUM greater than” query never fails to have an eye-popping effect  the first time anyone sees it. If you haven’t worked with ROWNUM much before, be prepared!

First things first. What is ROWNUM?

ROWNUM is a pseudocolumn, assigning a number to every row returned by a query. The numbers follow the sequence 1, 2, 3…N, where N is the total number of rows in the selected set. This is useful when you want to do some filtering based on the number of rows, such as:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL> -- Rownum to limit result set SQL> -- to three rows only SQL> select empno   2       , ename   3       , sal   4       , rownum   5  from emp   6  where rownum < 4;        EMPNO ENAME        SAL     ROWNUM ---------- ---------- ----- ----------       7369 SMITH        800          1       7499 ALLEN       1600          2       7521 WARD        1250          3

So out of those three rows if I want to select only rownum = 2, this should work. Right?

1 2 3 4 5 6 7 8

-- Query attempt to select row -- with rownum = 2 select empno      , ename      , sal      , rownum from emp where rownum = 2;

Run it on SQL.

1 2 3 4 5 6 7 8 9 10

SQL> -- Query attempt to select rows SQL> -- with rownum = 2 SQL> select empno   2       , ename   3       , sal   4       , rownum   5  from emp   6  where rownum = 2;   no rows selected

What just happened?

Why did adding rownum = 2 return no results?

How ROWNUM Works

Here is the secret. Rownum values are not preassigned, they are determinedon the fly, as the rows are output. The common misconception is that every row in the table has a permanent ROWNUM. In truth, rows in a table are not ordered or numbered – you cannot ask for row#5 from the table, there is no such thing.

The pseudocode for a query using rownum is:

rownum = 1 
for x in ( select * from query) 
loop 
    if ( x satisfies the predicate ) 
    then  
          output the row 
          rownum = rownum + 1 
    end if; 
end loop;

The first selected row is always assigned rownum = 1, and is tested against the predicate. When the test is "< 4", rownum = 1 passes the test and the rownum is set to 2, and so the loop continues. The first 3 rows pass the test and get printed out, till rownum becomes 4 and fails the test.

When the test is "= 2", the first row itself does not pass the test (since it is rownum = 1). The increment never happens and no rows get printed.

All of which explains why the WHERE condition can only filter on what rownum is less than, not what it is great than.

Summary

ROWNUM is a pseudocolumn that assigns a number to every row returned by a SQL query. It can be of great use in filtering data based on the number of rows returned by the query.

ROWNUM gets its value as the query is executed, not before, and gets incremented only after the query passes the WHERE clause. Therefore, your WHERE condition can filter data based on "rownum < 2/3/4/." but not "rownum > 2/3/4.". The second filter will invariably return no rows selected.

6 Reasons Why You Should Use PL/SQL Packages

A package is:

…a database object that groups together logically related procedures/functions, and other constructs like variables, constants, PL/SQL types, cursors, and exceptions.

One may well ask: when it’s possible to write standalone procedures/functions and define related variables et al within them, why have packages at all?

This article highlights six key reasons that make PL/SQL packages one of the most useful elements of PL/SQL programming. In a PL/SQL development environment, you simply cannot do without them.

1. Organized code management Packages act like containers for related subprograms, types and items. Say you have several subprograms for inventory management (add_item, modify_item and so on) in your application – all of it can be bundled into a single inventory_mgmtpackage. This is of great help in application development as it makes code easy to locate, understand and reuse.
2. Easy (top-down) application design A crucial feature of a package is the separation between its public interface (specification or specs), and its implementation (body). You can code and compile a package specification without its body.All you need for dependent programs to work is the interface information in the package specs. The package as well as the stored subprograms that reference it, will compile successfully. This means that you can develop the application in a planned, modular fashion and need not face bottlenecks due to incomplete implementation details. This is of special significance in top-down application development.
3. Painless implementation changes Changes to a subprogram can create havoc with other programs that reference it. Not so with packages. If only code in the package body is changed, no change is needed in the dependent objects. Not even a recompile is required.
4. Security and maintainability through private code With packages, you can specify which subprograms, types and items are public (visible and accessible outside the package) or private (hidden and inaccessible outside the package). For example, if a package contains four subprograms, three might be public and one private.The ability to have private portions in a package ensures that subprograms and other constructs that need not, or SHOULD not, be accessible publicly – remain hidden. This protects the integrity of the package, and also simplifies maintenance – any change to a private element impacts only this package and nothing else.
5. New functionality: Session-wide persistence of public variables Packaged public variables and cursors persist for the duration of a session. They let you maintain data across transactions without storing it in the database. All subprograms that run in the environment share public package variables and cursors, and will read/edit the same values.This unique feature of PL/SQL packages can be used by applications for storing values with session-wide relevance, such as trace/debug options. (Caution: Judicious use is recommended for editing public variable values, as it can make the application messy and error-prone.)
6. Better Performance When you invoke a packaged subprogram for the first time, the entire package is loaded into memory. Later calls to subprograms in the same package need no disk I/O. This translates to better performance.

Given all the benefits of PL/SQL packages, it is recommended that you them as the default program unit for PL/SQL subprograms rather than standalone procedures and functions.

VARRAY

Example 1

The following program illustrates using varrays:

DECLARE

   type namesarray IS VARRAY(5) OF VARCHAR2(10);

   type grades IS VARRAY(5) OF INTEGER;

   names namesarray;

   marks grades;

   total integer;

BEGIN

   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');

   marks:= grades(98, 97, 78, 87, 92);

   total := names.count;

   dbms_output.put_line('Total '|| total || ' Students');

   FOR i in 1 .. total LOOP

      dbms_output.put_line('Student: ' || names(i) || '

      Marks: ' || marks(i));

   END LOOP;

END;

/

When the above code is executed at SQL prompt, it produces the following result:

Student: Kavita  Marks: 98

Student: Pritam  Marks: 97

Student: Ayan  Marks: 78

Student: Rishav  Marks: 87

Student: Aziz  Marks: 92

PL/SQL procedure successfully completed.

Example 2

Select * from customers;

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

+----+----------+-----+-----------+----------+

Following example makes use of cursor, which you will study in detail in a separate chapter.

DECLARE

   CURSOR c_customers is

   SELECT  name FROM customers;

   type c_list is varray (6) of customers.name%type;

   name_list c_list := c_list();

   counter integer :=0;

BEGIN

   FOR n IN c_customers LOOP

      counter := counter + 1;

      name_list.extend;

      name_list(counter)  := n.name;

      dbms_output.put_line('Customer('||counter ||'):'||name_list(counter));

   END LOOP;

END;

/

When the above code is executed at SQL prompt, it produces the following result:

Customer(1): Ramesh

Customer(2): Khilan

Customer(3): kaushik   

Customer(4): Chaitali

Customer(5): Hardik

Customer(6): Komal

PL/SQL procedure successfully completed.

Wireless Hotspot - From Ethernet (Windows 7)

This step will guide you through enabling the "Microsoft Virtual WiFi Miniport Adapter": From my knowledge the "Microsoft Virtual WiFi Miniport Adapter" is not compatible with all wireless cards but if it yours is compatible, and you follow my guide, it will work.

To enable the Virtual adapter open the "Run" dialogue ("Windows key"+ "R")

Then open the Command Prompt (type in "cmd" and click "Enter")

Once CMD is open type in the following command:

netsh wlan set hostednetwork mode=allow ssid=Hotspot key=PasswordkeyUsage=persistent

Where "Hotspot" is the SSID (the name of your connection) and "Password" is the password locking the connection.

Once this is done, you have enabled the Microsoft Virtual WiFi Miniport Adapter!

Windows 7 has a feature called "VirtualWifi". It allows you to split your WLAN access in Windows 7 on your Laptop or PC and to act like an accesspoint for other Laptops, so you can share your internet connection.

1.	Enable VirtualWifi You must run a command line with admin rights. Type in: netsh wlan set hostednetwork mode=allow ssid="MySSID" key="Mykey" keyUsage=persistent Leave the commanline-window open
2.	Check the new virtual wifi adaptor Go to your Device Manager and check if you see a "Microsoft Virtual Wifi Miniport Adaptor" when expand the network adaptors. If you check your wireless network cards in your network and sharing center you´ll see a second wireless network connection.
3.	Start the virtual network card Go back to the admin-rights command shell and type in: netsh wlan start hostednetwor