High Power 1Watt (1000mW) 27dBm 802.11b/g Wirelss Router - this is a router Amplifier and Booster Combo - with 4 Port Switch -Improving 3X Signal Reception and 7XSpeed (Compare to a regular 802.11g router)

This High Power AP/Router w/built-in 4 LAN ports fully supports the features and functional compliance of IEEE 802.11b/g standard to provide high-speed data transmission up to 54Mbps and can increase the coverage range efficiently. With built-in 1W amplifier, it is increasing your signal coverage up to 3X, and speed up to 7X. Getting connected for long distant is what this high power can offer while other router can't. You will never need an external WiFi signal booster/amplifier anymore.

Features:
* High Power with 5dBi detachable antenna
* 1W amplifier booster built-in
* Distance up to 3300ft (1km) (with high power adapter 500mW on recipiency end)*
* Wireless Data transfer rates up to 54Mbps
* Provides one 10/100Mbps Auto-Negotiation Ethernet WAN ports for WAN connection
* Provides four 10/100Mbps Auto-Negotiation Ethernet LAN ports for LAN connections
* Supports remote/local Web management
* TCP/UDP/ICMP/ARP protocol stack
* UPnP, Dynamic DNS, Static Routing, VPN Pass-through.
* WDS
* DHCP Client and Server
* WAN access mode
* PPPoE PPTP
* Fixed IP address

Price:

Click here to buy from Amazon

Read more »

Saturday Night Live - The Best of Mike Myers (Bonus Edition)

SNL:BEST OF MIKE MYERS - DVD Movie

Price: $9.98

Click here to buy from Amazon

Read more »

At Home With Friends (Special Edition with Bonus Track, "White Christmas")

Special edition includes bonus track, "White Christmas", featuring Chris Botti! At Home With Friends is inspired by Joshua Bell's fondness for hosting informal musicales in his Manhattan home. Join the Grammy Award-winning violinist as he celebrates the many musical friendships he has made over the years - a diverse group of artists sharing their love of music in an intimate setting. Features vocal and instrumental duets with Chris Botti, Sting, Josh Groban, Kristin Chenoweth, Regina Spektor and many others.

Track listing:

1. I Loves You Porgy (featuring Chris Botti)

2. Come Again (featuring Sting)

3. Oblivion (featuring Carel Kraayenhof)

4. Cinema Paradiso (featuring Josh Groban)

5. Para Tí (featuring Tiempo Libre)

6. My Funny Valentine (featuring Kristin Chenoweth)

7. Maybe So (featuring Edgar Meyer, Sam Bush and Mike Marshall)

8. Grieg: Violin Sonata No. 3, Movement II (featuring Sergei Rachmaninoff (Zenph Re-Performance))

9. Eleanor Rigby (featuring Frankie Moreno)

10. O, Cease Thy Singing, Maiden Fair, Op. 4 No. 4 (featuring Nathan Gunn)

11. Il Postino (featuring Carel Kraayenhof)

12. Left Hand Song (featuring Regina Spektor)

13. Chovendo Na Roseira (featuring Dave Grusin)

14. Look Away (featuring Edgar Meyer and Chris Thile)

15. Variant Moods - Duet For Sitar & Violin (Abridged Version) (featuring Anoushka Shankar)

16. I'll Take Manhattan (featuring Chris Botti)

Bonus track:

17. White Christmas (featuring Chris Botti)

Price:

Click here to buy from Amazon

Read more »

Wilton Rubber Ducky Guest Book, Baby Shower

WIL 1003-1074 SIZE: EACH DUCKY BABY BOOK Wilton 1003-W-1074 Cake-Party WILTON Cake, Candy, Baking, Bakeware & Party Supplies WILTON BABY PARTY PRODUCTS

Price:

Click here to buy from Amazon

Read more »

Walt Disney Treasures - Your Host, Walt Disney

Celebrate Walt Disney and his magical vision with this look back at several of the most memorable hours from his groundbreaking television shows. As its friendly approachable host Disney endeared himself to millions and became much more than an icon for family entertainment. He became Uncle Walt. Join Walt as he celebrates the rededication of Disneyland with a parade of celebrities and guest stars the fourth anniversary of his weekly show featuring a surprise party arranged by the Mouseketeers and a tenth anniversary program showcasing some of his talented Imagineers at work. And in a rare interview Diane Disney Miller shares warm and personal memories of growing up with the man we all admired from afar.System Requirements:Running Time 388 Mins.Format: DVD MOVIE Genre: CHILDREN/FAMILY Rating: NR UPC: 786936702255 Manufacturer No: 5035103

Price: $32.99

Click here to buy from Amazon

Read more »

Baby Shower Game ~ Charades

Throwing a baby shower? Here's a fun card game to entertain your guests! A fun icebreaker for your party, this fun form of baby charades challenges guests to see just how baby savvy they are! Here's what you do: divide your guests up into two teams. Players take turns drawing a card from the deck. Each card includes a baby word and number of points. Players act out the words while the remaining team members try to guess the word. The first team to earn 15 points wins! What a fun game to liven up a baby shower! (52 cards per game deck) 3-1/2"

Price:

Click here to buy from Amazon

Read more »

Underscore character (_) in PL/SQL.

The underscore character is a special wildcard character or we can say it is a special SQL pattern which used to represent a single character in a string. This pattern match to a single character and should be escaped if you are in fact looking for an underscore character in the LIKE clause of a query. It can be used in any position and any number of times within the string. Just add the following after a LIKE statement: '_ABC' Underscore character ( _ ) is a very clear separator, it is used to name your variables. That gives you lots of room to come up with unambiguous names. Example:

SQL> -- create demo table
SQL> create table Student(
2    ID                        VARCHAR2(4 BYTE)         NOT NULL,
3    First_Name           VARCHAR2(10 BYTE),
4    Last_Name           VARCHAR2(10 BYTE),
5    DOB                     DATE,
6    Fees                     Number(8,2),
7    City                      VARCHAR2(10 BYTE),
8    Field                     VARCHAR2(15 BYTE)
9  )
10  / Table created. SQL>
SQL> -- prepare data
SQL> insert into Student(ID,  First_Name, Last_Name, DOB,                     End_Date,                       Fees,  City,       Field)
2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
3  / 1 row created. SQL> insert into Student(ID,  First_Name, Last_Name, DOB,                     End_Date,                       Fees,  City,       Field)
2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3  / 1 row created. SQL> insert into Student(ID,  First_Name, Last_Name, DOB,                     End_Date,                       Fees,  City,       Field)
2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3  / 1 row created. SQL> insert into Student(ID,  First_Name, Last_Name, DOB,                     End_Date,                       Fees,  City,       Field)
2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3  / 1 row created. SQL> insert into Student(ID,  First_Name, Last_Name, DOB,                     End_Date,                       Fees,  City,       Field)
2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3  / 1 row created. SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Student
2  /
ID   FIRST_NAME  LAST_NAME       DOB         Fees      CITY                 Field
---- --------------   --------------  -----------   ---------  --------             ----------
01    Harsh               Gupta        25-JUL-06    1234.56   Toronto        Programmer
02    Joliy                 Pera          21-FEB-86    6661.78   Vancouver    Tester
03    Cutter              Selmon     15-MAR-90    6544.78   Vancouver   Tester
04    Brad                 tom          21-APR-99    2344.78   Vancouver    Manager
05    Noman             Javk          08-AUG-98   2334.78   Vancouver    Tester 5 rows selected. SQL>
SQL>
SQL> SELECT * FROM Student WHERE first_name LIKE '_arsh ';
ID   FIRST_NAME   LAST_NAME     DOB           Fees        CITY          Field
---- ----------         --------------  ------------   ---------    --------      ----------
01    Harsh               Gupta         25-JUL-06    1234.56   Toronto       Programmer
1 rows selected.

Read more »

Learning Web Design: A Beginner's Guide to (X)HTML, StyleSheets, and Web Graphics

Everything you need to know to create professional web sites is right here. Learning Web Design starts from the beginning -- defining how the Web and web pages work -- and builds from there. By the end of the book, you'll have the skills to create multi-column CSS layouts with optimized graphic files, and you'll know how to get your pages up on the Web.

This thoroughly revised edition teaches you how to build web sites according to modern design practices and professional standards. Learning Web Design explains:

• How to create a simple (X)HTML page, how to add links and images
• Everything you need to know about web standards -- (X)HTML, DTDs, and more
• Cascading Style Sheets -- formatting text, colors and backgrounds, using the box model, page layout, and more
• All about web graphics, and how to make them lean and mean through optimization
• The site development process, from start to finish
• Getting your pages on the Web -- hosting, domain names, and FTP

The book includes exercises to help you to learn various techniques, and short quizzes to make sure you're up to speed with key concepts. If you're interested in web design, Learning Web Design is the place to start.

Price: $44.99

Click here to buy from Amazon

Read more »

Combining LIKE and UPPER operator.

You learned that the LIKE operator is used in character string comparisons with pattern matching it compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not. The pattern can include the two "wildcard" characters underscore (_) and percent sign (%). It allows you to use wildcards in the where clause of an PL/SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.

PL/SQL is not case sensitive, so lower-case letters are equivalent to corresponding upper-case letters except within string and character literals. In the given below example you see how we can combine these operators: Example:
SQL> -- create demo table
SQL> create table Student(
2    ID                        VARCHAR2(4 BYTE)         NOT NULL,
3    First_Name           VARCHAR2(10 BYTE),
4    Last_Name           VARCHAR2(10 BYTE),
5    DOB                     DATE,
6    Fees                     Number(8,2),
7    City                      VARCHAR2(10 BYTE),
8    Field                     VARCHAR2(15 BYTE)
9  )
10  / Table created. SQL>
SQL> -- prepare data
SQL> insert into Student(ID,  First_Name, Last_Name, DOB,                     End_Date,                       Fees,  City,       Field)
2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
3  / 1 row created. SQL> insert into Student(ID,  First_Name, Last_Name, DOB,                     End_Date,                       Fees,  City,       Field)
2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3  / 1 row created. SQL> insert into Student(ID,  First_Name, Last_Name, DOB,                     End_Date,                       Fees,  City,       Field)
2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3  / 1 row created. SQL> insert into Student(ID,  First_Name, Last_Name, DOB,                     End_Date,                       Fees,  City,       Field)
2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3  / 1 row created. SQL> insert into Student(ID,  First_Name, Last_Name, DOB,                     End_Date,                       Fees,  City,       Field)
2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3  / 1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Student
2  /
ID   FIRST_NAME  LAST_NAME       DOB         Fees      CITY                 Field
---- --------------   --------------  -----------   ---------  --------             ----------
01    Harsh               Gupta        25-JUL-06    1234.56   Toronto        Programmer
02    Joliy                 Pera          21-FEB-86    6661.78   Vancouver    Tester
03    Cutter              Selmon     15-MAR-90    6544.78   Vancouver   Tester
04    Brad                 tom          21-APR-99    2344.78   Vancouver    Manager
05    Noman             Javk          08-AUG-98   2334.78   Vancouver    Tester 5 rows selected. SQL>
SQL>
SQL> SELECT * FROM Student
2  WHERE  UPPER(first_name) LIKE '%JA%';
ID   FIRST_NAME   LAST_NAME     DOB           Fees        CITY          Field
---- ----------         --------------  ------------   ---------    --------      ----------
01    Harsh               Gupta         25-JUL-06    1234.56   Toronto       Programmer
03    Cutter              Selmon       15-MAR-90   6544.78   Vancouver  Tester
2 rows selected.

Read more »

Effectively Using/Work with LIKE Operator in PL/SQL Asp.Net C#

The LIKE operator is used in character string comparisons with pattern matching it compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not. The pattern can include the two "wildcard" characters underscore (_) and percent sign (%). It allows you to use wildcards in the where clause of an PL/SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete. Underscore matches exactly one character. Percent sign (%) matches zero or more characters. The syntax for a condition using the LIKE operator is shown in this diagram:

Example:
SQL> create table Worker(
2    Worker_ID                  VARCHAR2(4 BYTE)         NOT NULL,
3    Worker_Name              VARCHAR2(10 BYTE),
4    Joining_Date               DATE,
5    Salary                        Number(8,2),
6    City                           VARCHAR2(10 BYTE),
7    Designation                 VARCHAR2(15 BYTE)
8  )
9  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Worker(Worker_ID,  Worker_Name,       Joining_Date,                       Salary,  City,       Designation)
2               values ('01','Jason',  , to_date('20060725','YYYYMMDD'), 1234.56, 'NY',  'TL')
3  /
1 row created.
SQL> insert into Worker(Worker_ID,  Worker_Name,       Joining_Date,                       Salary,  City,       Designation)
2                values('02','Black',   to_date('19760321','YYYYMMDD'),, 6661.78, 'Torr.','Executive')
3  /
1 row created.
SQL> insert into Worker(Worker_ID,  Worker_Name,       Joining_Date,                       Salary,  City,       Designation)
2                values('03','Mathews',  to_date('19900315','YYYYMMDD'), 6544.78, 'Torr.','Executive')
3  /
1 row created.
SQL> insert into Worker(Worker_ID,  Worker_Name,      Joining_Date,                       Salary,  City,       Designation)
2                values('04','Green',  to_date('19821024','YYYYMMDD'), , 2344.78, 'Torr.','Manager')
3  /
1 row created.
SQL> insert into Worker(Worker_ID,  Worker_Name,      Joining_Date,                       Salary,  City,       Designation)
2                values('05','Albert', to_date('19980808','YYYYMMDD'), 2334.78, 'Torr.','Executive')
3  /
1 row created.
SQL> insert into Worker(Worker_ID,  Worker_Name,      Joining_Date,                       Salary, City,        Designation)
2                values('06','Roma',   to_date('19870730','YYYYMMDD'), , 4322.78,'Van.',  'Executive')
3  /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Worker
2  /
Worker_ID      Worker_Name     Joining_Date        Salary      City      Designation
------------   ---------------   -------------    ----------   ------  ---------------
01                Jason            25-JUL-06        1234.56      NY        TL
02                 Black            21-MAR-76       6661.78      Torr.      Executive
03                 Mathews       15-MAR-90       6544.78      Torr.      Executive
04                 Green           24-OCT-82       2344.78      Torr.      Manager
05                 Albert           08-AUG-98       2334.78      Torr.      Executive
06                 Roma            30-JUL-87        4322.78      Van.       Executive
6 rows selected.
SQL>
SQL>
SQL>
SQL> SELECT * FROM Worker WHERE Worker_Name LIKE '_o%';
Worker_ID      Worker_Name     Joining_Date      Salary       City        Designation
-----------   ----------------  -------------    ----------  -------   ------------
05               Albert         08-AUG-98        2334.78      Torr.       Executive

Read more »

Effectively Using LIKE Operator with Subquery in PL/SQL Asp.Net C#

As you learned in previous article, the LIKE operator is used in character string comparisons with pattern matching it compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not. The pattern can include the two "wildcard" characters underscore (_) and percent sign (%).

And the subquery is a SELECT statement that is nested within another SQL statement it is often referred to as an inner query, and the statement within which it occurs is then called the outer query. There is nothing wrong with this terminology, except that it may imply that you can only have two levels, inner and outer.

A subquery can also have LIKE Operator, With the subquery out of the way, the containing query can now be evaluated they are most often found in the WHERE clause of a SELECT, UPDATE, or DELETE statement, as well as in the SET clause of an UPDATE statement.

Here you see how can we use this two PL/SQL statements together:

Example:

SQL> SELECT COUNT(*) Total_Stores, a.Brand
2      FROM dba_objects a
3      WHERE a.Brand NOT IN (SELECT b.Brand FROM dba_objects b
4      WHERE b.Brand LIKE 'S%')
5      GROUP BY a.Brand;
Total_Stores         Brand
-------------- --------------
1473              Pepssi
233              Hindustan
143               Cola
2389              Unilever
68              OUTLN
575               Bacardi
339               Nutralite
434               Cga
102               Amway
146               Oreflame
10 rows selected.

 

Read more »

ROUND() Function in Oracle SQL Plus

Home » Oracle » ROUND() Function in Oracle SQL Plus
ROUND() Function in Oracle SQL Plus
By  Jerry Gale March 07, 2011 The ROUND() Function is used to return the rounded values of a numeric field to the number of decimals specified.

Similar ArticlesMost ReadTop RatedLatest

ROUND() FUNCTION

The ROUND FUNCTION is used to round a numeric column. We need to specify the number of decimals.

Syntax :

SELECT column_name(s), ROUND(column_name, Decimals) FROM table_name;

Example :

Table Products:

From the above table we want to list Id, Product & rounded value of column Price (to one decimal place), we'll execute the following SQL Query:

SELECT Id, Product, ROUND(Price, 1) FROM Products;

The following result will be produced :

Now we want to list Id, Product & rounded value of column Price (without any decimal place), we'll execute the following SQL Query:

SELECT Id, Product, ROUND(Price, 0) FROM Products;

The following result will be produced :

Now we want to list Id, Product & rounded value of column Price (one digit to the left of the decimal place), we'll execute the following SQL Query:

SELECT Id, Product, ROUND(Price, -1) FROM Products;

The following result will be produced :

Now we want to list Id, Product & rounded value of column Price (two digit to the left of the decimal place), we'll execute the following SQL Query:

SELECT Id, Product, ROUND(Price, -2) FROM Products;

The following result will be produced :

Login to add your contents and source code to this article C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional consulting company, our consultants are well-known experts in .NET and many of them are MVPs, authors, and trainers. We specialize in Microsoft .NET development and utilize Agile Development and Extreme Programming practices to provide fast pace quick turnaround results. Our software development model is a mix of Agile Development, traditional SDLC, and Waterfall models.

View the original article here

Read more »

Creating Berkeley DBs and Adding Records

Since there is no GUI for Berkeley and no need for that(since its key-value pair oriented) we will be using a programming language to access its datas.I preferred creating a C#(Console) Application.

Im assuming you've already downloaded Berkeley DB from Oracle's website here

Lets create a new Console Application and then add reference to "libdb_dotnet51.dll" which is Oracle Berkeley DB's Managed Library for accessing Berkeley datas.

I've created a (.NET Framework 4.0) C# Console Application and named it "AccessingBerkeley"

Now Add Reference to this project and choose Browse tab and get to this location:

For 64-bit: C:\Program Files (x86)\Oracle\Berkeley DB 11gR2 5.1.25\bin
For 32-bit: C:\Program Files\Oracle\Berkeley DB 11gR2 5.1.25\bin

Choose libdb_dotnet51.dll.This is the Managed Library for Berkeley DB.

Now to introduce Berkeley Library in your code first you need to import its namespace to the project.

Use this code:

Dont forget to add a couple more namespaces:

using System;
using System.IO;
using System.Collections.Generic;
using System.Collections;
using System.Diagnostics;
using System.Text;
using System.Runtime.Serialization.Formatters.Binary;
using BerkeleyDB;

Then before creating our database and adding records you'll need to add some variables:

 BTreeDatabase btreeDB;
BTreeDatabaseConfig btreeConfig;
Cursor dbc;
DatabaseEntry key;
DatabaseEntry data;
string buff, dbFileName, keyString;

Now add these codes for creating database,checking if db exists and adding records:

  try
{
String pwd = Environment.CurrentDirectory;
pwd = Path.Combine(pwd, "..");
pwd = Path.Combine(pwd, "..");
if (IntPtr.Size == 4)
pwd = Path.Combine(pwd, "Win32");
else
pwd = Path.Combine(pwd, "x64");
#if DEBUG
pwd = Path.Combine(pwd, "Debug");
#else
pwd = Path.Combine(pwd, "Release");
#endif
pwd += ";" + Environment.GetEnvironmentVariable("PATH");
Environment.SetEnvironmentVariable("PATH", pwd);
}
catch (Exception e)
{
Console.WriteLine(
"Unable to set the PATH environment variable.");
Console.WriteLine(e.Message);
return;
}

try
{
dbFileName = "access.db";
if (args.Length > 0)
dbFileName = args[0];
}
catch
{
usage();
return;
}

if (File.Exists(dbFileName))
{
while (true)
{
Console.Write
("{0} already exists. Delete it? (y/n) ", dbFileName);
buff = Console.ReadLine().ToLower();
if (buff == "y" || buff == "n")
break;
}

if (buff == "y")
{
try
{
File.Delete(dbFileName);
}
catch
{
Console.WriteLine("Unable to delete {0}.", dbFileName);
return;
}
}
}

btreeConfig = new BTreeDatabaseConfig();
btreeConfig.Duplicates = DuplicatesPolicy.SORTED;
btreeConfig.ErrorPrefix = "excs_access";
btreeConfig.Creation = CreatePolicy.IF_NEEDED;
btreeConfig.CacheSize = new CacheInfo(0, 64 * 1024, 1);
btreeConfig.PageSize = 8 * 1024;

try
{
btreeDB = BTreeDatabase.Open(dbFileName, btreeConfig);
}
catch (Exception e)
{
Console.WriteLine("Error opening {0}.", dbFileName);
Console.WriteLine(e.Message);
return;
}

key = new DatabaseEntry();
data = new DatabaseEntry();

while (true)
{
Console.Write("key [blank line to quit] > ");
keyString = Console.ReadLine();
if (keyString == "")
break;

dbtFromString(key, keyString);
dbtFromString(data, reverse(keyString));

try
{
btreeDB.Put(key, data);
}
catch
{
return;
}

}

using (dbc = btreeDB.Cursor())
{

Console.WriteLine("All key : data pairs:");
foreach (KeyValuePair p in dbc)
Console.WriteLine("{0}::{1}",
strFromDBT(p.Key), strFromDBT(p.Value));
}

Console.Write("Press any key to exit >");
Console.ReadKey(true);

btreeDB.Close();
}

And dont forget to add these utility methods that help it make readable.

 public static void usage()
{
Console.WriteLine(
"Usage: excs_access [database]");
}

static void dbtFromString(DatabaseEntry dbt, string s)
{
dbt.Data = System.Text.Encoding.ASCII.GetBytes(s);
}

public static string strFromDBT(DatabaseEntry dbt)
{

System.Text.ASCIIEncoding decode =
new ASCIIEncoding();
return decode.GetString(dbt.Data);
}

public static string reverse(string s)
{
StringBuilder tmp = new StringBuilder(s.Length);
for (int i = s.Length - 1; i >= 0; i--)
tmp.Append(s[i]);
return tmp.ToString();
}

After you run the project it will be creating a "access.db" in Debug folder(same location with your exe file)

Now run the project and see the results.It will wait you finish adding records until you press "Enter" key.

After you click Enter it will list all the datas with its reversed strings as values.

Now press Enter and exit application.Then re-run the application:

You'll be displaying a confirmation message informing you that access.db is about to be deleted.You could write "y" or "n" to confirm.

If you write "n" you can view your previously added datas:

Thats how we did it.

View the original article here

Read more »

Pseudo-column(Dummy) LEVEL in PL/SQL Asp.Net C#

Pseudo-column: Pseudo-columns are not actual columns in a table, it is an Oracle assigned value  used in the same context as an Oracle Database column, but not stored on disk. However, pseudo-columns are treated like columns, so their values must be SELECTED from a table. Sometimes it is convenient to select pseudo-column values from a dummy table, it is not physically existed in the table. If we want to use these column by specifying externally in select statement for projection or selection. but we can't insert, delete and update pseudo columns.

LEVEL: LEVEL is a pseudo-column used with CONNECT BY and denotes the node level of the tree structure, it is a special function in hierarchical queries-it returns the position of any row in the hierarchy. The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Whenever you use the START WITH.......CONNECT BY clauses in a hierarchical query, you can use the pseudo-column LEVEL to return the level number for each row returned by the query.

The hierarchy of pseudo-column Level is shows in figure given below: Suppose four employees working under one supervisor in two groups, So the hierarchy which shows three level are given below:

Example:

SQL> CREATE TABLE magazine (
2    ID      VARCHAR2(10) PRIMARY KEY,
3    parent_ID VARCHAR2(10),
4    series    VARCHAR2(20),
5    category  VARCHAR2(20),
6    title     VARCHAR2(100),
7    num_pages NUMBER,
8    price     NUMBER,
9    Licence_no NUMBER(4));
Table created.
SQL>
SQL> INSERT INTO magazine (ID, parent_ID, series, category, title, num_pages, price, Licence_no)
2    VALUES ('1', '2', 'Database', 'Database Server', 'SQL', 664, 49.99, 2002);
1 row created.
SQL>
SQL> INSERT INTO magazine (ID, parent_ID, series, category, title, num_pages, price, Licence_no)
2    VALUES ('2', null, 'Database', 'Database Server', 'Java', 772, 49.99, 2000);
1 row created.
SQL>
SQL> INSERT INTO magazine (ID, parent_ID, series, category, title, num_pages, price, Licence_no)
2    VALUES ('3', '1', 'Database', 'Database Server', 'XML', 1008, 54.99, 2004);
1 row created.
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2     v_level PLS_INTEGER;
3     v_title magazine.TITLE%TYPE;
4     CURSOR cur_tree IS SELECT ID, title, series FROM magazine;
5  BEGIN
6  FOR l IN cur_tree
7  LOOP
8      SELECT max(LEVEL) INTO v_level FROM magazine
9      START WITH ID = l.ID
10      CONNECT BY PRIOR parent_ID = ID;
11
12      DBMS_OUTPUT.PUT_LINE(l.title||' is magazine '||v_level||' in the '||l.series||' series');
13
14  END LOOP;
15  CLOSE cur_tree;
16  EXCEPTION
17     WHEN OTHERS
18     THEN
19        DBMS_OUTPUT.PUT_LINE(sqlerrm);
20  END;
21  /

SQL is magazine 2 in the Database series
Java is magazine 1 in the Database series
XML is magazine 3 in the Database series
ORA-01001: invalid cursor

PL/SQL procedure successfully completed.

Read more »

Effectively Using/Work with CONNECT BY and START WITH Clauses in PL/SQL Asp.Net C#

The start with .. connect by clause can be used to select data that has a hierarchical relationship, after implementing the clause START WITH and CONNECT BY. You can use the reserved word level in the select clause. Witch for those records where we are talking about the root row it will show 1. And for the rest of records it will show 2,3,etc.

Example:

SQL> -- create demo table
SQL> create table         Employee(
2    Worker_id            NUMBER(3),
3    Worker_name       VARCHAR2(15 BYTE),
4    Sal_pack             NUMBER(6),
5    REGION               VARCHAR2(1 BYTE),
6    Supervisor_id        NUMBER(3)
7  )
8  /
Table created.
SQL>
SQL> create table Profile (
2    Worker_id         NUMBER(3),
3    Profiletitle         VARCHAR2(20 BYTE)
4  )
5  /
Table created.
SQL>
SQL> insert into Profile (Worker_id, Profiletitle) values (1,'Painter');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (2,'Cashier');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (3,'Counsler');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (4,'Co-ordinator');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (5,'Human Resorce');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (6,'Advisor');
1 row created.
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (1,      'Petter',     8000,         'E',    2)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (2,      'Fara',      3350,         'W',    3)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (3,      'Lui',       5000,         'E',    3)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (4,      'Dik',       6000,         'W',    4)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (5,      'Jame',      4000,         'E',    4)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (6,      'Kalis',     7000,         'W',    5)
3  / 
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2  /
Worker_id   Worker_name     Sal_pack    R  Supervisor_id
---------- ---------------  ----------- -  -------------
1            Petter                8000     E        2
2            Fara                  3350     W       3
3            Lui                    5000     E        3
4            Dik                    6000     W       4
5            Jame                 4000     E        4
6            Kalis                  7000     W       5
6 rows selected.
SQL> select * from Profile
2  /
Worker_id  ProfileTITLE
---------- -------------
1        Painter
2        Cashier
3        Counsler
4        Co-ordinator
5        Human Resorce
6        Advisor
6 rows selected.
SQL>
SQL> SELECT Worker_id, Supervisor_id, Worker_name
2  FROM Employee
3  START WITH Worker_id = 1
4  CONNECT BY PRIOR Worker_id = Supervisor_id;
Worker_id  Supervisor_id  Worker_name
---------- -------------  ------------
1              2               Petter

Read more »

What is Berkeley?

What is Berkeley?
By  Ibrahim Ersoy March 01, 2011 This article will cover some points and show you how to install it.

Author Rank: Similar ArticlesMost ReadTop RatedLatest



Berkeley DB enables the development of custom data management solutions, without the overhead traditionally associated with such custom projects. Berkeley DB provides a collection of well-proven building-block technologies that can be configured to address any application need from the hand-held device to the datacenter, from a local storage solution to a world-wide distributed one, from kilobytes to petabytes.

Download Berkeley DB and review the source code, choose your build options and then compile the library in the configuration most suitable for your needs. The Berkeley DB library is a building block that provides the complex data management features found in enterprise class databases. These facilities include high throughput, low-latency reads, non-blocking writes, high concurrency, data scalability, in-memory caching, ACID transactions, automatic and catastrophic recovery when the application, system or harware fails, high availability and replication in an application configurable package. Simply configure the library and use the particular features available to satisfy your particular application needs. Berkeley DB is a reliable solution with over 15 years of production use in products ranging from cell phones to e-commerce. The primary goal of Berkeley DB is to deliver fast, scalable and flexible data management services to your application while remaining transparent to your end-user.

Oracle Berkeley DB fits where you need it regardless of programming language, hardware platform, or storage media. Berkeley DB APIs are available in almost all programming languages including ANSI-C, C++, Java, C#, Perl, Python, Ruby and Erlang to name a few. There is a pure-Java version of the Berkeley DB library designed for products that must run entirely within a Java Virtual Machine (JVM). Berkeley supports the Microsoft .NET environment and the Common Language Runtime (CLR) with a C# API. Oracle Berkeley DB is tested and certified to compile and run on all modern operating systems including Solaris, Windows, Linux, Android, Mac OS/X, BSD, iPhone OS, VxWorks, and QNX to name a few.
Oracle Berkeley DB 11g provides the foundational storage services for your application, no matter how demanding and unique your requirements may seem to be. Using Berkeley DB you can: Reduce time to market Reduce development costs Simplify data storage on mobile devices Lower the cost of deployment Eliminate costly re-writes as your system grows Eliminate costly administrative overhead Eliminate data loss and corruption Provide internet-scale, highly-available services

Download it from: http://www.oracle.com/technetwork/database/berkeleydb/downloads/index.html

And try it!

Login to add your contents and source code to this article Ibrahim Ersoy
Ibrahim Ersoy lives in Turkey,Istanbul.He helps running and managing C# Corner.He is a Software Consultant(Sharepoint),MindCracker MVP.He is writing about Microsoft Technologies such as XNA,Sharepoint,LINQ,SQL Server and Windows Phone 7 Development. C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional consulting company, our consultants are well-known experts in .NET and many of them are MVPs, authors, and trainers. We specialize in Microsoft .NET development and utilize Agile Development and Extreme Programming practices to provide fast pace quick turnaround results. Our software development model is a mix of Agile Development, traditional SDLC, and Waterfall models.

View the original article here

Read more »

Using Developing where clause with Hierarchical Query in PL/SQL Asp.Net C#

Hierarchical query: you may wish to traverse the tree either upwards or downwards, the hierarchical query clause is used to select the rows in a hierarchical order, If a table contains hierarchical data. Data can easily be made to fit into a relational table by using a self-referential relationship making use of hierarchical queries. With the hierarchical queries support in Enterprise DB complex hierarchical operations can be constructed on tree-structured data.

WHERE clause: whether you are querying, modifying, or deleting data, the WHERE clause is the mechanism for identifying what data you want to work with it is used to extract only those records that fulfill a specified criterion. The WHERE clause is an optional part of the SELECT statement. However, if the WHERE clause is absent, the resulting rowset can be extremely large because all rows of the virtual table defined by the FROM clause are returned in the resulting rowset. It is difficult to explain the basic syntax for the WHERE clause, so instead, we'll take a look at some examples.

When the user wants to retrieve tuples based on certain predicates then the user has to use the WHERE clause. Consider the query Find all the loan numbers for the loan made at the SARASWATIH PURAM branch and loan amount > = Rs 5000 .

This query can be written in SQL as

select loan-no from loan
where br_name = SARASWATHI PURAM and amount > 5000

The question raise in you mind that does we use this both statement together at a same time. So, the answer is yes you can use this two statement of PL/SQL at a same time in a single query to fetch out the specific output from the database.

Example:

SQL> -- create demo table
SQL> create table         Employee(
2    Worker_id            NUMBER(3),
3    Worker_name       VARCHAR2(15 BYTE),
4    Sal_pack             NUMBER(6),
5    REGION               VARCHAR2(1 BYTE),
6    Supervisor_id        NUMBER(3)
7  )
8  /
Table created.
SQL>
SQL> create table Profile (
2    Worker_id         NUMBER(3),
3    Profiletitle         VARCHAR2(20 BYTE)
4  )
5  /
Table created.
SQL>
SQL> insert into Profile (Worker_id, Profiletitle) values (1,'Painter');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (2,'Cashier');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (3,'Counsler');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (4,'Co-ordinator');
1 row created.

SQL> insert into Profile (Worker_id, Profiletitle) values (5,'Human Resorce');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (6,'Advisor');
1 row created. 
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (1,      'Petter',     8000,         'E',    2)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (2,      'Fara',      3350,         'W',    3)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (3,      'Lui',       5000,         'E',    3)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (4,      'Dik',       6000,         'W',    4)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (5,      'Jame',      4000,         'E',    4)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (6,      'Kalis',     7000,         'W',    5)
3  /
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2  /
Worker_id   Worker_name     Sal_pack    R  Supervisor_id
---------- ---------------  ----------- -  -------------
1            Petter                8000     E        2
2            Fara                  3350     W       3
3            Lui                    5000     E        3
4            Dik                    6000     W       4
5            Jame                 4000     E        4
6            Kalis                  7000     W       5 
6 rows selected.

SQL> select * from Profile

  2  /
Worker_id  ProfileTITLE
---------- -------------
1        Painter
2        Cashier
3        Counsler
4        Co-ordinator
5        Human Resorce
6        Advisor

6 rows selected.

SQL>
SQL> SELECT LEVEL,
2   LPAD(' ', 3 * LEVEL - 1) || Worker_name
3  FROM employee
4  WHERE salary <= 6000
5  START WITH Worker_id = 1
6  CONNECT BY PRIOR Worker_id = Supervisor_id;
WHERE salary <= 6000
*
ERROR at line 4:
ORA-00904: "SALARY": invalid identifier

Hope this helps,

Read more »

Deleting a Node from a Hierarchical Query in PL/SQL Asp.Net C#

"Working with Hierarchical Query's in PL/SQL".The following list defines terms that we'll use often when working with hierarchical data: Node: A row in a table that represents a specific entry in a hierarchical tree structure.Parent: A node that is one level up in a tree.Child: A node that is one level down in a tree.Root: The uppermost node in a hierarchical structure.Leaf: A node with no children, and sometimes called a leaf node.Level: A layer of nodes.

Now let suppose you want to delete a node from the middle, how..? Not to worry this article gives you the solution here I am taking a example of Employee table in which you see how to Eliminate Nodes from a Hierarchical Query.

Example:

SQL> -- create demo table
SQL> create table        Employee(
2    Worker_id            NUMBER(3),
3    Worker_name       VARCHAR2(15 BYTE),
4    Sal_pack             NUMBER(6),
5    REGION               VARCHAR2(1 BYTE),
6    Supervisor_id       NUMBER(3)
7  )
8  /
Table created.
SQL>
SQL> create table Profile (
2    Worker_id         NUMBER(3),
3    Profiletitle         VARCHAR2(20 BYTE)
4  )
5  /
Table created.
SQL>
SQL> insert into Profile (Worker_id, Profiletitle) values (1,'Painter');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (2,'Cashier');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (3,'Counsler');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (4,'Co-ordinator');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (5,'Human Resorce');
1 row created.
SQL> insert into Profile (Worker_id, Profiletitle) values (6,'Advisor');

1 row created.
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (1,      'Petter',     8000,         'E',    2)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (2,      'Fara',      3350,         'W',    3)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (3,      'Lui',       5000,         'E',    3)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (4,      'Dik',       6000,         'W',    4)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (5,      'Jame',      4000,         'E',    4)
3  /
1 row created.
SQL> insert into Employee(Worker_id,  Worker_name,      Sal_pack,  REGION, Supervisor_id)
2               values (6,      'Kalis',     7000,         'W',    5)
3  /  SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2  /
Worker_id   Worker_name     Sal_pack   R Supervisor_id
---------- ---------------  ----------- - -------------
1             Petter              8000      E        2
2             Fara                3350      W       3
3             Lui                  5000      E        3
4             Dik                  6000      W       4
5             Jame               4000      E        4
6             Kalis                7000      W       5 
6 rows selected.
SQL> select * from Profile
2  /
Worker_id  ProfileTITLE
---------- ---------------
1        Painter
2        Cashier
3        Counsler
4        Co-ordinator
5        Human Resorce
6        Advisor 
6 rows selected.
SQL>
SQL> SELECT LEVEL,
2   LPAD(' ', 2 * LEVEL - 1) || ename
3  FROM Employee
4  WHERE ename != 'Kalis'
5  START WITH Worker_id = 1
6  CONNECT BY PRIOR Worker_id = Supervisor_id;
LEVEL          LPAD('',2*LEVEL-1)||Worker_name
1          Petter

Hope this helps,

Read more »

Oracle Database on Windows and .NET FAQs

Q: How does the Oracle database on Windows work?  How do I tune the Oracle database on Windows?

A: The Oracle RDBMS on Windows web site describes how the Oracle database operates on Windows, as well as provides tips on performance tuning.

Q: I use C#, Visual Basic, Active Server Pages, Visual C++, or another Windows-specific programming language. How does Oracle provide data access?

A: Oracle offers a variety of data access methods for COM-based, .NET-based, and Win32/64-based programming languages. Oracle developers can use any one of the following data access drivers based on the Windows programming language employed:

These data access drivers have been engineered to provide superior performance with Oracle and expose the database's advanced features which may not be available in third-party drivers.

Q: I use C#, Visual Basic .NET, or another .NET language. How does Oracle support .NET? What about web services?

A: The Oracle database provides optimum .NET data access support through the Oracle Data Provider for .NET, allowing .NET applications to access advanced Oracle features.

The Oracle Developer Tools for Visual Studio .NET help streamline the development of Oracle database applications right from within Visual Studio. It enables developers to browse and modify their Oracle schemas, leverage powerful wizards and designers, automatically generate code, and view and edit Oracle data. These and other features, including stored procedure editing with Intellisense, syntax coloring, and an integrated help system, are designed to speed development and make it easy to learn how to develop Oracle applications for Windows.

Oracle Application Server allows programmers to easily and quickly build web services via SOAP, WSDL, UDDI, and XML. SOAP and XML support has been available since Oracle9iAS. By their very definition, web services are intended to be cross-platform. As such, Oracle web services support .NET web services.

Q: What is the maximum amount/number of memory, processors, etc. that Oracle on Windows can support?

A: Oracle supports the maximum amount of memory and maximum number of processors allowable under Windows 2000, XP, and Server 2003.  Oracle supports fail over using Microsoft Cluster Server with Oracle Fail Safe for up to eight nodes on Windows 2003 Datacenter.  Oracle Real Application Clusters on Windows is supported beyond four nodes.  Visit the High Availability home page for more information.

Q: Why does Oracle integrate with Windows?

A: Oracle is committed to provide open solutions for our customers. This allows customers to choose the software technologies that best fit their needs. Oracle is integrated closely with popular Windows-based languages and servers to make development and deployment of Oracle products on Windows simple and seamless.

Q: What is the target market for Oracle's integration with Windows?

A: Oracle's integration with Windows benefits all areas of IT infrastructure: client, middle-tier, and data tier. At each of these levels, Oracle allows developers, software architects, and database administrators to take advantage of native Windows servers and technologies. This integration ensures Oracle is scalable, fault-tolerant, high-performing, and easy to develop and deploy on Windows. Whether your applications using the native Windows services, .NET, COM, or Java, Oracle offers strong support for Windows technologies.

Q: What Windows integration features are available in the Oracle database?

Oracle is committed to providing the best database on Windows. Below is a brief description of the integration features:

Oracle Data Provider for .NET (ODP.NET)
The Oracle Data Provider for .NET (ODP.NET) features optimized data access to the Oracle database from a .NET environment. Unlike other .NET providers, ODP.NET is a native driver and does not use a data access bridge, which can hurt performance. ODP.NET allows developers to take advantage of advanced Oracle database functionality. The data provider can be used from any .NET language, including C# and Visual Basic .NET. The Oracle Developer Tools for Visual Studio .NET streamlines database development as an add-in for the development environment.

Oracle Services for Microsoft Transaction Server (MTS)
The Oracle Services for MTS leverage Oracle databases as resource managers for distributed transactions through the Microsoft Distributed Transaction Coordinator (DTC). These services provide client-side connection pooling to MTS and allow transaction coordination of COM and .NET components involving Oracle databases. MTS, which is also known as COM+ and .NET Enterprise Services, is a superset of the DTC.

Oracle Objects for OLE (OO4O)
OO4O is a COM-based API that provides high performance data access to Oracle databases and exposes Oracle features, such as objects and advanced queueing, not available through ADO. OO4O can be used in any COM development environment, such as Visual Basic, PowerBuilder, or Delphi. OO4O can interact with MTS and IIS.

OLE DB
The Oracle Provider for OLE DB offers high performance and efficient access to Oracle data for OLE DB and ADO developers. Developers programming with Visual Basic, C++, or any COM client can utilize the provider to access Oracle databases.

Oracle Open Database Connectvity (ODBC)
The Oracle ODBC driver provides optimized ODBC access to the Oracle database from Windows clients.

COM Automation Feature
The COM Automation Feature allows Oracle databases to communicate with COM Automation servers, such as Microsoft Office, using SQL, PL/SQL, and Java stored procedures. It is primarily used for server-initiated data access to a client COM Automation Server. The COM Automation Feature exposes APIs to instantiate COM objects, get and set their properties, and invoke their methods.

Very Large Memroy (VLM)
On Windows 2000 and higher, Oracle interfaces with the Microsoft's Address Windowing Extensions (AWE), which allows the Oracle database to access to up to 64 GB of memory. On Windows NT Server, Oracle interfaces with the Intel PSE36 device driver which makes up to 16 GB of memory available to Oracle. This support enables very large amounts of database buffers which will help performance for transaction-intensive applications by reducing disk I/O and keeping more data cached. This is one of the many integration features of the Oracle RDBMS on Windows.

Directory & Security
Oracle provides integration with Oracle Internet Directory, Microsoft Active Directory, and Windows native authentication in order to simplify and centralize administration on Windows. The integration permits single sign-on and flexible security management.

Oracle Fail Safe
Oracle Fail Safe provides easy-to-deploy high availability (through fast failover) on commodity Windows clusters for both data tier and application tier components for nearly all Oracle products shipping on Windows.

Q: Where can I find more information about the Oracle database on or integration with Windows?

A: Visit the Windows Technology Center on OTN for more information.

Q: Where can I find more information about Oracle integration with .NET?

A: Visit the .NET Developer Center on OTN for more information.

Q: Which versions of the Oracle database are supported on which versions of Windows?

A:

Supported Windows Operating Systems Windows 2000/XP/Server 2003/Server 2003 R2 Windows 2000/XP/Server 2003/Server 2003 R2 Windows 2000/XP/Server 2003/Server 2003 R2 Windows 2000/XP/Server 2003/Server 2003 R2

Supported Windows Operating Systems Windows 2000/XP/Server 2003/Server 2003 R2 Windows 2000/XP/Server 2003/Server 2003 R2 Windows 2000/XP/Server 2003/Server 2003 R2 Windows 2000/XP/Server 2003/Server 2003 R2 Supported Windows Operating Systems Windows 2000/XP/Server 2003/Server 2003 R2 Windows 2000/XP/Server 2003/Server 2003 R2 Windows 2000/XP/Server 2003/Server 2003 R2 Windows 2000/XP/Server 2003/Server 2003 R2

Supported Windows Operating Systems

Supported Windows Operating Systems

Note:
For AMD64/EM64T platform information, refer to this certification matrix.
Windows 2000 support indicates support for Professional, Server, Advanced Server, Datacenter editions.
Windows XP support indicates support for Professional Edition (32-bit) and XP 2003 (64-bit Itanium). Windows XP 2003 is supported for Oracle 9.2 and 10.1 only. Microsoft is discontinuing support for Windows XP for Itanium.
Windows Server 2003 support indicates support for Standard, Enterprise, Datacenter, Web editions (32-bit) and Enterprise and Datacenter editions (64-bit Itanium).
Windows NT and Windows 98 have been desupported by Oracle.
Oracle Standard Edition support includes support for Standard Edition One.
Consult Oracle Metalink for updated operating support information.

Q: Which Windows operating systems has Oracle desupported or plans to desupport?

A: In general, Oracle will end support of its products on a Windows operating system at the same time the operating system enters Microsoft's Non-Supported Phase or equivalent thereof. Currently, Oracle and Microsoft have desupported Windows NT and Windows 98. Consult Oracle Metalink for more details.

Q: Can I use one version of the Oracle Database (e.g. Oracle10g) with a different version of the Oracle client (e.g. Oracle9i)?

A: Yes, Oracle supports using different versions of the Oracle client with the Oracle Database. In general, a database server version supports the two major database client releases prior to its release and the two major releases after its release. For example, Oracle8i server supports the major client releases prior to it (Oracle7 and Oracle8) and the major releases after it (Oracle9i and Oracle10g). A database client version also supports two major server releases prior to its release and two major release after. Consult Oracle Metalink for more details on specific releases.

Q: Is Windows Terminal Server/Services and Remote Desktop Connection supported?

A: Terminal Server/Services and Remote Desktop Connection are supported. Some Oracle components will be exceptions to this support. Please check the Oracle Database release notes for your specific version for more details.

Q: Does Oracle certify against the latest Windows service packs?

A: Oracle certifies against the specific Microsoft operating system and, if applicable, service packs (SPs) stated in the Oracle product documentation. Oracle will support the use of our products on any later SP as soon as that SP becomes generally available. Depending upon the severity, quantity and impact of the SP-related issues found, Oracle may recommend that customers wait until relevant Oracle patches have been released before upgrading to a particular SP. Oracle may recommend or discourage the installation of specific SPs if the SPs will significantly affect the operation of Oracle software, either positively or negatively. If such a statement is deemed necessary, then Oracle Development will disseminate this statement in as timely a fashion as possible after the release of an SP.

Q: Does Oracle support 64-bit Windows on Itanium and on multi-core "Montecito" Itanium?

A: Yes, the Oracle database supports 64-bit Windows Server 2003 and XP 2003 and can be downloaded from OTN. Oracle was the first vendor to make database software publicly available for the 64-bit Windows Itanium platform in December, 2000. Oracle9i Database Release 2 was the first production database supported on 64-bit Windows.

For multi-core "Montecito" Itanium processors with Windows Server 2003 Service Pack 1 or higher, Oracle Database 10g Release 2 (10.2.0.2 or higher), Oracle Database 10g Release 1 (10.1.0.5 or higher), and Oracle9i Release 2 (9.2.0.8 or higher) are supported. Consult Metalink note: 400227.1 for more information on installation.

Does Oracle support Windows x64 (x86-64) for AMD64 and Intel EM64T?

A: Consult the following matrix for certification information.

Oracle Database 10g (10.2) Release 2 Supported Windows Operating Systems 32-bit Database Server and Client 64-bit Database Server and Client Supported Windows Operating Systems 32-bit Database Server and Client Supported Windows Operating Systems 32-bit Database Server and Client

Note:
32-bit Windows consists of the family of 32-bit Windows operating systems using AMD64 and EM64T.
Windows x64 consists of the following operating systems: Windows Server 2003 x64 Edition and Windows Server 2003 R2 x64 Edition (Standard, Enterprise, Datacenter) and Windows XP Professional x64 Edition.

Consult Oracle Metalink for updated operating support information. Oracle was the first to make a 64-bit database developer release publicly available for AMD64 and Intel EM64T chipsets.

Does Oracle support Windows Encrypting File System (EFS)?

A: Yes, EFS is supported and is transparent to Oracle.  However, there is a tradeoff in using it. EFS will slow database peformance as it constantly encrypts and decrypts data, so it's not encouraged for high performance applications.

Q: How do I migrate from SQL Server (or another database) to Oracle?

A: The Oracle Migration technology web site provides tools and documentation on moving your database to Oracle.

Q: How can Oracle interoperate with a non-Oracle database?

A: Oracle provides two connectivity solutions to address the needs of disparate data access: Oracle Transparent Gateways and Oracle Generic Connectivity. Oracle Transparent Gateways provides access to many non-Oracle data stores, including Microsoft SQL Server, Informix, Ingres, and Sybase from an Oracle environment. Oracle Generic Connectivity provides access to many data stores for which Oracle does not have a gateway solution. This feature enables transparent connectivity using ODBC and OLE DB.

Reference: http://www.oracle.com/technetwork/database/windows/faq-100614.html

View the original article here

Read more »

Creating Developing Working with Hierarchical Query's in PL/SQL Asp.Net C#

In a hierarchical query you may wish to traverse the tree either upwards or downwards, the hierarchical query clause is used to select the rows in a hierarchical order, If a table contains hierarchical data. Data can easily be made to fit into a relational table by using a self-referential relationship making use of hierarchical queries. With the hierarchical queries support in Enterprise DB complex hierarchical operations can be constructed on tree-structured data.

The following list defines terms that we'll use often when working with hierarchical data:

Node: A row in a table that represents a specific entry in a hierarchical tree structure. Parent: A node that is one level up in a tree. Child: A node that is one level down in a tree.Root: The uppermost node in a hierarchical structure. Leaf: A node with no children, and sometimes called a leaf node.Level: A layer of nodes.

Oracle evaluates the hierarchical query in the following way:

The result rowset is initialized with the rows determined by the START WITH clauseAll rows for which the CONNECT_BY condition is true are added to the result rowset.Step 2 is repeated until no further rows match.

The following are the Clauses which is used only in hierarchical queries:

CONNECT BYPRIOR START WITHSYS_CONNECT_BY_PATHCONNECT_BY_ROOTCONNECT_BY_ISLEAF

Example of an Hierarchical Query:

SQL> create table Worker
2  ( Workerno            NUMBER(4)    constraint E_PK priCutlar key
3  , Worker_name      VARCHAR2(8)
4  , init                    VARCHAR2(5)
5  , job                    VARCHAR2(8)
6  , mgr                   NUMBER(4)
7  , bdate                DATE
8  , sal                    NUMBER(6,2)
9  , comm                NUMBER(6,2)
10  , deptno              NUMBER(2)    default 10
11  ) ;

Table created.

SQL> insert into Worker values(1,'Joergiya','N',   'TRAINER', 13,date '1965-12-17',  800 , NULL,  20);
1 row created.
SQL> insert into Worker values(2,'Yalric','JAM', 'Tester',6,date '1961-02-20',  1600, 300,   30);
1 row created.
SQL> insert into Worker values(3,'Joy','TF' ,  'Tester',6,date '1962-02-22',  1250, 500,   30);
1 row created.
SQL> insert into Worker values(4,'Thomas','JM',  'Designer', 9,date '1967-04-02',  2975, NULL,  20);
1 row created.
SQL> insert into Worker values(5,'Cutlar','P',  'Tester',6,date '1956-09-28',  1250, 1400,  30);
1 row created.
SQL> insert into Worker values(6,'Nikab','R',   'Designer', 9,date '1963-11-01',  2850, NULL,  30);
1 row created.
SQL> insert into Worker values(7,'Branny','AB',  'Designer', 9,date '1965-06-09',  2450, NULL,  10);
1 row created.
SQL> insert into Worker values(8,'Janny','SCJ', 'TRAINER', 4,date '1959-11-26',  3000, NULL,  20);
1 row created.
SQL> insert into Worker values(9,'Joyson','CC',   'Designer',NULL,date '1952-11-17',  5000, NULL,  10);
1 row created.
SQL> insert into Worker values(10,'Take','JJ', 'Tester',6,date '1968-09-28',  1500, 0,     30);
1 row created.
SQL> insert into Worker values(11,'Aidam','AA',  'TRAINER', 8,date '1966-12-30',  1100, NULL,  20);
1 row created.
SQL> insert into Worker values(12,'Thomas','R',   'MAidamger',   6,date '1969-12-03',  800 , NULL,  30);
1 row created.
SQL> insert into Worker values(13,'Robin','MG',   'TRAINER', 4,date '1959-02-13',  3000, NULL,  20);
1 row created.
SQL> insert into Worker values(14,'Mike','TJA','MAidamger',   7,date '1962-01-23',  1300, NULL,  10);
1 row created.
SQL>
SQL> select  lpad(' ',2*level-1)||Worker_name as Worker_name
2  from    Worker
3  start   with mgr is null
4  connect by nocycle prior Workerno = mgr;

Worker_name
--------------------
Joyson
Thomas
Janny
Aidam
Robin
Joergiya
Nikab
Yalric
Joy
Cutlar
Take
Thomas
Branny
Mike

14 rows selected.

Hope this helps,happy coding.

Read more »

Update Statement in PL/SQL

Update Statement in PL/SQL
By  Thomas Mapother February 24, 2011 UPDATE statement is used to change existing values in a table or in the base table of a view or the master table

Similar ArticlesMost ReadTop RatedLatest

Use the UPDATE statement to change existing values in a table or in the base table of a view or the master table of a materialized view. It is used to manipulation and it modifies the rows, the WHERE clause identifies the rows that get affected. If you do not include the WHERE clause, column values for all the rows get affected. Before executing an UPDATE statement, Oracle marks an implicit savepoint. If the statement fails, Oracle rolls back to the savepoint.

Syntax for the UPDATE Command:

UPDATE table_name
set (column_name = value)
WHERE statement

Example:

First Create two Tables, Named Wholesaler and Consumer:

SQL> CREATE TABLE Wholesaler
2  ( Wholesaler_id number(10) not null,
3    Wholesaler_name varchar2(50) not null,
4    city varchar2(50),
5    CONSTRAINT Wholesaler_pk PRIMARY KEY (Wholesaler_id)
6  );

Table created.

SQL> CREATE TABLE Consumer
2  ( Consumer_id number(10) not null,
3    Consumer_name varchar2(50) not null,
4    city varchar2(50),
5    CONSTRAINT Consumer_pk PRIMARY KEY (Consumer_id)
6  );
Table created.

Insert Values into the Tables:

SQL> INSERT INTO Wholesaler (Wholesaler_id, Wholesaler_name, city)
2  VALUES (5001, 'Microsoft', 'New York');
1 row created.
SQL>
SQL> INSERT INTO Wholesaler (Wholesaler_id, Wholesaler_name, city)
2  VALUES (5002, 'IBM', 'Chicago');
1 row created.
SQL>
SQL> INSERT INTO Wholesaler (Wholesaler_id, Wholesaler_name, city)
2  VALUES (5003, 'Red Hat', 'Detroit');
1 row created.
SQL>
SQL> INSERT INTO Wholesaler (Wholesaler_id, Wholesaler_name, city)
2  VALUES (5005, 'NVIDIA', 'LA');
1 row created.
---------------------------------------------------------------------------------------
SQL> INSERT INTO Consumer (Consumer_id, Consumer_name, city)
2  VALUES (7001, 'Microsoft', 'San Francisco');
1 row created.
SQL>
SQL> INSERT INTO Consumer (Consumer_id, Consumer_name, city)
2  VALUES (7002, 'IBM', 'Toronto');
1 row created.
SQL>
SQL> INSERT INTO Consumer (Consumer_id, Consumer_name, city)
2  VALUES (7003, 'Red Hat', 'Newark');

1 row created.

Update the Values:

SQL> UPDATE Wholesaler
2  SET city = ( SELECT customers.city
3  FROM customers
4  WHERE customers.customer_name = Wholesaler.Wholesaler_name)
5  WHERE EXISTS
6    ( SELECT customers.city
7      FROM customers
8      WHERE customers.customer_name = Wholesaler.Wholesaler_name);  3 rows updated.

Final Output after updating the values:
Wholesaler_IDWholesaler_NAMECITY

Happy Learning

Login to add your contents and source code to this article C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional consulting company, our consultants are well-known experts in .NET and many of them are MVPs, authors, and trainers. We specialize in Microsoft .NET development and utilize Agile Development and Extreme Programming practices to provide fast pace quick turnaround results. Our software development model is a mix of Agile Development, traditional SDLC, and Waterfall models.

View the original article here

Read more »

INSERT INTO Statement in Oracle SQL Plus

The INSERT INTO Statement is used to insert new records in a table. A brief demonstration of INSERT INTO statement is provided in this article.

 In this syntax column names are specified and we can leave a cell as NULL i.e. we can fill the data in desired columns or fields.

INSERT INTO table_name (column1, column2, column3, .........)
VALUES (value1, value2, value3, .....................);

Example :
1.

INSERT INTO EmpInfo VALUES(5, 'BA0005', 'JOHN', 'SMITH', 'AUSTIN');

2.

INSERT INTO EmpInfo(RigNo, FirstName, LastName) VALUES(6, 'GARRY', 'BOND');

In above image we can see that EmpId & City Fields are remained NULL and data is filled in RigNo, FirstName & LastName.

Note - Field's Name (Column Name) is not case sensitive; if and only if not written in double quotes ("..."). For example please consider the first line in images given above...

Login to add your contents and source code to this article C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional consulting company, our consultants are well-known experts in .NET and many of them are MVPs, authors, and trainers. We specialize in Microsoft .NET development and utilize Agile Development and Extreme Programming practices to provide fast pace quick turnaround results. Our software development model is a mix of Agile Development, traditional SDLC, and Waterfall models.

Hope this helps,

Read more »

Effectively Using Exists operator with correlated subquery in PL/SQL Asp.Net C#

As you learned previously in my article "Using correlated subquery in PL/SQL". Correlated subquery: When you reference a column from the table in the parent query in the subquery, it is known as a correlated subquery, they are used for row-by-row processing. Each subquery is executed once for every row of the outer query, the inner query uses information from the outer query and executes once for every row in the outer query.

Usage of correlated subquery

The use of a correlated subquery is not very efficient. Using joins rather than a correlated subquery enables the optimizer to determine how to correlate the data in the most efficient way. A practical use of a correlated subquery is to transfer data from one table to another. However, be misled into thinking that the database optimizer actually executes it in that fashion, because this will lead you to believe that it's not very efficient, when in fact most optimizers will process a correlated subquery very efficiently, as a join.

Syntex:

SELECT id, Worker_name, salary FROM Worker outer WHERE salary > (SELECT AVG(salary)

FROM Worker inner WHERE inner.id = outer. id);

Exists Keyword: The PL/SQ EXISTS keyword is an operator which is used to correlate records from two participating queries it returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. Mainly, you use EXISTS with DELETE to maintain sparse nested tables. One of the participating query is an outer SELECT statement, with the other one being an inner sub query it is also use EXISTS to avoid raising an exception when you reference a nonexistent element, It returns a BOOLEAN output which specifies the result of logical comparison of two records matching on defined set of values.

Syntax:

SELECT  FROM TABLENAME

WHERE EXISTS (sub query)

Example of correlated subquery with Exists operator:

SQL> CREATE TABLE Worker(
2      WorkerNO NUMBER(4) NOT NULL,
3      Name VARCHAR2(10),
4      Profile VARCHAR2(9),
5      Batch_No NUMBER(4),
6      Joining_date DATE,
7      Salary NUMBER(7, 2),
8      Extra_bonus NUMBER(7, 2),
9      Dept_id NUMBER(2)
10  );
Table created. 
SQL> INSERT INTO Worker VALUES(2, 'mariya', 'Tester', 6,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
1 row created.
SQL> INSERT INTO Worker VALUES(3, 'solana', 'Tester', 6,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
1 row created.
SQL> INSERT INTO Worker VALUES(4, 'nailson', 'Designer', 9,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO Worker VALUES(5, 'cristna', 'Tester', 6,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO Worker VALUES(6, 'rosi', 'Designer', 9,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO Worker VALUES(7, 'gaeri', 'Designer', 9,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO Worker VALUES(8, 'brad', 'Helper', 4,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO Worker VALUES(9, 'pollson', 'Manager', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO Worker VALUES(10, 'tailor', 'Tester', 6,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
1 row created. 
SQL> INSERT INTO Worker VALUES(13, 'raison', 'Helper', 4,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created. 
SQL>
SQL> CREATE TABLE Department(
2      Dept_id NUMBER(2),
3      Dept_name VARCHAR2(14),
4      State VARCHAR2(13)
5  );
Table created.
SQL>
SQL> INSERT INTO Department VALUES (10, 'ACCOUNTING', 'NEW YORK');
1 row created.
SQL> INSERT INTO Department VALUES (20, 'PURCHASING', 'DALLAS');
1 row created.
SQL> INSERT INTO Department VALUES (30, 'SalaryES', 'CHICAGO');
1 row created.
SQL> INSERT INTO Department VALUES (40, 'OPERATIONS', 'BOSTON');
1 row created.
SQL>
SQL>
SQL> SELECT e1.Name, d.Dept_name
2      FROM   Worker e1, Department d
3      WHERE  e1.Dept_id = d.Dept_id
4      AND EXISTS
5      (SELECT 'x' FROM Worker e2
6      WHERE  e2.Name = 'brad' AND    e2.Dept_id = e1.Dept_id)
7  /
Name          Dept_name
--------    --------------
nailson       PURCHASING
brad          PURCHASING
raison        PURCHASING

Hope this helps,happy coding.

Read more »

Creating Using Querying correlated subquery in PL/SQL Asp.Net C#

Introduction

The first thing which you may know, that is what is correlated subquery, how to use that and why we use this queries in PL/SQL. So, When you reference a column from the table in the parent query in the subquery, it is known as a correlated subquery, they are used for row-by-row processing. Each subquery is executed once for every row of the outer query, the inner query uses information from the outer query and executes once for every row in the outer query. This correlation is accomplished by using a reference to the outside query within the subquery. It is one way of reading every row in a table and comparing the values in each row against the related data.

Usage of correlated subquery

The use of a correlated subquery is not very efficient. Using joins rather than a correlated subquery enables the optimizer to determine how to correlate the data in the most efficient way. A practical use of a correlated subquery is to transfer data from one table to another. However, be misled into thinking that the database optimizer actually executes it in that fashion, because this will lead you to believe that it's not very efficient, when in fact most optimizers will process a correlated subquery very efficiently, as a join.

Difference between simple subquery and correlated subquery

what's the difference between an ordinary subquery and a correlated subquery: The correlated subquery makes an actual reference, using a correlation variable, to the outer query and in the simple sub-query the inner query is executed in entirety before the outer query is executed. And in correlated subquery, for each record fetched in outer query corresponding matching records are fetched in sub-query because of join condition within inner sub-query.

Syntex:

SELECT id, Worker_name, salary FROM Worker outer WHERE salary > (SELECT AVG(salary)

FROM Worker inner WHERE inner.id = outer. id);

Complete Example:

SQL> create table Worker(
2    ID                       VARCHAR2(4 BYTE)         NOT NULL,
3    Worker_Name        VARCHAR2(10 BYTE),
4    S_Date                DATE,
5    L_Date                DATE,
6    Salary                 Number(8,2),
7    City                    VARCHAR2(10 BYTE),
8    Description           VARCHAR2(15 BYTE)
9  )
10  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Worker(ID,  Worker_Name,  S_Date,                     L_Date,                       Salary,  City,       Description)
2               values ('01','manis',    to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
3  /
1 row created.
SQL> insert into Worker(ID,  Worker_Name, S_Date,                     L_Date,                       Salary,  City,       Description)
2                values('02','Rahul',  to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3  /
1 row created.
SQL> insert into Worker(ID,  Worker_Name, S_Date,                     L_Date,                       Salary,  City,       Description)
2                values('03','Ravi',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3  /
1 row created.
SQL> insert into Worker(ID,  Worker_Name, S_Date,                     L_Date,                       Salary,  City,       Description)
2                values('04','pall',   to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3  /
1 row created.
SQL> insert into Worker(ID,  Worker_Name, S_Date,                     L_Date,                       Salary,  City,       Description)
2                values('05','peter',  to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3  /
1 row created.
SQL> insert into Worker(ID,  Worker_Name, S_Date,                     L_Date,                       Salary, City,        Description)
2                values('06','shinda',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Worker
2  /
ID   Worker_NAME   S_DATE    L_DATE     SALARY      CITY     DESCRIPTION
---- ----------   ---------   ---------- ---------- -------   ------------
01   manis         25-JUL-96   25-JUL-06    1234.56   Toronto      Programmer
02   Rahul         21-MAR-76  21-FEB-86    6661.78   Vancouver  Tester
03   Ravi          12-DEC-78   15-MAR-90   6544.78   Vancouver  Tester
04   pall           24-OCT-82   21-APR-99   2344.78   Vancouver  Manager
05   peter        15-JAN-84    08-AUG-98   2334.78   Vancouver  Tester
06   shinda       30-JUL-87    04-JAN-96   4322.78    New York   Tester
6 rows selected.
SQL>
SQL>
SQL> SELECT id, Worker_name, salary
2  FROM Worker outer
3  WHERE salary >
4    (SELECT AVG(salary)
5     FROM Worker inner
6     WHERE inner.id = outer. id);

 no rows selected

Hope this helps,happy coding.

Read more »

How to use NOT EXISTS operator with correlated subquery in PL/SQL

Home » PL/SQL » How to use NOT EXISTS operator with correlated subquery in PL/SQL
How to use NOT EXISTS operator with correlated subquery in PL/SQL
By  Thomas Mapother February 23, 2011 NOT EXISTS is used inside the subquery and returns NULL (no rows selected) the main query will return its rows accordingly, and viceversa.

Similar ArticlesMost ReadTop RatedLatest

As you learned previously in my article "Using Exists operator with correlated subquery in PL/SQL".

The PL/SQ EXISTS keyword is an operator which is used to correlate records from two participating queries it returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. Mainly, you use EXISTS with DELETE to maintain sparse nested tables. One of the participating query is an outer SELECT statement, with the other one being an inner sub query it is also use EXISTS to avoid raising an exception when you reference a nonexistent element, It returns a BOOLEAN output which specifies the result of logical comparison of two records matching on defined set of values.

Syntax:

SELECT  FROM TABLENAME

WHERE NOT EXISTS (sub query)

NOT EXISTS operator: NOT EXISTS works the opposite of EXISTS. The WHERE clause in NOT EXISTS is satisfied if no rows are returned by the subquery, when using NOT EXISTS if the subquery returns NULL (no rows selected) the main query will return its rows accordingly, and viceversa. The subquery is correlated and can be thought of as doing a lookup for each slected item in the selected table, the NOT EXISTS subquery returns at least one row then the main query will return no rows selected.

Syntax:

SELECT  FROM TABLENAME

WHERE NOT EXISTS (sub query)

Example of correlated subquery with NOT EXISTS operator:

SQL> create table Workers(
2    Worker_Id              NUMBER(3),
3    Worker_Name         VARCHAR2(12 BYTE),
4    Joining_Date           DATE,
5    Basic_Pay              NUMBER(6),
6    Inhand                  NUMBER(6),
7    REGION                 VARCHAR2(1 BYTE),
8    Dept_Id                 NUMBER(3)
9  )
10  /
Table created.
SQL>
SQL> create table work (
2    Worker_Id         NUMBER(3),
3    Profile              VARCHAR2(20 BYTE)
4  )
5  /
Table created.
SQL>
SQL> insert into work (Worker_Id, Profile) values (1,'Tester');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (2,'MIS');

1 row created.

SQL> insert into work (Worker_Id, Profile) values (3,'Developer');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (4,'HR_dept');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (5,'Supervisor');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (6,'Mediator');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (7,'Multimedia');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (8,'Programmer');
1 row created.
SQL> insert into work (Worker_Id, Profile) values (9,'Developer');
1 row created.
SQL>
SQL>
SQL> -- prepare data
SQL> insert into Workers(Worker_Id,  Worker_Name,   Joining_Date,                       Basic_Pay,       Inhand,  State, Dept_Id)
2               values (1,      'Marin', to_date('19960725','YYYYMMDD'), 1234,              8767,         'E',    2)
3  / 

1 row created.

SQL> insert into Workers(Worker_Id,  Worker_Name,   Joining_Date,                       Basic_Pay,       Inhand,  State, Dept_Id)
2               values (2,      'Colon',  to_date('19970712','YYYYMMDD'), 2341,              3456,         'W',    3)
3  /
1 row created.
SQL> insert into Workers(Worker_Id,  Worker_Name,   Joining_Date,                       Basic_Pay,       Inhand,  State, Dept_Id)
2               values (3,      'Robort',   to_date('19860125','YYYYMMDD'), 4321,              5654,         'E',    3)
3  /
1 row created.
SQL> insert into Workers(Worker_Id,  Worker_Name,   Joining_Date,                       Basic_Pay,       Inhand,  State, Dept_Id)
2               values (4,      'Dik',   to_date('20060913','YYYYMMDD'), 2413,              6787,         'W',    4)
3  /
1 row created.
SQL> insert into Workers(Worker_Id,  Worker_Name,   Joining_Date,                       Basic_Pay,       Inhand,  State, Dept_Id)
2               values (5,      'Harry',  to_date('20050420','YYYYMMDD'), 7654,              4345,         'E',    4)
3  / 
1 row created.
SQL> insert into Workers(Worker_Id,  Worker_Name,   Joining_Date,                       Basic_Pay,       Inhand,  State, Dept_Id)
2               values (6,      'Nikolas', to_date('20040718','YYYYMMDD'), 5679,              6546,         'W',    5)
3  /
1 row created.
SQL> insert into Workers(Worker_Id,  Worker_Name,   Joining_Date,                       Basic_Pay,       Inhand,  State, Dept_Id)
2               values (7,      'Sparo',  to_date('20030720','YYYYMMDD'), 5438,              7658,         'E',    6)
3  /
1 row created.
SQL> insert into Workers(Worker_Id,  Worker_Name,   Joining_Date,                       Basic_Pay,       Inhand,  State)
2               values (8,      'Roeli',  to_date('20020101','YYYYMMDD'), 8765,              4543,         'W')
3  /
1 row created.
SQL> insert into Workers(Worker_Id,  Worker_Name,   Joining_Date,                       Basic_Pay,       Inhand,  State)
2               values (9,      'Haydan',  to_date('20010829','YYYYMMDD'), 7896,              1232,         'E')
3  /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Workers
2  /
Worker_Id   Worker_Name   Joining_Date  Basic_Pay      Inhand     REGION   Dept_Id
---------- --------------- ------------- ----------- ----------- -------- ----------
1             Marin           25-DEC-96       1234         8767              E          2
2             Colon           12-DEC-97       2341         3456              W         3
3             Robort          25-FEB-86       4321         5654              E          3
4             Dik              13-SEP-06        2413         6787             W          4
5             Harry           20-APR-05        7654        4345               E          4
6             Nikolas         18-DEC-04        5679        6546              W          5
7             Sparo           20-DEC-03       5438        7658               E          6
8             Roeli             01-FEB-02       8765         4543              W
9             Haydan         29-AUG-01       7896        1232               E
9 rows selected.
SQL> select * from work
2  /
Worker_Id     Profile
----------  ----------
1           Tester
2           MIS
3           Developer
4           HR_dept
5           Supervisor
6           Mediator
7           Multimedia
8           Programmer
9           Developer
9 rows selected.
SQL>
SQL> SELECT Worker_Id, Worker_Name
2  FROM Workers outer
3  WHERE NOT EXISTS
4    (SELECT 1
5     FROM Workers inner
6     WHERE inner.Worker_Id = outer.Dept_Id);
Worker_Id   Worker_Name
---------- ---------------
9          Haydan
8          Roeli

Happy Learning.

Login to add your contents and source code to this article C# Consulting is founded in 2002 by the founders of C# Corner. Unlike a traditional consulting company, our consultants are well-known experts in .NET and many of them are MVPs, authors, and trainers. We specialize in Microsoft .NET development and utilize Agile Development and Extreme Programming practices to provide fast pace quick turnaround results. Our software development model is a mix of Agile Development, traditional SDLC, and Waterfall models.

View the original article here

Read more »

Reimplementing LINQ to Objects: Out-of-process queries with IQueryable Asp.Net C#

've been putting off writing about this for a while now, mostly because it's such a huge topic. I'm not going to try to give more than a brief introduction to it here - don't expect to be able to whip up your own LINQ to SQL implementation afterwards - but it's worth at least having an idea of what happens when you use something like LINQ to SQL, NHibernate or the Entity Framework.

Just as LINQ to Objects is primarily interested in IEnumerable and the static Enumerable class, so out-of-process LINQ is primarily interested in IQueryable and the static Queryable class... but before we get to them, we need to talk about expression trees.

To put it in a nutshell, expression trees encapsulate logic in data instead of code. While you can introspect .NET code via MethodBase.GetMethodBody and then MethodBody.GetILAsByteArray, that's not really a practical approach. The types in the System.Linq.Expressions define expressions in an easier-to-process manner. When expression trees were introduced in .NET 3.5, they were strictly for expressions, but the Dynamic Language Runtime uses expression trees to represent operations, and the range of logic represented had to expand accordingly, to include things like blocks.

While you certainly can build expression trees yourself (usually via the factory methods on the nongeneric Expression class), and it's fun to do so at times, the most common way of creating them is to use the C# compiler's support for them via lambda expressions. So far we've always seen a lambda expression being converted to a delegate, but it can also convert lambdas to instances of Expression, where TDelegate is a delegate type which is compatible with the lambda expression. A concrete example will help here. The statement:

Expression> addOne = x => x + 1;

will be compiled into code which is effectively something like this:

var parameter = Expression.Parameter(typeof(int), "x");
var one = Expression.Constant(1, typeof(int));
var addition = Expression.Add(parameter, one);
var addOne = Expression.Lambda>(addition,  new ParameterExpression[] { parameter });

The compiler has some tricks up its sleeves which allow it to refer to methods, events and the like in a simpler way than we can from code, but largely you can regard the transformation as just a way of making life a lot simpler than if you had to build the expression trees yourself every time.

Now that we've got the idea of being able to inspect logic relatively easily at execution time, let's see how it applies to LINQ.

There are three interfaces to introduce, and it's probably easiest to start with how they appear in a class diagram:

Most of the time, queries are represented using the generic IQueryable interface, but this doesn't actually add much over the nongeneric IQueryable interface it extends, other than also extending IEnumerable - so you can iterate over the contents of an IQueryable just as with any other sequence.

IQueryable contains the interesting bits, in the form of three properties: ElementType which indicates the type of the elements within the query (in other words, a dynamic form of the T from IQueryable), Expression returns the expression tree for the query so far, and Provider returns the query provider which is responsible for creating new queries and executing the existing one. We won't need to use the ElementType property ourselves, but we'll need both the Provider and Expression properties.

We're not going to implement any of the interfaces ourselves, but I've got a small sample program to demonstrate how they all work, imagining we were implementing most of Queryable ourselves. This static class contains extension methods for IQueryable just as Enumerable does for IEnumerable. Most of the query operators from LINQ to Objects appear in Queryable as well, but there are a few notable omissions, such as the To{Lookup, Array, List, Dictionary} methods. If you call one of those on an IQueryable, the Enumerable implementations will be used instead. (IQueryable extends IEnumerable, so the extension methods in Enumerable are applicable to IQueryable sequences as well.)

The big difference between the Queryable and Enumerable methods in terms of their declarations is in the parameters:

The "source" parameter in Queryable is always of type IQueryable instead of IEnumerable. (Other sequence parameters such as the sequence to concatenate for Queryable.Concat are expressed as IEnumerable, interestingly enough. This allows you to express a SQL query using "local" data as well; the query methods work out whether the sequence is actually an IQueryable and act accordingly.) Any parameters which were delegates in Enumerable are expression trees in Queryable; so while the selector parameter in Enumerable.Select is of type Func, the equivalent in Queryable.Select is of type Expression>

The big difference between the methods in terms of what they do is that whereas the Enumerable methods actually do the work (eventually - possibly after deferred execution of course), the Queryable methods themselves really don't do any work: they just ask the query provider to build up a query indicating that they've been called.

Let's have a look at Where for example. If we wanted to implement Queryable.Where, we would have to:

Perform argument checking Get the "current" query's Expression Build a new expression representing a call to Queryable.Where using the current expression as the source, and the predicate expression as the predicate Ask the current query's provider to build a new IQueryable based on that call expression, and return it.

It all sounds a bit recursive, I realize - the Where call needs to record that a Where call has happened... but that's all. You may very well wonder where all the work is happening. We'll come to that.

Now building a call expression is slightly tedious because you need to have the right MethodInfo - and as Where is overloaded, that means distinguishing between the two Where methods, which is easier said than done. I've actually used a LINQ query to find the right overload - the one where the predicate parameter Expression> rather than Expression>. In the .NET implementation, methods can use MethodBase.GetCurrentMethod() instead... although equally they could have created a bunch of static variables computed at class initialization time. We can't use GetCurrentMethod() for experimentation purposes, because the query provider is likely to expect the exact correct method from System.Linq.Queryable in the System.Core assembly.

Here's our sample implementation, broken up quite a lot to make it easier to understand:

public static IQueryable Where(
this IQueryable source,
Expression> predicate)
{
if (source == null)
{
throw new ArgumentNullException("source");
}
if (predicate == null)
{
throw new ArgumentNullException("predicate");
}
Expression sourceExpression = source.Expression;
Expression quotedPredicate = Expression.Quote(predicate);
MethodInfo method = typeof(Queryable).GetMethods()
.Where(m => m.Name == "Where")
.Where(m => m.GetParameters()[1]
.ParameterType
.GetGenericArguments()[0]
.GetGenericArguments().Length == 2)
.First();
MethodInfo closedMethod = method.MakeGenericMethod(new Type[] { typeof(TSource) });
Expression methodCall = Expression.Call(closedMethod, sourceExpression, quotedPredicate);
return source.Provider.CreateQuery(methodCall);
}

There's only one part of this code that I don't really understand the need for, and that's the call to Expression.Quote on the predicate expression tree. I'm sure there's a good reason for it, but this particular example would work without it, as far as I can see. The real implementation uses it though, so dare say it's required in some way.

EDIT: Daniel's comment has made this somewhat clearer to me. Each of the arguments to Expression.Call after the MethodInfo itself is meant to be an expression which represents the argument to the method call. In our example we need an expression which represents an argument of type Expression>. We already have the value, but we need to provide the layer of wrapping... just as we did with Expression.Constant in the very first expression tree I showed at the top. To wrap the expression value we've got, we use Expression.Quote. It's still not clear to me exactly why we can use Expression.Quote but not Expression.Constant, but at least it's clearer why we need something...

EDIT: I'm gradually getting there. This Stack Overflow answer from Eric Lippert has much to say on the topic. I'm still trying to get my head round it, but I'm sure when I've read Eric's answer several times, I'll get there.

We can even test that this works, by using the Queryable.AsQueryable method from the real .NET implementation. This creates an IQuerable from any IEnumerable using a built-in query provider. Here's the test program, where FakeQueryable is a static class containing the extension method above:

using System;
using System.Collections.Generic;
using System.Linq;

class Test
{
static void Main()
{        
List list = new List { 3, 5, 1 };
IQueryable source = list.AsQueryable();
IQueryable query = FakeQueryable.Where(source, x => x > 2);
foreach (int value in query)
{
Console.WriteLine(value);
}
}
}

This works, printing just 3 and 5, filtering out the 1. Yay! (I'm explicitly calling FakeQueryable.Where rather than letting extension method resolution find it, just to make things clearer.)

Um, but what's doing the actual work? We've implemented the Where clause without providing any filtering ourselves. It's really the query provider which has built an appropriate IQueryable implementation. When we call GetEnumerator() implicitly in the foreach loop, the query can examine everything that's built up in the expression tree (which could contain multiple operators - it's nesting queries within queries, essentially) and work out what to do. In the case of our IQueryable built from a list, it just does the filtering in-process... but if we were using LINQ to SQL, that's when the SQL would be generated. The provider recognizes the specific methods from Queryable, and applies filters, projections etc. That's why it was important that our demo Where method pretended that the real Queryable.Where had been called - otherwise the query provider wouldn't know what the call expression

Just to hammer the point home even further... Queryable itself neither knows nor cares what kind of data source you're using. Its job is not to perform any query operations itself; its job is to record the requested query operations in a source-agnostic manner, and let the source provider handle them when it needs to.

All the operators using deferred execution in Queryable are implemented in much the same way as our demo Where method. However, that doesn't cover the situation where we need to execute the query now, because it has to return a value directly instead of another query.

This time I'm going to use ElementAt as the sample, simply because it's only got one overload, which makes it very easy to grab the relevant MethodInfo. The general procedure is exactly the same as building a new query, except that this time we call the provider's Execute method instead of CreateQuery.

public static TSource ElementAt(this IQueryable source, int index)
{
if (source == null)
{
throw new ArgumentNullException("source");
}
Expression sourceExpression = source.Expression;
Expression indexExpression = Expression.Constant(index);
MethodInfo method = typeof(Queryable).GetMethod("ElementAt");        
MethodInfo closedMethod = method.MakeGenericMethod(new Type[] { typeof(TSource) });
Expression methodCall = Expression.Call(closedMethod, sourceExpression, indexExpression);
return source.Provider.Execute(methodCall);
}

The type argument we provide to Execute is the desired return type - so for Count, we'd call Execute for example. Again, it's up to the query provider to work out what the call actually means.

It's worth mentioning that both CreateQuery and Execute have generic and non-generic overloads. I haven't personally encountered a use for the non-generic ones, but I gather they're useful for various situations in generated code, particularly if you really don't know the element type - or at least only know it dynamically, and don't want to have to use reflection to generate an appropriate generic method call.

One of the aspects of LINQ which raises it to the "genius" status (and "slightly scary" at the same time) is that most of the time, most developers don't need to make any changes to their source code in order to use Enumerable or Queryable. Take this query expression and its translation:

var query = from person in family
where person.LastName == "Skeet"
select person.FirstName;

var query = family.Where(person => person.LastName == "Skeet")
.Select(person => person.FirstName);

Which set of query methods will that use? It entirely depends on the compile-time type of the "family" variable. If that's a type which implements IQueryable, it will use the extension methods in Queryable, the lambda expression will be converted into expression trees, and the type of "query" will be IQueryable. Otherwise (and assuming the type implements IEnumerable isn't some other interesting type such as ParallelEnumerable) it will use the extension methods in Enumerable, the lambda expressions will be converted into delgeates, and the type of "query" will be IEnumerable.

The query expression translation part of the specification has no need to care about this, because it's simply translating into a form which uses lambda expressions - the rest of overload resolution and lambda expression conversion deals with the details.

Genius... although it does mean you need to be careful that really you know where your query evaluation is going to take place - you don't want to accidentally end up performing your whole query in-process having shipped the entire contents of a database across a network connection...

This was really a whistlestop tour of the "other" side of LINQ - and without going into any of the details of the real providers such as LINQ to SQL. However, I hope it's given you enough of a flavour for what's going on to appreciate the general design. Highlights:

Expression trees are used to capture logic in a data structure which can be examined relatively easily at execution time Lambda expressions can be converted into expression trees as well as delegates IQueryable and IQueryable form a sort of parallel interface hierarchy to IEnumerable and IEnumerable - although the queryable forms extend the enumerable forms IQueryProvider enables one query to be built based on another, or executed immediately where appropriate Queryable provides equivalent extension methods to most of the Enumerable LINQ operators, except that it uses IQueryable sources and expression trees instead of delegates Queryable doesn't handle the queries itself at all; it simply records what's been called and delegates the real processing to the query provider

I think I've now covered most of the topics I wanted to mention after finishing the actual Edulinq implementation. Next up I'll talk about some of the thorny design issues (most of which I've already mentioned, but which bear repeating) and then I'll write a brief "series conclusion" post with a list of links to all the other parts.

 

Read more »