Hello friends if you are looking for Orcle Plsql Multiple choice questions | Orcle Plsql Objective type questions | Orcle Plsql Accenture Test | Orcle Plsql MCQ | Orcle Plsql Accenture TQ Here you will get the answers
1)You can overload subprograms if their formal parameters differ only in numeric data type.PL/SQL looks for matching numeric parameters in which order?
A) 1.PLS_INTEGER 2.NUMBER 3.BINARY_FLOAT 4.BINARY_4.DOUBLE
B) 1.BINARY_DOUBLE 2.BINARY_FLOAT 3.PLS_INTEGER 4.NUMBER
C) 1.NUMBER 2.PLS_INTEGER 3.BINARY_FLOAT 4.BINARY_DOUBLE
D) THere is no such ordernin which it looks
ANS-A
2)PL?SQL Exception message consists of which of the following parts?A. Type of Exception B.Error Code C.Error Message
A)Only A
B)Only B
C)Only A and C
D)A,B and C
ANS-D
3)How many IN arguments procedure DBMS_LOCK.SLEEP has?
A)0
B)1
C)2
D)It can have any number of arguments
ANS-B
4)What will be the output of the following code snippet?
DECLARE
Time TIMESTAMP(3);
BEGIN
Time:=’15-SEP-2013 07:48:53.275′;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(Time));
End;
A)15-SEP-13 07.48.53.275 AM
B)15-SEP-2013 07.48.53.275 AM
C)15-SEP-.
13 07.48.53.275
D)15-SEP-13 07.48.53.275 PM
ANS-A
5)Which of the following is considered as the best practice while designing trigger?
a)Use triggers to ensure that whenever a specific event occurs,any necessary actions are done(regardless of which us
er or application issues the triggering statement).
b)Do not create triggers that duplicate database features.
c)Do not create triggers that depend on the order in which a SQL statement processes rows(which can vary).
d)Use BEFORE row triggers to modify the row before writing the row data to risk.
e)Use AFTER row triggers to obtain the row ID and use it in operations.
f)Do not create recursive triggers.
g)Use DATABASE triggers judiciously.They fire every time any databases user initiates a triggering event.
A)a,b,c,d,e,f
B)a,b,c,d,e,f,g
C)a,c,d,e,f,g
D)c,d,e,f,g
E)a,b,c,d,f,g
ANS-B
6)Which of the following is false about parameterized cursors.
A)Parameters allow values to be passed to a cursor when it is opened and used within the query when it executes
B)These can be opened more than once in a block,by passing different sets of values each time
C)The datatype of the parameters are same as those for scalar variables,but are not given sizes and scales
D)The parameters are treated like PL/SQL,variables within the cursor’s query expression
E)None of the above
ANS-
7)Oracle error after installing or upgrading to a new databses that occurs when attempting to connect before Orcale has been started.This error states “Oracle not available
A)ORA-01034
B)ORA-01555
C)ORA-04031
D)ORA-12154
E)ORA-29516
ANS-A
8)Which of the functions are used in WHEN OTHERS exception handler
A)SQLCODE
B)SQLERRM
C)SQLERRNUM
D)ROWNUM
E)None of these
ANS-A and B
9)What is the output of the following code snippet?
DECLARE
SUBTYPE p_integer IS PLS_INTEGER RANGE-11..11;
y_axis p_integer;
PROCEDURE test(x_var IN p_integer)IS
BEGIN
DBMS_OUTPUT.PUT_LINE(x_var);
END test;
BEGIN
y_axis:=13;
test(12);
END.
A)Program executes successfully and prints MORE THAN TEN CHARACTERs
B)ORA-06501: PL/SQL:numeric or value error
C)program fails as NULL can be assigned to VARCHAR2
D)Program fails as NULL v_letter cannot be recognised inside procedure
ANS-B
10)Which of these is not a predefined PL/SQL Character Data Types?
A)NCHAR
B)NRAW
C)RAW
D)NVARCHAR
ANS-B
11)YOu have two triggers on the table.When you drop the table:A.ALLtriggers on a dropped table will remain in the system but would return an error when used. B.All triggers on a dropped table have been dropped as well.C Dropping a Trigger totally depends on whether the Trigger is on the statement or
a row level.
A)Only A
B)Only B
C)Both A and c
D)Both B and C
ANS-option B(Only B)
12)User-defined functions can be used in:A.Check constraints B.Indexes C.Virtual colums
A)only B
B)only C
C)Both B and C
D)BOth A and C
ANS-option C(Both B and C)
13)Ram developed the following code:What will be the output of above code?DECLARE
l_variable SIMPLE_DOUBLE: BEGIN
l_variable:=NULL;
dbms_output.put_line(‘Result is’||l_result);
END;
A)Program Executes Successful and displays Resukt is…..
B)Program fails as ther is a syntax error while assigning NULL
C)Program fails as there is no datatype like SIMPLE_DOUBLE
D)Program fails as SIMPLE_DOUBLE cannot hold NULL values
ANS-D
14)Which of following auditing types can be used as the simplest way to audit procedure Execution?A. Schema object auditing B.Priviledge auditing C.Statement auditing
A)Only A
B)Only B
C)Only C
D)Both B and C
ANS-Option C
15)To define independent block,you use which of the following Compiler directive pragma?
A)AUTONOMOUS_BLOCK
B)AUTONOMOUS_TRANSACTION
C)INDEPENDENT_BLOCK
D)AUTONOMOUS_STRING
ANS-B
16)Which system privilege enables someone to execute anyusers’s function in database?A.Execute any procedure BExecute any function C.Execute any unit
A)only A
B)only B
C)both B and C
D)Both A and B
ANS-option D
17)You have been asked to look employee table using LOCK TABLR statement in a specified lock mode so that to
allow concurrent access to a table;they prevent other users
from locking the entire table for exclusive use.Which type of lock you will use here?
A)LOCK TABLE employee IN ALLOW SHARE MODE NOWAIT;
B)LOCK TABLE employee IN TABLE SHARE MODE NOWAIT;
C)LOCK TABLE employee IN ROW SHARE MODE NOWAIT;
D)There is not such lock
ANS-C
18)What are database triggers?Please select all the correct answers
A)Set of PLSQL package
B)Set of PLSQL function
C)Set of code fires automatically after DDL operations performed
D)Set of code fires automatically after DML operations performed
E)Set of code fires automaticalyy before DML operations performed
ANS-B,C,D,E
19)Native Dynamic SQL has been integrated into the PL/SQL language by adding one new statement ,which executes a specified SQL statement immediately by enhancing the OPEN FOR statements
A)Open For
B)Execute Immediate
C)Execute Immediate and Open for
D)None of the above
E)All of the above
ANS-B
20)Which of the following is true about bind variables
A)Bind variables are also called non-PL/SQL variablesB)Bind variables are also calles PL/SQL variables
C)These variables are declared in the host environment such as SQL Plus
D)They can be referenced in PL/SQL statements,by prefixing the colon(:)before the variable name
E)They can be referenced in PL/SQL statements,by suffixing the colon(:)after the variable name
ANS-A,C,D
21)Which of the following is true about LOBs.A LOB can be
A)An attribute of a user-defined type
B)A column in a table
C)A bind or host variable
Ans-A,B and C
22)When you normalize a relation by breaking it into two similar relations,what must you do to maintain data integrity?
ANS-*Link the relations by a common field
*Create a primary key(s) for the new relation
23)In which of the following scenarios,you cannot overload subprograms
ANS-*You cannot overload two subprograms if thier formal parameters differ only in subtype and the different subt
ypes are based on types in the same family(…)
*Two functions that differ only in return type,even if the types are in different families
24)Which of these is a procedure(PL/SQL block) that contains the SQL statements and PL/SQL code to be executed?
ANS-Trigger Action
25)What will be the outcome of the following code?
DECLARE
BEGIN
NULL;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT>PUT_LINE
(Here is the code);
END;
ANS-COde will fail due to un-handled exception
26)TO handle error comditions(typically ORA messages)that have no predefined name,you must use which of the following?
ANS-EXCEPTION_INT
27)What will be the outcome?
DECLARE
marks NUMBER;
Subject NUMBER;
BEGIN
Marks:=100;
Subject:=4;
IF marks>30 THEN
marks:+35;
ELSIF subject<5 THEN
marks:=marks-2;
END IF;
CASE marks
WHEN 100 THEN DBMS_OUTPUT_LINE(‘EXCELLENT’);
WHEN 90 THEN DBMS_OUTPUT_LINE(‘Very Good’);
WHEN 80 THEN DBMS_OUTPUT_LINE(‘Good’);
WHEN 33 THEN DBMS_OUTPUT_LINE(‘Fair’);
WHEN 30 THEN DBMS_OUTPUT_LINE(‘Poor’);
END CASE;
ANS-Code fails due to exception RAISE CASE_NOT_FOUND
28)You have written a code which fails due system failure with disk storage or memory which has no relation……
ANS:- Handle named exceptions whenever possible, instead of using when others in exception handlers.
29)What can cause a high value for recursive calls?
ANS:- 1)A high value for recursive calls is cause by improper cursor usage
2)Due to excessive dynamic space management actions
3)Due to excessive statement re-parses.
30)The technology applies the resources of many computers in a network to a single problem at the same time-ususally to scientific or technical problem…..
ANS:-Grid computing
31)What type of index is recommended for a materialzed view containing only joins…
ANS:-An index on the Rowid’s of each of the tables used in the join.
32)Gaps in sequence values can occur when?
ANS:-1)A rollback occurs
2)The system crashes
3)A Sequence is used in another table
33)Complete the following and choose the correct option
PACKAGE pich_pkgu
iS
my_flag BOOLEAN = TRUE
END;
which of the following will display true
ANS;- “BEGIN
DBMS_OUTPUT_LINE(case WHEN pich_pkg.my_flag THEN ‘TRUE’ END);
END;”
34)Which of the following is true about functions when called from SQL statements
ANS:-1)When called from INSERT,UPDATE or DELETE statement function cannot query or modify any databas
e tables modified by that statement
2)When called from a SELECT,INSERT,UPDATE or DELETE statement the function cannot execute SQL tra
nsaction control statements………………
35)Which of the following data dictionary views can you use to get dynamic statastical information about queues?
ANS:-ALL_QUEUES
36)Explain Plan help developers which of the following ways
ANS:-1)Ensure that the the tables are joined in optimal order
2)Determine the most restrictive indexes to fetch the rows
3)Determine the Best internal join method
4)Determine that the SQL is executing the steps in the optimal order.
37)Which of the following is a restriction or cursor variable
ANS :- a)you cannot declare cursor variables in apackage specification
b)You cannot use comparison operators to test cursor variables for equality,inequality,nullity.
38)When the database detects an event the Trigger mechanism executes the action specified in the trigger.The action
can include publishing the event to a queue………
ANS:-DBMS_AQ_package
39)By default an anonymus block in which type of unit in terms of AUTHID Clauses?
Ans;-IR Unit (invoker)
40)Which of these Triggers can be defined only on views and not on table ?
ANS;-INSTEAD of
41)ACE’s are evaluated in the order they appear in the ACL.which of the following is the outcome of evaluating in access control entry
ANS:- 1)The application privilege is granted
2)The application privelege is denied
42)When should you use oracle external tables VS Oracle SQL “Loader
ANS:-1)To transfer the data as it is being loaded into the database
2)To Load very larger files with large quantities of data
43)Which of the following is true about Real Application Security?
ANS:-1)Supports declaritive security policies
2)Enables end-to-end security for multitier applications resources3)Provides an integrated solution to secure database and application resources
4)Advances the security architecture of oracle database to meet existing and emerging demands……..
44)What are all the parameters recquired to execute DBMS_MVIEWESTIMATE_MVIEW_SIZE?
ANS;-1)stmt_id and select_clause in VARCHAR@ data type
2)NUM_rows and num_bytes as number data type
3)stmt_id and select_clause will be IN clause
45)which of the following is true about Pl SQL exceptions
ANS;-1)you cannot declare an exception twice in the same block
2) you can declare the same exception in two different blocks
3)exceptions declared in a block are considered local to the block and global to all it’s sub blocksQ. package can be altered using A.ALTER B.CREATE C.SYS built-in packages