OLE DB and return value from stored procedure - Database Discussion Boards

My point is that I don't think EVERY query should be dynamic sql. It has it's place but not for every single query.

Never underestimate the power of human stupidity
RAH

I completely agree Thumbs Up | :thumbsup:

Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)

Not that this helps but...

I didn't know Oracle supported stored procedures, when I had to use it (1996-2001, Oracle 6?), we never used stored procedures, only PRO*C. And it was good. Cool | :cool:

You are correct, in terms of the naming convention they are called procedures - they are basically the same as Microsoft's stored procedures.

Also procedures can be stored in packages(basically classes).

Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)

Even way back when I was using it there were procedures (about the same time). I pointed out to the developer that I could more easily do the string concatenation in c#, as per your prefferred method, than have him do it in PL/SQL.

I pulled in another dev who has very good Oracle skills, the coding style will be changing to proper PL/SQL rather than dynamic sql.

Never underestimate the power of human stupidity
RAH

Mycroft Holmes wrote:
When building a SP is it reasonable to create a series of strings and then
EXECUTE IMMEDIATE to load the results into a temp table.

Only if it can't be done using normal PL/SQL.
Which would mean almost never.

That was my opinion, but without the authority of experience I am glad to have it confirmed. That dev now has a refactoring (rewrite) job to do. Luckilly this was the first couple of jobs I have given him.

Never underestimate the power of human stupidity
RAH

I have code and return value == 0. Why? It must be 99!

C++

ALTER PROCEDURE [dbo].[myproc] 
(
@mykod INT
)
RETURNS INT
AS
BEGIN 
select * from table1 where kod=@mykod
RETURN 99
END

//----------------------------------

ICommandText*   pICommandText;
hr = pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText,(IUnknown**) &pICommandText);
if (FAILED(hr)) AfxMessageBox("Command Create Command Failed");




WCHAR* wSQLString = L"{?=CALL myproc(?)}";

pICommandText->SetCommandText(DBGUID_DBSQL, wSQLString);

 SPROCPARAMS         sprocparams = {0, 2};

  
    // Command parameter data.
    DBPARAMS            Params;
    const ULONG         nParams = 2;
//-----------------

typedef struct tagSPROCPARAMS
    {
    long        lReturnValue;
    long 	lkodValue;
    } SPROCPARAMS;




   

DBBINDING           acDBBinding[nParams];
    DBBINDSTATUS        acDBBindStatus[nParams];


    for (ULONG i = 0; i < nParams; i++)
        {
        acDBBinding[i].obLength = 0;
        acDBBinding[i].obStatus = 0;
        acDBBinding[i].pTypeInfo = NULL;
        acDBBinding[i].pObject = NULL;
        acDBBinding[i].pBindExt = NULL;
        acDBBinding[i].dwPart = DBPART_VALUE;
        acDBBinding[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
        acDBBinding[i].dwFlags = 0;
        acDBBinding[i].bScale = 0;
        }


    acDBBinding[0].iOrdinal = 1;
    acDBBinding[0].obValue = offsetof(SPROCPARAMS, lReturnValue);
    acDBBinding[0].eParamIO = DBPARAMIO_OUTPUT;
    acDBBinding[0].cbMaxLen = sizeof(long);
    acDBBinding[0].wType = DBTYPE_I4;
    acDBBinding[0].bPrecision = 11;
	

	acDBBinding[1].iOrdinal = 2;
    acDBBinding[1].obValue = offsetof(SPROCPARAMS, lkodValue);
    acDBBinding[1].eParamIO = DBPARAMIO_INPUT;
    acDBBinding[1].cbMaxLen = sizeof(long);
    acDBBinding[1].wType = DBTYPE_I4;
	acDBBinding[1].bPrecision = 11;


    // Get the IAccessor interface, then create the accessor for
    // the defined parameters.

	IAccessor*  pIAccessor; 
HACCESSOR   hAccessor; 


pICommandText->QueryInterface(IID_IAccessor,
        (void**) &pIAccessor);
	if(FAILED(hr)) AfxMessageBox("Failed Query Interface IId_IAccessor");
	
	

    hr = pIAccessor->CreateAccessor(DBACCESSOR_PARAMETERDATA,
        nParams, acDBBinding, sizeof(SPROCPARAMS), &hAccessor,
        acDBBindStatus);
	if(FAILED(hr)) AfxMessageBox("Failed Create Accessor");
    
    // Fill the DBPARAMS structure for the command execution.
    Params.pData = &sprocparams;
    Params.cParamSets = 1;
    Params.hAccessor = hAccessor;

	LONG      cRowsAffected;

	IRowset*    pIRowset;


hr = pICommandText->Execute(NULL, IID_IRowset, &Params,&cRowsAffected, (IUnknown**) &pIRowset);
if (FAILED(hr)) AfxMessageBox("Execute Failed");

CString str;
str.Format("%d",sprocparams.lReturnValue);
//return 0 !!!!!!!!!!!!!!!!!!!!!!!!!!
AfxMessageBox(str);	
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

pIRowset->Release();


str.Format("%d",sprocparams.lReturnValue);
//return 0 !!!!!!!!!!!!!!!!!!!!!!!!!!
AfxMessageBox(str);	
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Íà ôîðóìå ÿ èùó ïðîãðàìû ïî Ñ++.

Hi All,
Can somebody who has SQL 2000 assist with this?
Create a simple DTS package using any table from Northwind DB and then prompt the number of rows in that table through a message box. This package should then be posted to me.
Aim: I want to experiment conversion of DTS to SSIS 2008 package but I don't have SQL 2000
I look forward to your response.
Regards,
Current

This is a great site that I use for such a requirment

SQL DTS[^]

They also run a site for SSIS to the link to that is at the top of the page.

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Thanks your response.
I just wonder that I must have SQL 2000 before I can experiment the conversion.Am I correct?
Thanks

current1999 wrote:
I just wonder that I must have SQL 2000 before I can experiment the conversion

The answer is no, if you are just converting an existing package then all you need is the target version.

<hr><small> Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
</small>
</hr>

hi,

anybody can tell me about joints

Thanks & Regards
vinodh

Ask on google[^].

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

www.cacttus.com

vinodh.K wrote:
anybody can tell me about joints

After smoking one everything seems very mellow.

Unrequited desire is character building. OriginalGriff

I assume you mean joins. Basically they are used to join tables together when some underlying criteria is satisfied. There are, effectively, three types - LEFT JOINS, RIGHT JOINS and equi-joins (otherwise known as the standard JOIN).

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

We occasionally use cross joins as well.

Read this article[^]

Never underestimate the power of human stupidity
RAH

vinodh.K wrote:
anybody can tell me about joints

Assuming you really meant SQL Joins

There are six, types of joins.

1: Left join
Return all records from the left most table on the join.

SQL

SELECT
    e.*
    , a.*
FROM employee AS e (Notice the AS, that creates an alias to that table)
    LEFT OUTER JOIN Address a (e.EmployeeKey = a.EmployeeKey)

Returns all of the records in the employees table, and only those from the address table that actualy match

2: Right join
Return all records from the right most table on the join.

SQL

SELECT
    e.*
    , a.*
FROM employee AS e (Notice the AS, that creates an alias to that table)
    RIGHT OUTER JOIN Address a (e.EmployeeKey = a.EmployeeKey)

Returns all of the records in the Adreess table, and only those from the employees table that actualy match

3: Full Join
Returns all rows from both Tables

SQL

SELECT
    e.*
    , a.*
FROM employee AS e (Notice the AS, that creates an alias to that table)
    FULL JOIN Address a (e.EmployeeKey = a.EmployeeKey)

4: Inner join

Return only records where both keys match exactly.

SQL

SELECT
    e.*
    , a.*
FROM employee AS e (Notice the AS, that creates an alias to that table)
    INNER OUTER JOIN Address a (e.EmployeeKey = a.EmployeeKey)

Returns all of the Employes and Adresses where both keys match exactly

5: Cross Join
Use this with caution, all records on all sides are paired and returned

SQL

SELECT
    e.*
    , a.*
FROM employee AS e (Notice the AS, that creates an alias to that table)
    CROSS JOIN Address a (e.EmployeeKey = a.EmployeeKey)

This is the most expensive join posible, as for every record on both sides are returned, so if you had two tables that had ten rows in each, 100 rows would be returned.

6 Self Join

SQL

SELECT
    e1.*
    , e2.*
FROM employee AS e1 (Notice the AS, that creates an alias to that table)
    JOIN employee e2 (e1.EmployeeKey = e2.EmployeeKey)

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

With Oracle, we are used to joining datasets by combinations of datasources in the FROM clause and a WHERE clause to join them together. Datasets are usually tables, but can also be views, in-inline views, subqueries, table functions, nested tables and so on. Oracle join syntax is generally as follows:

SELECT ...
FROM dataset_one d1
, dataset_two d2
WHERE d1.column(s) = d2.column(s)
AND ...

With this syntax we separate datasources by commas and code a single WHERE clause that will include the join predicates together with any filter predicates we might require. ANSI join syntax is slightly different on two counts. First, we specify the type of join we require and second we separate the join predicates from the filter predicates. ASNI syntax can notionally be expressed as follows:

SELECT ...
FROM dataset_one d1
JOIN TYPE
dataset_two d2
ON (d1.column(s) = d2.column(s)) --<-- can also use USING (column(s))
WHERE filter_predicates...

As commented, the ON clause is where we specify our joins. If the column names are the same, we can replace this with a USING clause. We will see examples of both methods for expressing join predicates throughout this article.

Given this pseudo-syntax, we will examples of the following join types in this article.

* INNER JOIN
* NATURAL JOIN
* CROSS JOIN
* LEFT OUTER JOIN
* RIGHT OUTER JOIN
* FULL OUTER JOIN

inner join

When we join two tables or datasets together on an equality (i.e. column or set of columns) we are performing an inner join. The ANSI method for joining EMP and DEPT is as follows.

SQL> SELECT d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 INNER JOIN
4 emp e
5 USING (deptno);

DNAME LOC ENAME JOB
-------------- ------------- ---------- ---------
RESEARCH DALLAS SMITH CLERK
SALES CHICAGO ALLEN SALESMAN
SALES CHICAGO WARD SALESMAN
RESEARCH DALLAS JONES MANAGER
SALES CHICAGO MARTIN SALESMAN
SALES CHICAGO BLAKE MANAGER
ACCOUNTING NEW YORK CLARK MANAGER
RESEARCH DALLAS SCOTT ANALYST
ACCOUNTING NEW YORK KING PRESIDENT
SALES CHICAGO TURNER SALESMAN
RESEARCH DALLAS ADAMS CLERK
SALES CHICAGO JAMES CLERK
RESEARCH DALLAS FORD ANALYST
ACCOUNTING NEW YORK MILLER CLERK

14 rows selected.

Note that the INNER keyword is optional and JOIN alone will work. In this example we used the USING(deptno) clause to specify our join predicate. We were able to use this because both tables have the same join-column name. When we use this clause, however, we cannot alias the join columns anywhere in our query as the following example demonstrates.

SQL> SELECT COUNT(d.deptno)
2 FROM dept d
3 INNER JOIN
4 emp e
5 USING (deptno);

SELECT COUNT(d.deptno)
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

The error message is reasonably meaningful (though "alias" might have been a better choice of word than "qualifier"). The correct way to express this query is to simply remove the alias as follows.

SQL> SELECT COUNT(deptno)
2 FROM dept d
3 INNER JOIN
4 emp e
5 USING (deptno);

COUNT(DEPTNO)
-------------
14

1 row selected.

As stated in the syntax overview earlier, we can also use an ON clause to express our join predicates. This is necessary when the join columns have different names or when we wish to alias our columns. The parentheses around the ON clause are optional.

SQL> SELECT d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 INNER JOIN
4 emp e
5 ON (d.deptno = e.deptno);

DNAME LOC ENAME JOB
-------------- ------------- ---------- ---------
RESEARCH DALLAS SMITH CLERK
SALES CHICAGO ALLEN SALESMAN
SALES CHICAGO WARD SALESMAN
RESEARCH DALLAS JONES MANAGER
SALES CHICAGO MARTIN SALESMAN
SALES CHICAGO BLAKE MANAGER
ACCOUNTING NEW YORK CLARK MANAGER
RESEARCH DALLAS SCOTT ANALYST
ACCOUNTING NEW YORK KING PRESIDENT
SALES CHICAGO TURNER SALESMAN
RESEARCH DALLAS ADAMS CLERK
SALES CHICAGO JAMES CLERK
RESEARCH DALLAS FORD ANALYST
ACCOUNTING NEW YORK MILLER CLERK

14 rows selected.

The Oracle equivalent of the inner joins we have seen so far is as follows.

SELECT d.dname, d.loc, e.ename, e.job
FROM dept d
, emp e
WHERE d.deptno = e.deptno;

One of the key differences between ANSI and Oracle syntax is that the former separates the join and filter predicates. On complex statements, this can be extremely useful. In the following example, we will join EMP and DEPT but add a filter predicate on DEPT using the standard WHERE clause.

SQL> SELECT d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 INNER JOIN
4 emp e
5 ON (d.deptno = e.deptno)
6 WHERE d.loc = 'DALLAS';

DNAME LOC ENAME JOB
-------------- ------------- ---------- ---------
RESEARCH DALLAS SMITH CLERK
RESEARCH DALLAS JONES MANAGER
RESEARCH DALLAS SCOTT ANALYST
RESEARCH DALLAS ADAMS CLERK
RESEARCH DALLAS FORD ANALYST

5 rows selected.

We could add the filter to the ON clause but this makes no difference to performance, even if the filter is on the second table. Furthermore, adding filter predicates to the ON clause can cause data issues with outer joins (we will see an example of this later), so we should avoid this practice altogether.

For reference, the Oracle syntax for the inner join with an additional filter predicate is as follows.

SELECT d.dname, d.loc, e.ename, e.job
FROM dept d
, emp e
WHERE d.deptno = e.deptno
AND d.loc = 'DALLAS';

natural join

A natural join will join two datasets on all matching column names, regardless of whether the columns are actually related in anything other than name. For example, the EMP and DEPT tables share one common column name and a natural join between the two tables would be correct in this scenario.

The following example converts our INNER JOIN from previous examples to a NATURAL JOIN.

SQL> SELECT d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 NATURAL JOIN
4 emp e;

DNAME LOC ENAME JOB
-------------- ------------- ---------- ---------
RESEARCH DALLAS SMITH CLERK
SALES CHICAGO ALLEN SALESMAN
SALES CHICAGO WARD SALESMAN
RESEARCH DALLAS JONES MANAGER
SALES CHICAGO MARTIN SALESMAN
SALES CHICAGO BLAKE MANAGER
ACCOUNTING NEW YORK CLARK MANAGER
RESEARCH DALLAS SCOTT ANALYST
ACCOUNTING NEW YORK KING PRESIDENT
SALES CHICAGO TURNER SALESMAN
RESEARCH DALLAS ADAMS CLERK
SALES CHICAGO JAMES CLERK
RESEARCH DALLAS FORD ANALYST
ACCOUNTING NEW YORK MILLER CLERK

14 rows selected.

Note that the only identifiable benefit of NATURAL JOIN is that we do not need to specify a join predicate. Oracle determines all matching column names and uses these to join the two tables. We cannot alias any columns used in the natural join, as the following example demonstrates.

SQL> SELECT COUNT(d.deptno)
2 FROM dept d
3 NATURAL JOIN
4 emp e;

SELECT COUNT(d.deptno)
*
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier

As we saw earlier with the USING clause for our INNER JOIN examples, the solution is to remove any aliases on shared column names.

SQL> SELECT COUNT(deptno)
2 FROM dept d
3 NATURAL JOIN
4 emp e;

COUNT(DEPTNO)
-------------
14

1 row selected.

With EMP and DEPT as our demonstration tables, we are fortunate that the NATURAL JOIN gives us the correct answer. Commercial data models are not always so rigid in the naming of columns and the data they contain, especially as systems grow over time. We can imagine the consequences of adding another column to EMP that shares the same name as one in DEPT.

SQL> ALTER TABLE emp ADD loc VARCHAR2(10) DEFAULT 'LONDON';

Table altered.

SQL> SELECT *
2 FROM dept d
3 NATURAL JOIN
4 emp e;

no rows selected

Probably the best advice to offer regarding NATURAL JOIN is to avoid it! Note that there is no equivalent Oracle syntax.
left outer join

In traditional Oracle syntax, outer joins are indicated by (+) and this can sometimes cause issues when attempting to outer join multiple tables or includeg expressions in join predicates. Oracle outer joins have no concept of direction, whereas ANSI-compliant outer joins do. In the following example, we will outer join DEPT to EMP using the ANSI LEFT OUTER JOIN. The way to interpret this is to read the tables in the FROM clause from left to right. The left-hand table is the superset and the table on the right is the potentially deficient set.

SQL> SELECT deptno, d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 LEFT OUTER JOIN
4 emp e
5 USING (deptno);

DEPTNO DNAME LOC ENAME JOB
---------- -------------- ------------- ---------- ---------
20 RESEARCH DALLAS SMITH CLERK
30 SALES CHICAGO ALLEN SALESMAN
30 SALES CHICAGO WARD SALESMAN
20 RESEARCH DALLAS JONES MANAGER
30 SALES CHICAGO MARTIN SALESMAN
30 SALES CHICAGO BLAKE MANAGER
10 ACCOUNTING NEW YORK CLARK MANAGER
20 RESEARCH DALLAS SCOTT ANALYST
10 ACCOUNTING NEW YORK KING PRESIDENT
30 SALES CHICAGO TURNER SALESMAN
20 RESEARCH DALLAS ADAMS CLERK
30 SALES CHICAGO JAMES CLERK
20 RESEARCH DALLAS FORD ANALYST
10 ACCOUNTING NEW YORK MILLER CLERK
40 OPERATIONS BOSTON

15 rows selected.

The OUTER keyword is optional but due to the lack of (+) symbols, including it seems to be more descriptive. Note that this example included the USING clause for our outer join predicates, but the ON clause would also work as well. The Oracle syntax for this join is as follows.

SELECT d.deptno, d.dname, d.loc, e.ename, e.job
FROM dept d
, emp e
WHERE d.deptno = e.deptno (+);

outer joins and expressions

One of the benefits of the ANSI outer join syntax is that it makes expressions much easier to work with (on the occasions where we join a column to an expression). By way of simplified example, we can multiply EMP.DEPTNO by 1 to create a small expression in our DEPT-EMP outer join. The Oracle outer join syntax in this case is uninituitive, as we can see below.

SQL> SELECT d.deptno, d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 , emp e
4 WHERE d.deptno = e.deptno*1 (+);

WHERE d.deptno = e.deptno*1 (+)
*
ERROR at line 4:
ORA-00933: SQL command not properly ended

The correct way to write this join predicate is as follows which can get tricky with more complicated expressions, especially those involving functions.

SQL> SELECT d.deptno, d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 , emp e
4 WHERE d.deptno = e.deptno (+) * 1;

DEPTNO DNAME LOC ENAME JOB
---------- -------------- ------------- ---------- ---------
20 RESEARCH DALLAS SMITH CLERK
30 SALES CHICAGO ALLEN SALESMAN
30 SALES CHICAGO WARD SALESMAN
20 RESEARCH DALLAS JONES MANAGER
30 SALES CHICAGO MARTIN SALESMAN
30 SALES CHICAGO BLAKE MANAGER
10 ACCOUNTING NEW YORK CLARK MANAGER
20 RESEARCH DALLAS SCOTT ANALYST
10 ACCOUNTING NEW YORK KING PRESIDENT
30 SALES CHICAGO TURNER SALESMAN
20 RESEARCH DALLAS ADAMS CLERK
30 SALES CHICAGO JAMES CLERK
20 RESEARCH DALLAS FORD ANALYST
10 ACCOUNTING NEW YORK MILLER CLERK
40 OPERATIONS BOSTON

15 rows selected.

ANSI syntax makes this much easier as no special considerations are needed, as follows.

SQL> SELECT d.deptno, d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 LEFT OUTER JOIN
4 emp e
5 ON (d.deptno = e.deptno * 1);

DEPTNO DNAME LOC ENAME JOB
---------- -------------- ------------- ---------- ---------
20 RESEARCH DALLAS SMITH CLERK
30 SALES CHICAGO ALLEN SALESMAN
30 SALES CHICAGO WARD SALESMAN
20 RESEARCH DALLAS JONES MANAGER
30 SALES CHICAGO MARTIN SALESMAN
30 SALES CHICAGO BLAKE MANAGER
10 ACCOUNTING NEW YORK CLARK MANAGER
20 RESEARCH DALLAS SCOTT ANALYST
10 ACCOUNTING NEW YORK KING PRESIDENT
30 SALES CHICAGO TURNER SALESMAN
20 RESEARCH DALLAS ADAMS CLERK
30 SALES CHICAGO JAMES CLERK
20 RESEARCH DALLAS FORD ANALYST
10 ACCOUNTING NEW YORK MILLER CLERK
40 OPERATIONS BOSTON

15 rows selected.

outer joins and predicates

Remember that ANSI join syntax separates join predicates from filter predicates. In the following example, we outer join DEPT and EMP but limit the results to those employees working in DALLAS only.

SQL> SELECT d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 LEFT OUTER JOIN
4 emp e
5 ON (d.deptno = e.deptno)
6 WHERE d.loc = 'DALLAS';

DNAME LOC ENAME JOB
-------------- ------------- ---------- ---------
RESEARCH DALLAS SMITH CLERK
RESEARCH DALLAS JONES MANAGER
RESEARCH DALLAS SCOTT ANALYST
RESEARCH DALLAS ADAMS CLERK
RESEARCH DALLAS FORD ANALYST

5 rows selected.

While it is syntactically possible to move the filter predicates into the join clause, in the case of an ANSI outer join, this can give incorrect results if the filter is on a column from the superset (or "driving table"). In the following example, we move the filter predicate from our previous query into the ON clause.

SQL> SELECT d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 LEFT OUTER JOIN
4 emp e
5 ON (d.deptno = e.deptno
6 AND d.loc = 'DALLAS');

DNAME LOC ENAME JOB
-------------- ------------- ---------- ---------
ACCOUNTING NEW YORK
RESEARCH DALLAS SMITH CLERK
RESEARCH DALLAS JONES MANAGER
RESEARCH DALLAS SCOTT ANALYST
RESEARCH DALLAS ADAMS CLERK
RESEARCH DALLAS FORD ANALYST
SALES CHICAGO
OPERATIONS BOSTON

8 rows selected.

We can see that these results are wrong and that the driving table has all of its data returned by this query. What if we wish to outer join to a subset of EMP? In this case, the filter predicates on EMP columns can be safely included in the join predicates, as follows.

SQL> SELECT d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 LEFT OUTER JOIN
4 emp e
5 ON (d.deptno = e.deptno
6 AND e.job = 'SALESMAN');

DNAME LOC ENAME JOB
-------------- ------------- ---------- ---------
SALES CHICAGO ALLEN SALESMAN
SALES CHICAGO WARD SALESMAN
SALES CHICAGO MARTIN SALESMAN
SALES CHICAGO TURNER SALESMAN
OPERATIONS BOSTON
RESEARCH DALLAS
ACCOUNTING NEW YORK

7 rows selected.

If we move the EMP filter predicates to the WHERE clause, we see inconsistent results again, as follows. This is the semantic equivalent of an inner join because all outer joined records will have a NULL JOB.

SQL> SELECT d.dname, d.loc, e.ename, e.job
2 FROM dept d
3 LEFT OUTER JOIN
4 emp e
5 ON (d.deptno = e.deptno)
6 WHERE e.job = 'SALESMAN';

DNAME LOC ENAME JOB
-------------- ------------- ---------- ---------
SALES CHICAGO ALLEN SALESMAN
SALES CHICAGO WARD SALESMAN
SALES CHICAGO MARTIN SALESMAN
SALES CHICAGO TURNER SALESMAN

4 rows selected.

For reference, the Oracle equivalent of this type of outer join is as follows.

SELECT d.dname, d.loc, e.ename, e.job
FROM dept d
, (SELECT ename, job, deptno
FROM emp
WHERE job = 'SALESMAN') e
WHERE d.deptno = e.deptno (+);

outer joins as anti-joins

Outer joins are often used as an alternative to anti-joins, particularly NOT IN queries. The Oracle syntax for this is as follows.

SQL> SELECT d.dname, d.loc
2 FROM dept d
3 , emp e
4 WHERE d.deptno = e.deptno (+)
5 AND e.deptno IS NULL;

DNAME LOC
-------------- -------------
OPERATIONS BOSTON

1 row selected.

This can be expressed as a NOT IN query as follows.

SQL> SELECT d.dname, d.loc
2 FROM dept d
3 WHERE d.deptno NOT IN (SELECT e.deptno FROM emp e);

DNAME LOC
-------------- -------------
OPERATIONS BOSTON

1 row selected.

The ANSI equivalent for this technique is shown below.

SQL> SELECT d.dname, d.loc
2 FROM dept d
3 LEFT OUTER JOIN
4 emp e
5 ON (d.deptno = e.deptno)
6 WHERE e.deptno IS NULL;

DNAME LOC
-------------- -------------
OPERATIONS BOSTON

1 row selected.

right outer join

As its name suggests, a right outer join is an outer join where datasets are driven from right to left (i.e. related to the ordering in the query). There are a few occasions where we might want to use RIGHT OUTER JOIN, but generally, there is nothing to be gained from switching the ordering of the tables in this way.

Everything described in the left outer join section of this article applies equally to right outer joins so we will not spend too much time on these. The following example simply shows how to use this type of join. Again, the OUTER keyword is optional.

SQL> SELECT deptno, d.dname, d.loc, e.ename, e.job
2 FROM emp e
3 RIGHT OUTER JOIN
4 dept d
5 USING (deptno);

DEPTNO DNAME LOC ENAME JOB
---------- -------------- ------------- ---------- ---------
20 RESEARCH DALLAS SMITH CLERK
30 SALES CHICAGO ALLEN SALESMAN
30 SALES CHICAGO WARD SALESMAN
20 RESEARCH DALLAS JONES MANAGER
30 SALES CHICAGO MARTIN SALESMAN
30 SALES CHICAGO BLAKE MANAGER
10 ACCOUNTING NEW YORK CLARK MANAGER
20 RESEARCH DALLAS SCOTT ANALYST
10 ACCOUNTING NEW YORK KING PRESIDENT
30 SALES CHICAGO TURNER SALESMAN
20 RESEARCH DALLAS ADAMS CLERK
30 SALES CHICAGO JAMES CLERK
20 RESEARCH DALLAS FORD ANALYST
10 ACCOUNTING NEW YORK MILLER CLERK
40 OPERATIONS BOSTON

15 rows selected.

Note that all we have done is to switch the ordering of the tables in the FROM clause such that Oracle will join from right to left. The Oracle equivalent of this query is as follows.

SELECT d.deptno, d.dname, d.loc, e.ename, e.job
FROM dept d
, emp e
WHERE e.deptno (+) = d.deptno;

full outer join

A full outer join will join two datasets from left-to-right and right-to-left. Records that join in both directions are output once to avoid duplication. In the following demonstration, we will create two different subsets of the EMP table. The two subsets will share a small number of common records but each will also have some unique data.

SQL> CREATE TABLE e1 AS SELECT * FROM emp WHERE deptno IN (10,20);

Table created.

SQL> CREATE TABLE e2 AS SELECT * FROM emp WHERE deptno IN (20,30);

Table created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'E1');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'E2');

PL/SQL procedure successfully completed.

Using SQL Trace to generate an execution plan, we can now see an ANSI FULL OUTER JOIN between these two subsets of EMP.

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

Session altered.

SQL> SELECT e1.ename, e1.deptno, e1.job
2 , e2.ename, e2.deptno, e2.job
3 FROM e1
4 FULL OUTER JOIN
5 e2
6 USING (empno);

ENAME DEPTNO JOB ENAME DEPTNO JOB
---------- ---------- --------- ---------- ---------- ---------
SMITH 20 CLERK SMITH 20 CLERK
JONES 20 MANAGER JONES 20 MANAGER
SCOTT 20 ANALYST SCOTT 20 ANALYST
ADAMS 20 CLERK ADAMS 20 CLERK
FORD 20 ANALYST FORD 20 ANALYST
KING 10 PRESIDENT
CLARK 10 MANAGER
MILLER 10 CLERK
TURNER 30 SALESMAN
BLAKE 30 MANAGER
ALLEN 30 SALESMAN
WARD 30 SALESMAN
MARTIN 30 SALESMAN
JAMES 30 CLERK

14 rows selected.

We can see that records from both datasets appear in the output, even if they do not have a match in the opposite table. This is what a full outer join does and can be useful when partial datasets (such as delta files) need to be joined.

From the trace file, the execution plan for this query is as follows.

Rows Row Source Operation
------- ---------------------------------------------------
14 VIEW
14 UNION-ALL
8 HASH JOIN OUTER
8 TABLE ACCESS FULL E1
11 TABLE ACCESS FULL E2
6 HASH JOIN ANTI
11 TABLE ACCESS FULL E2
8 TABLE ACCESS FULL E1

The full outer join has performed two separate query blocks (one outer join and one anti-join) and appended the results with UNION ALL as we can see in the execution plan.

This type of ANSI join does not have a direct equivalent in Oracle syntax, but we can simulate it with the following query.

SQL> SELECT e1.ename, e1.deptno, e1.job
2 , e2.ename, e2.deptno, e2.job
3 FROM e1
4 , e2
5 WHERE e1.empno (+) = e2.empno
6 UNION
7 SELECT e1.ename, e1.deptno, e1.job
8 , e2.ename, e2.deptno, e2.job
9 FROM e1
10 , e2
11 WHERE e1.empno = e2.empno (+);

ENAME DEPTNO JOB ENAME DEPTNO JOB
---------- ---------- --------- ---------- ---------- ---------
ADAMS 20 CLERK ADAMS 20 CLERK
CLARK 10 MANAGER
FORD 20 ANALYST FORD 20 ANALYST
JONES 20 MANAGER JONES 20 MANAGER
KING 10 PRESIDENT
MILLER 10 CLERK
SCOTT 20 ANALYST SCOTT 20 ANALYST
SMITH 20 CLERK SMITH 20 CLERK
ALLEN 30 SALESMAN
BLAKE 30 MANAGER
JAMES 30 CLERK
MARTIN 30 SALESMAN
TURNER 30 SALESMAN
WARD 30 SALESMAN

14 rows selected.

This time the execution plan looks slightly different, as follows.

Rows Row Source Operation
------- ---------------------------------------------------
14 SORT UNIQUE
19 UNION-ALL
11 HASH JOIN OUTER
11 TABLE ACCESS FULL E2
8 TABLE ACCESS FULL E1
8 HASH JOIN OUTER
8 TABLE ACCESS FULL E1
11 TABLE ACCESS FULL E2

Oracle has used two outer joins in opposite directions to perform this query, which is in fact what we asked it to do in the way we structured our query.

A final point to note is that full outer joins are "expensive" operations and can have a fine tipping-point at which performance degrades. This point can be at relatively low volumes for some systems (especially when outer joining more than two datasets) so is something for developers to be aware of while testing.
cross join

A cross join is what we call a Cartesian Product in Oracle. A Cartesian Product "joins" each row in the first dataset to every row in the second dataset. To understand how many records are generated by a Cartesian Product or cross join, simply multiply the number of rows in the two datasets together. In the following example, we will cross join two small datasets of 2 rows each, thereby outputting 4 rows. This will keep the screen output to a minimum for demonstration purposes.

SQL> WITH two_rows AS (
2 SELECT 1 AS r FROM dual
3 UNION ALL
4 SELECT 2 AS r FROM dual
5 )
6 SELECT a.r, b.r
7 FROM two_rows a
8 CROSS JOIN
9 two_rows b;

R R
---------- ----------
1 1
2 1
1 2
2 2

4 rows selected.

We can see that there is no join predicate with a cross join, though we can add filter predicates (WHERE clause) if required. Note how each row in dataset A has joined to both rows in dataset B. The Oracle equivalent of this syntax is as follows.

SQL> WITH two_rows AS (
2 SELECT 1 AS r FROM dual
3 UNION ALL
4 SELECT 2 AS r FROM dual
5 )
6 SELECT a.r, b.r
7 FROM two_rows a
8 , two_rows b;

R R
---------- ----------
1 1
2 1
1 2
2 2

4 rows selected.

mutable join

Mutable joins are simply multi-table joins. These can include any combination of ANSI joins as required.
mutable inner join

We can see an example of this by joining DEPT to EMP and to EMP again as follows.

SQL> SELECT d.dname, d.loc, e.ename, e.job, e2.sal
2 FROM dept d
3 INNER JOIN
4 emp e
5 USING (deptno)
6 INNER JOIN
7 emp e2
8 USING (empno);

DNAME LOC ENAME JOB SAL
-------------- ------------- ---------- --------- ----------
RESEARCH DALLAS SMITH CLERK 800
SALES CHICAGO ALLEN SALESMAN 1600
SALES CHICAGO WARD SALESMAN 1250
RESEARCH DALLAS JONES MANAGER 2975
SALES CHICAGO MARTIN SALESMAN 1250
SALES CHICAGO BLAKE MANAGER 2850
ACCOUNTING NEW YORK CLARK MANAGER 2450
RESEARCH DALLAS SCOTT ANALYST 3000
ACCOUNTING NEW YORK KING PRESIDENT 5000
SALES CHICAGO TURNER SALESMAN 1500
RESEARCH DALLAS ADAMS CLERK 1100
SALES CHICAGO JAMES CLERK 950
RESEARCH DALLAS FORD ANALYST 3000
ACCOUNTING NEW YORK MILLER CLERK 1300

14 rows selected.

In ANSI syntax, joins are parsed from left to right and join conditions can only reference preceding tables in the FROM clause. We can see this in the following example where we attempt to include a reference to the second EMP join in the first DEPT-EMP join predicates.

SQL> SELECT d.dname, d.loc, e.ename, e.job, e2.sal
2 FROM dept d
3 INNER JOIN
4 emp e
5 ON (d.deptno = e.deptno
6 AND e.empno = e2.empno) --<-- E2 has not been joined to query yet
7 INNER JOIN
8 emp e2
9 USING (empno);

AND e.empno = e2.empno) --<-- E2 has not been joined to query yet
*
ERROR at line 6:
ORA-00904: "E2"."EMPNO": invalid identifier

Note that this restriction is syntactic only. It does not mean that the CBO has to physically join the datasets in the order in which they appear. To do so would render ANSI-compliant joins too restrictive. As with Oracle join syntax, the CBO is free to order the tables or datasets in the way it considers the "cheapest".

The Oracle syntax for mutable inner joins is as follows.

SELECT d1.dname, d1.loc, e1.ename, e1.job, e2.sal
FROM dept d1
, emp e1
, emp e2
WHERE d1.deptno = e1.deptno
AND e1.empno = e2.empno;

mutable outer join

One of the benefits of ANSI syntax is that it makes outer joins easier to write and understand. It also helps us to workaround the minor restrictions of Oracle syntax (we saw an example earlier when attempting to outer join using an expression). Another restriction with Oracle syntax is that a table can only be outer joined once in any one query block.

We can setup an example of this. We will create three small datasets, D1, D2 and D3, as three variations on the contents of DEPT as follows.

SQL> CREATE TABLE d1 AS SELECT * FROM dept;

Table created.

SQL> CREATE TABLE d2 AS SELECT * FROM dept WHERE ROWNUM <= 3;

Table created.

SQL> CREATE TABLE d3 AS SELECT * FROM dept WHERE ROWNUM <= 2;

Table created.

If we are required to outer join both D1 and D2 to the D3 dataset, Oracle will not allow us to use a single query block for this, as we can see below.

SQL> SELECT d1.dname, d1.deptno, d2.deptno, d3.deptno
2 FROM d1
3 , d2
4 , d3
5 WHERE d1.deptno = d3.deptno(+)
6 AND d2.deptno = d3.deptno(+);

WHERE d1.deptno = d3.deptno(+)
*
ERROR at line 5:
ORA-01417: a table may be outer joined to at most one other table

The Oracle workaround to this is to use an in-line view for one of the outer joins, as follows.

SQL> SELECT d1.dname, d1.deptno, d2_d3.d2_deptno, d2_d3.d3_deptno
2 FROM d1
3 , (
4 SELECT d2.deptno AS d2_deptno
5 , d3.deptno AS d3_deptno
6 FROM d2
7 , d3
8 WHERE d2.deptno = d3.deptno (+)
9 ) d2_d3
10 WHERE d1.deptno = d2_d3.d3_deptno(+);

DNAME DEPTNO D2_DEPTNO D3_DEPTNO
-------------- ---------- ---------- ----------
ACCOUNTING 10 10 10
RESEARCH 20 20 20
SALES 30
OPERATIONS 40

4 rows selected.

ANSI syntax makes this easier as we can combine left and right outer joins in a single query as follows.

SQL> SELECT d1.dname, d1.deptno, d2.deptno, d3.deptno
2 FROM d2
3 LEFT OUTER JOIN
4 d3
5 ON (d2.deptno = d3.deptno)
6 RIGHT OUTER JOIN
7 d1
8 ON (d1.deptno = d3.deptno);

DNAME DEPTNO DEPTNO DEPTNO
-------------- ---------- ---------- ----------
ACCOUNTING 10 10 10
RESEARCH 20 20 20
SALES 30
OPERATIONS 40

4 rows selected.

mixed mutable joins

Finally, mutable joins can combine any of the ANSI joins. For completeness, we will run a query that combines everything we've seen in this article. The results will be completely useless of course!

SQL> SELECT COUNT(*)
2 FROM dept d1
3 --
4 INNER JOIN
5 dept d2
6 USING (deptno)
7 --
8 LEFT OUTER JOIN
9 emp e1
10 USING (deptno)
11 --
12 RIGHT OUTER JOIN
13 emp e2
14 ON (e1.empno = e2.empno)
15 --
16 NATURAL JOIN
17 emp e3
18 --
19 CROSS JOIN
20 dept d4;

COUNT(*)
----------
784

1 row selected.

ansi joins and the cbo

As we saw in the mutable joins section, the ANSI FROM syntax is ordered from left-to-right and datasets/tables are added and joined to the query one-by-one. While this implies an explicit ordering in the application of join predicates to the SQL, this does not mean that this is how the optimiser (CBO) will deal with the joins. As with queries that use standard Oracle syntax, the CBO is free to shuffle the join orders according to its own costs and arithmetic.

In most cases, the execution plans for equivalent ANSI and Oracle syntax queries will be the same as they are based on the same underlying statistics. The obvious exception to this is the FULL OUTER JOIN that we saw earlier, though we could code an Oracle equivalent based on what we now know about Oracle's treatment of the new ANSI method.

To demonstrate that the order of the ANSI joins does not dictate the actual join order chosen by the CBO, we will run a simple example that joins the D1, D2 and D3 sample tables created earlier in this article. Using Autotrace, we will first see how Oracle might choose to optimise such a query written with ANSI joins as follows.

SQL> set autotrace traceonly explain

SQL> SELECT *
2 FROM d1
3 --
4 INNER JOIN
5 d2
6 USING (deptno)
7 --
8 LEFT OUTER JOIN
9 d3
10 USING (deptno);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=3 Bytes=180)
1 0 HASH JOIN (Cost=8 Card=3 Bytes=180)
2 1 HASH JOIN (OUTER) (Cost=5 Card=3 Bytes=120)
3 2 TABLE ACCESS (FULL) OF 'D2' (Cost=2 Card=3 Bytes=57)
4 2 TABLE ACCESS (FULL) OF 'D3' (Cost=2 Card=2 Bytes=42)
5 1 TABLE ACCESS (FULL) OF 'D1' (Cost=2 Card=4 Bytes=80)

We can see that the Explain Plan join order does not match the order of the tables in our SQL. The CBO has started with the LEFT OUTER JOIN between D2 and D3, hashing D3 first. If we write this statement using Oracle syntax, we can verify that the optimiser treats it the same as the ANSI equivalent.

SQL> SELECT *
2 FROM d1
3 , d2
4 , d3
5 WHERE d1.deptno = d2.deptno
6 AND d2.deptno = d3.deptno (+);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=3 Bytes=180)
1 0 HASH JOIN (Cost=8 Card=3 Bytes=180)
2 1 HASH JOIN (OUTER) (Cost=5 Card=3 Bytes=120)
3 2 TABLE ACCESS (FULL) OF 'D2' (Cost=2 Card=3 Bytes=57)
4 2 TABLE ACCESS (FULL) OF 'D3' (Cost=2 Card=2 Bytes=42)
5 1 TABLE ACCESS (FULL) OF 'D1' (Cost=2 Card=4 Bytes=80)

Finally, we can add a hint to change the order in which we wish the CBO to join the tables. In the following example, we will try to force the CBO to begin with the D1 table by using the LEADING hint.

SQL> SELECT /*+ LEADING(d1) */
2 *
3 FROM d1
4 --
5 INNER JOIN
6 d2
7 USING (deptno)
8 --
9 LEFT OUTER JOIN
10 d3
11 USING (deptno);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=3 Bytes=180)
1 0 HASH JOIN (OUTER) (Cost=8 Card=3 Bytes=180)
2 1 HASH JOIN (Cost=5 Card=3 Bytes=117)
3 2 TABLE ACCESS (FULL) OF 'D1' (Cost=2 Card=4 Bytes=80)
4 2 TABLE ACCESS (FULL) OF 'D2' (Cost=2 Card=3 Bytes=57)
5 1 TABLE ACCESS (FULL) OF 'D3' (Cost=2 Card=2 Bytes=42)

I wanted to create a trigger on a table. Whenever an update statement gets executed on the table, the trigger will be fired and a column “UPDATED_DT” of the table will be updated with the current date.

i have used the following but it has compilation errors cause with after update i cannot use the new keyword as it is technically incorrect. If works fine when i use BEFORE UPDATE. Could you guide me with this?

CREATE OR REPLACE TRIGGER TRG_AU
AFTER UPDATE ON X_TABLE
FOR EACH ROW

BEGIN

:NEW.UPDATED_DT := SYSDATE; --error with the new keyboard any other approach

END TRG_AU

Can someone advice a solution?

What's the reason you cannot use BEFORE trigger? Before trigger is the only version where you can modify the values to be written to the row.

The need to optimize rises from a bad design.My articles[^]

I have been asked to do so.

Quite odd. If you're asked to modify the values of an updating row inside a trigger, this cannot be done in after trigger. You could possibly update the same row again in the after trigger BUT that would be a recursion and could run until you run out of memory...

If the goal is to have the latest update timestamp on every row (implemented using a trigger), I would re-check why before trigger isn't an option.

The need to optimize rises from a bad design.My articles[^]

The only way you can change the values of a row being updated is in a BEFORE UPDATE trigger. AFTER UPDATE trigger is triggered "after" the row is updated, and therefore, understandably, the :NEW table can't be modified.

"Don't confuse experts with facts" - Eric_V

modified on Friday, August 19, 2011 10:34 AM