Semester 2 Midterm Exam
How would you invoke the constant km_to_mile from the
global_consts bodiless package at VARIABLE A?
SELECT trail_name, distance_in_km * VARIABLE A
FROM trails
WHERE park_name = 'YOSEMITE';
global_consts
(km_to_mile)
km_to_mile.global_consts
km_to_mile
(global_consts)
global_consts.km_to_mile
(*)
Examine the following code:
CREATE OR REPLACE PACKAGE emppack IS
PROCEDURE upd_emp
(p_empno IN NUMBER, p_salary IN NUMBER);
END emppack;
CREATE OR REPLACE PACKAGE BODY emppack IS
-- Line A
PROCEDURE upd_emp
(p_empno IN NUMBER, p_salary IN NUMBER) IS
BEGIN
IF NOT
sal_ok(p_salary) THEN
RAISE_APPLICATION_ERROR(-20201,'Invalid salary');
END IF;
END upd_emp;
FUNCTION
sal_ok(pf_salary NUMBER) RETURN BOOLEAN IS
BEGIN
IF pf_salary
> 50000 THEN RETURN FALSE;
ELSE RETURN
TRUE;
END IF;
END sal_ok;
END emppack;
What must be coded at Line A for this package to compile
successfully?
FUNCTION
sal_ok(pf_salary NUMBER) RETURN BOOLEAN; (*)
Nothing
is needed at Line A
FUNCTION
sal_ok(pf_salary NUMBER);
FUNCTION
sal_ok;
PROCEDURE
upd_emp (p_empno IN NUMBER, p_salary IN NUMBER);
A package initialization block
is executed automatically every time a user invokes any procedure or function
in the package. True or False?
True
False
(*)
When using a package function
in DML statements, which rules must you follow? (Choose three)
(Choose
all correct answers)
Changes
to a package variable could have an impact on another stored function (*)
Can
read or modify the table being changed by that DML statement
Cannot
execute a DML statement or modify the database (*)
Must
not end the current transaction (*)
Package HRPACK contains the
following public function:
FUNCTION empfunc (p_deptno NUMBER) RETURN NUMBER IS
BEGIN
UPDATE employees
SET salary =
salary * 1.1
WHERE
department_id = p_deptno;
RETURN
SQL%ROWCOUNT;
END empfunc;
What will happen when the following SQL statement is
executed?
SELECT department_name, hrpack.empfunc(department_id)
FROM departments;
The
SELECT will succeed because it is referencing a different table from the
function. (*)
The
SELECT will fail because you cannot return SQL%ROWCOUNT from a packaged
function.
The
SELECT will fail because you cannot call packaged functions from within a SQL
statement.
The
SELECT will fail because you cannot execute a DML statement from within a
query.
A
public function in a package is invoked from within a SQL statement. The
function's code can include a COMMIT statement. True or False?
True
False
(*)
Suppose you want to
automatically execute some code every time you make the first call to a package
in your session? For example, you want to automatically load a tax rate into a
package variable.
Which of the following should you use?
forward
declaration
None of
these.
bodiless
package
package
initialization block (*)
If a subprogram is public
(declared in the package specification), its detailed code can be written
anywhere in the package body without the need to use forward declarations. True
or False?
True
(*)
False
Which two of these functions
could not be in the same package?
1. FUNCTION get_emp (p1 DATE) RETURN VARCHAR2;
2. FUNCTION get_emp (p1 DATE, p2 NUMBER) RETURN VARCHAR2;
3. FUNCTION get_emp (p1 DATE, p2 NUMBER) RETURN NUMBER;
4. FUNCTION get_emp (p1 NUMBER, p2 DATE) RETURN VARCHAR2;
1 and 2
2 and 4
2 and 3
(*)
1 and 4
3 and 4
Which of the following are not
allowed in a bodiless package? (Choose three)
(Choose
all correct answers)
User-defined
exceptions
Global
variables
Private
variables (*)
DML
statements (*)
Subprograms
(*)
We want to remove both the
specification and the body of package CO_PACK from the database. Which of the
following commands will do this?
None of
these.
DROP
BOTH co_pack;
DROP
PACKAGE co_pack; (*)
DROP
PACKAGE BODY co_pack;
DROP
PACKAGE SPECIFICATION co_pack;
Examine the following package
specification:
CREATE OR REPLACE PACKAGE taxpack IS
CURSOR empcurs IS
SELECT * FROM employees;
PROCEDURE taxproc;
END mypack;
The package body of TAXPACK also includes a function called
TAXFUNC. Which one of the following statements is NOT true?
TAXPROC
is public and TAXFUNC is private.
The
package will not compile because you cannot declare a cursor in the
specification.
(*)
TAXPROC
can invoke TAXFUNC if TAXPROC is coded before TAXFUNC.
TAXPROC
can open the cursor.
The procedure
can be invoked by:
BEGIN
taxpack.taxproc;
END;
Your schema contains a package
called EMP_PKG. You want to remove the package body but not the specification.
The correct syntax to do this is: DROP BODY emp_pkg; True or False?
True
False
(*)
We want to remove the
specification (but not the body) of package BIGPACK from the database. Which of
the following commands will do this?
None of
these. (*)
DROP
PACKAGE HEADER bigpack;
DROP
PACKAGE SPECIFICATION bigpack;
DROP
PACKAGE bigpack;
DROP
PACKAGE bigpack SPECIFICATION;
SCOTT's schema contains a package
EMP_PKG which contains a public procedure EMP_SAL which accepts a NUMBER
parameter. Which of the following will invoke the procedure successfully?
scott.emp_pkg.emp_sal(101):
(*)
None of
these.
emp_pkg.emp_sal(101);
All of
these.
emp_sal(101);
Which of the following will
display the detailed code of the subprograms in package DEPTPACK in your schema
?
SELECT
text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE BODY'
ORDER BY line;
(*)
SELECT
text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'BODY'
ORDER BY line;
SELECT
text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE'
ORDER BY line;
SELECT
text FROM USER_SOURCE
WHERE object_name = 'DEPTPACK'
AND object_type = 'PACKAGE BODY'
ORDER BY line;
A public component declared in
the package specification can be referenced by a private component defined in
the package body. True or False?
True
(*)
False
Package NEWPACK contains several
procedures and functions, including private function PRIVFUNC. From where can
PRIVFUNC be invoked? (Choose two.)
(Choose
all correct answers)
From an
anonymous block
From
any procedure in NEWPACK (*)
From
any function in NEWPACK (*)
From
any private function in another package
From
any public procedure in another package
When a change is made to the
detailed code of a public procedure in a package (but not to the procedure's
name or parameters), both the specification and the body must be recompiled.
True or False?
True
False
(*)
In a package, public components
are declared in the specification but private components are not. True or
False?
True
(*)
False
The following package specification has been created:
CREATE OR REPLACE PACKAGE mypack IS
FUNCTION
myfunc(p_funcparam DATE) RETURN BOOLEAN;
PROCEDURE
myproc(p_procparam IN NUMBER);
END mypack;
Which of the following will correctly invoke the package
subprograms? (Choose two.)
(Choose
all correct answers)
myproc(40);
IF NOT
mypack.myfunc(SYSDATE) THEN
DBMS_OUTPUT.PUT_LINE('Message');
END IF;
mypack.myproc(35);
v_num
:= mypack.myproc(22);
mypack.myfunc('22-Jan-2007');
Which of the following statements
about packages is NOT true ?
The
specification must be created before the body.
The
body contains the detailed code of the subprograms.
Variables
can be declared in the body.
Cursors
can be declared in the specification.
All
procedures and functions must be declared in the specification. (*)
Package Specification DEPT_PACK
was created by the following code:
CREATE OR REPLACE PACKAGE dept_pack IS
PROCEDURE
ins_dept(p_deptno IN NUMBER);
FUNCTION
get_dept(p_deptno IN NUMBER) RETURN VARCHAR2;
END dept_pack;
Which of the following are correct syntax for invoking the
package subprograms? (Choose two.)
(Choose
all correct answers)
CREATE
PROCEDURE dept_proc IS
v_deptname
VARCHAR2(20);
BEGIN
v_deptname :=
dept_pack.get_dept(40);
END;
BEGIN
dept_pack.ins_dept(20);
END;
BEGIN
dept_pack.get_dept(20);
END;
DECLARE
v_deptname
VARCHAR2(20);
BEGIN
v_deptname :=
get_dept(50);
END;
BEGIN
dept_pack(30);
END;
Which of the following can be
included in a package?
procedures
Exceptions
variables
PL/SQL
types
All of
these. (*)
Which one of the following can
NOT be part of a Package ?
Functions
Procedures
Explicit
cursors
Global
variables
Triggers
(*)
Package MYPACK contains procedure MYPROC. You can see which
parameters MYPROC uses by executing: DESCRIBE mypack.myproc. True or False?
True
False
(*)
In which component of a package
is the full definition of a public procedure written?
Body
(*)
Neither
the body nor the specification
Specification
Both
the body and the specification
Every subprogram which has been
declared in a package specification must also be included in the package body.
Triue or False?
True
(*)
False
When a user session changes the
value of a package variable, the new value can immediately be seen by other
sessions. True or False?
True
False
(*)
A cursor is declared in a package
specification. User SIOBHAN opens the cursor and fetches the first three rows
from the cursor's active set, but does not close the cursor.
User FRED now connects to the database. FRED can immediately
fetch the next three rows without opening the cursor. True or False?
True
False
(*)
Package CURSPACK declares a global cursor in the package
specification. The package contains three public procedures: OPENPROC opens the
cursor; FETCHPROC fetches 5 rows from the cursor's active set; CLOSEPROC closes
the cursor.
What will happen when a user session executes the following
commands in the order shown?
curspack.openproc;
-- line 1
curspack.fetchproc; -- line 2
curspack.fetchproc; -- line 3
curspack.openproc;
-- line 4
curspack.fetchproc; -- line 5
curspack.closeproc; -- line 6
The
first 15 rows will be fetched.
The
first 5 rows will be fetched three times.
An
error will occur at line 2.
An
error will occur at line 4. (*)
The
first 10 rows will be fetched, then the first 5 rows will be fetched again.
In the following example, which
statement best fits in Line 1? (Choose 1)
DECLARE
v_more_rows_exist
BOOLEAN := TRUE;
BEGIN
-- Line 1
LOOP
v_more_rows_exist
:= curs_pkg.fetch_n_rows(3);
DBMS_OUTPUT.PUT_LINE('-------');
EXIT WHEN NOT
v_more_rows_exist;
END LOOP;
curs_pkg.close_curs;
END;
curs_pkg.open_curs;
(*)
EXIT
WHEN curs_pkg.emp_curs%NOTFOUND;
curs_pkg.emp_curs%ISOPEN;
curs_pkg.close_curs;
The UTL_FILE package contains
several exceptions exclusively used in this package. Which are they? (Choose 3)
(Choose
all correct answers)
WRITE_ERROR
(*)
ZERO_DIVIDE
NO_DATA_FOUND
INVALID_OPERATION
(*)
INVALID_PATH
(*)
Using the FOPEN function, you can
do which actions with the UTL_FILE package? (Choose 2)
(Choose
all correct answers)
It is
used to find out how much free space is left on an operating system disk.
It is
used to append to a file until processing is complete. (*)
It is
used to read and write text files stored outside the database. (*)
It is
used to manipulate large object data type items in columns.
Which of the following best
describes the purpose of the UTL_FILE package?
It is
used to find out how much free space is left on an operating system disk.
It is
used to load binary files such as employees' photos into the database.
It is
used to query CHAR and VARCHAR2 columns in tables.
It is
used to read and write text files stored outside the database. (*)
Which general exceptions may be handled by the UTL_FILE
package? (Choose 2)
(Choose
all correct answers)
VALUE_ERROR
(*)
NO_DATA_FOUND
(*)
TOO_MANY_ROWS
ZERO_DIVIDE
Which of the following exceptions
can be raised ONLY when using the UTL_FILE package? (Choose two.)
(Choose
all correct answers)
INVALID_PATH
(*)
READ_ERROR
(*)
VALUE_ERROR
E_MYEXCEP
NO_DATA_FOUND
The DBMS_OUTPUT gives programmers
an easy-to-use interface to see, for instance, the current value of a loop
counter, or whether or not a program reaches a particular branch of an IF
statement. (True or False?)
True
(*)
False
The following example code will
compile successfully. True or False?
CREATE OR REPLACE PROCEDURE dept_proc IS
TYPE t_dept IS TABLE
OF departments%ROWTYPE INDEX BY BINARY_INTEGER;
BEGIN
(p_small_arg IN
NUMBER, p_big_arg OUT NOCOPY t_dept);
-- remaining code
END dept_proc;
True
(*)
False
You want to take make a copy of
all the cities in the world listed in the cities table, which contains millions
of rows. The following procedure accomplishes this efficiently. True or False?
CREATE OR REPLACE PROCEDURE copy_cities IS
TYPE t_cities IS
TABLE OF cities%ROWTYPE INDEX BY BINARY_INTEGER;
v_citiestab t_emp;
BEGIN
SELECT * BULK
COLLECT INTO v_citiestab FROM cities;
FORALL i IN
v_citiestab.FIRST..v_citiestab.LAST
INSERT INTO new_cities
VALUES v_citiestab(i);
END copy_cities;
True
(*)
False
Deterministic means the function will always return the same
output return value for any given set of input argument values. True or False?
True
(*)
False
To create a list of the top 20
movies from a catalog of millions of titles, the following statement grabs
those rows using a collection. True or False?
...
TYPE nametab IS
TABLE OF movies.title%TYPE;
Title_tab nametab;
...
SELECT title BULK COLLECT INTO title_tab FROM movies ORDER
BY rental_count DESC;
...
True
(*)
False
FORALL can be used with any DML
statement. True or False?
True
(*)
False
A function-based index may be
made using your own functions, but only if the function is created using the
DETERMINISTIC clause. True or False?
True
(*)
False
Dynamic SQL enables
data-definition, data-control, or session-control statements to be written and
executed from PL/SQL.
True
(*)
False
Name two reasons for using
Dynamic SQL.
(Choose
all correct answers)
Enables
system control statements to be written and executed from PL/SQL
Enables
data-definition statements to be written and executed from PL/SQL (*)
Creates
a SQL statement with varying column data, or different conditions (*)
Avoids
errrors at compile time of DML statements
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 can never drop a table from inside a function
Because
the PL/SQL compiler cannot check if the argument of p_tab_name is a valid
table-name (*)
Because
you do not have the privilege needed to drop a table
Because
you cannot use RETURN in the exception section
A public packaged procedure
contains the following SQL statement:
UPDATE employees
SET salary = salary * 1.1;
When is this SQL statement parsed?
Only
the first time the procedure is executed
When
the package body is created (*)
When
the package is loaded into memory
When
the package specification is created
When
the package header is loaded into memory
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)
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;
(*)
v_sql_statement
:= 'DROP TABLE ';
EXECUTE IMMEDIATE v_sql_statement p_table_name;
EXECUTE
IMMEDIATE 'DROP TABLE ' || p_table_name;
(*)
The DBMS_SQL package is easier to
use than EXECUTE IMMEDIATE. True or False?
True
False
(*)
AJO_QQ poker
ReplyDeletekami dari agen poker terpercaya dan terbaik di tahun ini
Deposit dan Withdraw hanya 15.000 anda sudah dapat bermain
di sini kami menyediakan 9 permainan dalam 1 aplikasi
- play aduQ
- bandar poker
- play bandarQ
- capsa sunsun
- play domino
- play poker
- sakong
-bandar 66
-perang baccarat (new game )
Dapatkan Berbagai Bonus Menarik..!!
PROMO MENARIK
di sini tempat nya Player Vs Player ( 100% No Robot) Anda Menang berapapun Kami
Bayar tanpa Maksimal Withdraw dan Tidak ada batas maksimal
withdraw dalam 1 hari.Bisa bermain di Android dan IOS,Sistem pembagian Kartu
menggunakan teknologi yang mutakhir dengan sistem Random
Permanent (acak) |
Whatshapp : +855969190856