This is the entry point for the Oracle World - a static page that indexes all the pages on this and other sites.

If you want to use the more traditional web pages entry point you can bookmark this URL for ordinary postings. This is also the URL for the page referenced to the right called All Postings.

Email:
bhargavtripathi@yahoo.com [ 4 any queries mails are most welcomed ]
bhargavtripathi@live.com

Homepage: http://bhargavtripathi.blogspot.com

A few Inspirational Thoughts:

Neils Bohr:: An expert is someone who has made all the mistakes that can be made, but in a very narrow field

Michael Shermer: We have evolved brains that pay attention to anecdotes because false positives (believing there is a connection between A and B when there is not) are usually harmless, whereas false negatives (believing there is no connection between A and B when there is) may take you out of the gene pool.

Stephen Hawking:The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge.

Erwin Schrödinger: Thus, the task is, not so much to see what no one has yet seen; but to think what nobody has yet thought, about that which everybody sees.

Richard Dawkins: Science has no methods for deciding what is ethical. That is a matter for individuals and for societies. But science can clarify the questions being asked, and can clear up obfuscating misunderstandings. This usually amounts to the useful: “you cannot have it both ways” style of arguing.

Granny Weatherwax (auth: Terry Pratchett): Trouble is, just because things are obvious doesn’t mean they’re true.

Albert Einstein: Any man who reads too much and uses his own brain too little falls into lazy habits of thinking.

Carl Sagan: You can’t convince a believer of anything; for their belief is not based on evidence, it’s based on a deep seated need to believe.

Charles Darwin: Ignorance more frequently begets confidence than does knowledge.

Richard Feynman: It doesn’t matter how beautiful your theory is, it doesn’t matter how smart you are. If it doesn’t agree with experiment, it’s wrong.

Stephen Jay Gould: The invalid assumption that correlation implies cause is probably among the two or three most serious and common errors of human reasoning.

History of Oracle

Way back in June 1970, Dr E F Codd published a paper entitled A Relational Model of Data for Large Shared Data Banks. This relational model, sponsored by IBM, then came to be accepted as the definitive model for relational database management systems – RDBMS. The language developed by IBM to manipulate the data stored within Codd's model was originally called Structured English Query Language, or SEQUEL, with the word 'English' later being dropped in favor Structured Query Language – SQL.

In 1979 a company called Relational Software, Inc. released the first commercially available implementation of SQL. Relational Software later came to be known as Oracle Corporation.

Oracle Version 8

You'll find versions of Oracle8 available for many of today's popular computing environments, in particular Windows, UNIX and Linux. This is one of the reasons why it's so popular, and luckily for us as developers, doesn't make that much of a difference which platform it is running on.

The original version of Oracle8 was designed to support applications in the up-and-coming network-computing era, ranging from a small departmental application right up to a high-volume enterprise-wide system. In order to provide this level of flexibility, Oracle8 comes in two different editions:

Oracle OpenWorld 2009 Call for Papers

By justin.kestelyn

For the first time in many years, a public call-for-papers has been issued for Oracle OpenWorld.

Can anyone remember the last time this happened, if ever? Not in my tenure here (since 2003).

You have until April 19 to submit your paper; every submission will be carefully considered - and those lucky few will be notified on June 9. Some sessions that are not picked for the show will available for public voting via Oracle Mix starting in early June. After three weeks of voting, winners will be announced.

The Oracle OpenWord blog has some useful suggestions for getting your paper noticed herehttp://www.oracle.com/openworld/index.html

Very good advice...

I've been reading Seth Godin's Web page for many years. He is a 'marketing' person, with a lot of good old fashioned common sense. I agree with most of what he writes - and he just did a longish post (for Seth Godin it was long). It was on Slack.

His two points in these unique times - if you find yourself unexpectedly with more free time than you had anticipated you should consider:

a) Continuing your education, learn something new. As you go to interview and look around, people will ask you what you've been doing with your time. If you can arrive at an interview with "I've been learning X in my free time" and be really excited about it - be able to converse about it, that'll be a really positive thing.

b) Participate - join the forums - become known. I've said that myself many times in the past.

So think about that if you find you have more time on your hands than you anticipated having... Not bad ideas. Even if you don't have a sudden abundance of free time - maybe find the time to do these two things..

You can use the SqlDataSource control to connect to an Oracle database. You connect the control to an Oracle database by first establishing connection information in the Web.config file, and then by referencing the connection information in a SqlDataSource control.
Note

The System.Data.OracleClient provider requires Oracle client software version 8.1.7 or later to be installed on the computer that connects to the Oracle database; in this case, on the computer that runs ASP.NET pages.
To use the SqlDataSource control to connect to an Oracle database

1. Open the page from which you want to connect to an Oracle database.

2. Switch to Design view.

3. From the Data tab of the Toolbox, drag a SqlDataSource control onto your page.

4. If the SqlDataSource Tasks smart tag does not appear, right-click the control and click Show Smart Tag.

5. In the SqlDataSource Tasks list, click Configure Data Source.

6. In the Configure Data Source dialog box, click New Connection.
The Choose Data Source dialog box is displayed.

7. In the Data source list, click Oracle Database and then click Continue.
The Add Connection dialog box is displayed.

8. In the Server name box, type the name of the Oracle server.

9. Type the user name and password to connect with the database.

10. Select the Save my password box to save authentication information as part of the connection string, and then click OK.

Note

If you include explicit authentication information in a connection string, you should encrypt the ConnectionStrings section of the Web.config file. For details, see Encrypting Configuration Information Using Protected Configuration.

You are returned to the Configure Data Source dialog with the new connection string information displayed.

11. Click Next.

12. Make sure that the Yes, save this connection check box is selected, change the name of the connection string if you want, and then click Next to have the connection string information saved in the application's Web.config file.

The Configure the Select Statement pane appears.

13. Click Specify a custom SQL statement or stored procedure if you want to type in your own SQL statement, or Specify columns from a table or view to use the wizard to create the query.

14. If you clicked Specify columns from a table or view, in the Name list click the name of the table or view to use, and then in the Columns list select the columns to return.

Note

To generate update statements, click Advanced and then click Generate INSERT, UPDATE, and DELETE statements.

15.If you clicked Specify a custom SQL statement or stored procedure, click Next to move to the Define Custom Statements or Stored Procedures pane to write your query.
Note

To define update statements, click the UPDATE, INSERT, and DELETE tabs and specify the SQL command or stored procedure to use for each operation.

16. Click Next.

17. Click the Test Query button to test your query against the database, and then click Finish.

An “upsert” in the combination of an INSERT and UPDATE statement, built into a single clause. The Upsert model is especially useful in data warehouses where you need the following logic:

IF FOUND

THEN UPDATE

ELSE

INSERT;

Upserts are great for processes that you normally require multiple insert as select statements IAS) statements. This is because upserts remove the need for row-at-a-time processing and enable the entire transaction as a single set.

Let’s illustrate upserts with a simple example. Let’s assume that we need to take a NEW_CUSTOMERS table and spread the row into two other tables. The RICH_CUSTOMERS table is populated by selecting only those customers with a credit_limit > 100000, and all tables are moved into the CUSTOMER table.

In Oracle8i, this operation required two statements:

INSERT INTO

rich_customers

(cust_id,cust_credit_limit)

SELECT cust_id, cust_credit_limit

FROM new_customers

WHERE credit_limit >=100000;



INSERT INTO customers SELECT * FROM new_customers;

In Oracle9i, an UPSERT can accomplish this task in a single statement:

INSERT

FIRST WHEN

credit_limit >=100000

THEN INTO

rich_customers

VALUES(cust_id,cust_credit_limit)

INTO customers

ELSE

INTO customers SELECT * FROM new_customers;

We have "alter table" syntax from Oracle to add data columns in-place in this form:

alter table
table_name
add
(
column1_name column1_datatype column1_constraint,
column2_name column2_datatype column2_constraint,
column3_name column3_datatype column3_constraint
);

Here are some examples of Oracle "alter table" syntax to add data columns.

alter table
cust_table
add
cust_sex varchar2(1) NOT NULL;

Her is an example of Oracle "alter table" syntax to add multiple data columns.

ALTER TABLE
cust_table
ADD
(
cust_sex char(1) NOT NULL,
cust_credit_rating number
);

This statement shows how the employees table owned by the sample human resources (hr) schema was created. A hypothetical name is given to the table and constraints so that you can duplicate this example in your test database:

CREATE TABLE employees_demo
   ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
        CONSTRAINT emp_last_name_nn_demo NOT NULL
    , email          VARCHAR2(25)
        CONSTRAINT emp_email_nn_demo     NOT NULL
    , phone_number   VARCHAR2(20)
    , hire_date      DATE  DEFAULT SYSDATE
        CONSTRAINT emp_hire_date_nn_demo  NOT NULL
    , job_id         VARCHAR2(10)
       CONSTRAINT     emp_job_nn_demo  NOT NULL
    , salary         NUMBER(8,2)
      CONSTRAINT     emp_salary_nn_demo  NOT NULL
    , commission_pct NUMBER(2,2)
    , manager_id     NUMBER(6)
    , department_id  NUMBER(4)
    , dn             VARCHAR2(300)
    , CONSTRAINT     emp_salary_min_demo
                    CHECK (salary > 0)
    , CONSTRAINT     emp_email_uk_demo
                    UNIQUE (email)
   ) ;

This table contains twelve columns. The employee_id column is of datatype NUMBER. The hire_date column is of datatype DATE and has a default value of SYSDATE. The last_name column is of type VARCHAR2 and has a NOT NULL constraint, and so on.

Oracle Database 10g on Mac OS/X

Oracle Database 10g release 10.2.0.4 for Apple MAC OS X (Leopard 10.5.4) Intel x86-64 Available on OTN http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10204macsoft_x86-64.html

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity.
Join Conditions

Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.
Equijoins

An equijoin is a join with a join condition containing an equality operator ( = ). An equijoin combines rows that have equivalent values for the specified columns.

For Example :
the following query returns empno,name,sal,deptno and department name and city from department table.

select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,dept.city from emp,dept where emp.deptno=dept.deptno;


The above query can also be written like, using aliases, given below.

select e.empno, e.ename, e.sal, e.deptno, d.dname, d.city from emp e, dept d where emp.deptno=dept.deptno;

The above query can also be written like given below without using table qualifiers.

select empno,ename,sal,dname,city from emp,dept where emp.deptno=dept.deptno;


And if you want to see all the columns of both tables then the query can be written like this.

select * from emp,dept where emp.deptno=dept.deptno;


Non Equi Joins.

Non equi joins is used to return result from two or more tables where exact join is not possible.

For Example : we have emp table and salgrade table. The salgrade table contains grade and their low salary and high salary. Suppose you want to find the grade of employees based on their salaries then you can use NON EQUI join.

select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.lowsal and s.hisal
Self Joins

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.

For example:The following query returns employee names and their manager names for whom they are working.

Select e.empno, e.ename, m.ename “Manager” from emp e,

emp m where e.mgrid=m.empno
Inner Join

An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.
Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

* To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax, or apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B.

* To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the outer join operator (+) to all columns of A in the join condition. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.

* To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSI FULL [OUTER] JOIN syntax.

For example the following query returns all the employees and department names and even those department names where no employee is working.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.city from emp e, dept d where e.deptno(+)=d.deptno;

That is specify the (+) sign to the column which is lacking values.
Cartesian Products

If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product.

To install the Oracle software, you must use the Oracle Universal installer.

1.For this installation, you need either the DVDs or a downloaded version of the DVDs. In this tutorial, you install from the downloaded version. From the directory where the DVD files were unzipped, open Windows Explorer and double-click on setup.exe from the \db\Disk1directory. You can download it from here (available now).

2. The product you want to install is Oracle Database 11g. Make sure the product is selected and click Next.



3. You will perform a custom installation. Click Advanced Installation and click Next.



4. If this is your first install, you need to specify your Inventory directory. The location should be set to /u01/app/oraInventory. Accept the default Operating System group name, oinstall. Then, click Next.

5. You want to install the Real Application Testing component so you need to perform a custom install. Select Custom and click Next.



6. Accept the install location defaults and click Next.



7. The list of Product Components appears. Scroll down the list. In addition to the defaults, you want to select the Real Application Testing and click Next.



8. At this time, you only want to install the software. Select Install database Software only and click Next.



9. Review the Summary window to verify what is to be installed. Then, click Install.



10. The progress window appears.



11. Click Exit. Click Yes to confirm exit.