Oracle PL SQL Interview Questions

 
1.
What is difference b/w stored procedures and application procedures, stored function and application function?
2.
Explian rowid, rownum?what are the psoducolumns we have?
3.
what is the starting oracle error number?
what is meant by forward declaration in functions?
4.
In a Distributed Database System Can we execute two queries simultaneously? Justify?
5.
How we can create a table in PL/SQL block. insert records into it? is it possible by some procedure or function? please give example?
6.
How to avoid using cursors? What to use instead of cursor and in what cases to do so?
7.
State the difference between implict and explict cursors?
8.
How to know the last executed procedure?
9.
How can a function retun more than one value in oracle with proper example?
10.
If the application is running very slow? At what points you need to go about the database in order to improve the performance?
11.
What is the basic structure of PL/SQL?
12.
What are the components of a PL/SQL block?
13.
What are the datatypes a available in PL/SQL?
14.
What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?
15.
What is difference between % ROWTYPE and TYPE RECORD?
16.
What will the Output for this Coding>
Declare
Cursor c1 is select * from emp FORUPDATE;
Z c1%rowtype;
Begin
Open C1;
Fetch c1 into Z;
Commit;
Fetch c1 in to Z;
end;
17.
What are the Restrictions on Cursor Variables?
18.
Can we declare a column having number data type and its scale is larger than pricesion
ex: column_name NUMBER(10,100),
column_name NUMBAER(10,-84)
19.
What will happen after commit statement?
20.
Explain the usage of WHERE CURRENT OF clause in cursors?
21.
What is a database trigger? Name some usages of database trigger?
22.
How many types of database triggers can be specified on a table? What are they?
23.
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?
24.
What are two virtual tables available during database trigger execution?
25.
What happens if a procedure that updates a column of table X is called in a database trigger of the same table?
26.
Write the order of precedence for validation of a column in a table?
i. done using Database triggers.
ii. done using Integarity Constraints
27.
What is an Exception? What are types of Exception?
28.
What is Pragma EXECPTION_INIT? Explain the usage?
29.
What is Raise_application_error?
30.
What are the return values of functions SQLCODE and SQLERRM?
31.
Where the Pre_defined_exceptions are stored?
32.
What is an oracle stored procedure?
33.
What is a cursor for loop?
34.
What are the cursor attributes used in PL/SQL?
35.
What are the PL/SQL Statements used in cursor processing?
36.
Explain the two type of Cursors?
37.
What is a cursor ? Why Cursor is required?
38.
What is PL/SQL table?
39.
What is difference between a PROCEDURE & FUNCTION?
40.
What are advantages fo Stored Procedures?
41.
What are the modes of parameters that can be passed to a procedure?
42.
What are the two parts of a procedure?
43.
Give the structure of the procedure?
44.
Give the structure of the function?
45.
Explain how procedures and functions are called in a PL/SQL block?
46.
What is Overloading of procedures?
47.
What are two parts of package?
48.
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?
49.
How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
50.
Name the tables where characteristics of Package, procedure and functions are stored?
51.
Give the Types of modules in a form?
52.
What is difference b/w stored procedures and application procedures, stored function and application function?
53.
Explian rowid, rownum?what are the psoducolumns we have?
54.
what is the starting oracle error number?
what is meant by forward declaration in functions?
55.
In a Distributed Database System Can we execute two queries simultaneously? Justify?
56.
How we can create a table in PL/SQL block. insert records into it? is it possible by some procedure or function? please give example?
57.
How to avoid using cursors? What to use instead of cursor and in what cases to do so?
58.
State the difference between implict and explict cursors?
59.
How to know the last executed procedure?
60.
How can a function retun more than one value in oracle with proper example?
61.
If the application is running very slow? At what points you need to go about the database in order to improve the performance?
62.
What is the basic structure of PL/SQL?
63.
What are the components of a PL/SQL block?
64.
What are the datatypes a available in PL/SQL?
65.
What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?
66.
What is difference between % ROWTYPE and TYPE RECORD?
67.
What will the Output for this Coding>
Declare
Cursor c1 is select * from emp FORUPDATE;
Z c1%rowtype;
Begin
Open C1;
Fetch c1 into Z;
Commit;
Fetch c1 in to Z;
end;
68.
What are the Restrictions on Cursor Variables?
69.
Can we declare a column having number data type and its scale is larger than pricesion
ex: column_name NUMBER(10,100),
column_name NUMBAER(10,-84)
70.
What will happen after commit statement?
71.
Explain the usage of WHERE CURRENT OF clause in cursors?
72.
What is a database trigger? Name some usages of database trigger?
73.
How many types of database triggers can be specified on a table? What are they?
74.
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?
75.
What are two virtual tables available during database trigger execution?
76.
What happens if a procedure that updates a column of table X is called in a database trigger of the same table?
77.
Write the order of precedence for validation of a column in a table?
i. done using Database triggers.
ii. done using Integarity Constraints
78.
What is an Exception? What are types of Exception?
79.
What is Pragma EXECPTION_INIT? Explain the usage?
80.
What is Raise_application_error?
81.
What are the return values of functions SQLCODE and SQLERRM?
82.
Where the Pre_defined_exceptions are stored?
83.
What is an oracle stored procedure?
84.
What is a cursor for loop?
85.
What are the cursor attributes used in PL/SQL?
86.
What are the PL/SQL Statements used in cursor processing?
87.
Explain the two type of Cursors?
88.
What is a cursor ? Why Cursor is required?
89.
What is PL/SQL table?
90.
What is difference between a PROCEDURE & FUNCTION?
91.
What are advantages fo Stored Procedures?
92.
What are the modes of parameters that can be passed to a procedure?
93.
What are the two parts of a procedure?
94.
Give the structure of the procedure?
95.
Give the structure of the function?
96.
Explain how procedures and functions are called in a PL/SQL block?
97.
What is Overloading of procedures?
98.
What are two parts of package?
99.
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?
100.
How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
101.
Name the tables where characteristics of Package, procedure and functions are stored?
102.
Give the Types of modules in a form?
103.
What is difference b/w stored procedures and application procedures, stored function and application function?
104.
Explian rowid, rownum?what are the psoducolumns we have?
105.
what is the starting oracle error number?
what is meant by forward declaration in functions?
106.
In a Distributed Database System Can we execute two queries simultaneously? Justify?
107.
How we can create a table in PL/SQL block. insert records into it? is it possible by some procedure or function? please give example?
108.
How to avoid using cursors? What to use instead of cursor and in what cases to do so?
109.
State the difference between implict and explict cursors?
110.
How to know the last executed procedure?
111.
How can a function retun more than one value in oracle with proper example?
112.
If the application is running very slow? At what points you need to go about the database in order to improve the performance?
113.
What is the basic structure of PL/SQL?
114.
What are the components of a PL/SQL block?
115.
What are the datatypes a available in PL/SQL?
116.
What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?
117.
What is difference between % ROWTYPE and TYPE RECORD?
118.
What will the Output for this Coding>
Declare
Cursor c1 is select * from emp FORUPDATE;
Z c1%rowtype;
Begin
Open C1;
Fetch c1 into Z;
Commit;
Fetch c1 in to Z;
end;
119.
What are the Restrictions on Cursor Variables?
120.
Can we declare a column having number data type and its scale is larger than pricesion
ex: column_name NUMBER(10,100),
column_name NUMBAER(10,-84)
121.
What will happen after commit statement?
122.
Explain the usage of WHERE CURRENT OF clause in cursors?
123.
What is a database trigger? Name some usages of database trigger?
124.
How many types of database triggers can be specified on a table? What are they?
125.
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?
126.
What are two virtual tables available during database trigger execution?
127.
What happens if a procedure that updates a column of table X is called in a database trigger of the same table?
128.
Write the order of precedence for validation of a column in a table?
i. done using Database triggers.
ii. done using Integarity Constraints
129.
What is an Exception? What are types of Exception?
130.
What is Pragma EXECPTION_INIT? Explain the usage?
131.
What is Raise_application_error?
132.
What are the return values of functions SQLCODE and SQLERRM?
133.
Where the Pre_defined_exceptions are stored?
134.
What is an oracle stored procedure?
135.
What is a cursor for loop?
136.
What are the cursor attributes used in PL/SQL?
137.
What are the PL/SQL Statements used in cursor processing?
138.
Explain the two type of Cursors?
139.
What is a cursor ? Why Cursor is required?
140.
What is PL/SQL table?
141.
What is difference between a PROCEDURE & FUNCTION?
142.
What are advantages fo Stored Procedures?
143.
What are the modes of parameters that can be passed to a procedure?
144.
What are the two parts of a procedure?
145.
Give the structure of the procedure?
146.
Give the structure of the function?
147.
Explain how procedures and functions are called in a PL/SQL block?
148.
What is Overloading of procedures?
149.
What are two parts of package?
150.
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?
151.
How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
152.
Name the tables where characteristics of Package, procedure and functions are stored?
153.
Give the Types of modules in a form?
154.
What is difference b/w stored procedures and application procedures, stored function and application function?
155.
Explian rowid, rownum?what are the psoducolumns we have?
156.
what is the starting oracle error number?
what is meant by forward declaration in functions?
157.
In a Distributed Database System Can we execute two queries simultaneously? Justify?
158.
How we can create a table in PL/SQL block. insert records into it? is it possible by some procedure or function? please give example?
159.
How to avoid using cursors? What to use instead of cursor and in what cases to do so?
160.
State the difference between implict and explict cursors?
161.
How to know the last executed procedure?
162.
How can a function retun more than one value in oracle with proper example?
163.
If the application is running very slow? At what points you need to go about the database in order to improve the performance?
164.
What is the basic structure of PL/SQL?
165.
What are the components of a PL/SQL block?
166.
What are the datatypes a available in PL/SQL?
167.
What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?
168.
What is difference between % ROWTYPE and TYPE RECORD?
169.
What will the Output for this Coding>
Declare
Cursor c1 is select * from emp FORUPDATE;
Z c1%rowtype;
Begin
Open C1;
Fetch c1 into Z;
Commit;
Fetch c1 in to Z;
end;
170.
What are the Restrictions on Cursor Variables?
171.
Can we declare a column having number data type and its scale is larger than pricesion
ex: column_name NUMBER(10,100),
column_name NUMBAER(10,-84)
172.
What will happen after commit statement?
173.
Explain the usage of WHERE CURRENT OF clause in cursors?
174.
What is a database trigger? Name some usages of database trigger?
175.
How many types of database triggers can be specified on a table? What are they?
176.
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?
177.
What are two virtual tables available during database trigger execution?
178.
What happens if a procedure that updates a column of table X is called in a database trigger of the same table?
179.
Write the order of precedence for validation of a column in a table?
i. done using Database triggers.
ii. done using Integarity Constraints
180.
What is an Exception? What are types of Exception?
181.
What is Pragma EXECPTION_INIT? Explain the usage?
182.
What is Raise_application_error?
183.
What are the return values of functions SQLCODE and SQLERRM?
184.
Where the Pre_defined_exceptions are stored?
185.
What is an oracle stored procedure?
186.
What is a cursor for loop?
187.
What are the cursor attributes used in PL/SQL?
188.
What are the PL/SQL Statements used in cursor processing?
189.
Explain the two type of Cursors?
190.
What is a cursor ? Why Cursor is required?
191.
What is PL/SQL table?
192.
What is difference between a PROCEDURE & FUNCTION?
193.
What are advantages fo Stored Procedures?
194.
What are the modes of parameters that can be passed to a procedure?
195.
What are the two parts of a procedure?
196.
Give the structure of the procedure?
197.
Give the structure of the function?
198.
Explain how procedures and functions are called in a PL/SQL block?
199.
What is Overloading of procedures?
200.
What are two parts of package?
201.
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification?
202.
How packaged procedures and functions are called from the following?
a. Stored procedure or anonymous block
b. an application program such a PRC *C, PRO* COBOL
c. SQL *PLUS
203.
Name the tables where characteristics of Package, procedure and functions are stored?
204.
Give the Types of modules in a form?