PL SQL Question Answers

Hello friends in this post we are going to discuss about PL SQL Multiple choice questions | PL SQL Objective type questions | PL SQL MCQ with answers | PL SQL Wipro Myskillz trendnxt Answers | PL SQL Question answers

If you are looking for more Dumps for MYSKILLZ Visit Here

How many types of Scrollable Cursors are there in PL/SQL	1	2	3	ANS)4
How to control how many cursors are open in an session	ANS)Set OPEN_CURSORS parameter in initialization parameters	There is no absolute limit to the total number of cursors one session can have open at one time	Set SESSION_CURSORS parameter in initialization Parameter	Set SESSION_OPEN_CURSORS parameter in the initialization parameter
What is the default Value for OPEN_CURSORS parameter for a session	ANS)50	25	65535	0
Select the Wrong Statements from the following:
1) In PL/SQL, a cursor is a name assigned to a specific private SQL area for a specific SQL statement.	2,4	2,3,4	ANS)2,3	1,2,5
2) For static Cursors,SQL statement is determined at run time
3) The cursor attribute %FOUND behaves same for both implicit Cursors and explicit cursors
4) Explicit cursor declarations can contain a RETURN clause in place of the SELECT statement.
5) Whenever a SQL statement is directly in the execution or exception section of a PL/SQL block, you are working with implicit cursors.
When a user process fails, what background process cleans up after it?	ANS)PMON	SMON	The Job Queue Processes.

What background process refreshes materialized views?	PMON	SMON	ANS)The Job Queue Processes.
Name a tablespace automatically created when you create a database.	ANS)SYSTEM tablespace	Temporary tablespace	Permanent Tablespace
How can you enable a trace for a session	1	2	ANS)1 & 2	2,3
1) DBMS_SESSION.SET_SQL_TRACE;
2) ALTER SESSION SET SQL_TRACE = TRUE;
3) FND_SESSION.SET_SQL_TRACE;
You have created the following database link: CREATE DATABASE LINK db_link2 CONNECT TO scott IDENTIFIED BY tiger USING ‘sales’; You attempt to update the EMP_SAL table by issuing the following SQL command: UPDATE emp_sal@db_link2 SET BALANCE = BALANCE + 100 WHERE emp_id=1001; You receive the following error message: ORA-01031: insufficient privileges ORA-02063: preceding line from DB_LINK2 Which of the following reasons might explain your inability to update the EMP_SAL table in the remote database?	You do not have the UPDATE object privilege on the EMP_SAL table.	ANS) Scott does not have the UPDATE object privilege on the EMP_SAL table	The above statement has a syntax error.
You cannot grant privileges on remote objects to other users.	ANS)TRUE	FALSE
Which of the following operations cannot be performed using database links to a remote database.
A) Grant privileges on remote objects	ANS)A & D	A,B,D	A,C	All of the above
B) Execute DESCRIBE operations on remote procedure
C) To perform DML operations on the remote data
D) Execute DESCRIBE operation on a remote package
An —————- is a user-defined composite datatype representing a data structure and functions and procedures to manipulate the data.	ANS)Object type	Attribute	Method	Object attribute
What is the Basic PL/SQL Code Structure?	Declare(mandatory) Begin (mandatory) …… sql stmts ……plsql stmts Exception(mandatory) End; (mandatory)	Declare(mandatory) Begin (mandatory) …… sql stmts ……plsql stmts Exception(optional) End; (mandatory)	Declare (optinal) Begin (mandatory) …… sql stmts ……plsql stmts Excecution (optional) End; (mandatory)	ANS)Declare (optional) Begin (mandatory) …… sql stmts ……plsql stmts Exception (Optional) End; (mandatory)
Which statement is wrong?	Variable must be declared before the usage	Only TRUE or FALSE can be assigned to BOOLEAN type of variable	Users can customize the variable types by using TYPE … IS … statement.	ANS)Variable Declaration is done in the Begin section of pl/sql code structure
Which Statement is Wrong about Functions in plsql?	A functions must returns a value.	Function can be called as a part of a expression	ANS)A Fucntion is is a standalone executable statement in PL/SQL	A function will returns a Scalar value and must have a return statement in the Begin section
The Procedue Body must have at least one executable statement after the BEGIN and before the END or EXCEPTION keywords Is this statement true or false.	ANS)TRUE	FALSE
Which statement is true regarding anonymous block	Anonymous blocks don’t have names under which they can be stored in the database	By using block labels, you can give a name to your block for the duration of its execution, using PL/SQL labels .	Anonymous blocks Cannot use CURSORS	ANS)Option 1 & Option 2
Which is the PL/SQL procedure that are associated with tables and are called whenever certain modifications(events) occurs.	ANS)Triggers	Functions	Packages	collections
The pl/sql block in the structure of atrigger is a usual code block where you can place pl/sql commands excepts COMMIT or ROLLBACK	FALSE	ANS)TRUE
The PERSON table contains 5 records.An update trigger is created on the table as below CREATE OR REPLACE TRIGGER PERSON_TRIGGER BEFORE UPDATE ON PERSON BEGIN DBMS_OUTPUT.PUT_LINE(‘BEFORE UPDATING PERSON(S)’); END; What will be the result of below update statement on the table? Update PERSON SET DOB = SYSDATE;	ANS)BEFORE UPDATING SOME PERSON(S) 5 Rows Updated	BEFORE UPDATING SOME PERSON(S) BEFORE UPDATING SOME PERSON(S) BEFORE UPDATING SOME PERSON(S) BEFORE UPDATING SOME PERSON(S) BEFORE UPDATING SOME PERSON(S) 5 Rows Updated	Compilation Error	Runtime Error
What is the syntax to drop a trigger?	DROP trigger_name;	ANS)DROP TRIGGER trigger_name;	DROP TRIGGER trigger_name ON table_name;	DROP trigger_name ON table_name;
is a database structure that provides quick lookup of data in a column or columns of a table.	PRIMARY KEYS	UNIQUE KEY	ANS)INDEX	Option 1 & Option 2
A —- is a specific representation of data from one or more tables	Synonym	ANS)Views	collections	Recordset
Maximum number of columns that can be defined in a view	1000	512	same as for a table	ANS)Option 1,3

The view will become invalid whenever the base table is altered	ANS)TRUE	FALSE
what is true about inline views	Inline view is a subquery that appears in the from clause of the select statement	Inline view is enclosed in parenthesis and may be given an alias name.	The columns selected in the inline view can be referenced in the parent query	ANS)All of the above
For a view to be inherently updatable,which of the following conditions must be met?	Each column in the view must map to a column of a single table	if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions	the select statement should not contains a DISTINCT operator	ANS)All of the above
What are the restrictions on BEFORE trigger	You cannot specify a BEFORE trigger on a view or an object view	You cannot perform DML with BEFORE trigger	You can write to the :NEW value but not to the :OLD value.	ANS)Option 1,3
What are the restrictions on AFTER trigger	You cannot specify an AFTER trigger on a view or an object view	You cannot write either the :OLD or the :NEW value.	ANS)Option 1,2
What are the restrictions on INSTEAD OF triggers	INSTEAD OF triggers are valid only for views.	You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value.	You cannot specify UPDATE OF for an INSTEAD OF trigger	ANS)all of the above
How to know the last executed procedure?	ANS)Select timestamps,owner,obj_name from dba_audit_trial;	Select timestamps,owner,obj_name from dba_objects;	Select timestamps,owner,obj_name from dba_users;	Select timestamps,owner,obj_name from all_objects
What is mutating table error?	Occurs whena trigger is disabled	This occurs when a table is updated	Occurs when a view is deleted	ANS)This occurs when a trigger trys to update a row it is currently using
Which is not a oracle package?	ANS)DBMS_OUT	DBMS_PIPE	DBMS_JOB	UTL_FILE
The —– returns the actual error message for the last error encountered	SQLERROR	SQLCODE	SQLMSG	ANS)SQLERRM
A table can be created through a procedure	TRUE	FALSE
How we can create a table through procedure ?	EXECUTE SQL ‘CREATE TABLE table_name AS query’;	ANS)EXECUTE IMMEDIATE ‘CREATE TABLE table_name AS query’;	CREATE TABLE table_name AS query;	A table cannot created through a procdure
Can we use a subquery in FOR clause?	ANS)Yes, Instead of a cursor we can use subquery in the FOR clause	No, We cannot use subquery in FOR clause
How to disable multiple triggers on a table at a time?	ALTER TABLE table_name DISABLE;	ANS)ALTER TABLE table_name DISABLE ALL TRIGGERS;	DROP TRIGGER trigger1,trigger2,…,triggern ON table_name;	ALTER TRIGGER trigger1,trigger2,…triggern ON table_name DISABLE;
Cursor variable cannot be declared in a package specification	ANS)TRUE	FALSE
What are the restrictions on Cursor variable?	Cannot assign NULL to curosr variable	Cannot use comparision operators to test cursor variable for equality,inequality or nullity	Database columns cannot store the values of cursor variable.	ANS)all of the above
What are the cursor attribute used in PL/SQl?	%ROWCOUNT	%CURSOROPEN	%NOTFOUND	ANS)option,1,3
What are the components of PL/SQL blocks?	Declarative Part	Definition Part	Execution part	ANS)Option 1,3
_______provides the record type that represents a entire row of a table or view or columns selected in the cursor	ANS)%ROWTYPE	%TYPE	%DATATYPE	%ROWCOUNT
What will happen after the execution of COMMIT statement?	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.	After commit statements all changes will be stored permanently in the database.	ANS)all of these
Which all are the part of database trigger?	Triggering event	Triggering action	A trigger constraint	ANS)Option 1,2,3
Which is not a triggering event	INSERT	DELETE	UPDATE	ANS)none of these
Triggering Constraint is specified using _______ clause	ANS)WHEN	FOR EACH ROW	PER STATEMENT	all of these
Which all are the different types of database trigger?	Row-level triggers	Statement level triggers	instead of triggers	ANS)all of these
Is it possible to use transaction control statements in database triggers?	ANS)NO	YES
Which of the follwing statements are true ?	For triggers related to DELETE only OLD.column_name values only available.	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.	ans)All of the above
What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?	ANS)Mutation of tables occurs	Trigger is executed successfully	the table get locked	Will get an error message saying ” table is locked for updation by another user”
Which are the plsql statement used in Cursor processing?	CURSOR Cursor_name	OPEN Cursor name	FETCH .. INTO …	ans)all of these
Name the tables where characteristics of Package, procedure and functions are stored ?	User_objects	user_source	user_error	ANS)all of these
The _____________ tells the compiler to assosiate an exception with an oracle error.	ANS)PRAGMA_EXCEPTION_INIT	EXCEPTION	Raise_application_error	TOO_MANY_ROWS
is a procedure of package DBMS which allows to issue user_defined error message	PRAGMA_EXCEPTION_INIT	EXCEPTION	ANS)Raise_application_error	TOO_MANY_ROWS
What are the modes of parameters that can be passed to a procedure ?	IN	OUT	IN-OUT	ANS)ALL of these
What is the difference in calling functions and procedure in a PL/SQL block	Procudute is called as a part of an expression . Functions are called as a plsql statement	ANS)Procedure is called as plsql statement.Function is called as a part of an expression	Both procedure and functions are called a apart of the expression	Both procedure and functions are called as plsql statement
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification	Cursor defined in package specification is global	Cursor defined in a procedure is local to that procedure	Cursor defined in the package specification must have a RETURN type.	All of these
How to call packaged procedure from SQL *PLUS	EXCECUTE package_name.procedure,if procedure doesnot have any in,in-out p[arameters.	Variable a NUMBER; EXCECUTE package_name.procuder(:a); , if the procedure have a out or inout parameter of type NUMBER	EXCECUTE package_name.procuder(1); , if the procedure have a IN parameter of type NUMBER	ANS)All of these
How to call packaged function from SQL *PLUS	Execute package_name.function;	Return package_name.function	RUN Package_name.function	ANS)Function cannot be called from SQL *PLUS
Which one is not a named system exception?	NO_DATA_FOUND	DUP_VAL_ON_INDEX	TRANSACTION_TIMED_OUT	ANS)NOT_VALID_NUMBER
Which is not a pseudocolumns?	ROWNUM	ROWID	NEXTVAL	ANS)ROWCOUNT
What is a nested subquery?	It’s a subquery in the FROM clause with an alias that can be used as a view within the SQL statement	ANS)The subquery used in WHERE clause	The subquery used in WHERE clause and it refers to any column in the parent statement
What is a correlated subquery?	It’s a subquery in the FROM clause with an alias that can be used as a view within the SQL statement	The subquery used in WHERE clause	ANS)The subquery used in WHERE clause and it refers to any column in the parent statement
Why use Truncate over Delete while deleting all rows ?	When truncate is used, Triggers are not fired	It deallocates space	Truncate will deletes the table structure also.	option 1,2
What best describes the relationship between indexes and SQL performance?	Indexes are only used in special cases	Indexes are used to make table storage more efficient	ANS)Indexes exist solely to improve query speed	Indexes rarely make a difference in SQL performance
Which of the following is an important feature of relational databases and SQL?	ANS)Independence of table relationships	High speed of SQL	Powerful GUI front-end	Easy to install and use
Which of the following is an important consideration when tuning an SQL statement?	The number of CPUs on the server	The degree of parallelism on the tables	The use of bitmap indexes	ANS)The quality of the SQL optimization
Which is a major problem with SQL?	SQL cannot support object-orientation	SQL syntax is too difficult for non-computer professionals to use	ANS)The same query can be written in many ways, each with vastly different execution plans	SQL creates excessive locks within the Oracle database
Which statement is correct?	GRANT UPDATE ON CUSTOMER (title,address) TO ANDREW;	ANS)GRANT UPDATE (title,address) ON CUSTOMER TO ANDREW;	GRANT UPDATE ON customer COLUMNS(title,addres) TO Adrew	GRANT UPDATE ON customer.title,customer.address TO Adrew;
Which of the following statements about a role are true 1)Privileges are assigned to a role using the grant stmt. 2) A role can comprise a maximum of 100 privileges 3) A role is a named group of related privileges,which can only be assigned to users. 4 )A role can be assigned to maximum of 1000 users 5) A user can be assigned several roles, and a single role can be assigned toseveral users. 6) A roel is created using create role statement.	Option 1,2,3	Option 2,5,6	ANS)options 1,5,6	options 1,6,4
A user can be assigned several roles and a single role can be granted to several users	ANS)TRUE	FALSE
What are the different types of privileges	Object privilege	Role privilege	System privilege	ANS)All of these
MERGE command is a combination of?	ANS)INSERT & UPDATE	INSERT & DELETE	UPDATE & DELETE	None of these
You have created a function named My_fun in the data base. Which of the following data dictionary views will be used to see the names of the procedures and functions that invoke the My_fun function	USER_DB_LINKS	ALL_DEPENDENCIES	USER_DEPENDENCIES	ANS)Option 2,3
The database contains a package named my_pack.you wast to delete the package specification from the database while retaining the package body.which of the following statement will accomplish this?	ANS)You cannot accomplish this	DROP package_specification My_pack;	DROP PACKAGE My_pack	DROP package Body My_pack;
Database Contains a table audit_tab.You require to create a trigger at the database level to ensure that whenever a user logs on the database, his name and current dates are inserted into audit_tab. CREATE OR REPLACE TRIGGER aud_trigger BEFORE LOGON DATABASE BEGIN INSERT INTO audit_tab VALUES (USER,SYSDATE ); END; Which statement is true about aud_trigger?	ANS)It’s a invalid trigger	It’s a DDL Trigger	It is a system event trigger	It is an application trigger
CREATE OR REPLACE TRIGGER audit_trigger AFTER INSERT ON Employee BEGIN INSERT INTO employee_audit VALUES(emp_id,:NEW.last_name,:NEW.first_name); END; What will happen when this statement is executed?	The trigger wil created successfully	The statement will fail because the NEW qualifier cannot be prefixed with colon.	ANS)The statement will fail because the NEW qualifier cannot be used in a statement trigger	The statement will fail because the NEW qualifier cannot be used in an AFTER trigger.
Which of the following statement about LOB are true?	LOB can be stored inside or outside a datasase	LOB represnts a datatype used to store large,unstructured data	Internal LOB is a category of LOB	ANS)all of these
How to display row number with the records	Using ROWID in the select statement	ANS)using ROWNUM in the select statement	Using Sequence	Using ROWCOUNT in select statement
How to display a number value in words where vriable sal is of type number?	ANS)TO_CHAR ( TO_DATE(sal,’j’) , ‘JSP’ )	TO_CHAR(sal)	TO_CHAR(sal,’WORDS’)	TO_DATE(TO_CHAR(sal,’JSP’),’j’)
What is maximum no of triggers applied to a single table	100	10	ANS)12	50
What does the below statement does? select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);	ANS)Dispalys odd number of records	Give runtime error	Get execution error	Display all records in table emp.
Which date function returns a number value	ADD_MONTHS	ANS)MONTHS_BETWEEN	NEXT_DAY	ROUND
Omitting the WHERE clause of an UPDATE or DELETE statement has what effect?	ANS)All rows in the specified table are affected	The query returns an error because the WHERE clause is required.	Only the current row is affected.	Only the rows with the correct data type are affected
Referential integrity refers to what?	The enforced uniqueness of a row in a table	The enforced uniqueness of a column in a table	Ensuring that a Foreign Key attribute cannot be NULL	ANS)The enforced synchronization of Primary Key and Foreign Key values
What statement could you use in the WHERE clause to select all the rows in a table where no price is defined	WHERE price IS NOT NULL	WHERE price IS < 0	ANS)WHERE price IS NULL	WHERE price != 0
When using an INSERT statement, data to be inserted may come from which of the following?	The VALUES clause	A stored procedure	A SELECT statement
Which of the following is not a aggregate functions?	SUM	ANS)ABS	COUNT	MAX
What is the lifetime of a user-defined variable?	ANS)Duration of the script	Duration of the statement block	Duration of the batch	Duration of the current SQL Server connection
Which of the following commands can be used to interrupt processing of a While loop?	BREAK	HALT	ANS)EXIT	CONTINUE
What are the space requirements for views?	Views require as much space as all the tables they are created from.	Views require as much space as would a table that presented the same data.	ANS)Views require the space used to store a few records in various system tables.	Views do not store data and therefore use no space.
Which of the following best describes a situation in which the statistics for a table would be misleading	Random rows in the table have been deleted	Deleting all of the records of a specific gender	ANS)Dropping Foreign Key constraints on the table	Dropping indexes on a table
what is the maximum size of the message that we can give in dbms_output.putline();	ANS)the maximum output is determined by the size you specified in dbms_output.enable(<size>).	255bytes	1000 bytes	2000bytes
How can we retrive multiple rows in PLSQL	BULK COLLLECT	REF CURSOR	CURSOR	ANS)all of these
Can you add not null column to a table already containing data ?	ANS)Yes,we can add a not null constraint to a column, but already existing row should have a data for that column.	No	Yes, We can add a not null column but it should be defaulted to some value.
A function can return more than one value. Is this statement true?	NO	ANS)YES, function can return more than one value using ref cursor
Which statement is true about ‘TABLE OF’ in PL/SQL	ANS)Table of is use to define a collection type.	A variable declared based on TABLE OF data type is called cursor variable.	TABLE OF is dynamic cursor in which the contents of cursor can be changed dynamically at run time depending upon our requirement.
What is difference between %ROWTYPE and TYPE RECORD?	No difference	ANS)% 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.	TYPE rec RECORD is to be used whenever query returns a entire row of a table or view. % ROWTYPE is to be used whenever query returns columns of different table or views and variables.


The following are Oracle supplied packages	DBMS_OUTPUT	DBMS_SQL	UTL_FILE	ans) All of the above
PLSQL is referred to as a	ans) block structured language	unstructed langugage	SQL language	ANSI language
PLSQL block can contain the following	variable declarations	program code	other PL/SQL blocks	ans) All of the above
A PLSQL block cannot contain the below	other PL/SQL blocks	error handlers	functions	ans) none
What is the use of DBMS_OUTPUT	To handle errors	ans) to display the output	used as an exception handler	to create an output file
Which one of these is not a valid privelege	CREATE TYPE	CREATE TRIGGER	ans) CREATE FILE	CREATE TABLE
Every PLSQL statement is followed by a	delimeter	quote	ans) semi colon	comma
PL/SQL commands are case sesitive	Yes	ans) No	depends on the OS	defined at the database parameter
PLSQL blocks can be nested	ans) Yes	No	Maximum limit 10	only for packages
Which of these is not a PLSQL keyword	BEGIN	END	DECLARE	ans) DATE
Which symbol is used in assignment statement	=	ans) :=	==	.=
How many rows the table DUAL has	No rows	Multiple	ans) one row	DBA creates the table and inserts rows as needed
VARCHAR2 data type is used to store	ans) Variable length character strings	Character string with 2 bytes size	Any data type can be stored	Used to limit at the maximum 2K size data
CHAR data type is used to store	Character data types	ans) Fixed length character strings	char data types	Not a valid data type
RTRIM function is used to	elimiate leading spaces	ans) eliminate trailing spaces	to trim a string to a given length	not a valid function
LTRIM function is used to	ans) elimiate leading spaces	eliminate trailing spaces	to trim a string to a given length	not a valid function
TRIM function is used to	elimiate leading spaces	eliminate trailing spaces	to trim a string to a given length	ans) eliminate both leading and trailing spaces
BINARY_INTEGER variables are stored using	same as number data type	ans) binary format	Integer format	Oracle database format
Among NUMBER and BINARY_INTEGER data types which one takes less space	NUMBER	BINARY_INTEGER	ans) BINARY_INTEGER	Same
Which exception is raised when we assign the wrong type of value to a variable	ILLEGAL	WRONG_DATA_TYPE	UNHANDLED	ans) VALUE_ERROR
The syntax for declaring a procedure	PROCEDURE <name> BEGIN <program code> END	PROCEDURE <name> IS BEGIN <program code> END	PROCEDURE <name> AS BEGIN <program code> END	ans) B & C
The syntax for declaring a function	FUNCTION <name> RETURN <datatype> DECLARE BEGIN <program code> END	ans) FUNCTION <name> RETURN <datatype> IS BEGIN <program code> END	FUNCTION <name> AS RETURN <datatype> DECLARE BEGIN <program code> END	FUNCTION <name> RETURN <datatype> AS DECLARE BEGIN <program code> END
Functional can have an OUT variable	ans) No	Yes	Declaure an input variable as INOUT	OUT variable of NUMBER type is allowed
Which of the does not return a value	FUNCTION	PROCEDURE	PACKAGE	ans) B&C
What can you do to ignore the time portion of a DATE variable	ans) TRUNC	TRIM	SUBSTR	A&D
Which functions are useful when comparing case-insenitive string comparisons	LOWER	UPPER	ans) A & B	BETWEEN
Operators of the same precedence level are evaluated	RIGHT to LEFT	ans) LEFT to RIGHT	RANDOM	RUNTIME
Use of parentheses in complexe expressions	is necessary	not permitted	ans) to make the intended order of evaluation clear	is aesthetic
Comparison of NULL with any other value evaluates	ans) TRUE	FALSE	NO ROWS FOUND	RAISES syntax eror
Expression TRUE OR FALSE evaluates	FALSE	ans) TRUE	NO ROWS FOUND	RAISES syntax eror
IS NULL operator is used	ans) when checking for NULL values	not a valid command	when checking for an empty string	is equal to comparing with ”
Which of these are conversion functions	TO_DATE	TO_NUMBER	TO_CHAR	ans) All of the above
Converts either a number or a date to a character string	ans) TO_CHAR	TO_DATE	TO_NUMBER	TO_VARCHAR
Expression TRUE AND NULL evaluates to	TRUE	FALSE	ans) NULL	RAISES syntax eror
Expression TRUE OR NULL evaluates to	ans) TRUE	FALSE	NULL	RAISES syntax eror
Condition X IN (1,2,3) is equallent to	X=1 AND X=2 AND X=3	X BETWEEN 1 AND 3	ans) X=1 OR X=2 OR X=3	X >= 1 AND X<=3
Which is the right way of dealing with NULL values	x=NULL	ans) x IS NULL	x=”	x=’ ‘
TRUNC(SYSDATE,’YYYY’) gives	ans) year	last two digits of the year	Removes the time portion	Not a valid command
TO_CHAR(SYSDATE,’Mon’) results if the sysdate is 12-JAN-2009	JAN	ans) Jan	JANUARY	09-Jan
Which operator needs only one operand	binary	ans) unary	Logical	Arithmetic
Which is the right syntax for IF statement	ans) IF <condition> THEN <program_code> ELSE <program_code> END IF;	IF <condition> THEN <program_code> ELSE <program_code> END;	IF <condition> <program_code> ELSE <program_code> END;	IF <condition> <program_code> ELSEIF <program_code> END;
The syntax for Loops	FOR loop_index low_value..high_value LOOP <program_code END LOOP;	ans) FOR loop_index IN low_value..high_value LOOP <program_code END LOOP;	FOR loop_index IN high_value..low_value LOOP <program_code END LOOP;	FOR loop_index IN high_value..low_value LOOP <program_code END;
Does Oracle allow you to create your own functions	ans) Yes	No
Can you call functions from any PL/SQL code	ans) Yes	No
Syntax for GO TO statement	GO TO lable_name	ans) GOTO label_name	GOTO is not a valid statement	GO TO Exit
Syntax for While Loops	WHILE <condition is TRUE> LOOP <statements> END LOOP;	WHILE <condition is TRUE> LOOP <statements> ENDLOOP;	WHILE <condition is FALSE> LOOP <statements> ENDLOOP;	LOOP WHILE <condition is TRUE> <statements> ENDLOOP;
How many times must the WHILE loop execute	Atleast once	ans) might never have to execute	More than once	While loop does not exists in Oracle as in JAVA
Which command is used to exit from a simple LOOP	EXIT	EXIT WHEN	ans) A & B	Not needed
Can you potentially write a WHILE loop that never ends	FALSE	ans) TRUE	WHILE loop always ends	gives syntax error
What statement(s) allow you to abort the processing of a loop	EXIT	EXIT WHEN	ans) A & B	Not needed
A Procedure is a	ans) logically grouped set of SQL and PL/SQL statements that perform a specific task	logically grouped set of database functions that perform a specific task	logically grouped set of stored events that perform a specific task	logically grouped set of SQL statements that perform a specific task
A stored procedure is	ans) stored in the database and compiled	stored in the database as uncomiled	compilation is performed during run time
How do you drop a procedure	using a replace command	ans) using drop procedure command	using remove procedure command	using del procedure command
What is a recursive procedure	a procedure that goes into infinite loop	a procedure that always returns 1	ans) a procedure that calls itself	a procedure that does not have a return statement
How do you recompile a package	ALTER PACKGE <name> compile body	ALTER PACKGE <name> compile package	ans) A & B
Which of these are predefined exceptions	no_data_found	too_many_rows	invalid_cursor	ans) All of the above
Which of the below is a DML statement	INSERT	UPDATE	DELETE	ans) All of the above
Which of the below is a DDL statement	COMMIT	ROLLBACK	CREATE	ans) All of the above
DDL statements means	ans) Data definition language	Data dictionary language	Data driven language
Which statement will implicitly issue a commit	DELETE	INSERT	ans) TRUNCATE	ROLLBACK
PL/SQL table functions	COUNT	EXISTS	FIRST	ans) All of the above
PL/SQL table functions	last	next	prior	ans) All of the above
What is the variable attribute you use to base a variable on a table column	ans) %type	%rowtype	%recordtype	%table of
What is a PLSQL record	It is a dynamic variable	ans) variable with the type record	It is a variable of type table	variable with fixed number of columns
Which of these are Pseudo columns	ROWNUM	NEXTVAL	ROWID	ans) All of the above
Name a few implicit cursor attributes	%FOUND	%ROWCOUNT	%NOTFOUND	ans) All of the above
What are the types of cursors	implict	explicit	ans) A&B	defined
Which attribute tells the cursor is already in use	ans) isopen	%found	%rowcount	%open
Which attribute can tell the number of rows fetched so far for the cursor	%count	ans)%rowcount	%recordcount	%total
Triggers are fired in response to the following events	A row is inserted into a table	A row in a table is updated	A row in a table is deleted	ans) All of the above
The following are database privileges	CREATE PROCEDURE	CREATE REDOFILES	CREATE SESSION	ans) A&C
Keyword DECLARE is used while defining	ans) anonymous block	procedure	functions	Packages
Why do we type / at the end of PLSQL block	to compile the code	does not have any meaning	ans) tells SQL & PLUS that we are done typing PLSQL code	used as a standard, semicolon can be used instead
Which command is used to display a line of text	ans) DBMS_OUTPUT.PUT_LINE	DBMS_OUTPUT.GET_LINE	UTL_FILE.WRITE	UTL_FILE.FOPEN
Where does PL/SQL code execution take place	Server	Client	ans) A & B
Oracle products such as Oracle Forms have the capability to execute PL/SQL code at client side	No	ans) Yes
Which of these is a fundamental unit of PL/SQL programming	variable	function	declare section	ans) block
Which of the below is not a PL/SQL data type	VARCHAR2	NUMBER	PLS_INTEGER	ans) INT
What is the maximum size limit allowed for VARCHAR2 variable in PL/SQL block	ans) 32767	2000	4000	No limit
Number data type is used for declaring	fixed point numbers	floading pointing number	ans) Both	None
Number data type allows maximum precision of	There is no limit	108 demimal digits	1008 decimal digits	ans) 38 decimal digits
Precision in number data type specifies	ans) how many digits are used to represent the number	where the decimal point is	the size and number of decimal points	none
Scale in number data type specifies	how many digits are used to represent the number	ans) where the decimal point is	the size and number of decimal points	none
BINARY_INTEGER datatype is used for declaring	ans) signed integer variables	unsigned integer variables	ans) both	none
Which data type is used to store strings of byte-oriennted data	LONG	ans) RAW	VARCHAR2	TYPE
Size of RAW data type in PL/SQL	ans) 32767	255	block size	2000
Syntax for database triggers	CREATE OR REPLACE TRIGGER <trigger_name> {BEFORE\|AFTER} verb_list ON <table_name> [FOR EACH ROW] <variable list> BEGIN <program_code> END	CREATE OR REPLACE <trigger_name> {BEFORE\|AFTER} verb_list ON <table_name> [FOR EACH ROW] <variable list> BEGIN <program_code> END	ans) CREATE OR REPLACE TRIGGER <trigger_name> {BEFORE\|AFTER} verb_list ON <table_name> [FOR EACH ROW] DECLARE <variable list> BEGIN <program_code> END	ans) CREATE OR REPLACE TRIGGER <trigger_name> {BEFORE\|AFTER} verb_list ON <table_name> FOR EACH ROW DECLARE <variable list> BEGIN <program_code> END
Which of the below can be used in expression	PROCEDURE	ans) FUNCTION	TRIGGER	TABLE
While evaluating expressions, Oracle evaluates operators with a higher precedence	ans) FIRST	LAST	LEFT	RIGHT
Which among these operators has highest precedence	AND	BETWEEN	Integer format	ans) OR
execute dbms_utility.compile_all	compiles invalid packages	compiles packages in dbms_utility	validates packages	ans) recompiles all packages
Which exception is raised when PL/SQL encounters an internal erro	ans) program_error	internal_error	storate_error	others
Conversion of a number to a character string failes raises	value_error	ans) invalid_number	storate_error	None
Legal or not legal DECLARE emp_rec emp_rec_type	ans) Not legal because emp_rec_type must be declared prior to this declaration	Not legal because emp_rec_type must be declared after this declaration	Legal because emp_rec takes the type of emp_rec_type
Legal or not legal DECLARE emp_last_name %type	Not legal because it should include table reference	ans) Not legal because it should include table and column reference	Legal because it can reference any table and column type	Legal because %type is new data type available in higher versions
Legal or not legal DECLARE TYPE emp_table is table of VARCHAR2(55); emp_dept_table emp_table_type;	Not legal because the table name is missing	Legal because we are delaring a table type variable	ans) Not legal because the INDEX BY clause is missing
Difference between anonymous blocks and stored procedures ?	Anonymous block is compiled only when it is called	ans) stored procedure is compiled only when it is called	Former has declare Statement and latter doesn’t.	ans) A&C
Why use Truncate over Delete while deleting all rows	truncate has an implicit commit	it de allocates space	truncate is efficient, triggers are not fired	ans) All of the above
How do you run Native Dynamic SQL	ans) use “Execute immediate”	use “dbms_execute”	use “utl_file”
Which of the below is not a predefined exception	dup_val_on_index	not_logged_on	login_dened	ans) None
TYPE type_name IS TABLE OF element_type; In the adove nested table syntax,element_type can be PL/SQL datatype except	Binary_interger	Varchar2	Ref Cursor and Boolean	ANS)A & D

Which all statements are true about REF CURSOR	A,B,C	ANS)B,C	A,D,E	D,E
A) The syntax of a REF CURSOR type should include a ‘ RETURN ‘ clause
B) The cursor variable declared from a strong REF CURSOR can be associated with queries whose result sets match the number and datatype of the record structure after the RETURN clause at runtime
C) Cursor variables declared from weak REF CURSORs can be associated with any query at runtime
D) Cursor variables declared from strong REF CURSORs can be associated with any query at runtime
E) The cursor variable declared from a weak REF CURSOR can be associated with queries whose result sets match the number and datatype of the record structure after the RETURN clause at runtime
Is it possible to use Commit/rollback in Exception ?	ANS)YES	NO
Is there any diff between the below two statements a char(20) := ‘name’; a Varchar(20) := ‘name’; ?	No difference.	ANS)In Char type spaces will be padded after ‘name’ upto max length 20.in Varchar spaces will not be padded	Both the cases the space will be padded after ‘name’ upto max length 20	In Char type spaces will be padded after ‘name’ uptp max length 20.in Varchar spaces will padded before ‘name’ upto max length 20
What is the difference between using IS and AS while creating a procedure, function package and package body?	ANS)There absolutely no difference	Using IS will throw error	Using AS will throw error while compilation	After AS keyword we specify the return datatype. After IS keyword we do the variable declaration
Functionality of Right OUTER JOIN is same as right join. Is this statement true?	No	ANS)Yes
Which statement is true about EXISTS Usage?	the outer query is executed first and then inner query is excecuted for each value of outer query	the inner query is executed first and then outer query is excecuted for each value of inner query	exist is faster than IN usage	ANS)Option 1,3
In PL/SQL , we write select statement with INTO clause, this mat returns which all exceptions??	NO_DATA_FOUND	TOO_MANY_ROWS	INVALID_CURSOR	ANS)Option1,2
Is it possible to have same name for package and the procedure in a package	ANS)Yes	No
what is the order of execution if there is a statement level and row level trigger on a same table? 1) Before Statement level 2)After Statement level 3)Before Row level 4)After Row level	1,2,3,4	ANS)1,3,4,2	3,4,1,2	2,1,4,3
What will happen to an anonymus block,if there is no statement inside the block?	ANS)Compilation error	Statement Comopiled successfully.but gives run time error	No action is performed
Suppose I have 2 triggers on table T, tr1- a before insert trigger & tr2- a before update trigger. tr1 has update (T) statement inside body of tr1 and tr2 has insert (T) statement inside body of tr2 Now, I’m tring to insert a row into T. What will happen	Trigger tr1 is fired and the table T is update	ANS)will get a mutating table error	Trigger tr2 is fired and a record is inserted into table T
You need to store currency data and you know that data will always have two digits to the right of the decimal points. However the number of digits to the left of the decimal place will vary greatly. Which data type would be most appropriate to store the data?	ANS)NUMBER	NUMBER(T)	LANG	LANGRA
You need to update employee salaries if the salary of an employee is less than 1000.The salary needs to be incremented by 10%. Use SQL*Plus substitution variable to accept the employee number. Which PL/SQL block successfully updates the salaries?	Declare V_sal emp.sal % TYPE; Begin SELECT Sal INTO V_sal FROM emp WHERE empno = and P_empno; IF (V_Sal<1000) THEN UPDATE emp INTO Sal := Sal*1.1 WHERE empno = and P_empno; END IF;END;	Declare V_sal emp.sal % TYPE; Begin SELECT Sal INTO V_sal FROM emp WHERE empno = and P_empno; IF (V_Sal<1000) THEN SAL := SAL * 1.1; END IF;END;	Declare V_sal emp.sal % TYPE; Begin SELECT Sal INTO V_sal FROM emp WHERE empno = and P_empno; IF (V_Sal<1000) THEN UPDATE emp Sal := Sal*1.1 WHERE empno = and P_empno; END IF; END;	ANS)Declare V_sal emp.sal % TYPE; Begin SELECT Sal INTO V_sal FROM emp WHERE empno = and P_empno; IF (V_Sal<1000) THEN UPDATE emp Set Sal := Sal*1.1 WHERE empno = and P_empno; END IF; END;
The employee table contains these columns. LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) DEPT_ID NUMBER(9) You need to display the names of the employees that are not assigned to the department. Evaluate this SQL statement. SELECT last_name, first_nameFROM employee WHERE dept_id is NULL Which change should you make to achieve the desired result?	Create an outer join.	Change the column in the where condition.	ANS)Change the operator in the where condition	Add a second condition to the where condition
Written a PL/SQL loop, you need to test if the current FETCH was successful.Which SQL cursor attribute would you use to accomplish this task?	SQL % ISOPEN	SQL % ROWCOUNT	ANS)SQL % FOUND	This task cannot be accomplished with a SQL cursor attribute
The structure of the DEPT table is as follows: NAME NULL TYPE Deptno Not Null NUMBER(2) Dname VARCHAR2(14) Loc VARCHAR2(13) Examine the code Declare Type dept_record_type is record (dno NUMBER, name VARCHAR2(20)); dept_rec dept_record_type; Begin Select deptno, dname INTO dept_rec FROM dept WHERE deptno=10; END Which statement displays the name of selected department?	DBMS_OUTPUT.PUT_LINE (name);	DBMS_OUTPUT.PUT_LINE (dname);	ANS)DBMS_OUTPUT.PUT_LINE (dept_rec.name);	DBMS_OUTPUT.PUT_LINE (dept_rec.dname);
Evaluate this IF statement. IF v_value>100 THEN v_new-value:=2v-value; ELSIF v-value>200 THEN v-new-value:=3v-value; ELSIF v-value>300 THEN v-new-value:=4v-value; ELSE v-new-value:=5v-value; END IF What would be assigned to v_new_value if v_value=250?	250	ANS)500	750	1000
How would you declare a PL/SQL table of records to hold the rows selected from the EMP table?	DECLARE emp-table is TABLE of emp%ROWTYPE.	BEGIN TYPE emp-table is TABLE of emp%ROWTYPE emp-table emp-table-type;	DECLARE TYPE emp-table is TABLE of emp%ROWTYPE INDEX BY WHOLE NUMBER; emp-table emp-table-type;	ANS)DECLARE TYPE emp-table-type is TABLE of emp%ROWTYPE INDEX BY BINARY INTEGER; emp-table emp-table-type;
You want to create a cursor that can be used several times in a block. Selecting a different active set each time that it is opened. Which type of cursor do you create?	A cursor FOR loop.	A multiple selection cursor.	A cursor for each active set.	ANS)A cursor that uses parameters.
Which statement is true when writing a cursor FORloop?	You must explicitly fetch the rows within a cursor FOR loop.	You must explicitly open the cursor prior to the cursor FOR loop.	You must explicitly close the cursor prior to the end of program.	ANS)You do not explicitly open, fetch or close a cursor within a cursor FOR loop.
Which statement about implicit cursors is true?	Implicit cursors are declared implicitly only for DML statements.	ANS)Implicit cursors are declared implicitly for all the DML and SELECT statements.	Programmers need to close all the implicit cursors before the end of the PL/SQL program.	Programmers can declare implicit cursors by using the cursor type in the declaration section
Evaluate this PL/SQL block: DECLARE v-result NUMBER(2); BEGIN DELETE FROM employee WHERE dep_id IN (10,20,30); v-result:= SQL%ROWCOUNT; COMMIT; END; What will be the value of v_result if no rows are deleted?	ANS)0	1	TRUE	NULL
Which two conditions in a PL/SQL block cause an exception error to occur?	Select statement does not return a row.	Select statement returns more than one row.	The data type in the select list are inconsistent with the data types in the into clause.	ANS)Both A &B
You need to create a PL/SQL program to insert records into employee table. Which block of code successfully uses the insert command?	DECLARE v_hiredate DATE:=SYSDATE: BEGIN INSERT INTO emp(empnp, ename, hiredate, deptno) VALUES(empno_sequence.nextval, ‘and name’,v_hiredate and deptno)	DECLARE v-hiredate DATE:=SYSDATE: BEGIN INSERT INTO emp(empnp,ename,hiredate,deptno)	ANS)DECLARE v-hiredate DATE:=SYSDATE: BEGIN INSERT INTO emp(empnp,ename,hiredate) VALUES(empno_sequence.nextval, name, v_hiredate) END:	DECLARE v-hiredate DATE:=SYSDATE: BEGIN INSERT INTO emp(empnp,ename,heridate,deptno) VALUES(empno_sequence.nextval, ‘and name’,v_hitedate and deptno) Job=Clerk END:
Evaluate this PL/SQL block. BEGIN FOR i IN 1..10 LOOP IF I=4 OR I=6 THEN null; ELSE INSERT INTO test(result) VALUES (I) ; END IF; COMMIT; END LOOP; ROLL BACK; END. How many values will be inserted into the TEST table?	0	4	6	ANS)8
You attempt to query the database with this command: SELECT dept_no,AVG(MONTHS_BETWEEN(SYSDATE,hire-data)) FROM employee WHERE AVG(MONTHS_BETWEEN(SYSDATE,hire_date))>60 GROUP BY by dept_no ORDER BY AVG(MONTHS_BETWEEN(SYSDATE,hire_date)); Why does this statement cause an error?	A select clause cannot contain a group function.	ANS)A where clause cannot be used to restrict groups.	An order by clause cannot contain a group function.	A group function cannot contain a single row function.
In which section of a PL/SQL block is a user defined exception waste?	Heading	ANS)Executable	Declarative	Exception handling
Examine the code: SET SERVER OUTPUT ON DECLARE v_char_val varchar2(100); BEGIN v_char_val:= ‘Hello World’, DBMS_OUTPUT.PUT_LINE(v_char_val); END SET SERVER OUTPUT OFF This code is stored in a script file name “myproc.sql”. Which statement executes the code in the script file?	Myproc.sql	RUN myproc,sql	ANS)START myproc.sql	EXECUTE myproc.sql
Examine this block F code Set server output ON Declare X NUMBER; V_SAL NUMBER; V_found VARCHAR2(10) := ‘TRUE’ Begin X:=1; V_SAL :=1000; Declare V_found VARCHAR2(10); Y NUMBER; Begin IF (V_sal>500) THEN V_found := ‘YES’; END IF; DBMS_OUTPUT.PUT_LINE(‘value f V_found is’ \|\| V_found); DBMS_OUTPUT.PUT_LINE (‘value f V_found is’ \|\| V_found); Y:20; END DBMS_OUTPUT.PUT_LINE (‘value f V_found is’ \|\| V_found); DBMS_OUTPUT.PUT_LINE (‘value f Y is’ \|\| TO_CHAR (Y); END Why does this code produce an error when executed?	The value f V_found cannot be YES.	Variable V_found is declared at more than one location.	ANS)Variable Y is declared in the inner block and referenced in the outer block.	Variable V_sal is declared in the outer block and referenced in the inner block.
Which statement is valid within the executable section of Pl/SQL block?	BEGIN emp_rec emp%ROWTYPE END;	WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT.LINE(‘No records found’);	ANS)Select ename,sal into v_ename,v_sal from emp where empno=101;	Procedure cal_max(n1 NUBER n2 NUMBER, p_max OUT NUMBER) IS BEGIN If n1>n2 then p_max:=n1; Else p_max=n2; END.
In the declarative section of a PL/SQL block, you created but did not initialize a number variable. When the block executes what will be the initial value of the variable?	0	ANS)NULL	It depends on the scale and precision of the variable.	The block will not execute because the variable was not initialized.
When selecting data which statement is valid about projection?	Projection allows due to choose rows.	ANS)Projection allows due to choose columns.	Projection allows due to joined tables together.	Projection allows due to add columns to a table.
Structure of DEPT table is as follows: Name Null Type DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) Examine the declaration section. DECLARE TYPE dept_table_type IS TABLE OF dept%ROWTYPE INDEX BY BINARY INTEGER dept_table dept_table_type; You need to assign LOC field in record 15 the value of ‘Atlanta’. Which PL/SQL statement makes this assignment?	dept_table.loc.15 := ‘Atlanta’;	dept_table[15].loc := ‘Atlanta’;	ANS)dept_table(15).loc := ‘Atlanta’;	dept_table_type(15).loc := ‘Atlanta’;
You need to change the job title Clerk to Administrative Clerk for all Clerks. Which statement does this?	UPDATE emp SET job = ‘Administrative Clerk’;	UPDATE emp Job := ‘Administrative Clerk’ WHERE UPPER (job) = ‘Clerk’;	ANS)UPDATE emp SET job = ‘Administrative Clerk’ WHERE UPPER (job) = ‘CLERK’;	UPDATE emp SET values job = ‘Administrative Clerk’ WHERE UPPER (job) = ‘Clerk’;
Given the executable section of a PL/SQL block FOR employee_record IN Salary_Cursor Loop employee_id_table (employee_id):= employee_record.last_name; END Loop Close Salary_Cursor; END; Why does this section cause an error?	The cursor needs to be opened.	Terminating conditions are missing.	No FETCH statements were issued.	ANS)The cursor does not need to be explicitly closed.
Examine this function: CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE) RETURN NUMBER IS V_AVG NUMBER; BEGIN SELECT HITS / AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which statement will successfully invoke this function in SQL *Plus?	ANS)SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;	EXECUTE CALC_PLAYER_AVG(31);	CALC_PLAYER(‘RUTH’);	START CALC_PLAYER_AVG(31);
Which are true statements about dependent objects?	An object with status of invalid cannot be a referenced object.	All schema objects have a status that is recorded in the data dictionary.	You can view whether an object is valid or invalid in the USER_OBJECTS data dictionary view	ANS)ALL the above
CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT ‘placeholder’, p_location VARCHAR2 DEFAULT ‘Boston’) IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept; / Which are valid calls to the add_dep procedure?	add_dept;	add_dept(‘Accounting’);	add_dept(, ‘New York’);	ANS)Exec add_dept;
Which statements about packages are true?	Packages can be nested.	You can pass parameters to packages.	A package is loaded into memory each time it is invoked.	ANS)You can achieve information hiding by making package constructs private
Which two programming constructs can be grouped within a package?	Cursor	Constant	Trigger	ANS)Both A &B
Which statements describe the state of a package variable after executing the package in which it is declared?	ANS)It persists across transactions within a session.	It persists from session to session for the same user.	It does not persist across transaction within a session.	It persists from user to user when the package is invoked.
Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased?	ALTER TABLE emp ADD CONS TRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1);	ANS)CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( – 20508, ‘Do not decrease salary not increase by more than 10%’); END;	CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( – 20508, ‘Do not decrease salary not increase by more than 10%’); END;	CREATE OR REPLACE TRIGGER check_sal AFTER UPDATE OR sal ON emp WHEN (new.sal < old.sal OR -new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( – 20508, ‘Do not decrease salary not increase by more than 10%’); END;
Examine this code: CREATE OR REPLACE PACKAGE bonus IS g_max_bonus NUMBER := .99; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER; FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY bonus IS v_salary employees.salary%TYPE; v_bonus employees.commission_pct%TYPE; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employee_id = p_emp_id; RETURN v_bonus * v_salary; END calc_bonus FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employees RETURN v_bonus * v_salary + v_salary; END cacl_salary; END bonus; / Which statement is true?	You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table.	ANS)You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table	You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the EMPLOYEES table	You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table.
Which statement is valid when removing procedures?	ANS)Use a drop procedure statement to drop a standalone procedure.	Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package specification	Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package body.	For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause
You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE. What type of trigger do you create?	ANS)FOR EACH ROW trigger on the EMP table.	Statement-level trigger on the EMP table.	FOR EACH ROW trigger on the AUDIT_TABLE table.	Statement-level trigger on the AUDIT_TABLE table
Which statements are true?	If errors occur during the compilation of a trigger, the trigger is still created.	If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_TRIGGERS data dictionary view to see the compilation errors.	If errors occur during the compilation of a trigger you can use the SHOW ERRORS command within iSQL *Plus to see the compilation errors.	ANS). If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_ERRORS data dictionary view to see compilation errors
Which dictionary view track dependencies?	USER_SOURCE	UTL_DEPTREE	ANS)USER_DEPENDENCIES	DBA_DEPENDENT_OBJECTS
Given a function CALCTAX: CREATE OR REPLACE FUNCTION calctax (sal NUMBER) RETURN NUMBER IS BEGIN RETURN (sal * 0.05); END; If you want to run the above function from the SQL *Plus prompt, which statement is true?	You need to execute the command CALCTAX(1000);.	You need to execute the command EXECUTE FUNCTION calctax;.	You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000);.	ANS)You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);
What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?	The rows are selected and ordered.	The validity of the SQL statement is established	An area of memory is established to process the SQL statement.	ANS)The SQL statement is run and the number of rows processed is returned.
What part of a database trigger determines the number of times the trigger body executes?	ANS)Trigger type	Trigger body	Trigger event	Trigger timing
Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2 is v_email_name VARCHAR2(19); BEGIN v_email_home := SUBSTR(p_first_name, 1, 1) \|\| SUBSTR(p_last_name, 1, 7) \|\| ‘@Oracle.com’; UPDATE employees SET email = v_email_name WHERE employee_id = p_id; RETURN v_email_name; END; You run this SELECT statement: SELECT first_name, last_name gen_email_name(first_name, last_name, 108) EMAIL FROM employees; What occurs?	Employee 108 has his email name updated based on the return result of the function.	ANS)The statement fails because functions called from SQL expressions cannot perform DML	The statement fails because the functions does not contain code to end the transaction.	The SQL statement executes successfully, because UPDATE and DELETE statements are ignoring in stored functions called from SQL expressions
Which table should you query to determine when your procedure was last compiled?	USER_PROCEDURES	USER_PROCS	ANS)USER_OBJECTS	USER_PLSQL_UNITS
Examine this code: CREATE OR REPLACE TRIGGER secure_emp BEFORE LOGON ON employees BEGIN IF (TO_CHAR(SYSDATE, ‘DY’) IN (‘SAT’, ‘SUN’)) OR (TO_CHAR(SYSDATE, ‘HH24:MI’) NOT BETWEEN ’08:00′ AND ’18:00′) THEN RAISE_APPLICATION_ERROR (-20500, ‘You may insert into the EMPLOYEES table only during business hours.’); END IF; END; / What type of trigger is it? `	DML trigger	INSTEAD OF trigger	System event trigger	ANS)This is an invalid trigger
Examine this package: CREATE OR REPLACE PACKAGE discounts IS g_id NUMBER := 7829; discount_rate NUMBER := 0.00; PROCEDURE display_price (p_price NUMBER); END discounts; / CREATE OR REPLACE PACKAGE BODY discounts IS PROCEDURE display_price (p_price NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE(‘Discounted ‘\|\| TO_CHAR(p_price*NVL(discount_rate, 1))); END display_price; BEGIN discount_rate := 0.10; END discounts; / Which statement is true?	The value of DISCOUNT_RATE always remains 0.00 in a session.	The value of DISCOUNT_RATE is set to 0.10 each time the package is invoked in a session.	The value of DISCOUNT_RATE is set to 1.00 each time the procedure DISPLAY_PRICE is invoked.	ANS)The value of DISCOUNT_RATE is set to 0.10 when the package is invoked for the first time in a session
CREATE OR REPLACE TRIGGER update_emp AFTER UPDATE ON emp BEGIN INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE); END; You issue an UPDATE command in the EMP table that results in changing 10 rows. How many rows are inserted into the AUDIT_TABLE?	ANS)1	10	NONE	A value equal to the number of rows in the EMP table.
You want to retrieve the employee details from the emp table and process them in PL/SQL block. Which type of variable do you create in the PL/SQL block to retrieve all the rows and columns using a single select statement from the emp table?	PL/SQL record.	%ROWTYPE variable	PL/SQL table of scalars.	ANS)PL/SQL table of records.
Which statements about packages are true?	ANS)The specification and body of the package are stored separately in the database	The specification and body of the package are stored together in the database	The package specification is optional, but the package body is required.	Both the specification and body are required components of a package
When creating a function in SQL *Plus, you receive this message: “Warning: Function created with compilation errors.” Which command can you issue to see the actual error message?	SHOW FUNCTION_ERROR	SHOW USER_ERRORS	ANS)SHOW ERRORS	SHOW ALL_ERRORS
Which triggering events can cause a trigger to fire?	A specific error or any errors occurs	A database is shut down or started up.	A specific user or any user logs on or off.	ANS)ALL THE ABOVE
Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER1 (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID,LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must invoke the APD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?	EXECUTE UPD_BAT_STAT(V_ID);	ANS)UPD_BAT_STAT(V_ID);	RUN UPD_BAT_STAT(V_ID);	START UPD_BAT_STAT(V_ID);
Which statement about triggers is true?	You use an application trigger to fire when a DELETE statement occurs	ANS)You use a database trigger to fire when an INSERT statement occurs.	You use a system event trigger to fire when an UPDATE statement occurs.	You use INSTEAD OF trigger to fire when a SELECT statement occurs.
You want to create a PL/SQL block of code that calculates discounts on customer orders. -This code will be invoked from several places, but only within the program unit ORDERTOTAL. What is the most appropriate location to store the code that calculates the discounts?	ANS)A stored procedure on the server.	A block of code in a PL/SQL library.	A s0tandalone procedure on the client machine.	A block of code in the body of the program unit ORDERTOTAL
Which type of argument passes a value from a procedure to the calling environment?	VARCHAR2	BOOLEAN	ANS)OUT	IN
You create a DML trigger. For the timing information, which is valid with a DML trigger?	DURING	INSTEAD	ON SHUTDOWN	ANS)BEFORE
You are about to change the arguments of the CALC_TEAM_AVG function. Which dictionary view can you query to determine the names of the procedures and functions that invoke the CALC_TEAM_AVG function?	USER_PROC_DEPENDS	ANS)USER_DEPENDENCIES	USER_REFERENCES	USER_SOURCE
A CALL statement inside the trigger body enables you to call	A package	A stored function	ANS)A stored procedure	Another database trigger
You need to remove the database trigger BUSINESS_HOUR. Which command do you use to remove the trigger in the SQL *Plus environment?	ANS)DROP TRIGGER business_hour;	DELETE TRIGGER business_hour;	REMOVE TRIGGER business_hour;	ALTER TRIGGER business_hour REMOVE;
How can you migrate from a LONG to a LOB data type for a column?	Use the DBMS_MANAGE_LOB.MIGRATE procedure.	Use the UTL_MANAGE_LOB.MIGRATE procedure	Use the DBMS_LOB.MIGRATE procedure	ANS)Use the ALTER TABLE command.
Examine this procedure: CREATE OR REPLACE PROCEDURE INSERT_TEAM (V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT ‘AUSTIN’, V_NAME in VARCHAR2) IS BEGIN INSERT INTO TEAM (id, city, name) VALUES (v_id, v_city, v_name); COMMIT; END Which statements will successfully invoke this procedure in SQL *Plus?	EXECUTE INSERT_TEAM (3, ‘LONGHORNS’);	EXECUTE INSERT_TEAM (3, V_NAME=>’LONGHORNS’, V_CITY=>’AUSTIN’);	ANS)EXECUTE INSERT_TEAM(3, ‘AUSTIN’,’LONGHORNS’);	BOTH B AND C
Which describe a stored procedure?	A stored procedure is a type of PL/SQL subprogram that performs an action.	ANS)A stored procedure has three parts: the specification, the body, and the exception handler part.	The executable section of a stored procedure contains statements that assigns values, control execution,and return values to the calling environment.	A stored procedure is typically written in SQL.
Examine this code: CREATE OR REPLACE PROCEDURE add_dept ( p_name departments.department_name%TYPE DEFAULT ‘unknown’, p_loc departments.location_id%TYPE DEFAULT 1700) IS BEGIN INSERT INTO departments(department_id, department_name, loclation_id) VALUES(dept_seq.NEXTVAL,p_name, p_loc); END add_dept; / You created the add_dept procedure above, and you now invoke the procedure in SQL *Plus. Which is not valid invocations?	ANS)EXECUTE add_dept(p_loc=>2500)	EXECUTE add_dept(‘Education’, 2500)	EXECUTE add_dept(‘2500’, p_loc =>2500)	EXECUTE add_dept (p_name=>’Education’, 2500)
Which is not a valid way to minimize dependency failure?	Querying with the SELECT * notification.	ANS)Specifying schema names when referencing objects.	Declaring variables with the %TYPE attribute.	Declaring records by using the %ROWTYPE attribute
Which dopes the INSTEAD OF clause in a trigger identify?	The view associated with the trigger	The table associated with the trigger	The package associated with the trigger.	The statement level or for each row association to the trigger.
Examine this package: CREATE OR REPLACE PACKAGE manage_emps IS tax_rate CONSTANT NUMBER(5,2) := .28; v_id NUMBER; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER); PROCEDURE delete_emp; PROCEDURE update_emp; FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER; END manage_emps; / CREATE OR REPLACE PACKAGE BODY manage_emps IS PROCEDURE update_sal (p_raise_amt NUMBER) IS BEGIN UPDATE emp SET sal = (sal * p_raise_emt) + sal WHERE empno = v_id; END; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS BEGIN INSERT INTO emp(empno, deptno, sal) VALYES(v_id, p_depntno, p_sal); END insert_emp; PROCEDURE delete_emp IS BEGIN DELETE FROM emp WHERE empno = v_id; END delete_emp; PROCEDURE update_emp IS v_sal NUMBER(10, 2); v_raise NUMBER(10, 2); BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = v_id; IF v_sal < 500 THEN v_raise := .05; ELSIP v_sal < 1000 THEN v_raise := .07; ELSE v_raise := .04; END IF; update_sal(v_raise); END update_emp; FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER IS BEGIN RETURN p_sal * tax_rate; END calc_tax; END manage_emps; / What is the name of the private procedure in this package?	CALC_TAX	INSERT_EMP	ANS)UPDATE_SAL	DELETE_EMP
What can you do with the DBMS_LOB package?	Use the DBMS_LOB.WRITE procedure to write data to a BFILE	Use the DBMS_LOB.BFILENAME function to locate an external BFILE.	Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE.	ANS)Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed.
Examine this code: CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(p_comm IN NUMBER); END comm_package; / User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which statement is true?	g_comm has a value of 15 at 9:06am for Smith.	ANS)g_comm has a value of 15 at 9:06am for Jones.	g_comm has a value of 20 at 9:06am for both Jones and Smith.	g_comm has a value of 15 at 9:03 am for both Jones and Smith.
Which statement removes the function?	ANS)DROP FUNCTION gen_email_name;	REMOVE gen_email_name;	DELETE gen_email_name;	NONE
Examine this procedure: CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID IN NUMBER DEFAULT 10, V_AB IN NUMBER DEFAULT 4) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB WHERE PLAYER_ID = V_ID; COMMIT; END; Which two statements will successfully invoke this procedure in SQL *Plus?	EXECUTE UPD_BAT_STAT;	UPD_BAT_STAT(V_AB=>10, V_ID=>31);	EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);	ANS)BOTH AND C
Examine this code: CREATE OR REPLACE PROCEDURE audit_action (p_who VARCHAR2) AS BEGIN INSERT INTO audit(schema_user) VALUES(p_who); END audit_action; / CREATE OR REPLACE TRIGGER watch_it AFTER LOGON ON DATABASE CALL audit_action(ora_login_user) / What does this trigger do?	The trigger records an audit trail when a user makes changes to the database.	The trigger marks the user as logged on to the database before an audit statement is issued.	The trigger invoked the procedure audit_action each time a user logs on to his/her schema and adds the username to the audit table	ANS)The trigger invokes the procedure audit_action each time a user logs on to the database and adds the username to the audit table.
Examine the declaration section: DECLARE CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS SELECT EMPNO, ENAME FROM EMPWHERE DEPTNO=p_deptno AND JOB=p_job; BEGIN . . . Which statement opens the cursor successfully?	OPEN emp_cursor.	OPEN emp_cursor(‘clerk’,10);	ANS)OPEN emp_cursor(10, ‘analyst’);	OPEN emp_cursor (p_deptno,p_job);
The OLD and NEW qualifiers can be used in which type of trigger?	Row level DML trigger	ANS)Row level application trigger	Statement level DML trigger	Row level system trigger
Which statement is true?	Stored functions do not permit calculations that involve database links in a distributed environment.	Stored functions can be called from the SELECT and WHERE clauses only.	ANS)Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application	Stored functions cannot manipulate new types of data, such as longitude and latitude
Examine the trigger: CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON Emp_tab FOR EACH ROW DELCARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM Emp_tab; DBMS_OUTPUT.PUT_LINE(‘ There are now ‘ \|\| n\|\| ‘ employees,’); END; This trigger results in an error after this SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499; How do you correct the error?	Remove the DBMS_OUTPUT statement because it is not allowed in a trigger.	ANS)Change the trigger to a statement-level trigger by removing FOR EACH ROW	Take out the COUNT function because it is not allowed in a trigger.	Change the trigger type to a BEFORE DELETE.
What is true about stored procedures?	A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters.	A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification.	ANS)A stored procedure must have at least one executable statement in the procedure body.	A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters.
The creation of which database objects will not cause a DDL trigger to fire?	ANS)Synonyms	Index	Cluster	Package
Which two program declarations are correct for a stored program unit?	CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER) RETURN NUMBER	ANS)CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER	CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER(10,2)	CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER(10, 2))
You need to implement a virtual private database (vpd). In order to have the vpd functionality, a trigger is required to fire when every user initiates a session in the database. What type of trigger needs to be created?	DML trigger	ANS)System event trigger	INSTEAD OF trigger	Application trigger
You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a SELECT statement on the EMP table to ensure that the new salary value falls within the minimum and maximum salary for a given job title. What happens when you try to update a salary value in the EMP table?	The trigger fires successfully.	The trigger fails because it needs to be a row level AFTER UPDATE trigger.	ANS)The trigger fails because a SELECT statement on the table being updated is not allowed.	The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger
Examine this code: CREATE OR REPLACE STORED FUNCTION get_sal (p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE) RETURN NUMBER IS v_salary NUMBER; v_raise NUMBER(8,2); BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id; v_raise := p_raise_amt * v_salary; RETURN v_raise; END; Which statement is true?	This statement creates a stored procedure named get_sal.	This statement returns a raise amount based on an employee id.	This statement creates a stored function named get_sal with a status of invalid.	ANS)This statement fails.
You need to disable all triggers on the EMPLOYEES table. Which command accomplishes this?	ALTER employees DISABLE ALL TRIGGERS;	ALTER TRIGGERS ON TABLE employees DISABLE;	ANS)ALTER TABLE employees DISABLE ALL TRIGGERS;	None of these commands; you cannot disable multiple triggers on a table in one command
An internal LOB is _____.	A table.	ANS)Stored in the database.	A column that is a primary key.	A file stored outside of the database, with an internal pointer to it from a database column.
Examine this code: CREATE OR REPLACE FUNCTION calc_sal(p_salary NUMBER) RETURN NUMBER IS v_raise NUMBER(4,2) DEFAULT 1.08; BEGIN RETURN v_raise * p_salary; END calc_sal; / Which statement accurately call the stored function CALC_SAL?	UPDATE employees (calc_sal(salary)) SET salary = salary * calc_sal(salary);	INSERT calc_sal(salary) INTO employees WHERE department_id = 60;	DELETE FROM employees(calc_sal(salary)) WHERE calc_sal(salary) > 1000;	ANS)SELECT salary, calc_sal(salary) FROM employees WHERE department_id = 60;
This statement fails when executed: CREATE OR REPLACE TRIGGER CALC_TEAM_AVG AFTER INSERT ON PLAYER BEGIN INSERT INTO PLAYER_BATSTAT (PLAYER_ID, SEASON_YEAR,AT_BATS,HITS) VALUES (:NEW.ID, 1997, 0,0); END; To which type must you convert the trigger to correct the error?	ANS)Row	Statement	ORACLE FORM trigger	Before
Examine this code: CREATE OR REPLACE PACKAGE metric_converter IS c_height CONSTRAINT NUMBER := 2.54; c_weight CONSTRAINT NUMBER := .454; FUNCTION calc_height (p_height_in_inches NUMBER) RETURN NUMBER; FUNCTION calc_weight (p_weight_in_pounds NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY metric_converter IS FUNCTION calc_height (p_height_in_inches NUMBER) RETURN NUMBER IS BEGIN RETURN p_height_in_inches * c_height; END calc_height; FUNCTION calc_weight (p_weight_in_pounds NUMBER) RETURN NUMBER IS BEGIN RETURN p_weight_in_pounds * c_weight END calc_weight END metric_converter; / CREATE OR REPLACE FUNCTION calc_height (p_height_in_inches NUMBER) RETURN NUMBER IS BEGIN RETURN p_height_in_inches * metric_converter.c_height; END calc_height; / Which statement is true?	ANS)If you remove the package specification, then the package body and the stand alone stored function CALC_HEIGHT are removed.	If you remove the package body, then the package specification and the stand alone stored function CALC_HEIGHT are removed	If you remove the package specification, then the package body is removed	If you remove the package body, then the package specification is removed.
What is a condition predicate in a DML trigger?	A conditional predicate allows you to specify a WHEN-LOGGING-ON condition in the trigger body.	A conditional predicate means you use the NEW and OLD qualifiers in the trigger body as a condition.	ANS)A conditional predicate allows you to combine several DBM triggering events into one in the trigger body.	A conditional predicate allows you to specify a SHUTDOWN or STARTUP condition in the trigger body.
Which command must you issue to allow users to access the UPD_TEAM_STAT trigger on the TEAM table?	ANS)GRANT SELECT, INSERT, UPDATE, DELETE ON TEAM TO PUBLIC;	GRANT SELECT, INSERT, UPDATE, DELETE ON UPD_TEAM_STAT TO PUBLIC;	GRANT EXECUTE ON TEAM TO PUBLIC	GRANT SELECT, EXECUTE ON TEAM, UPD_TEAM_STAT TO PUBLIC;
When creating a function, in which section will you typically find the RETURN keyword?	HEADER only	DECLARATIVE	ANS)EXECUTABLE and HEADER	DECLARATIVE,EXECUTABLE and EXCEPTION HANDLING
In which section of a PL/SQL block is a user defined exception raised?	Heading.	ANS)Executable	Declarative	Exception handling.
Which statement is true about nesting blocks?	Variable name must be unique between blocks.	ANS)A variable defined in the outer block is visible in the inner blocks.	A variable defined in the inner block is visible in the outer blocks.	A variable is in an inner block may have the same name as a variable in an outer block only if the data types are different.
Examine Code: 1 DECLARE 2 i NUMBER := 0; 3 v_date DATE ; 4 BEGIN 5 i := i + 1; 6 LOOP 7 i := v_date + 5; 8 i := i + 1; 9 EXIT WHEN i = 5; 10 END LOOP; 11 END You have encountered the unexpected results when above block of code is executed. How can u trace the values of counter variable i and date variable v_date in SQL* PLUS environment?	By setting SQL* PLUS session variable DEBUGGER=TRUE	By inserting the statement DBMS_OUTPUT.PUT_LINE (i , v_date); Between lines 8-9	By inserting the statement DBMS_OUTPUT.DEBUG_VAR (i , v_date); Between lines 8-9	ANS)By inserting the statement DBMS_OUTPUT.PUT_LINE (i \| \| “\| \| TO_CHAR( v_date)); Between lines 8-9
Examine code: SET SERVEROUTPUT ON DECLARE v_name emp.ename%TYPE; v_num NUMBER; v_sal NUMBER(8,2); BEGIN — This code displays salaries if larger than 10,000. SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno=101; IF(v_sal.GT.10000) THEN DBMS_OUTPUT.PUT_LINE(‘Salary is ‘\|\|’ v_sal \|\| ‘for employee’ \|\| v_name); END IF; END SET SERVER OUTPUT OF This statement produces a compilation error when above PL/SQL block is executed?	v_num NUMBER;	v_name emp.ename%TYPE;	ANS)IF (v_sal.GT.10000) THEN	This code displays salaries if larger than 10000.
You are a user of PROD database which contains over 1000 tables and you need to determine the number of tables you can access. Which data dictionary view could you query to display this information?	USER_OBJECTS.	ANS)ALL_OBJECTS.	DBA_SEGMENTS.	DBA_TABLES.
You query the database with this command.SELECT last_name, first_nameFROM employeeWHERE SALARY IN SELECT salary FROM employee WHERE dept_no=3 OR dept_no=5); Which values are displayed?	Last name and the first name of only the employees in the department number 3 and 5.	Last name and first name of all the employees except those working in the department 3and 5.	ANS)Last name and first name of all the employees with the same salary as employee in the department 3 and 5.	Last name and first name of only the employees whose salary falls in the range of salary from department 3 or 5.
Which operator is not appropriate in the joined condition of none equijoin select statement?	In operator.	Like operators	ANS)Equal operators.	Between x and y operator.
What should you do after each fetch statement in the PL/SQL block?	Open the cursor.	Close the cursor	Initialize the loop.	ANS)Test for the rows using the cursor attribute.
Which SELECT statement would you use in a PL/SQL block to query the employee table and retrieve the last name and salary of the employee whose ID is 3?	SELECT last_name,salary FROM employee;	SELECT last_name,salary FROM employee; WHERE id=3;	ANS)SELECT last_name,salary INTO v_last_name,v_salary FROM employee WHERE id=3;	SELECT l last_name,salary FROM employee; INTO v_last_name,v_salary WHERE id=3;
The structure of the DEPT table as: Name Null? Type DEPT NO Not NULL Number(25) DNAME VARCHAR2(14) LOC VARCHAR2(13). Examine the declaration section. DECLARE TYPE dept_record_type is RECORD (dno NUMBER, name VARCHER(20)); dept_rec dept_record_type; How do you retrieve an entire row of the DEPT table using the DEPT-REC variable?	SELECT* INTO dept_rec FROM dept WHERE dept no=10;	SELECT deptno,dname,loc INTO dept_rec FROM dept WHERE dept no=10;	ANS)You can’t retrieve the entire row using the DEPT_REC variable declared in the code.	SELECT* INTO dept_rec.dno,dept_rec.name,dept_rec. FROM dept WHERE dept no=10;
Examine the code: DECLARE. CURSOR emp_cursor ISSELECT ename,deptno FROM emp; emp_rec emp_cursor %ROWTYPE BEGIN OPEN emp_cursor LOOP FETCH emp_cursor INTO emp_rec EXIT WHEN emp_cursor NOT FOUND; INSERT INTO temp_emp(name’dno) VALUES(emp_rec.ename,emp_rec deptno); END LOOP; CLOSE emp_cursor; END; Using a cursor FOR loop,which PL/SQL block equivalent to the above code?	ANS)DECLARE CURSOR emp-cursor 1S SELECT ename,dept no FROM emp; BEGIN FOR emp-rec IN emp-cursor LOOP INSERT INTO temp-emp(name,dno) VALUES (emp-rec.ename, emp-re.deptno); END LOOP END;	DECLARE CURSOR emp-cursor 1S SELECT ename,dept no FROM emp; BEGIN FOR emp-rec IN emp-cursor LOOP OPEN emp-cursor; INSERT INTO temp-emp(name,dno) VALUES (emp-rec.ename, emp_rec.deptno); END LOOP END;	DECLARE CURSOR emp-cursor 1S SELECT ename,dept no FROM emp; BEGIN FOR emp-rec IN emp-cursor LOOP OPEN emp-cursor; INSERT INTO temp-emp(name,dno) VALUES (emp-rec.ename, emp-rec.deptno); END LOOP CLOSE emp-cursor; END;	DECLARE CURSOR emp-cursor 1S SELECT ename,dept no FROM emp; emp-rec emp-cursor%ROWTYPE; BEGIN FETCH emp-cursor INTO emp-rec; FOR emp-recIN emp-cursor LOOP INSERT INTO temp-emp(name,dno) VALUES (emp-rec.ename, emp-rec.deptno); END LOOP END;
Under which situation it is necessary to use an explicit cursor?	When any DML or select statement is used in a PL/SQL block	When a delete statement in a PL/SQL block deletes more than one row.	ANS)When a select statement in a PL/SQL block is more than one row.	When an update statement in a PL/SQL block has to modify more than one row.
Which data dictionary view contains the definition of a view?	MY_VIEWS.	ANS)USER_VIEWS.	SYSTEM_VIEWS.	USER_TAB_VIEWS.
Evaluate this PL/SQL block: for I in 1..5 IF i=1 THEN NULL; ELSIF i=3 THEN COMMIT; ELSIF i=5 THEN ROLLBACK; ELSE INSERT INTO test (results); VALUES(i); END IF; END LOOP; COMMIT; END; How many values will be permanently inserted into the TEST table?	0	ANS)1	2	3
A collection is an ordered group of elements of ————————–	ANS)all of the same data type	all of different data type	some of same data type and some of different data type	none
Which is a pl/sql collection type	Index-by tables	Nested tables	Varrays	ANS)all the above
Index by table is also known as	varrays	ANS)associative arrays	Nested tables	none
Index by table let you lok up elements using ——- for subscript values	arbitrary numbers	strings	sequential numbers	ANS)both a and b
Nested table let you lok up elements using ——- for subscript values	arbitrary numbers	strings	ANS)sequential numbers	both a and b
Which PL/SQL collection type will use sequential numbers as subscript types	Index-by tables	Nested tables	Varrays	ANS)both b and c
Which PL/SQL collection type will hold fixed number of elements	Index-by tables	Nested tables	ANS)Varrays	both b and c
PL/SQL collection types can be declared in	procedure	Function	Package	ANS)All the above
Which PL/SQL data type can be stored as a column in a data base table	ANS)nested tables	Associative array	Both a and b	none
Nested Tables cannot use the following data types	BINARY_INTEGER	SIGNTYPE	STRING	ANS)all the above
which PL/SQL collection type does not need Constructors	ANS)Index-by tables	Nested tables	Varrays	None
Which PL/SQL collection needs a constructor	Index-by tables	Nested tables	Varrays	ANS)Both b and c
If the subscript of a PL/SQL collection is null,or is not convertable to the right datatype then it throws the following exception	ANS)VALUE_ERROR	SUBSCRIPT_BEYOND_COUNT.	SUBSCRIPT_NULL	COLLECTION_IS_NULL
If the subscript of a PL/SQL collection refers to an uninitialized element, PL/SQL raises	VALUE_ERROR	ANS)SUBSCRIPT_BEYOND_COUNT.	SUBSCRIPT_NULL	COLLECTION_IS_NULL
If the collection is atomically null, PL/SQL raises	VALUE_ERROR	SUBSCRIPT_BEYOND_COUNT.	SUBSCRIPT_NULL	ANS)COLLECTION_IS_NULL
Associative arrays cannot use the following collection methods	Exists	Trim	Limit	ANS)Both and b
Which collection method is a procedure	EXTEND	TRIM	DELETE	ANS)All the above
Which collection method is a function	EXTEND	EXISTS	PRIOR	ANS)BOTH B AND C
Which collection method can be applied to automatically null collections	ANS)Exists	Extend	Prior	Count
Which collection method returns TRUE if the nth element in a collection exists	Extend(n)	ANS)Exists(n)	Next(n)	Count
Which Syntax is correct for Counting the elements in a PL/SQL collection	ANS)IF projects.COUNT = 25 THEN	IF COUNT = 25 THEN	IF COUNT(projects) = 25 THEN	IF COUNT(*) = 25 THEN
which Pl/SQL collection method will return NULL for nested tables and associative arrays	Count	Exists	ANS)Limit	Extends
which PL/SQL collection method returns the index number that precedes index n in a collection	Extends(n)	ANS)Prior(n)	Next(n)	First(n)
Subscript range for nested tables is	ANS)1 .. 2**31	1 .. size_limit	-231 .. 231	none
Subscript range for varrays is	1 .. 2**31	ANS)1 .. size_limit	-231 .. 231	none
Subscript range for associative arrays with numeric key	1 .. 2**31	1 .. size_limit	ANS)-231 .. 231	none
Which is allowed for a PL/SQL collections	ANS)We can check whether a collection is null	We can check whether two collections are the same	greater than operator between two collections	less than operator between two collections
Pl/SQL collections cannot appear in ———-list	Distinct	Group By	Order By	ANS)All the above
LIMIT returns the maximum number of elements that the collection can contain for which Pl/SQL collection type	Nested tables	Associative array	ANS)Varrays	None
Which PL/SQL collection method returns the smallest Index number in a collection	Count	Limit	Last	ANS)First
Which PL/SQL collection method returns the largest index number in a collection	Count	Limit	ANS)Last	First
If the collection is empty First and Last Pl/SQL collection methods return	0	ANS)Null	1	None
Which PL/SQL method returns the index number that succeeds index n.	Extends(n)	Prior(n)	ANS)Next(n)	Last(n)
Which PL/SQL method is used to remove one element from the end of a collection	ANS)TRIM	TRIM(n)	DELETE	DELETE(n)
Which PL/SQL method is used to remove n elements from the end of a collection	TRIM	ANS)TRIM(n)	DELETE	DELETE(n)
Which PL/SQL method is used to remove all elements from a collection	TRIM	TRIM(n)	ANS)DELETE	DELETE(n)
Which PL/SQL method removes the nth element from an associative array with a numeric key	TRIM	TRIM(n)	DELETE	ANS)DELETE(n)

PL SQL Question Answers

Like this:

Clustering – The Data Ensemble MCQ with Answer

MECH Techgenics MCQ

Compiler Design MCQ with Answers

Leave a Reply Cancel reply

Share this:

Like this:

Related Posts

Leave a Reply Cancel reply