MCQ: Procedures

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
Answer: D

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.
Answer: C

Declare a number := 5; b number := null; c number := 10; Begin if a > b AND a ( 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
Answer: A

The default parameter type in stored procedures is
(a) IN
(b) OUT
(c) INOUT
(d) There is no default type
Answer: A

Declare fvar number := null; svar number := 5 Begin goto > if fvar is null then > 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
Answer: A

MCQ: SQL data types

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
Answer: B

PL/SQL supports datatype(s)
A. Scalar datatype
B. Composite datatype
C. All of the above
D. None of the above
Answer:  C

Find the ODD datatype out
A. VARCHAR2
B. RECORD
C. BOOLEAN
D. RAW
Answer: B

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
Answer: A

SYSTEM.EFFECTIVE.DATE varaible is
A. Read only
B. Read & Write
C. Write only
D. None of the above
Answer: C

The CHAR and NCHAR data types
(a) have no difference
(b) belong to two different character sets
(c) have different storage requirements
(d) have different storage requirements and retrieval time
ANSWER: B

The wild card character % used in strings meAnswer:
(a) any number of characters
(b) one or more characters
(c) at least one character
(d) at least one alphabet
ANSWER: A

The maximum length of varchar2 when used in PL/SQL block is
A. 16k
B. 32k
C. 4k
D. Either b or c
ANSWER: B

Any value added to NULL gives
A. the value itself.
B. 0
C. NULL
D. Unpredictable
ANSWER: C

MCQ: Cursors

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
Answer: D

Is it possible to open a cursor which is in a Package in another procedure ?
A. True
B. False
Answer: A

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
Answer: B

For reading through row by row,we use
A. Trigger
B. Stored procedure
C. cursor
D. Function
ANSWER: C

Which of the following is not a feature of a CURSOR FOR loop?
A. Record type declaration
B. Opening and parsing of SQL statements
C. Fetches records from cursor
D. Requires exit condition to be defined
ANSWER: D

Which line in the following statement will produce an error?
A. cursor action_cursor is
B. select name, rate, action
C. into action_record
D. from action_table;
ANSWER: C

Which of the following statements is true about implicit cursors?
A. Implicit cursors are used for SQL statements that are not named.
B. Developers should use implicit cursors with great care.
C. Implicit cursors are used in cursor for loops to handle data processing.
D. Implicit cursors are no longer a feature in Oracle.
ANSWER: A

The command used to open a CURSOR FOR loop is
A. open
B. fetch
C. parse
D. None, cursor for loops handle cursor opening implicitly.
ANSWER: D

After executing an UPDATE statement, the developer codes a PL/SQL block to perform an operation based on SQL%ROWCOUNT. What data is returned by the SQL%ROWCOUNT operation?
A. A Boolean value representing the success or failure of the update
B. A numeric value representing the number of rows updated
C. A VARCHAR2 value identifying the name of the table updated
D. A LONG value containing all data from the table
ANSWER: B