Action Triggers are pl/sql procedure executed when select a 
button in the  Reports previewer. It is mainly used to call 
another report.

Srw.Run_report(Command_line char)   It executes  the  
specified R25RUN.

This is a virtual table do not have physical existence 
only at run time it retrive valves from one or more than 
one tables.

opertion: insert , update , delete

case 1:- If view created on only one table than DML
       can  perform on that TABLE

case 2:-  If view Made on more than one then DML cant 
possiple

It is an object in the database.
Which doesn't store any data.
Which is stored as a select statement in the datadictionary 
tables.

It is an object in the database.It doesn't store any data.
It is stored as a select statement in the datadictionary 
tables.This is a virtual table which do not have physical  
existance only at runtime.It retrive values from one 
table,if it is created only one table than DML can perform 
on that table.
Operations are used in view:-Insert,Delete,Update.

View in sql called the virtual table.Physically it has no 
existance, so that it don't occupy the space.It retrives 
the data from original table at runtime.
Views are stored in ORACLE system catalog.
Basically, in realtime environment views are used to 
protect the main table for operation.Besides for security 
purpose view must be used.i.e. one user can hide the main 
tale from other user for creating view.

On basic of creation of view, it is divied into two pars....

1. Simple view: In that case columns for same table(single 
table) are fetched for creating a view.

Syntax: SQL>create view <view_name> as select <column 
name1.column name2....> from table where <condition>;

2. Complex view: Columns are fetched from different tables 
on basis of operation would perform.Generally for join 
operation it is used.

Syntax: SQL>create view <view_name> as select <col1..coln 
from table 1>,<col 1..col n for table 2>....< .. for table 
n> from table 1,..,table n where <condition>;

On basis of operations on view ... it is divided inti two 
parts:

1. Read only view, 2. Updatable view
** incase of updatable view insertion,deletion and updation 
can be done...... aggregation,rowid,rownum and any group 
function cann't be performed.
** incase of read only view, only view canbe read, no DML 
operations cann't be perfomed.

to drop a view ..... we use:

SQL>drop view <view_name>;

View is a virtual table or logical table or it can be 
represented as mirror if table.View allows performing 
transaction on table virtually.Table may contain "n" number 
of views.View can be based on single table or more than one 
table with help of join.

This is a virtual table do not have physical existence 
only at run time it retrive valves from one or more than 
one tables.

opertion: insert , update , delete

case 1:- If view created on only one table than DML
       can  perform on that TABLE

case 2:-  If view Made on more than one then DML is 
possible only when all not null values will be included in 
the query. means we can insert values in multiple tables 
with the help of view if we take all not null field at the 
time view creation.

UNIQUE and DISTINCT

UNIQUE(Constraint):- Can be only once for a   column in  a  
table


DISTINCT(Clause):- Useed in select query to retrive distinct
(non repeated) data from table(s)

Yeah Manoj, Thatz ok, UNIQUE as a constraint. But I like to
know about the diff while its usage in select statement. For
me it seems very similar to DISTINCT, can anyone give me if
there is any difference, please.

see cautiously the major difference  b/w  unique and 
distinct

UNIQUE  :Allways take part on DATA INSERTION (In brief)
DINTINCT:Allways concern on data retrival     (In brief)

Now we will take following case
when 

case 1: UNIQUE KEY is defined (may b 4 one or more column)

       It means this constraint will not allow us to
       INSERT duplicate record/column_value(s)(i.e. exactly 
same record/column_value(s) again for specified key column
(s)only) 


NOW if u use DISTINCT clause in query 
(select DISTINCT.from.)the will b same as simple query 
(select....from...)

Bcoz  data are already not repeated (bcoz of constraint)


case 2:if UNIQUE not defined

data may b repeated and DISTINCT will  now useful


Have eye on Major difference

(and we never use UNIQUE CONS.. in select.....)

yeah Manoj, this was analysed with your previous answer
itself  but please try these statements and help me with
it's significance on the statements.
select unique(<clomun_name>) from <table_name>;
select distinct(<clomun_name>) from <table_name>;

Buddy i dont found any difference in using distinct and 
unique in the select statement, as both are performing same 
operation in the select statement, almost every diff you 
have discussed i want to add one that is u cant use 
distinct in the create whereas unique u can.

the diff between unique and distinct is 
unique is applied before insertion and retrival.it consists
of non duplicate values.if unique constraint is given it
does not take duplicate values.distinct is used in retrival
it gives the suppressed row(ex  if two rows are same it will
show single row and non duplicate row) therefore distinct is
the combination of  suppressed duplicate and non duplicate rows.

DISTINCT AND UNIQUE are same operation.
But, You cannot specify DISTINCT if the select_list 
contains LOB columns.

Hi Howard Cheon,
               you are currect... but we cant able to use 
both DISTINCT and UNIQUE, if the select_list contains LOB, 
LONG columns. It may not a difference.

Could anyone plz give some another major difference..

Thanks in Advance

Vijay Reddy.

to this u need to know all other Isolation levelels

Transaction Isolation Levels
Closely tied in with the modes and methods of locking is 
the transaction isolation level. To understand the new 
locking behavior, you need to understand the four 
transaction isolation levels in SQL Server 7.0: Uncommitted 
Read (also called "dirty read"), Committed Read, Repeatable 
Read, and Serializable.
IsolationLevels 
The isolation level that your transaction runs in 
determines how sensitive your application is to changes 
other users' transactions make, and consequently, how long 
your transaction must hold locks to protect against these 
changes. The ANSI SQL standard defines four levels of 
transaction isolation. Although previous versions of SQL 
Server let you specify all four distinct levels of 
transaction isolation, there were only three different 
behaviors because SQL Server internally treated two of the 
syntactic specifications (i.e., Repeatable Read and 
Serializable) as synonymous.
You can change the level of isolation that a particular 
connection is operating in by using the SET TRANSACTION 
ISOLATION LEVEL command. Keep in mind that the SET command 
applies only to your current connection, and every time you 
make a new connection (or open a new window in the Query 
Analyzer), you'll be back in the default isolation level. 
I'll use each of the four isolation levels in the examples 
to follow.
To see how each level behaves, you can use the script in 
Listing 1, page 20, to create a table with a few rows in 
it. I'll refer back to this table in examples for each of 
the four isolation levels.
UncommittedRead
Uncommitted Read, or dirty read, lets a transaction read 
any data currently on a data page, whether or not that data 
has been committed. For example, although another user 
might have a transaction in progress that has updated data, 
and that transaction is holding exclusive locks on the 
data, your transaction can read the data anyway, and 
possibly take further actions based on the values you read. 
The other user might then decide to roll back his or her 
transaction, so logically, those changes never occurred. 
Although this scenario isn't desirable, with Uncommitted 
Read you won't get stuck waiting for a lock, nor will your 
reads acquire share locks that might affect others.
Let's see how Uncommitted Read behaves. Use the SQL Server 
7.0 Query Analyzer, and start two separate connections. Use 
the pubs database in each one. In the first connection, 
begin a transaction, but don't commit it:
BEGIN TRAN
UPDATE ISOLATION_TEST
   SET col2 = 'New Value'
<click the green arrow to execute >
Now, use the second connection, and change your isolation 
level before trying to access the same table.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM ISOLATION_TEST
<click the green arrow to execute >
All the values in col1 are 0, even though the transaction 
in the first connection has not committed yet. In fact, the 
transaction might never commit. If you took some action 
based on the fact that all the values are 0, you could 
regret it if the changes turned out not to be permanent. 
Back in the first connection, roll back the transaction:
ROLLBACK TRAN
<click the green arrow to execute >
Now rerun the SELECT statement in the second connection to 
see that all the values are back to what they were before. 
If you're following along with these examples, make sure 
you close your connections after each one, so that all 
outstanding locks are released.
CommittedRead 
Committed Read is SQL Server's default isolation level. It 
ensures that an operation will never read data another 
application has changed but not yet committed. Because you 
can never read uncommitted data, if a transaction running 
with Committed Read isolation revisits data, that data 
might have changed, or new rows might appear that meet the 
criteria of the original query. Rows that appear in this 
way are called phantoms.
So Committed Read behavior has two aspects. To see the 
first aspect, you can run the above example, without 
setting the second connection to use isolation level Read 
Uncommitted. The second connect would then block on the 
SELECT statement; it can't read the changes the first 
connection has made but not yet committed (or rolled back). 
To see the second Committed Read behavior, close all the 
connections in the Query Analyzer from the previous 
example, and open two new connections using pubs again. In 
the first connection, run the following batch:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT AVG(col1) from ISOLATION_TEST
 <click the green arrow to execute >
In the second connection, update the table:
UPDATE ISOLATION_TEST 
   SET col1 = 500 WHERE col1 = 50
<click the green arrow to execute >
Notice that the update is successful, even though the first 
connection is still inside a transaction.
Go back to the first connection and run the same SELECT 
statement:
SELECT AVG(col1) from ISOLATION_TEST
 <click the green arrow to execute >
The average value is now different. The default isolation 
level does not prevent another connection from changing 
data you have read. Because you are not guaranteed to see 
the same data if you rerun the SELECT within the 
transaction, the read operations are not guaranteed to be 
repeatable.
RepeatableRead
If you want the read operations to be repeatable, choose 
the third isolation level. The Repeatable Read isolation 
level adds to the properties of Committed Read by ensuring 
that if a transaction revisits data or if a query is 
reissued, the data will not have changed. In other words, 
issuing the same query twice within a transaction won't 
pick up any changes to data values that another user's 
transaction has made. No other user can modify the data 
that your transaction visits as long as you have not yet 
committed or rolled back your transaction.
To see Repeatable Read behavior, close all the connections, 
and open two new ones in pubs. Issue the same two queries 
as above, but this time, have the first connection SET 
ISOLATION LEVEL REPEATABLE READ.
The second connection will have to use a slightly different 
update statement, because the value of 50 for col1 no 
longer exists:
UPDATE ISOLATION_TEST 
   SET col1 = 5000 WHERE col1 = 500
<click the green arrow to execute >
This update will block when it tries to update the 
ISOLATION_TEST table. And the first connection will get the 
same result when it reissues its original SELECT. 
Preventing nonrepeatable reads is a desirable safeguard, 
but it comes at a price. The cost of this extra safeguard 
is that all the shared locks in a transaction must be held 
until the completion (COMMIT or ROLLBACK) of the 
transaction.
However, Repeatable Read isolation doesn't prevent all 
possible changes. It protects only the data that you have 
read. The following example shows you what this protection 
means. Close all connections, and open two new ones 
connecting to pubs. In the first connection, start a 
transaction in Repeatable Read isolation level and look for 
all rows that meet a certain condition.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM ISOLATION_TEST
   WHERE col1 BETWEEN 20 AND 40
 <click the green arrow to execute >
In the second connection, insert a new row:
INSERT INTO ISOLATION_TEST
   VALUES (25, 'New Row')
 <click the green arrow to execute >
Go back to the first connection, and reexecute the SELECT:
SELECT * FROM ISOLATION_TEST
   WHERE col1 BETWEEN 20 AND 40
<click the green arrow to execute >
The second time you execute the same statement, the new row 
appears. Because the row doesn't even exist the first time 
you run the SELECT statement, it isn't locked. This new row 
that appears is called a phantom. You can prevent phantoms 
with the fourth isolation level.
Serializable 
The Serializable isolation level ensures that if a query is 
reissued, no data will have changed and no new rows will 
appear in the interim. In other words, you won't see 
phantoms if the same query is issued twice within a 
transaction. Rerun the example from the Repeatable Reads 
section, inserting a row with a col1 value of 35. But this 
time, set your isolation level to SERIALIZABLE. The second 
connection will block when you try to do the INSERT, and 
the first connection will read exactly the same rows each 
time.
You pay a price to prevent phantoms. In addition to locking 
all the data you have read, enforcing the Serializable 
isolation level requires that SQL Server also lock data 
that doesn't exist! The Serializable level gets its name 
from the fact that running multiple serializable 
transactions at the same time is the equivalent of running 
them one at a time&#8212;that is, serially&#8212;regardless of sequence.
Controlling the Isolation Level SQL Server's default 
isolation level is Committed Read, but as you've seen, you 
can override this setting within your application. The most 
straightforward way is by using the SET command:
SET TRANSACTION ISOLATION LEVEL
   [READ UNCOMMITTED | READ COMMITTED | REPEATABLE
   READ | SERIALIZABLE]
Previous versions of SQL Server treated Repeatable Read and 
Serializable as synonymous. I thought the difference was 
that Repeatable Reads prevented UPDATE operations, and 
Serializable prevented INSERTs and DELETEs. But the 
difference is in what data is locked. Repeatable Read locks 
only the data that has been read. With Serializable, SQL 
Server has to guarantee complete serializability, so it 
locks ranges of data.
Previous versions of SQL Server gave you no way to 
distinguish between these two levels, because the smallest 
unit of locking was an entire page. Locking the page 
containing the col1 values between 20, 30, and 40 prevented 
changes to those specific rows but also prevented inserts 
anywhere on the same page. With row-level locking, SQL 
Server 7.0 can lock only the accessed rows. SQL Server 7.0 
also introduced a new kind of lock to lock the ranges of 
data between two rows, which I'll discuss in an upcoming 
article.

in 2005--database mirroring,CLR Integarion,SIS,Data 
paratition, xml datatype,Dynamic management views,Mirror 
backup

basically we use joins to query a sql with 1 or more tables.
1) self join.. join within the same table using aliases
2) equi join.. join between the 2 tables with commom columns
3) outer join.. and it is divided into 2 catagories left 
outer join and right outer join.

View :- its a logical representation of one or more tables.
View doesn't have the data of its own. But still you can 
perform DML on a view ..the changes will be done in the 
underlying base tables..

VIEWS
View is a virtual table
If we want to access specific column from specific table we
can go for views
JOINS
Sometimes we have to select data from two or more tables to
make our result complete we have to perform join

1.For user defined function one must have to specify it's 
return type while in stored procedure it is not mandatory.

2. u can use function in expression but not stored 
procedures

1. Stored Procedure to return the output parameter.
    but function only return single values no output 
parameter.

2. Select Clause we cann't use stored procdure. but 
fucntion we can use.

3. XMl Clause contain we can use stored procdure. but 
function we cann't use.

4. Whenever to execute function at present any error in the 
function automatically to exit from the function. but 
stored procudure to ignore the error then go to next line.

By
Kumar.T

having cluase can only be used with group by clause.

if there are 500 records i will give rownumber<250 then it 
fetch 249 records.

NVL2( string1, value_if_NOT_null, value_if_null )
if string1 is not null then it returns 'value_if_NOT_null' 
and if string1 is null then it returns 'value_if_null'.


if there are four tables without using cartesian we have n-
1 join where n is number of tables. i.e 4-1=3 join required 
four tables without using cartesian.

primary key has a unique & it is only one in a 
databasewhile fkey is not unique. it is based on primary key

HI

Primary key = (Not Null + Unique).

Foreign key = (For Referential table access).


Bye
sun_ramprasad@yahoo.com

To establish a relationship between 2 tables.
pk=(unique+not null)
fk=unique

primary key has unique value, it dostnt contain null values
and duplicate values.


foregin key is primary key of another table

it will return the integer value of a variable

for example

intval(42);                      // 42
intval(4.2);                     // 4
intval('42');                    // 42
intval('+42');                   // 42

delegates used to invoke one function. Multicasst delegate 
is used to invoke mmore than one function 
Say for example 
This is our delegate
delegate void  myDelegate (string s);

Weh ve two functions like 
publc void sayHello(String Name)
{
//implementation goes here
}
public void sayHai(String Name)
{
//implementation goes here
}

If we want to invoke both of these functions 
use like


myDelegate md= new myDelegate(sayHello);
md+=new myDelegate(sayHai);

yes,you can only create one clustered index on a table.

Clustered index based on Primary Key. So if we create a PK,
automatically Clustered Index will be created. So only one
CI available for a table.

Clustered Index creation does not depend upon primary key , 
it can be created on any column, but the write answer is 
whenever we create clustered index on a table it physically
sorts the column values, so another clustered index is not 
possible.

we can create ony ONE clustered index per table .. for Non-
clusrtered index we can create 249 ...

A clustered index sorts and stores the data rows in the
table based on the index key values. Therefore only one
clustered index can be created on each table because the
data rows themselves can only be sorted in one order.

select text from sysindexes

SELECT   syscomments.text AS text
FROM         sysobjects INNER JOIN
                      syscomments ON sysobjects.id =
syscomments.id where  sysobjects.name=(stored procedure name)