Oracle DBA Interview Questions

1. SNAPSHOT is used for
[DBA] a] Synonym, b] Table space, c] System server, d] Dynamic data
replication
Ans : D

2. We can create SNAPSHOTLOG for
[DBA] a] Simple snapshots, b] Complex snapshots, c] Both A & B, d]
Neither A nor B
Ans : A

3. Transactions per rollback segment is derived from
[DBA] a] Db_Block_Buffers, b] Processes, c] Shared_Pool_Size, d] None
of the above
Ans : B

4. ENQUEUE resources parameter information is derived from
[DBA] a] Processes or DDL_LOCKS and DML_LOCKS, b] LOG_BUFFER,
c] DB__BLOCK_SIZE..
Ans : A

5. LGWR process writes information into
a] Database files, b] Control files, c] Redolog files, d] All the
above.
Ans : C

6. SET TRANSACTION USE ROLLBACK SEGMENT is used to create user
objects
in a particular Tablespace
a] True, b] False
Ans : False

7. Databases overall structure is maintained in a file called
a] Redolog file, b] Data file, c] Control file, d] All of the
above.
Ans : C

8. These following parameters are optional in init.ora parameter file
DB_BLOCK_SIZE,
PROCESSES
a] True, b] False
Ans : False

9. Constraints cannot be exported through EXPORT command
a] True, b] False
Ans : False

10. It is very difficult to grant and manage common privileges needed by
different groups of
database users using the roles
a] True, b] False
Ans : False

11. What is difference between a DIALOG WINDOW and a DOCUMENT WINDOW
regarding
moving the window with respect to the application window
a] Both windows behave the same way as far as moving the window is
concerned.
b] A document window can be moved outside the application window while
a dialog
window cannot be moved
c] A dialog window can be moved outside the application window while a
document
window cannot be moved
Ans : C

12. What is the difference between a MESSAGEBOX and an ALERT
a] A messagebox can be used only by the system and cannot be used in
user application
while an alert can be used in user application also.
b] A alert can be used only by the system and cannot be use din user
application
while an messagebox can be used in user application also.
c] An alert requires an response from the userwhile a messagebox just
flashes a message
and only requires an acknowledment from the user
d] An message box requires an response from the userwhile a alert just
flashes a
message an only requires an acknowledment from the user
Ans : C

13. Which of the following is not an reason for the fact that most of the
processing is done at the
server ?
a] To reduce network traffic. b] For application sharing, c] To
implement business rules
centrally, d] None of the above
Ans : D

14. Can a DIALOG WINDOW have scroll bar attached to it ?
a] Yes, b] No
Ans : B

15. Which of the following is not an advantage of GUI systems ?
a] Intuitive and easy to use., b] GUI's can display multiple
applications in multiple windows
c] GUI's provide more user interface objects for a developer
d] None of the above
Ans :D

16. What is the difference between a LIST BOX and a COMBO BOX ?
a] In the list box, the user is restricted to selecting a value from a
list but in a combo box
the user can type in a value which is not in the list
b] A list box is a data entry area while a combo box can be used only
for control purposes
c] In a combo box, the user is restricted to selecting a value from a
list but in a list box the
user can type in a value which is not in the list
d] None of the above
Ans : A

17. In a CLIENT/SERVER environment , which of the following would not be
done at the client ?
a] User interface part, b] Data validation at entry line, c]
Responding to user events,
d] None of the above
Ans : D

18. Why is it better to use an INTEGRITY CONSTRAINT to validate data in a
table than to use a
STORED PROCEDURE ?
a] Because an integrity constraint is automatically checked while data
is inserted into or
updated in a table while a stored procedure has to be
specifically invoked
b] Because the stored procedure occupies more space in the database
than a integrity
constraint definition
c] Because a stored procedure creates more network traffic than a
integrity constraint
definition
Ans : A

19. Which of the following is not an advantage of a client/server model ?
a] A client/server model allows centralised control of data and
centralised implementation
of business rules.
b] A client/server model increases developer;s productivity
c] A client/server model is suitable for all applications
d] None of the above.
Ans : C

20. What does DLL stands for ?
a] Dynamic Language Library
b] Dynamic Link Library
c] Dynamic Load Library
d] None of the above
Ans : B

21. POST-BLOCK trigger is a
a] Navigational trigger
b] Key trigger
c] Transactional trigger
d] None of the above
Ans : A

22. The system variable that records the select statement that SQL * FORMS
most recently used
to populate a block is
a] SYSTEM.LAST_RECORD
b] SYSTEM.CURSOR_RECORD
c] SYSTEM.CURSOR_FIELD
d] SYSTEM.LAST_QUERY
Ans: D

23. Which of the following is TRUE for the ENFORCE KEY field
a] ENFORCE KEY field characterstic indicates the source of the value
that SQL*FORMS
uses to populate the field
b] A field with the ENFORCE KEY characterstic should have the INPUT
ALLOWED
charaterstic turned off
a] Only 1 is TRUE
b] Only 2 is TRUE
c] Both 1 and 2 are TRUE
d] Both 1 and 2 are FALSE
Ans : A

24. What is the maximum size of the page ?
a] Characters wide & 265 characters length
b] Characters wide & 265 characters length
c] Characters wide & 80 characters length
d] None of the above
Ans : B

25. A FORM is madeup of which of the following objects
a] block, fields only,
b] blocks, fields, pages only,
c] blocks, fields, pages, triggers and form level procedures,
d] Only blocks.
Ans : C

26. For the following statements which is true
1] Page is an object owned by a form
2] Pages are a collection of display information such as constant text
and graphics.
a] Only 1 is TRUE
b] Only 2 is TRUE
c] Both 1 & 2 are TRUE
d] Both are FALSE
Ans : B

27. The packaged procedure that makes data in form permanent in the
Database is
a] Post
b] Post form
c] Commit form
d] None of the above
Ans : C

28. Which of the following is TRUE for the SYSTEM VARIABLE $$date$$
a] Can be assigned to a global variable
b] Can be assigned to any field only during design time
c] Can be assigned to any variable or field during run time
d] None of the above
Ans : B

29. Which of the following packaged procedure is UNRESTRICTED ?
a] CALL_INPUT, b] CLEAR_BLOCK, c] EXECUTE_QUERY, d] USER_EXIT
Ans : D

30. Identify the RESTRICTED packaged procedure from the following
a] USER_EXIT, b] MESSAGE, c] BREAK, d] EXIT_FORM
Ans : D

31. What is SQL*FORMS
a] SQL*FORMS is a 4GL tool for developing & executing Oracle based
interactive
applications.
b] SQL*FORMS is a 3GL tool for connecting to the Database.
c] SQL*FORMS is a reporting tool
d] None of the above.
Ans : A

32. Name the two files that are created when you generate a form using
Forms 3.0
a] FMB & FMX, b] FMR & FDX, c] INP & FRM, d] None of the above
Ans : C

33. What is a trigger
a] A piece of logic written in PL/SQL
b] Executed at the arrival of a SQL*FORMS event
c] Both A & B
d] None of the above
Ans : C

34. Which of the folowing is TRUE for a ERASE packaged procedure
1] ERASE removes an indicated Global variable & releases the memory
associated with it
2] ERASE is used to remove a field from a page
1] Only 1 is TRUE
2] Only 2 is TRUE
3] Both 1 & 2 are TRUE
4] Both 1 & 2 are FALSE
Ans : 1

35. All datafiles related to a Tablespace are removed when the Tablespace
is dropped
a] TRUE
b] FALSE
Ans : B

36. Size of Tablespace can be increased by
a] Increasing the size of one of the Datafiles
b] Adding one or more Datafiles
c] Cannot be increased
d] None of the above
Ans : B

37. Multiple Tablespaces can share a single datafile
a] TRUE
b] FALSE
Ans : B

38. A set of Dictionary tables are created
a] Once for the Entire Database
b] Every time a user is created
c] Every time a Tablespace is created
d] None of the above
Ans : A

39. Datadictionary can span across multiple Tablespaces
a] TRUE
b] FALSE
Ans : B

40. What is a DATABLOCK
a] Set of Extents
b] Set of Segments
c] Smallest Database storage unit
d] None of the above
Ans : C

41. Can an Integrity Constraint be enforced on a table if some existing
table data does not satisfy
the constraint
a] Yes
b] No
Ans : B

42. A column defined as PRIMARY KEY can have NULL's
a] TRUE
b] FALSE
Ans : B

43. A Transaction ends
a] Only when it is Committed
b] Only when it is Rolledback
c] When it is Committed or Rolledback
d] None of the above
Ans : C

44. A Database Procedure is stored in the Database
a] In compiled form
b] As source code
c] Both A & B
d] Not stored
Ans : C

45. A database trigger doesnot apply to data loaded before the definition
of the trigger
a] TRUE
b] FALSE
Ans : A

46. Dedicated server configuration is
a] One server process - Many user processes
b] Many server processes - One user process
c] One server process - One user process
d] Many server processes - Many user processes
Ans : C

47. Which of the following does not affect the size of the SGA
a] Database buffer
b] Redolog buffer
c] Stored procedure
d] Shared pool
Ans : C

48. What does a COMMIT statement do to a CURSOR
a] Open the Cursor
b] Fetch the Cursor
c] Close the Cursor
d] None of the above
Ans : D

49. Which of the following is TRUE
1] Host variables are declared anywhere in the program
2] Host variables are declared in the DECLARE section
a] Only 1 is TRUE
b] Only 2 is TRUE
c] Both 1 & 2are TRUE
d] Both are FALSE
Ans : B

50. Which of the following is NOT VALID is PL/SQL
a] Bool boolean;
b] NUM1, NUM2 number;
c] deptname dept.dname%type;
d] date1 date := sysdate
Ans : B.

 

51. Declare
fvar number := null; svar number := 5
Begin
goto << fproc>>
if fvar is null then
<< fproc>>
svar := svar + 5
end if;
End;

What will be the value of svar after the execution ?
a] Error
b] 10
c] 5
d] None of the above
Ans : A

52. Which of the following is not correct about an Exception ?
a] Raised automatically / Explicitly in response to an ORACLE_ERROR
b] An exception will be raised when an error occurs in that block
c] Process terminates after completion of error sequence.
d] A Procedure or Sequence of statements may be processed.
Ans : C

53. Which of the following is not correct about User_Defined Exceptions ?
a] Must be declared
b] Must be raised explicitly
c] Raised automatically in response to an Oracle error
d] None of the above
Ans : C

54. A Stored Procedure is a
a] Sequence of SQL or PL/SQL statements to perform specific function
b] Stored in compiled form in the database
c] Can be called from all client environmets
d] All of the above
Ans : D

55. Which of the following statement is false
a] Any procedure can raise an error and return an user message and
error number
b] Error number ranging from 20000 to 20999 are reserved for user
defined messages
c] Oracle checks Uniqueness of User defined errors
d] Raise_Application_error is used for raising an user defined error.
Ans : C

56. Is it possible to open a cursor which is in a Package in another
procedure ?
a] Yes
b] No
Ans : A

57. Is it possible to use Transactional control statements in Database
Triggers ?
a] Yes
b] No
Ans : B

58. Is it possible to Enable or Disable a Database trigger ?
a] Yes
b] No
Ans : A

59. PL/SQL supports datatype(s)
a] Scalar datatype
b] Composite datatype
c] All of the above
d] None of the above
Ans C

60. Find the ODD datatype out
a] VARCHAR2
b] RECORD
c] BOOLEAN
d] RAW
Ans : B

61. Which of the following is not correct about the "TABLE" datatype ?
a] Can contain any no of columns
b] Simulates a One-dimensional array of unlimited size
c] Column datatype of any Scalar type
d] None of the above
Ans : A

62. Find the ODD one out of the following
a] OPEN
b] CLOSE
c] INSERT
d] FETCH
Ans C

63. Which of the following is not correct about Cursor ?
a] Cursor is a named Private SQL area
b] Cursor holds temporary results
c] Cursor is used for retrieving multiple rows
d] SQL uses implicit Cursors to retrieve rows
Ans : B

64. Which of the following is NOT VALID in PL/SQL ?
a] Select ... into
b] Update
c] Create
d] Delete
Ans : C

65. What is the Result of the following 'VIK'||NULL||'RAM' ?
a] Error
b] VIK RAM
c] VIKRAM
d] NULL
Ans : C

66. Declare
a number := 5; b number := null; c number := 10;
Begin
if a > b AND a < c then
a := c * a;
end if;
End;
What will be the value of 'a' after execution ?
a] 50
b] NULL
c] 5
d] None of the above

Ans : C
67. Does the Database trigger will fire when the table is TRUNCATED ?
a] Yes
b] No
Ans : B

68. SUBSTR(SQUARE ANS ALWAYS WORK HARD,14,6) will return
a] ALWAY
b} S ALWA
c] ALWAYS
Ans : C

69. REPLACE('JACK AND JUE','J','BL') will return
a] JACK AND BLUE
b] BLACK AND JACK
c] BLACK AND BLUE
d] None of the above
Ans : C

70. TRANSLATE('333SQD234','0123456789ABCDPQRST','0123456789') will return
a] 333234
b] 333333
c] 234333
d] None of the above
Ans : A

71. EMPNO ENAME SAL
A822 RAMASWAMY 3500
A812 NARAYAN 5000
A973 UMESH 2850
A500 BALAJI 5750

Use these data for the following Questions
Select SAL from EMP E1 where 3 > ( Select count(*) from Emp E2
where E1.SAL > E2.SAL ) will retrieve
a] 3500,5000,2500
b] 5000,2850
c] 2850,5750
d] 5000,5750
Ans : A

72. Is it possible to modify a Datatype of a column when column contains
data ?
a] Yes
b] No
Ans B

73. Which of the following is not correct about a View ?
a] To protect some of the columns of a table from other users
b] Ocuupies data storage space
c] To hide complexity of a query
d] To hide complexity of a calculations
Ans : B

74. Which is not part of the Data Definiton Language ?
a] CREATE
b] ALTER
c] ALTER SESSION
Ans : C

75. The Data Manipulation Language statements are
a] INSERT
b] UPDATE
c] SELECT
d] All of the above
Ans : D

76. EMPNO ENAME SAL
A822 RAMASWAMY 3500
A812 NARAYAN 5000
A973 UMESH
A500 BALAJI 5750

Using the above data
Select count(sal) from Emp will retrieve
a] 1
b] 0
c] 3
d] None of the above
Ans : C

77. If an UNIQUE KEY constraint on DATE column is created, will it accept
the rows that are
inserted with SYSDATE ?
a] Will
b] Won't
Ans : B

78. What are the different events in Triggers ?
a] Define, Create
b] Drop, Comment
c] Insert, Update, Delete
d] All of the above
Ans : C

79. What built-in subprogram is used to manipulate images in image items ?
a] Zoom_out
b] Zoom_in'
c] Image_zoom
d] Zoom_image
Ans : C

80. Can we pass RECORD GROUP between FORMS ?
a] Yes
b] No
Ans : A

81. SHOW_ALERT function returns
a] Boolean
b] Number
c] Character
d] None of the above
Ans : B

82. What SYSTEM VARIABLE is used to refer DATABASE TIME ?
a] $$dbtime$$
b] $$time$$
c] $$datetime$$
d] None of the above
Ans : A

83. :SYSTEM.EFFECTIVE.DATE varaible is
a] Read only
b] Read & Write
c] Write only
d] None of the above
Ans : C

84. How can you CALL Reports from Forms4.0 ?
a] Run_Report built_in
b] Call_Report built_in
c] Run_Product built_in
d] Call_Product built_in
Ans : C

85. When do you get a .PLL extension ?
a] Save Library file
b] Generate Library file
c] Run Library file
d] None of the above
Ans : A

86. What is built_in Subprogram ?
a] Stored procedure & Function
b] Collection of Subprogram
c] Collection of Packages
d] None of the above
Ans : D

87. GET_BLOCK property is a
a] Restricted procedure
b] Unrestricted procedure
c] Library function
d] None of the above
Ans : D

88. A CONTROL BLOCK can sometimes refer to a BASETABLE ?
a] TRUE
b] FALSE
Ans : B

89. What do you mean by CHECK BOX ?
a] Two state control
b] One state control
c] Three state control
d] none of the above
Ans : C - Please check the Correcness of this Answer ( The correct answer
is 2 )

90. List of Values (LOV) supports
a] Single column
b] Multi column
c] Single or Multi column
d] None of the above
Ans : C

91. What is Library in Forms 4.0 ?
a] Collection of External field
b] Collection of built_in packages
c] Collection of PL/SQl functions, procedures and packages
d] Collection of PL/SQL procedures & triggers
Ans : C

92. Can we use a RESTRICTED packaged procedure in WHEN_TEXT_ITEM trigger ?
a] Yes
b] No
Ans : B

93. Can we use GO_BLOCK package in a PRE_TEXT_ITEM trigger ?
a] Yes
b] No
Ans : B

94. What type of file is used for porting Forms 4.5 applications to various
platforms ?
a] .FMB file
b] .FMX file
c] .FMT file
d] .EXE file
Ans : C

95. What built_in procedure is used to get IMAGES in Forms 4.5 ?
a] READ_IMAGE_FILE
b] GET_IMAGE_FILE
c] READ_FILE
d] GET_FILE
Ans A

96. When a form is invoked with CALL_FORM does Oracle forms issues
SAVEPOINT ?
a] Yes
b] No
Ans : A

97. Can we attach the same LOV to different fields in Design time ?
a] Yes
b] No
Ans : A

98. How do you pass values from one form to another form ?
a] LOV
b] Parameters
c] Local variables
d] None of the above
Ans : B

99. Can you copy the PROGRAM UNIT into an Object group ?
a] Yes
b] No
Ans : B

100. Can MULTIPLE DOCUMENT INTERFACE (MDI) be used in Forms 4.5 ?
a] Yes
b] No
Ans : A.

 

Generating form
b] Executing form
c] Save form
d] Run form
Answer: C

102. What is a Built_in subprogram ?
a] Library
b] Stored procedure & Function
c] Collection of Subprograms
d] None of the above
Answer: D

103. What is a RADIO GROUP ?
a] Mutually exclusive
b] Select more than one column
c] Above all TRUE
d] Above all FALSE
Answer: A

104. Identify the Odd one of the following statements ?
a] Poplist
b] Tlist
c] List of values
d] Combo box
Answer: C

105. What is an ALERT ?
a] Modeless window
b] Modal window
c] Both are TRUE
d] None of the above
Answer: B

106. Can an Alert message be changed at runtime ?
a] Yes
b] No
Answer: A

107. Can we create an LOV without an RECORD GROUP ?
a} Yes
b] No
Answer: B

108. How many no of columns can a RECORD GROUP have ?
a] 10
b] 20
c] 50
d] None of the above
Answer: D

109. Oracle precompiler translates the EMBEDDED SQL statements into
a] Oracle FORMS
b] Oracle REPORTS
c] Oracle LIBRARY
d] None of the above
Answer: D

110. Kind of COMMENT statements placed within SQL statements ?
a] Asterisk(*) in column ?
b] ANSI SQL style statements(...)
c] C-Style comments (/*......*/)
d] All the above
Answer: D

111. What is the appropriate destination type to send the output to a
printer ?
a] Screen
b] Previewer
c] Either of the above
d] None of the above
Answer: D

112. What is TERM ?
a] TERM is the terminal definition file that describes the terminal
from which you are
using R20RUN ( Reports run time )
b] TERM is the terminal definition file that describes the terminal
from which you are
using R20DES ( Reports designer )
c] There is no Parameter called TERM in Reports 2.0
d] None of the above
Answer: A

113. If the maximum records retrieved property of a query is set to 10, then a summary value will be calculated
a] Only for 10 records
b] For all the records retrieved
c] For all the records in the referenced table
d] None of the above
Answer: A

114. With which function of a summary item in the COMPUTE AT option
required ?
a] Sum
b] Standard deviation
c] Variance
d] % of Total function
Answer: D

115. For a field in a repeating frame, can the source come from a column which does not exist in the datagroup which forms the base of the frame ?
a] Yes
b] No
Answer: A

116. What are the different file extensions that are created by Oracle
Reports ?
a] .RDF file & .RPX file
b] .RDX file & .RDF file
c] .REP file & .RDF file
d] None of the above
Answer: C

117. Is it possible to Disable the Parameter form while running the report?
a] Yes
b] No
Answer: A

118.What are the SQL clauses supported in the link property sheet ?
a] WHERE & START WITH
b] WHERE & HAVING
c} START WITH & HAVING
d] WHERE, START WITH & HAVING
Answer: D

119. What are the types of Calculated columns available ?
a] Summary, Place holder & Procedure column
b] Summary, Procedure & Formula columns
c] Procedure, Formula & Place holder columns
d] Summary, Formula & Place holder columns
Answer: D

120. If two groups are not linked in the data model editor, what is the hierarchy between them?
a] There is no hierarchy between unlinked groups
b] The group that is right ranks higher than the group that is to the
left
c] The group that is above or leftmost ranks higher than the group
that is to right or below it
d] None of the above
Answer: C

121. Sequence of events takes place while starting a Database is
a] Database opened, File mounted, Instance started
b] Instance started, Database mounted & Database opened
c] Database opened, Instance started & file mounted
d] Files mounted, Instance started & Database opened
Answer: B

122. SYSTEM TABLESPACE can be made off-line
a] Yes
b] No
Answer: B

123. ENQUEUE_RESOURCES parameter information is derived from
a] PROCESS or DDL_LOCKS & DML_LOCKS
b] LOG BUFFER
c] DB_BLOCK_SIZE
d] DB_BLOCK_BUFFERS
Answer: A

124. SMON process is used to write into LOG files
a] TRUE
b] FALSE
Answer: B

125. EXP command is used
a] To take Backup of the Oracle Database
b] To import data from the exported dump file
c] To create Rollback segments
d] None of the above
Answer: A

126. SNAPSHOTS cannot be refreshed automatically
a] TRUE
b] FALSE
Answer: B

127. The User can set Archive file name formats
a] TRUE
b] FALSE
Answer: A

128. The following parameters are optional in init.ora parameter file
DB_BLOCK_SIZE,
PROCESS
a} TRUE
b] FALSE
Answer: B

129. NOARCHIEVELOG parameter is used to enable the database in Archive
mode
a] TRUE
b] FALSE
Answer: B

130. Constraints cannot be exported through Export command?
a] TRUE
b] FALSE
Answer: B

131. It is very difficult to grant and manage common privileges needed by different groups of database users using roles
a] TRUE
b] FALSE
Answer: B

132. The status of the Rollback segment can be viewed through
a] DBA_SEGMENTS
b] DBA_ROLES
c] DBA_FREE_SPACES
d] DBA_ROLLBACK_SEG
Answer: D

133. Explicitly we can assign transaction to a rollback segment
a] TRUE
B] FALSE
Answer: A

134. What file is read by ODBC to load drivers ?
a] ODBC.INI
b] ODBC.DLL
c] ODBCDRV.INI
d] None of the above
Answer: A

Basics of PL/SQL

1. What is PL/SQL ?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

2. What is the basic structure of PL/SQL ?
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

3. What are the components of a PL/SQL block ?
A set of related declarations and procedural statements is called block.

4. What are the components of a PL/SQL Block ?
Declarative part, Executable part and Exception part.
Datatypes PL/SQL

5. What are the datatypes a available in PL/SQL ?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.

6. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
% 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.
The advantages are : I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

7. What is difference between % ROWTYPE and TYPE RECORD ?
% 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.
E.g. 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.

8. What is PL/SQL table ?
Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.

9. What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

10. Explain the two type of Cursors ?
There are two types of cursors, Implicit Cursor and Explicit Cursor.
PL/SQL uses Implicit Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.

11. What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

12. What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows fetched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND - to check whether cursor has fetched any row. True if no rows are fetched.
These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

13. What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes
when all the records have been processed.
eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;

14. What will happen after commit statement ?
Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;-----
commit;
end loop;
end;

The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even after COMMIT/ROLLBACK.

15. Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE, DELETE statement refers to the latest row fetched from a cursor.

16. What is a database trigger ? Name some usages of database trigger ?
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

17. How many types of database triggers can be specified on a table ? What are they ?
Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the returned boolean value.

18. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

19. What are two virtual tables available during database trigger execution ?
The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.

20. What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Mutation of table occurs.

21. Write the order of precedence for validation of a column in a table ?
I. done using Database triggers.
ii. done using Integrity Constraints.
I & ii.

22. What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined exceptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.

23. What is Pragma EXECPTION_INIT ? Explain the usage ?
The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.
e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

24. What is Raise_application_error ?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.

25. What are the return values of functions SQLCODE and SQLERRM ?
SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.

26. Where the Pre_defined_exceptions are stored ?
In the standard package.

27. What is a stored procedure ?
A stored procedure is a sequence of statements that perform specific function.

28. What is difference between a PROCEDURE & FUNCTION ?
A FUNCTION always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.

29. What are advantages fo Stored Procedures /
Extensibility, Modularity, Reusability, Maintainability and one time compilation.

30. What are the modes of parameters that can be passed to a procedure ?
IN, OUT, IN-OUT parameters.

31. What are the two parts of a procedure ?
Procedure Specification and Procedure Body.

32. Give the structure of the procedure ?
PROCEDURE name (parameter list.....)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;

33. Give the structure of the function ?
FUNCTION name (argument list .....) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;

34. Explain how procedures and functions are called in a PL/SQL block ?
Function is called as part of an expression.
sal := calculate_sal ('a822');
procedure is called as a PL/SQL statement
calculate_bonus ('A822');

35. What is Overloading of procedures ?
The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line

36. What is a package ? What are the advantages of packages ?
Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Application Design, Information. Hiding,. reusability and Better Performance.

37.What are two parts of package ?
The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.
Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.

38. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

39. 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
a. PACKAGE NAME.PROCEDURE NAME (parameters);
variable := PACKAGE NAME.FUNCTION NAME (arguments);
EXEC SQL EXECUTE
b.
BEGIN
PACKAGE NAME.PROCEDURE NAME (parameters)
variable := PACKAGE NAME.FUNCTION NAME (arguments);
END;
END EXEC;
c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any
out/in-out parameters. A function can not be called.

40. Name the tables where characteristics of Package, procedure and functions are stored ?
User_objects, User_Source and User_error.

SQL PLUS Statements

1. What are the types of SQL Statement ?
Data Definition Language : CREATE,ALTER,DROP,TRUNCATE,REVOKE,NO AUDIT & COMMIT.
Data Manipulation Language : INSERT,UPDATE,DELETE,LOCK TABLE,EXPLAIN PLAN & SELECT.
Transactional Control : COMMIT & ROLLBACK
Session Control : ALTERSESSION & SET ROLE
System Control : ALTER SYSTEM.

2. What is a transaction ?
Transaction is logical unit between two commits and commit and rollback.

3. What is difference between TRUNCATE & DELETE ?
TRUNCATE commits after deleting entire table i.e., can not be rolled back. Database triggers do not fire on TRUNCATE
DELETE allows the filtered deletion. Deleted records can be rolled back or committed.
Database triggers fire on DELETE.

4. What is a join ? Explain the different types of joins ?
Join is a query which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

5. What is the Subquery ?
Subquery is a query whose return values are used in filtering conditions of the main query.

6. What is correlated sub-query ?
Correlated sub_query is a sub_query which has reference to the main query.

7. Explain Connect by Prior ?
Retrieves rows in hierarchical order.
e.g. select empno, ename from emp where.

8. Difference between SUBSTR and INSTR ?
INSTR (String1,String2(n,(m)),
INSTR returns the position of the mth occurrence of the string 2 in
string1. The search begins from nth position of string1.
SUBSTR (String1 n,m)
SUBSTR returns a character string of size m in string1, starting from nth position of string1.

9. Explain UNION,MINUS,UNION ALL, INTERSECT ?
INTERSECT returns all distinct rows selected by both queries.
MINUS - returns all distinct rows selected by the first query but not by the second.
UNION - returns all distinct rows selected by either query
UNION ALL - returns all rows selected by either query, including all duplicates.

10. What is ROWID ?
ROWID is a pseudo column attached to each row of a table. It is 18 character long, blockno, rownumber are the components of ROWID.

11. What is the fastest way of accessing a row in a table ?
Using ROWID.

CONSTRAINTS

12. What is an Integrity Constraint ?
Integrity constraint is a rule that restricts values to a column in a table.

13. What is Referential Integrity ?
Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

14. What are the usage of SAVEPOINTS ?
SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

15. What is ON DELETE CASCADE ?
When ON DELETE CASCADE is specified ORACLE maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

16. What are the data types allowed in a table ?
CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW.

17. What is difference between CHAR and VARCHAR2 ? What is the maximum SIZE allowed for each type ?
CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.

18. How many LONG columns are allowed in a table ? Is it possible to use LONG columns in WHERE clause or ORDER BY ?

Only one LONG columns is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

19. What are the pre requisites ?
I. to modify datatype of a column ?
ii. to add a column with NOT NULL constraint ?
To Modify the datatype of a column the column must be empty.
to add a column with NOT NULL constrain, the table must be empty.

20. Where the integrity constraints are stored in Data Dictionary ?
The integrity constraints are stored in USER_CONSTRAINTS.

21. How will you a activate/deactivate integrity constraints ?
The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE constraint/DISABLE constraint.

22. If an unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE ?
It won't, Because SYSDATE format contains time attached with it.

23. What is a database link ?
Database Link is a named path through which a remote database can be accessed.

24. How to access the current value and next value from a sequence ? Is it possible to access the current value in a session before accessing next value ?
Sequence name CURRVAL, Sequence name NEXTVAL.
It is not possible. Only if you access next value in the session, current value can be accessed.

25. What is CYCLE/NO CYCLE in a Sequence ?
CYCLE specifies that the sequence continues to generate values after reaching either maximum or minimum value. After pan ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.

NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

26. What are the advantages of VIEW ?
To protect some of the columns of a table from other users.
To hide complexity of a query.
To hide complexity of calculations.

27. Can a view be updated/inserted/deleted? If Yes under what conditions ?
A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

28.If a View on a single base table is manipulated will the changes be reflected on the base table?
If changes are made to the tables which are base tables of a view will the changes be reference on the view.

PACKAGE PROCEDURE & FUNCTION

29. What is a Package Procedure ?
A Package procedure is built in PL/SQL procedure.

30. What are the different types of Package Procedure ?
1. Restricted package procedure.
2. Unrestricted package procedure.

31. What is the difference between restricted and unrestricted package procedure ?
Restricted package procedure that affects the basic basic functions of SQL * Forms. It cannot used in all triggers except key triggers.
Unrestricted package procedure that does not interfere with the basic functions of SQL * Forms it can be used in any triggers.

32. Classify the restricted and unrestricted procedure from the following.
a. Call
b. User-Exit
c. Call-Query
d. Up
e. Execute-Query
f. Message
g. Exit-From
h. Post
i. Break

a. Call - unrestricted
b. User Exit - Unrestricted
c. Call_query - Unrestricted
d. Up - Restricted
e. Execute Query - Restricted
f. Message - Restricted
g. Exit_form - Restricted
h. Post - Restricted
i. Break - Unrestricted.

33. Can we use a restricted package procedure in ON-VALIDATE-FIELD Trigger ?
No.

34. What SYNCHRONIZE procedure does ?
It synchronizes the terminal screen with the internal state of the form.

35. What are the unrestricted procedures used to change the popup screen position during run time ?
Anchor-view
Resize -View
Move-View.

36. What Enter package procedure does ?
Enter Validate-data in the current validation unit.

37. What ERASE package procedure does ?
Erase removes an indicated global variable.

38. What is the difference between NAME_IN and COPY ?
Copy is package procedure and writes values into a field.
Name in is a package function and returns the contents of the variable to which you apply.

38. Identify package function from the following ?
1. Error-Code
2. Break
3. Call
4. Error-text
5. Form-failure
6. Form-fatal
7. Execute-query
8. Anchor_View
9. Message_code


1. Error_Code
2. Error_Text
3. Form_Failure
4. Form_Fatal
5. Message_Code

40. How does the command POST differs from COMMIT ?
Post writes data in the form to the database but does not perform database commit
Commit permanently writes data in the form to the database.

41. What the PAUSE package procedure does ?
Pause suspends processing until the operator presses a function key

42. What package procedure is used for calling another form ?
Call (E.g. Call(formname)

43. What package procedure used for invoke sql *plus from sql *forms ?
Host (E.g. Host (sqlplus))

44. Error_Code is a package procedure ?
a. True b. false
False.

45. EXIT_FORM is a restricted package procedure ?
a. True b. False
True.

46. When the form is running in DEBUG mode, If you want to examine the values of global variables and other form variables, What package procedure command you would use in your trigger text ?
Break.

SYSTEM VARIABLES

47. List the system variables related in Block and Field?
1. System.block_status
2. System.current_block
3. System.current_field
4. System.current_value
5. System.cursor_block
6. System.cursor_field
7. System.field_status.

48. What is the difference between system.current_field and system.cursor_field ?
1. System.current_field gives name of the field.
2. System.cursor_field gives name of the field with block name.

49. The value recorded in system.last_record variable is of type
a. Number
b. Boolean
c. Character.
b. Boolean.

50. What is an User Exits ?
A user exit is a subroutine which are written in programming languages using pro*C pro *Cobol , etc., that link into the SQL * forms executable.

51. What are the type of User Exits ?
ORACLE Precompliers user exits
OCI (ORACLE Call Interface)
Non-ORACEL user exits.

52. What do you mean by a page ?
Pages are collection of display information, such as constant text and graphics.

53. How many pages you can in a single form ?
Unlimited.

54. Two popup pages can appear on the screen at a time ?
a. True b. False
a. True.

55.What is the significance of PAGE 0 in forms 3.0 ?
Hide the fields for internal calculation.

56. Deleting a page removes information about all the fields in that page ?
a. True. b. False
a. True.

57. What do you mean by a pop-up window ?
Pop-up windows are screen areas that overlay all or a portion of the
display screen when a form is running.

58. What are the types of Pop-up window ?
the pop-up field editor
pop-up list of values
pop-up pages.

59. What is an Alert ?
An alert is window that appears in the middle of the screen overlaying a portion of the current display.