A table has to be dropped from within a stored procedure. How can this be implemented?
A table cannot be dropped from a stored procedure 0.0%
Use the DROP command in the procedure to drop the table 0.0%
Use the DBMS_DDL packaged routines in the procedure to drop the table 83.0%
Use the DBMS_DROP packaged routines in the procedure to drop the table. 16.0%
All packages can be recompiled by using an Oracle utility called:
Dbms_Output 0.0%
Dbms_Lob 0.0%
Dbms_utility 100.0%
Dbms_Error 0.0%
An internal LOB is _____.
A table 0.0%
A column that is a primary key 0.0%
Stored in the database 100.0%
A file stored outside of the database, with an internal pointer to it from a database column. 0.0%
CREATE OR REPLACE PACKAGE manage_emp 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...
CAL_TAX 0.0%
INSERT_EMP 0.0%
UPDATE_SAL 100.0%
DELETE_EMP 0.0%
UPDATE_EMP 0.0%
Evaluate the following PL/SQL block: DECLARE result BOOLEAN; BEGIN DELETE FROM EMPloyee WHERE dept_id IN (10,40,50); result:=SQL%ISOPEN; COMMIT: END; What will be the value of RESULT if three rows...
0 0.0%
3 0.0%
TRUE 25.0%
NULL 0.0%
FALSE 75.0%
Evaluate the following PL/SQL block: DECLARE v_low NUMBER:=2; v_upp NUMBER:=100; v_count NUMBER:=1; BEGIN FOR i IN v_low..v_low LOOP INSERT INTO test(results) VALUES (v_count) v_count:=v_count+...
0 0.0%
1 100.0%
2 0.0%
98 0.0%
100 0.0%
Examine the following code: CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(p_comm IN NUMBER); END comm_package; User MILLER executes the following code at 9:0...
g_comm has a value of 15 at 9:06am for Smith 0.0%
g_comm has a value of 15 at 9:06am for Miller 100.0%
g_comm has a value of 20 at 9:06am for both Miller and Smith 0.0%
g_comm has a value of 15 at 9:03 am for both Miller and Smith 0.0%
Examine the following code: CREATE OR REPLACE FUNCTION gen_email (first_name VARCHAR2, last_name VARCHAR2, id NUMBER) RETURN VARCHAR2 IS email_name VARCHAR2(19); BEGIN email_name := SUBSTR(first_na...
DROP gen_email; 0.0%
REMOVE gen_email; 0.0%
DELETE gen_email; 0.0%
DROP FUNCTION gen_email; 100.0%
Examine the following 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' AN...
DML trigger 0.0%
INSTEAD OF trigger 0.0%
Application trigger 0.0%
This is an invalid trigger 100.0%
Examine the following code: CREATE OR REPLACE TRIGGER UPD_SALARY FOR EACH ROW BEGIN UPDATE TEAM SET SALARY=SALARY+:NEW.SALARY WHERE ID=:NEW.TEAM_ID END; Which statement must be added to make this t...
AFTER UPDATE ON PLAYER 75.0%
AFTER SALARY UPDATE OF PLAYER 0.0%
AFTER UPDATE(SALARY) ON PLAYER 0.0%
AFTER UPDATE OF SALARY ON PLAYER 25.0%
Examine the following code: CREATE OR REPLACE TRIGGER update_emp AFTER UPDATE ON emp BEGIN INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE); END; / An UPDATE command is issued in the EMP...
1 100.0%
10 0.0%
None 0.0%
A value equal to the number of rows in the EMP table 0.0%
Examine the following package specification: CREATE OR REPLACE PACKAGE combine_all IS v_string VARCHAR2(100); PROCEDURE combine (p_num_val NUMBER); PROCEDURE combine (p_date_val DATE); PROCEDURE co...
PROCEDURE combine; 100.0%
PROCEDURE combine (p_no NUMBER); 0.0%
PROCEDURE combine (p_val_1 VARCHAR2, p_val_2 NUMBER); 0.0%
PROCEDURE combine_all(p_num_val VARCHAR2, p_char_val NUMBER); 0.0%
Examine the following procedure: PROCEDURE emp_salary (v_bonus BOOLEAN, V_raise BOOLEAN, V_issue_check in out BOOEAN) is BEGIN v_issue_check:=v_bonus or v_raise; END; If v_bonus=TRUE and v_raise=N...
TRUE 66.0%
FALSE 0.0%
NULL 0.0%
none 33.0%
Examine the following trigger: CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON Employee FOR EACH ROW DECLARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM employee; DMBS_OUTPUT.PUT_LINE( 'There...
Change the trigger type to a BEFORE DELETE 0.0%
Take out the COUNT function because it is not allowed in a trigger 40.0%
Remove the DBMS_OUTPUT statement because it is not allowed in a trigger 0.0%
Change the trigger to a statement-level trigger by removing FOR EACH ROW 60.0%
How can migration be done from a LONG to a LOB data type for a column?
Use the DBMS_MANAGE_LOB.MIGRATE procedure 0.0%
Use the UTL_MANAGE_LOB.MIGRATE procedure 0.0%
Use the DBMS_LOB.MIGRATE procedure 0.0%
You cannot migrate from a LONG to a LOB data type for a column 0.0%
Using ALTER TABLE statement 100.0%
If user defined error condition exists,Which of the following statements made a call to that exception?
Trap 0.0%
Raise 100.0%
call 0.0%
call Exception 0.0%
In Pl/Sql, if the where clause evaluates to a set of data, which lock is used?
Row Level Lock 33.0%
Page Level lock 66.0%
Column Level lock 0.0%
Exclusive Lock 0.0%
In which type of trigger can the OLD and NEW qualifiers can be used?
Row level DML trigger 100.0%
Row level system trigger 0.0%
Statement level DML trigger 0.0%
Row level application trigger 0.0%
SQL%ISOPEN always evaluates to false in case of a/an:
Explicit Cursor 0.0%
Implicit Cursor 100.0%
Paramertized Cursor 0.0%
Cursor with Arguments 0.0%
The CHECK_SAL procedure calls the UPD_SAL procedure. Both procedures are INVALID.Which command can be issued to recompile both procedures?
COMPILE PROCEDURE CHECK_SAL; 0.0%
COMPILE PROCEDURE UPD_SAL; 0.0%
ALTER PROCEDURE CHECK_SAL compile 100.0%
ALTER PROCEDURE UPD_sAL compile 0.0%
The oracle server implicitly opens a cursor to process:
A Sql select statement 50.0%
A PL/SQL Select statement 0.0%
DML Statements 50.0%
DDL Statements 0.0%
The technique employed by the Oracle engine to protect table data, when several people are accessing it is called:
Concurrency Control 100.0%
Program Control 0.0%
PL/SQL Control 0.0%
Locking 0.0%
What can be done with the DBMS_LOB package?
Use the DBMS_LOB.WRITE procedure to write data to a BFILE 0.0%
Use the DBMS_LOB.BFILENAME function to locate an external BFILE 0.0%
Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE 100.0%
Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed 0.0%
What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?
The validity of the SQL statement is established 0.0%
An area of memory is established to process the SQL statement 0.0%
The SQL statement is run and the number of rows processed is returned 0.0%
The area of memory established to process the SQL statement is released 100.0%
What happens when rows are found using a FETCH statement?
The cursor opens 0.0%
The cursor closes 0.0%
The current row values are loaded into variables 100.0%
Variables are created to hold the current row values 0.0%
What is the maximum number of handlers processed before the PL/SQL block is exited, when an exception occurs?
Only one 100.0%
All that apply 0.0%
All referenced 0.0%
None 0.0%
What type of trigger is created on the EMP table that monitors every row that is changed, and places this information into the AUDIT_TABLE?
FOR EACH ROW trigger on the EMP table 100.0%
Statement-level trigger on the EMP table 0.0%
FOR EACH ROW trigger on the AUDIT_TABLE table 0.0%
Statement-level trigger on the AUDIT_TABLE table 0.0%
When the procedure or function is invoked, the Oracle engine loads the compiled procedure or function in the memory area called:
PGA 100.0%
SGA 0.0%
Redo Log Buffer 0.0%
Data base buffer cache 0.0%
Which code is stored in the database when a procedure or function is created in SQL*PLUS?
Only P-CODE 33.0%
Only SOURCE code 33.0%
Both SOURCE CODE and P-CODE 33.0%
Neither SOURCE CODE or P-CODE 0.0%
Which command is used to disable all triggers on the EMPLOYEES table?
Multiple triggers on a table in one command cannot be disabled 0.0%
ALTER TRIGGERS ON TABLE employees DISABLE; 0.0%
ALTER employees DISABLE ALL TRIGGERS; 0.0%
ALTER TABLE employees DISABLE ALL TRIGGERS; 100.0%
Which cursor dynamically allows passing values to a cursor while opening another cursor?
Implicit Cursor 100.0%
User Defined Cursor 0.0%
Parameterized Cursor 0.0%
Explicit Cursor 0.0%
Which datatype does the cursor attribute '%ISOPEN' return?
BOOLEAN 100.0%
INTEGER 0.0%
NUMBER 0.0%
VARCHAR2 0.0%
Which of the following are identified by the "INSTEAD OF" clause in a trigger?
The view associated with the trigger 75.0%
The table associated with the trigger 25.0%
The event associated with the trigger 0.0%
The package associated with the trigger 0.0%
The statement level or for each row association to the trigger 0.0%
Which of the following is a benefit of using procedures and functions?
Procedures and Function increases the number of calls to the database 0.0%
Procedures and Function are reparsed for multiple users by exploiting shared SQL area 0.0%
Procedures and Function avoid reparsing for multiple users by exploiting shared SQL areas 100.0%
Testing of procedures and functions requires the database to be restarted, to clear out shared SQL areas and future access 0.0%
Which of the following is not a legal declaration?
declare v number(4); 0.0%
declare x,y varchar2(10); 50.0%
declare birthdate date not null; 0.0%
declare Sex boolean:=1; 50.0%
Which of the following statements is true regarding stored procedures?
A stored procedure uses the DECLARE keyword in the procedure specification to declare formal parameters 0.0%
A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification 0.0%
A stored procedure must have at least one executable statement in the procedure body 100.0%
A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters 0.0%
Which of the following statements is true?
Stored functions can be called from the SELECT and WHERE clauses only 0.0%
Stored functions do not permit calculations that involve database links in a distributed environment 0.0%
Stored functions cannot manipulate new types of data, such as longitude and latitude 0.0%
Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application 100.0%
Which package construct must be declared and defined within the packages body?
Exception 0.0%
Boolean Variable 0.0%
Public Procedure 0.0%
Private Procedure 100.0%
Which part of a database trigger determines the number of times the trigger body executes?
Trigger type 100.0%
Trigger body 0.0%
Trigger event 0.0%
Trigger timing 0.0%
Which precomplied word is called, which when encountered, immediately binds the numbered exception handler to a name?
Pragma 0.0%
Raise 0.0%
Trap 0.0%
Exception_init 100.0%
Which procedure is called after a row has been fetched to transfer the value, from the select list of the cursor into a local variable?
Row_value 100.0%
Column_value 0.0%
Raise_application 0.0%
Exception_init 0.0%
Which Section deals with handling of errors that arise during execution of the data manipulation statements, which makeup the PL/SQL Block?
Declare 0.0%
Exception 100.0%
Begin 0.0%
End 0.0%
Which table and column can be queried to see all procedures and functions that have been marked invalid?
USER_ERRORS table,STATUS column 0.0%
USER_OBJECTS table,STATUS column 100.0%
USER_ERRORS table,INVALID column 0.0%
USER_OBJECTS table,INVALID column 0.0%
Which table should be queried to check the status of a function?
USER_PROCS 0.0%
USER_OBJECTS 100.0%
USER_PROCEDURES 0.0%
USER_PLSQL_UNITS 0.0%
Which table should be queried to determine when the procedure was last compiled?
USER_PROCEDURES 0.0%
USER_PROCS 0.0%
USER_OBJECTS 100.0%
USER_PLSQL_UNITS 0.0%
Which two among the following programming constructs can be grouped within a package?
Cursor 0.0%
Constant 50.0%
Trigger 0.0%
Sequence 50.0%
View 0.0%
Which two statements among the following, regarding oracle database 10g PL/SQL support for LOB migration, are true?
Standard package functions accept LOBs as parameters 66.0%
Standard package function do not accept LOBs as parameters 0.0%
Implicit data conversion is supported for converting LOB to RAW 33.0%
Implicit data conversion is not supported for converting varchar to LOB 0.0%
Which two statements out of the following regarding packages are true?
Both the specification and body are required components of a package 0.0%
The package specification is optional, but the package body is required 0.0%
The package specification is required, but the package body is optional 50.0%
The specification and body of the package are stored separately in the database 50.0%
Which two statements, among the following, describe the state of a package variable after executing the package in which it is declared?
It persists across transactions within a session 50.0%
It persists from session to session for the same user 50.0%
It does not persist across transaction within a session 0.0%
It persists from user to user when the package is invoked 0.0%
It does not persist from session to session for the same user 0.0%
Which type of variable should be used to assign the value TRUE, FALSE?
Constant 0.0%
Scalar 100.0%
Reference 0.0%
Composite 0.0%