Section 12
The
following procedure adds a column of datatype DATE to the EMPLOYEES table. The
name of the new column is passed to the procedure as a parameter.
CREATE OR REPLACE PROCEDURE addcol
(p_col_name IN
VARCHAR2) IS
v_first_string
VARCHAR2(100) := 'ALTER TABLE EMPLOYEES ADD (';
v_second_string
VARCHAR2(6) := ' DATE)';
BEGIN
... Line A
END;
Which of the following will work correctly when coded at
line A? (Choose two.)
(Choose
all correct answers)
EXECUTE
IMMEDIATE v_first_string || p_col_name || v_second_string;
(*)
EXECUTE
IMMEDIATE 'v_first_string' || p_col_name || 'v_second_string';
EXECUTE
v_first_string || p_col_name || v_second_string;
v_first_string
:= v_first_string || p_col_name;
EXECUTE IMMEDIATE v_first_string || v_second_string;
(*)
v_first_string
|| p_col_name || v_second_string;
You want to create a function
which drops a table. You write the following code:
CREATE OR REPLACE FUNCTION droptab
(p_tab_name IN
VARCHAR2)
RETURN BOOLEAN IS
BEGIN
DROP TABLE
p_tab_name;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END;
Why will this procedure not compile successfully?
Because
you cannot use RETURN in the exception section
Because
the PL/SQL compiler cannot check if the argument of p_tab_name is a valid table-name
(*)
Because
you can never drop a table from inside a function
Because
you do not have the privilege needed to drop a table
Name two reasons for using
Dynamic SQL.
(Choose
all correct answers)
Creates
a SQL statement with varying column data, or different conditions (*)
Enables
data-definition statements to be written and executed from PL/SQL (*)
Enables
system control statements to be written and executed from PL/SQL
Avoids
errrors at compile time of DML statements
Which of the following SQL
statements can be included in a PL/SQL block only by using Dynamic SQL? (Choose
two.)
(Choose
all correct answers)
DELETE
SAVEPOINT
ALTER
(*)
SELECT
..... FOR UPDATE NOWAIT
GRANT
(*)
A public packaged procedure
contains the following SQL statement:
UPDATE employees
SET salary = salary * 1.1;
When is this SQL statement parsed?
When
the package header is loaded into memory
When
the package is loaded into memory
When
the package body is created (*)
Only
the first time the procedure is executed
When
the package specification is created
Examine the following procedure, which drops a table whose
name is passed as an IN parameter:
CREATE OR REPLACE PROCEDURE drop_tab
(p_table_name IN
VARCHAR2) IS
v_sql_statement
VARCHAR2(100);
BEGIN
...
END;
Which of the following will work correctly when coded in the
procedure's executable section? (Choose two.)
(Choose
all correct answers)
v_sql_statement
:= 'DROP TABLE ';
EXECUTE IMMEDIATE v_sql_statement p_table_name;
EXECUTE
IMMEDIATE 'DROP TABLE ' || p_table_name;
(*)
EXECUTE
IMMEDIATE 'DROP TABLE p_table_name';
v_sql_statement
:= 'DROP TABLE ' || p_table_name;
EXECUTE IMMEDIATE v_sql_statement;
(*)
v_sql_statement
:= 'DROP TABLE ' || p_table_name;
EXECUTE IMMEDIATE 'v_sql_statement';
The easiest way to include DDL
statements in a PL/SQL block is to use the DBMS_SQL package. True or False?
True
False
(*)
What does the RETURNING clause
do in the example below?
CREATE OR REPLACE PROCEDURE new_dept
(p_dept_name IN
departments.name%TYPE) IS
v_new_dept_id
departments.dept_id%TYPE;
BEGIN
INSERT INTO
departments (dept_id, name)
VALUES
dept_seq.NEXTVAL, p_dept_name
RETURNING
dept_seq.CURRVAL INTO v_new_dept_id;
DBMS_OUTPUT.PUT_LINE(p_dept_name ||' is department number ' ||
v_new_dept_id);
END new_dept;
Inserts
the new department id in the department table
Performs
the SELECT statement to determine the department id of the new department (*)
Uses
the new department number in a cursor
What is wrong with this code
example?
CREATE OR REPLACE PROCEDURE insert_emps IS
TYPE t_emp IS TABLE
OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
v_emptab t_emp;
BEGIN
FORALL i IN
v_emptab.FIRST..v_emptab.LAST
INSERT INTO
employees VALUES v_emptab(i);
END LOOP;
END insert_emps;
The
phrase should be FOR ALL.
Nothing
is wrong; it will compile successfully.
FORALL
does not require END LOOP. (*)
v_emptab
is incorrectly typed.
Which of the following are NOT
benefits of using the NOCOPY hint? (Choose two)
(Choose
all correct answers)
Faster
because a single copy of the data is used
Eliminates
extra processing
Uses a
larger block of server memory for faster access (*)
Safer
because it uses passing by value (*)
Efficient
since it uses less memory
In the
following example, where do you place the phrase DETERMINISTIC?
CREATE OR REPLACE FUNCTION total_sal
(p_dept_id IN --
Position A
employees.department_id%TYPE)
RETURN NUMBER --
Position B
IS v_total_sal
NUMBER;
BEGIN
SELECT SUM(salary)
INTO v_total_sal
FROM employees
WHERE department_id = p_dept_in;
RETURN v_total_sal
-- Position C;
END total_sal;
Position
A
Position
B (*)
Position
C
In the following example, where
do you place the phrase BULK COLLECT?
...
BEGIN
SELECT -- Position A
salary --
Position B
INTO v_saltab --
Position C
FROM employees
WHERE department_id = 20 ORDER BY salary
-- Position D
;
...
Position
A
Position
B (*)
Position
C
Position
D
What is the main purpose for
using the RETURNING clause?
Return
more readily any exceptions that are raised by the statement
Improve
performance by minimizing the number of statements
Improve
performance by returning a single value
Improve
performance by making one call to the SQL engine (*)
What is the correct syntax to use
the RETURNING phrase at Position A?
DECLARE
TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE,
salary employees.salary%TYPE);
emp_info EmpRec;
emp_id NUMBER := 100;
BEGIN
UPDATE employees
SET salary = salary
* 1.1 WHERE employee_id = emp_id
-- Position A
dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ',
who now makes ' || emp_info.salary);
END;
RETURNING
FROM emp_info;
last_name,
salary RETURNING INTO emp_info;
RETURNING
last_name, salary TO emp_info;
RETURNING
last_name, salary INTO emp_info; (*)
Where would you place the BULK
COLLECT statement in the following example?
DECLARE
TYPE DeptRecTab IS
TABLE OF departments%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT department_id, department_name, manager_id,
location_id
-- Position A
FROM departments
WHERE department_id
> 70;
BEGIN
OPEN c1
-- Position B;
FETCH c1
-- Position C
INTO dept_recs;
END;
Mark for Review
(1) Points
Position
A
Position
B
Position
C (*)
No comments:
Post a Comment