Oracle Apps Interview Questions and Answers Technical
1. Introduce yourself .
2. Difference between having and group by
HAVING is only for conditions involving aggregates used in conjunction with the GROUP BY clause. eg. COUNT, SUM, AVG, MAX, MIN. WHERE is for any non-aggregage conditions. They can even be used together in the same query
. SELECT t1.id, COUNT(*) FROM table1 AS t1
INNER JOIN table2 AS t2 ON t2.t1_id = t1.id
WHERE t1.score > 50
GROUP BY t1.id HAVING COUNT(*) > 2;.
3. What is IN parameter and OUT parameter in PLSQL?
1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
2. OUT - The parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
4. Difference between Value sets and Lookups ?
Value sets and Lookups might look similar and confuse some people but there are some basic differences between the two.
ValueSets Lookups
Valuesets can be attached to parameters of a concurrent program. Lookups can n’t be attached to parameters of a concurrent program.
Value Sets are almost never maintained by end users, with the exception of GL Flexfield codes.
Lookups are maintainable by the users.
Value sets are usually maintained by System Administrators. HR Users will maintain "Ethnic Minority" lookups.
Value sets can contain values that are a result of an SQL Statement. Hence it is possible to make Value Set list of values dynamic.
5. How we can eliminate duplicates without using distinct command?
DELETE FROM emp WHERE rowid IN(SELECT eno,ename,sal,max(rowid) FROM emp GROUP BY eno,ename,sal); .
6. How to retrieve a second highest salary from a table?
Select Max(Salary) from Emp Where Salary < (Select Max(Salary) from Emp).
7. Difference between case and decode ?
DECODE is a function where CASE is a Expression.
DECODE is Oracle standard and CASE is ANSI standard
Both CASE and DECODE can be used in WHERE clause.
CASE can be directly used in PL/SQL but DECODE can be used in PL/SQL through SQL statements only.
Case cannot process null while decode can.
8. The two different types of Exceptions in PL/SQL are: ?
User Defined
Pre-Defined
9. Cursor attributes ?
There are five cursor attributes:
%isopen
%found
%notfound
%rowcount
%bulk_rowcount
10. What is %TYPE and %ROWTYPE in PL/SQL?
%TYPE provides the data type of a variable or a database column to that variable.
%ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
Ex:- DECLARE
l_rec employee%rowtype;
CURSOR c1
IS
SELECT * FROM employee WHERE empno = 100;
BEGIN
OPEN c1;
FETCH c1 INTO l_rec;
CLOSE c1;
END;
11. List all different types of Data Types in PL/SQL?
Here are the complete lists of Data Types supported by Oracle
Number Data Types
BINARY_INTEGER
NUMBER(p,s)
NATURAL
NATURALN
PLS_INTERGER
POSITIVE
POSITIVEN
SIGNTYPE
NUMERIC(p,s)
FLOAT
DEC(p,s)
DECIMAL(p,s)
INTEGER
INT
SMALLINT
REAL
DOUBLE PRECISION .
12.Types of Report Triggers:-
a) Report Trigger: Order of Execution of Report Trigger is,
• Before Parameter form trigger is fired
• Runtime Parameter form appears
• After Parameter form trigger is fired
• Report is Compiled & Queries are Parsed
• Before Report Trigger is fired
• Report is Executed & Data is fetched from database
• Between pages fires for each page except last one
• After Report trigger is fired .
13.What is difference between a PROCEDURE & a FUNCTION ?
Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter.
14. What is difference between % ROWTYPE and TYPE RECORD?
Advantages:
% ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.
Example:
TYPE r_emp IS RECORD (eno emp.empno% type,ename emp ename %type
);
e_rec emp% ROWTYPE
cursor c1 is SELECT empno,deptno FROM emp;
e_rec c1 %ROWTYPE.
15. Can you have multiple layout templates for a singe data template?
Yes! Multiple layouts can be attached, so that user will have a choice here to use one among them at the time of concurrent program submission .
16. What is Bursting in XML Publisher?
What are the different ways to show/insert an image on XMLP layout?
There are 4 different ways to insert/show an image in rtf:
Insert an image directly on rtf just by using MS word standard feature
Using OA_MEDIA: storing on server and giving physical path of the image
Using URL of a image hosted on some website
Retrieving the image store in a database as BLOB type .
17. Difference between delete and truncate .
You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE Trigger get fired.
You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause .
18. How many types of indexes
We have mainly two different indexes.
Implicit index and explicit index.
Explicit indexes are again of many types like
simple index, unique index, Bitmap index, Functional
index, Organisational index, cluster index.
19. What is trigger and types
The types of triggers are:
- Row level triggers
- Statement level triggers
- BEFORE and AFTER triggers
-Combination Trigger
Before Statement Trigger : Trigger fire only once for each statement before the triggering DML statement.
Before Row Trigger : Trigger fire for each and every record before the triggering DML statement.
After Statement Trigger : Trigger fire only once for each statement after the triggering DML statement executing.
After Row Trigger : Trigger fire for each and every record after the triggering DML statement executing.
20. Diff b/w view and materialized view?
Materialized view is physical duplicate data in a table
view is logical representation of table .
21. Can we update the view ?
Suppose you want update view means that time you need to use insted of trigger with that only possible to update view .
22. I have one table i created view and materialized view on a table if i delete a table, Can i see the view and materialized view?
23. Can u call function in same function .
Yes .
24.Can we use tcl commands in trigger ?
Nope, you cannot.
25. What is the cursor and ref cursor ?
Generally ref cursor used for passing cursor parameter and also dynamically building the query.
Normal cursor are static cursors and also we can't able to pass like parameters .
26. What is the purpose of Token in Concurrent Program Definition form?
Token is used for transferring values towards report builder. Tokens are usually not case – sensitive.
27. Formula column?
We use formula column to calculate some information dynamically using information based on the columns of the data model or from the concurrent program parameters. It is basically used to apply some custom logic on input data and return some value.
Formula columns can be written using PL/SQL syntax and we can use pl/sql functions for computation on the data. Formula column will accept data of Character, Number, or Date type.
Ex:- CF_
28. Summary column :-
Summary columns are used for calculating summary information like sum, average etc,. on specific columns of a data group. This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.
29. To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
30. To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
31. Find the 3rd MAX salary in the emp table ?
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
32. Find the 3rd MIN salary in the emp table?
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
33. Select FIRST n records from a table.
select * from emp where rownum <= &n;
34. Select LAST n records from a table
select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
35. List dept no., Dept name for all the departments in which there are no employees in the department.
select * from dept where deptno not in (select deptno from emp);
alternate solution: select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
altertnate solution: select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
36. How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
37. How to get 3 Min salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);
38. How to get nth max salaries ?
select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b where a.sal >= b.sal);
39. Select DISTINCT RECORDS from emp table ?
select * from emp a where rowid = (select max(rowid) from emp b where a.empno=b.empno);
40. How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);
41. Count of number of employees in department wise.
select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;
42. Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
select ename,sal/12 as monthlysal from emp;
43. Select all record from emp table where deptno =10 or 40.
select * from emp where deptno=30 or deptno=10;
44. Select all record from emp table where deptno=30 and sal>1500.
select * from emp where deptno=30 and sal>1500;
45. Select all record from emp where job not in SALESMAN or CLERK.
select * from emp where job not in ('SALESMAN','CLERK');
46. Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
47. Select all records where ename starts with ‘S’ and its lenth is 6 char.
select * from emp where ename like'S____';
48. Select all records where ename may be any no of character but it should end with ‘R’.
select * from emp where ename like'%R';
49. Count MGR and their salary in emp table.
select count(MGR),count(sal) from emp;
50. In emp table add comm+sal as total sal .
select ename,(sal+nvl(comm,0)) as totalsal from emp;
51. Select any salary <3000 from emp table.
select * from emp where sal> any(select sal from emp where sal<3000);
52. Select all salary <3000 from emp table.
select * from emp where sal> all(select sal from emp where sal<3000);
53. Select all the employee group by deptno and sal in descending order.
select ename,deptno,sal from emp order by deptno,sal desc;
54. How can I create an empty table emp1 with same structure as emp?
Create table emp1 as select * from emp where 1=2;
55. How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And sal<2000
56. Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
57. If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)
58. How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)
59. How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)
60. Count the totalsa deptno wise where more than 2 employees exist.
SELECT deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2
61. How can a break order be created on a column in an existing group?
By dragging the column outside the group
62. What are the types of calculated columns available?
Summary, Formula, Placeholder column.
63. What is the use of place holder column?
A placeholder column is used to hold a calculated values at a specified place rather than allowing is to appear in the actual row where it has to appeared.
64. What is the use of hidden column?
A hidden column is used to when a column has to embed into boilerplate text.
65. What is the use of break group?
A break group is used to display one record for one group ones. While multiple related records in other group can be displayed.
67. If two groups are not linked in the data model editor, what is the hierarchy between them?
Two group that is above are the left most rank higher than the group that is to right or below it.
68. The join defined by the default data link is an outer join yes or no?
Yes.
69. How can a text file be attached to a report while creating in the report writer?
By using the link file property in the layout boiler plate property sheet.
70. Can a repeating frame be created without a data group as a base?
No.
71. Can a field be used in a report without it appearing in any data group?
Yes.
72. For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame?
Yes.
73. Is it possible to centre an object horizontally in a repeating frame that has a variable horizontal size?
Yes.
74. If yes, how?
By the use anchors.
75. What are the two repeating frame always associated with matrix object?
One down repeating frame below one across repeating frame.
76. Is it possible to split the print previewer into more than one region?
Yes.
77. Does a grouping done for objects in the layout editor affect the grouping done in the data model editor?
No.
78. How can a square be drawn in the layout editor of the report writer?
By using the rectangle tool while pressing the (Constraint) key.
79. To display the page no. for each page on a report what would be the source & logical page no. or & of physical page no.?
& physical page no.
80. What does the term panel refer to with regard to pages?
A panel is the no. of physical pages needed to print one logical page.
81. What is an anchoring object & what is its use?
An anchoring object is a print condition object which used to explicitly or implicitly anchor other objects to itself.
82. What is a physical page? & what is a logical page?
A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the Previewer.
83. What is the frame & repeating frame?
A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the no. of records that are to displayed is not known before.
84. What is SQLCODE and SQLERRM in PL/SQL?
The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code.
The SQLERRM function returns the error message associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code.
No comments :
Post a Comment