1.
What are the environments in your company?
2.
How to know which version is currently running
in your database?
SELECT * FROM V$VERSION
or
SELECT version FROM V$INSTANCE
or
BEGIN
DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || '.' ||
DBMS_DB_VERSION.RELEASE); END;
3.
Do you have any experience in data modeling?
4.
How to disable all triggers in a table? How
many triggers are you created?
Alter table table_name disable all triggers;
5.
How many triggers created on one table?
12
6.
Which will fire default first statement level
or row level trigger?
always
constraint will fire first.
7.
What is bulk collect? And any restrictions in
bulk collect? What is the use of limit clause in bulk collect?
8.
How to debugg your code?
9.
How to trace error handling?
10. How to find
which line error was raised?
11. What are the
methods there in save exceptions?
12. What is
functional based index? Write syntax?
13. How to
update complex view?
you can update
complex view through 'instead of triggers' in oracle.
let take an example to update the view v1 (below)
create or replace view v1 as
select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,dname
from emp e ,dept d where d.deptno = e.deptno
Instead of updating the view, you can create a trigger which overrides the default operation of
the update statement:
create or replace trigger update_emp_thru_v1_view
instead of update on v1
referencing new as new
begin
update emp
set ename = :new.ename,
empno = :new.empno,
job = :new.job,
mgr = :new.mgr,
hiredate = :new.hiredate,
sal = :new.sal,
comm = :new.comm,
deptno = ( select deptno from dept where dname = :new.dname )
where empno = :old.empno;
if ( sql%rowcount = 0 )
then
raise_application_error
( -20001, 'Error updating the outemp view ...' );
end if;
end;
Then, you can update this from SQL*Plus as normally you do to a table ..
let take an example to update the view v1 (below)
create or replace view v1 as
select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,dname
from emp e ,dept d where d.deptno = e.deptno
Instead of updating the view, you can create a trigger which overrides the default operation of
the update statement:
create or replace trigger update_emp_thru_v1_view
instead of update on v1
referencing new as new
begin
update emp
set ename = :new.ename,
empno = :new.empno,
job = :new.job,
mgr = :new.mgr,
hiredate = :new.hiredate,
sal = :new.sal,
comm = :new.comm,
deptno = ( select deptno from dept where dname = :new.dname )
where empno = :old.empno;
if ( sql%rowcount = 0 )
then
raise_application_error
( -20001, 'Error updating the outemp view ...' );
end if;
end;
Then, you can update this from SQL*Plus as normally you do to a table ..
14. Can you
alter procedure with in package?
[...] does not
change the declaration or definition of an existing package. To redeclare or
redefine a package, use the CREATE PACKAGE or the CREATE PACKAGE BODY statement with the OR REPLACEclause.
15. Is it
possible to open cursor which is in package in another procedure?
YES
16. What is
substr()&instr()?
SUBSTR('This is
a test', 6, 2)
Result: 'is'
SUBSTR('This is
a test', 6)
Result: 'is a test'
SUBSTR('TechOnTheNet',
1, 4)
Result: 'Tech'
SUBSTR('TechOnTheNet',
-3, 3)
Result: 'Net'
SUBSTR('TechOnTheNet',
-6, 3)
Result: 'The'
SUBSTR('TechOnTheNet',
-8, 2)
Result: 'On'
INSTR('Tech on
the net', 'e')
Result: 2 (the first occurrence of 'e')
INSTR('Tech on
the net', 'e', 1, 1)
Result: 2 (the first occurrence of 'e')
INSTR('Tech on
the net', 'e', 1, 2)
Result: 11 (the second occurrence of 'e')
INSTR('Tech on
the net', 'e', 1, 3)
Result: 14 (the third occurrence of 'e')
INSTR('Tech on
the net', 'e', -3, 2)
Result: 2
17. Difference
between case and decode?
18. Can you use
sysdate in check constraints? If no, why?
Oracle check constraint has some limitations. For one,
subqueries cannot be usedwithin
your Oracle check constraints.
Also, an Oracle check
constraint is able to
reference another column. Sysdate,
currval, nextval, level, rowid, uid, user or userenv cannot be referenced with
Oracle check constraint.
19. Difference
between column level constraints & table level constraints?
a column level constraint has scope only to the column it is defined on. A table level constraint can see every column in the table. That is the major difference between the two - that of "scoping".
Any column level constraint (exception: not null) can be expressed
at the table level - but the opposite is not true
20. What is
optimizer?
The output from the optimizer is a plan that describes an optimum method of execution. The Oracle server provides the cost-based (CBO) and rule-based (RBO) optimization.
The output from the optimizer is a plan that describes an optimum method of execution. The Oracle server provides the cost-based (CBO) and rule-based (RBO) optimization.
