1: wt is the diff b/w greast and max ,least and min? Wt is the diff b/w case and decod?
Answer
Greatest/least: we can pass any no. Of exp
Max/min: we can pass one exp that may a column
Case: is a statement function, cannot process null
Decode: is a function, can process null, we can use in
Update statement.
2: how to print * ** *** **** ***** by using sql query?
(may be using script)
Answr
Select lpad('*',rownum,'*') star from person where rownum<6
*
**
***
****
*****
Write a query to filter improper date format following table?
Date
20-apr
22-may-2010
26-jun-2010
Feb-2009
I want the output
Date
22-may-2010
26-jun-2010
Select to date('column_name','dd-mon-yyyy') from table_name;
Write a query to remove null following table are
Id name
101 dinesh
Null jyothi
Null bharathi
102 suresh
Null shilpha
103 prakesh
Null suma
I want the output format like
Id name
101 dinesh
102 suresh
103 prakesh
Select * from table where id is not null;
Wirte a query to remove null? Following table are
Col1 col2 col3
Dinesh null null
Null suresh null
Null null prakesh
I want the output like
Col1 col2 col3
Dinesh suresh prkaesh
Select distinct((select col1 from coll where
Col1<>'null')),(select col2 from coll where col2<>'null'),
(select col3 from coll where col3<>'null')
From coll
Select max(col1) col1,max(col2) col2,max(col3) col3
From table;
Write a query filter the null value data following source?
Name age
John 30
Smith null
Null 24
Sharp 35
I want output
Name age
John 30
Sharp 35
Select * from test_1 where name is not null and age is not
Null;
When do we create bitmap indexes
Answer
If there is very less cardinality (i.e. 1% of total column
Values) or distinct values in column then we have to create
Bitmap index for that column like gender column will always
Have 2 values male or female.
Syntex:-
Create bitmap index index_name on table_name(column_name);
A.oltp
B.dss
C.where clause has functions
D.tables that have only one record
Can we use out parameter in function?
Answer
1.yes we can use out parameters in a function.but we have to assign the out parameter value to the return datatype.
2.but when we use out parameters in function we can't call the function from a select statement
Write a query to genarate target column.please answer me.
Advance thanks.
Src tgt
Q10 quarter to 2010
Q90 quarter to 1990
Q80 quarter to 1980
Q74 quarter to 1974
Select
'Quarter to '||to_char((to_date('01/01/'||substr(src,2),
'Dd/mm/rrrr')),'rrrr') as "target"
From table
How to call the function and procedure in trigger?
Answer
With the help of call statement..without semicolon
Create or replace trigger tri_call
Begin
Call p1()
End tri_call;
What is different between union and minus?
Answer
Unoin:- this operator returns from all the queries(combined
Through union) but not duplicate record will be display.
Ex- a={1,2,3,4}
B={2,3,4,5}
Aub={1,2,3,4,5}............
Minus:- this operator displays records which belongs to only
The first query.
Ex:- a={1,2,3,4}
B= {2,3,5}
A-b={1,4}...................
Can we interchange parameters in procedure while calling
Answer
If you name the parameter then you can interchange
Otherwise you cannot. Like in the following example the
First case is positional and you cannot interchange. In the
Second one its named and you can interchange.
Regular oradatabase.standproc(102,'ram'); -- positional
Oradatabase.standproc(empno=>102,ename=>'ram'); --named
Oradatabase.standproc(ename=>'ram',empno=>102);
Re: what is 'force view'?
Answer
The view can be created without base table then the view is
Called force view. The view must be crated with force
Sql> create or replace view test_view
2 as
3 select * from non_existent_table;
Select * from non_existent_table
*
Error at line 3:
Ora-00942: table or view does not exist
/* hence, the view does not exists */
Sql> select * from test_view;
Select * from test_view
*
Error at line 1:
Ora-00942: table or view does not exist
/* specifying force creates the view object (albeit with
Errors) */
Sql> create or replace force view test_view
2 as
3 select * from non_existent_table;
Warning: view created with compilation errors.
/* trying to select from the view implies it's been created
*/
Sql> select * from test_view;
Select * from test_view
*
Error at line 1:
Ora-04063: view "orauser.test_view" has errors
/* creating the missing object then allows us to select
From it */
Sql> create table non_existent_table
2 (
3 a varchar2(10)
4 );
Table created.
Sql> select * from test_view;
No rows selected
What are global temporary tables
Answer
Global temporary tables are session dependant tables which Could be used as temporary storage for calculations, sorting
Etc. What i mean by session dependant is, the data being Stored in the global temporary table is not written into the Database or stored anywhere. Once the session ends (in which The global temporary table is used), the data also vanishes.
However the structure would still be available even after the session is logged out. Also, the structure is available to other sessions even when one session is using it, but not the data. I.e multiple sessions could use the same global temporary table without interfering the data. Each session could insert/update/delete their own data into the same Global temporary table as if the table is available to only that session. Any data inserted in one session is not available to another.
Now, why do we need global temporary tables? Well, imagine a requirement where you need to fetch some data from the database, do some kind of calculations, aggregations and provide the result set (many records) to a front end. Again, in the front end, you need to fetch the result set may times, for some purpose. Then you could make use of the Global temporary table. Until the user gets disconnected from that database session, the data is available for him in the memory.
Global temporary tables belongs to that session only
Create global temporary table test_gbl
( l_number_no number,
L_char_vc varchar2(100)
) [on commit delete rows]
On commit delete rows:- it's a default one
If any commit will issued that total data of a table will
Losses. But table is exit
To overcome this we have option
On commit preserve rows:-
Means, if commit will issue the data of a table willn't loss
Up to end of the session. Is session ends the data will
Losses
What are nested tables? How will u delete 5 rows from nested
Tables
Answer
Create or replace type addresstype as object (
Street varchar2(15),
City varchar2(15),
State char(2),
Zip varchar2(5)
);
Create or replace type nested_table_addresstype as table of
Addresstype;
Create table employee (
Id integer primary key,
First_name varchar2(10),
Last_name varchar2(10),
Addresses nested_table_addresstype
)
Nested table
Addresses
Store as
Nested_addresses;
Insert into employee values (
1, 'steve', 'brown',
Nested_table_addresstype(
Addresstype('2 ave', 'city', 'ma', '12345'),
Addresstype('4 ave', 'city', 'ca', '54321')
)
);
Delete from table (
Select addresses from employee where id = 1
) addr
Where
Value(addr) = addresstype(
'4 ave', 'city', 'ca', '54321'
);
Re: select top 3 sal from each dept?
Answer
Select* from(select ename,deptno,sal,row_number()
Over(partiton by deptno order by sal)num from emp)
Where num<=3
Order by deptno;
Re: what is difference between having and where clause?
Answer
Having clause is used for filtering grouped data and where
Clause is used for filtering rows.
Where clause filter the data before gruop by clause and
Having clause clause filter the data after group by clause
Can you have multiple sps with the same name on a database?
Answer
Both above answers are correct,but situvationd diff
1) we can't create same db obj with same name,
2) we want that go for packages ,with overloading concept
I want to create a materialized view from a left join of 2
Tables. However the following gives me an error:
Select field1
From table_1 a
Left join table_2 b
On a.field1=b.field2
Ora-12054: cannot set the on commit refresh attribute for
The materialized view
However the following works:
Select field1
From table_1 a, table_2 b
Where a.field1=b.field2
Does anyone have any ideas why this is happening.
Thx for the help
Oracle views join materialized
Share|improve this question
There are two conditions that are not satisfied to make that
Materialized view refresh fast. First one is that you did
Not specify the rowid columns of every table involved. And
The second one is an undocumented restriction: ansi-joins
Are not supported.
Here is an example with dept being table_1, alias a and emp
Being table_2, alias b:
Sql> create materialized view log on emp with rowid
2 /
Materialized view log created.
Sql> create materialized view log on dept with rowid
2 /
Materialized view log created.
Sql> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.deptno
5 from dept a
6 left join emp b on (a.deptno = b.deptno)
7 /
From dept a
*
Error at line 5:
Ora-12054: cannot set the on commit refresh attribute for
The materialized view
That mimics your situation. First add the rowid's:
Sql> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.rowid dept_rowid
5 , b.rowid emp_rowid
6 , a.deptno
7 from dept a
8 left join emp b on (a.deptno = b.deptno)
9 /
From dept a
*
Error at line 7:
Ora-12054: cannot set the on commit refresh attribute for
The materialized view
Still it cannot fast refresh, because of the ansi joins.
Converting to old-style outer join syntax:
Sql> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.rowid dept_rowid
5 , b.rowid emp_rowid
6 , a.deptno
7 from dept a
8 , emp b
9 where a.deptno = b.deptno (+)
10 /
Materialized view created.
And to prove that it works:
Sql> select * from empdept_mv
2 /
Dept_rowid emp_rowid deptno
------------------ ------------------ ----------
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aaa 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aab 30
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aac 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aad 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aae 30
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aaf 30
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aag 10
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aah 20
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aai 10
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aaj 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aak 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aal 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aam 20
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aan 10
Aaarhmaaeaaaai/aad 40
15 rows selected.
Sql> insert into dept values (50,'it','utrecht')
2 /
1 row created.
Sql> commit
2 /
Commit complete.
Sql> select * from empdept_mv
2 /
Dept_rowid emp_rowid deptno
------------------ ------------------ ----------
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aaa 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aab 30
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aac 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aad 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aae 30
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aaf 30
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aag 10
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aah 20
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aai 10
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aaj 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aak 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aal 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aam 20
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aan 10
Aaarhmaaeaaaai/aad 40
Aaarhmaaeaaaai7aaa 50
16 rows selected.
Answer
Greatest/least: we can pass any no. Of exp
Max/min: we can pass one exp that may a column
Case: is a statement function, cannot process null
Decode: is a function, can process null, we can use in
Update statement.
2: how to print * ** *** **** ***** by using sql query?
(may be using script)
Answr
Select lpad('*',rownum,'*') star from person where rownum<6
*
**
***
****
*****
Write a query to filter improper date format following table?
Date
20-apr
22-may-2010
26-jun-2010
Feb-2009
I want the output
Date
22-may-2010
26-jun-2010
Select to date('column_name','dd-mon-yyyy') from table_name;
Write a query to remove null following table are
Id name
101 dinesh
Null jyothi
Null bharathi
102 suresh
Null shilpha
103 prakesh
Null suma
I want the output format like
Id name
101 dinesh
102 suresh
103 prakesh
Select * from table where id is not null;
Wirte a query to remove null? Following table are
Col1 col2 col3
Dinesh null null
Null suresh null
Null null prakesh
I want the output like
Col1 col2 col3
Dinesh suresh prkaesh
Select distinct((select col1 from coll where
Col1<>'null')),(select col2 from coll where col2<>'null'),
(select col3 from coll where col3<>'null')
From coll
Select max(col1) col1,max(col2) col2,max(col3) col3
From table;
Write a query filter the null value data following source?
Name age
John 30
Smith null
Null 24
Sharp 35
I want output
Name age
John 30
Sharp 35
Select * from test_1 where name is not null and age is not
Null;
When do we create bitmap indexes
Answer
If there is very less cardinality (i.e. 1% of total column
Values) or distinct values in column then we have to create
Bitmap index for that column like gender column will always
Have 2 values male or female.
Syntex:-
Create bitmap index index_name on table_name(column_name);
A.oltp
B.dss
C.where clause has functions
D.tables that have only one record
Can we use out parameter in function?
Answer
1.yes we can use out parameters in a function.but we have to assign the out parameter value to the return datatype.
2.but when we use out parameters in function we can't call the function from a select statement
Write a query to genarate target column.please answer me.
Advance thanks.
Src tgt
Q10 quarter to 2010
Q90 quarter to 1990
Q80 quarter to 1980
Q74 quarter to 1974
Select
'Quarter to '||to_char((to_date('01/01/'||substr(src,2),
'Dd/mm/rrrr')),'rrrr') as "target"
From table
How to call the function and procedure in trigger?
Answer
With the help of call statement..without semicolon
Create or replace trigger tri_call
Begin
Call p1()
End tri_call;
What is different between union and minus?
Answer
Unoin:- this operator returns from all the queries(combined
Through union) but not duplicate record will be display.
Ex- a={1,2,3,4}
B={2,3,4,5}
Aub={1,2,3,4,5}............
Minus:- this operator displays records which belongs to only
The first query.
Ex:- a={1,2,3,4}
B= {2,3,5}
A-b={1,4}...................
Can we interchange parameters in procedure while calling
Answer
If you name the parameter then you can interchange
Otherwise you cannot. Like in the following example the
First case is positional and you cannot interchange. In the
Second one its named and you can interchange.
Regular oradatabase.standproc(102,'ram'); -- positional
Oradatabase.standproc(empno=>102,ename=>'ram'); --named
Oradatabase.standproc(ename=>'ram',empno=>102);
Re: what is 'force view'?
Answer
The view can be created without base table then the view is
Called force view. The view must be crated with force
Sql> create or replace view test_view
2 as
3 select * from non_existent_table;
Select * from non_existent_table
*
Error at line 3:
Ora-00942: table or view does not exist
/* hence, the view does not exists */
Sql> select * from test_view;
Select * from test_view
*
Error at line 1:
Ora-00942: table or view does not exist
/* specifying force creates the view object (albeit with
Errors) */
Sql> create or replace force view test_view
2 as
3 select * from non_existent_table;
Warning: view created with compilation errors.
/* trying to select from the view implies it's been created
*/
Sql> select * from test_view;
Select * from test_view
*
Error at line 1:
Ora-04063: view "orauser.test_view" has errors
/* creating the missing object then allows us to select
From it */
Sql> create table non_existent_table
2 (
3 a varchar2(10)
4 );
Table created.
Sql> select * from test_view;
No rows selected
What are global temporary tables
Answer
Global temporary tables are session dependant tables which Could be used as temporary storage for calculations, sorting
Etc. What i mean by session dependant is, the data being Stored in the global temporary table is not written into the Database or stored anywhere. Once the session ends (in which The global temporary table is used), the data also vanishes.
However the structure would still be available even after the session is logged out. Also, the structure is available to other sessions even when one session is using it, but not the data. I.e multiple sessions could use the same global temporary table without interfering the data. Each session could insert/update/delete their own data into the same Global temporary table as if the table is available to only that session. Any data inserted in one session is not available to another.
Now, why do we need global temporary tables? Well, imagine a requirement where you need to fetch some data from the database, do some kind of calculations, aggregations and provide the result set (many records) to a front end. Again, in the front end, you need to fetch the result set may times, for some purpose. Then you could make use of the Global temporary table. Until the user gets disconnected from that database session, the data is available for him in the memory.
Global temporary tables belongs to that session only
Create global temporary table test_gbl
( l_number_no number,
L_char_vc varchar2(100)
) [on commit delete rows]
On commit delete rows:- it's a default one
If any commit will issued that total data of a table will
Losses. But table is exit
To overcome this we have option
On commit preserve rows:-
Means, if commit will issue the data of a table willn't loss
Up to end of the session. Is session ends the data will
Losses
What are nested tables? How will u delete 5 rows from nested
Tables
Answer
Create or replace type addresstype as object (
Street varchar2(15),
City varchar2(15),
State char(2),
Zip varchar2(5)
);
Create or replace type nested_table_addresstype as table of
Addresstype;
Create table employee (
Id integer primary key,
First_name varchar2(10),
Last_name varchar2(10),
Addresses nested_table_addresstype
)
Nested table
Addresses
Store as
Nested_addresses;
Insert into employee values (
1, 'steve', 'brown',
Nested_table_addresstype(
Addresstype('2 ave', 'city', 'ma', '12345'),
Addresstype('4 ave', 'city', 'ca', '54321')
)
);
Delete from table (
Select addresses from employee where id = 1
) addr
Where
Value(addr) = addresstype(
'4 ave', 'city', 'ca', '54321'
);
Re: select top 3 sal from each dept?
Answer
Select* from(select ename,deptno,sal,row_number()
Over(partiton by deptno order by sal)num from emp)
Where num<=3
Order by deptno;
Re: what is difference between having and where clause?
Answer
Having clause is used for filtering grouped data and where
Clause is used for filtering rows.
Where clause filter the data before gruop by clause and
Having clause clause filter the data after group by clause
Can you have multiple sps with the same name on a database?
Answer
Both above answers are correct,but situvationd diff
1) we can't create same db obj with same name,
2) we want that go for packages ,with overloading concept
I want to create a materialized view from a left join of 2
Tables. However the following gives me an error:
Select field1
From table_1 a
Left join table_2 b
On a.field1=b.field2
Ora-12054: cannot set the on commit refresh attribute for
The materialized view
However the following works:
Select field1
From table_1 a, table_2 b
Where a.field1=b.field2
Does anyone have any ideas why this is happening.
Thx for the help
Oracle views join materialized
Share|improve this question
There are two conditions that are not satisfied to make that
Materialized view refresh fast. First one is that you did
Not specify the rowid columns of every table involved. And
The second one is an undocumented restriction: ansi-joins
Are not supported.
Here is an example with dept being table_1, alias a and emp
Being table_2, alias b:
Sql> create materialized view log on emp with rowid
2 /
Materialized view log created.
Sql> create materialized view log on dept with rowid
2 /
Materialized view log created.
Sql> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.deptno
5 from dept a
6 left join emp b on (a.deptno = b.deptno)
7 /
From dept a
*
Error at line 5:
Ora-12054: cannot set the on commit refresh attribute for
The materialized view
That mimics your situation. First add the rowid's:
Sql> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.rowid dept_rowid
5 , b.rowid emp_rowid
6 , a.deptno
7 from dept a
8 left join emp b on (a.deptno = b.deptno)
9 /
From dept a
*
Error at line 7:
Ora-12054: cannot set the on commit refresh attribute for
The materialized view
Still it cannot fast refresh, because of the ansi joins.
Converting to old-style outer join syntax:
Sql> create materialized view empdept_mv
2 refresh fast on commit
3 as
4 select a.rowid dept_rowid
5 , b.rowid emp_rowid
6 , a.deptno
7 from dept a
8 , emp b
9 where a.deptno = b.deptno (+)
10 /
Materialized view created.
And to prove that it works:
Sql> select * from empdept_mv
2 /
Dept_rowid emp_rowid deptno
------------------ ------------------ ----------
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aaa 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aab 30
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aac 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aad 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aae 30
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aaf 30
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aag 10
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aah 20
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aai 10
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aaj 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aak 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aal 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aam 20
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aan 10
Aaarhmaaeaaaai/aad 40
15 rows selected.
Sql> insert into dept values (50,'it','utrecht')
2 /
1 row created.
Sql> commit
2 /
Commit complete.
Sql> select * from empdept_mv
2 /
Dept_rowid emp_rowid deptno
------------------ ------------------ ----------
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aaa 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aab 30
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aac 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aad 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aae 30
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aaf 30
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aag 10
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aah 20
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aai 10
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aaj 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aak 20
Aaarhmaaeaaaai/aac aaarhlaaeaaaai3aal 30
Aaarhmaaeaaaai/aab aaarhlaaeaaaai3aam 20
Aaarhmaaeaaaai/aaa aaarhlaaeaaaai3aan 10
Aaarhmaaeaaaai/aad 40
Aaarhmaaeaaaai7aaa 50
16 rows selected.
