Section 9
Which
of the following is found in a function and not a procedure?
Return
statement in the header (*)
An
exception section
IN
parameters
Local
variables in the IS/AS section
To create a function
successfully, the following steps should be performed.
A Re-execute the
code until it compiles correctly
B Write the code
containing the CREATE or REPLACE FUNCTION followed by the function code
C Test the function
from a SQL statement or an anonymous block
D If the function
fails to compile, correct the errors
E Load the code into
Application Express
F Execute the code
in Application Express
What is the correct order to perform these steps?
D,B,E,F,A,C
A,B,E,F,D,C
B,E,F,D,A,C
(*)
B,C,E,F,D,A
To create a function
successfully,the first step is to test the code in an anonymous block.
True
False
(*)
Which statement is true
regarding the following subprogram?
PROCEDURE at_proc IS
PRAGMA
AUTONOMOUS_TRANSACTION;
dept_id NUMBER := 90;
BEGIN
UPDATE ...
INSERT ...
END at_proc;
The
subprogram's success depends on the calling program.
The
subprogram's success is independent of the calling program. (*)
The
subprogram cannot do a COMMIT.
The
subprogram will fail because the RETURN is not specified.
How do you specify that you
want a procedure MYPROCA to use Invoker's Rights?
CREATE
OR REPLACE PROCEDURE myproca
AUTHID OWNER IS...
CREATE
OR REPLACE PROCEDURE myproca
AUTHID CURRENT_USER IS...
(*)
Invoker's
Rights are the default, therefore no extra code is needed.
ALTER
PROCEDURE myproca TO INVOKER;
GRANT
INVOKER TO myprocA;
You want to allow user JOE to query the CD_DETAILS table in
your schema. Which command should you use?
GRANT
SELECT TO joe ON cd_details;
GRANT
QUERY ON cd_details TO joe;
GRANT
cd_details TO joe;
GRANT
SELECT ON joe TO cd_details;
GRANT
SELECT ON cd_details TO joe; (*)
When a database object is first
created, only its owner (creator) and the Database Administrator are privileged
to use it. True or False?
True
(*)
False
What is one of the main
purposes of the Data Dictionary?
To
prevent users from accidentally dropping tables
To
ensure correct spelling of the values in VARCHAR2 table columns
To
provide a list of all objects in your schema, but not in other users' schemas
To
translate data from one language to another
To
provide a structured list of all objects in the database (*)
User BOB is not a database
administrator. BOB wants to see the names of all the tables in his schema, as
well as all the tables in other users' schemas which he has privileges to use.
Which Data Dictionary view would BOB query to do this?
USER_TABLES
DBA_TABLES
None of
these.
ALL_TABLES
(*)
USER_TAB_COLUMNS
You have forgotten the name of
the Dictionary view USER_TABLES. Which of the following statements is the best
and quickest way to remind yourself?
SELECT
* FROM dict
WHERE table_name LIKE 'USER%TAB%';
(*)
Phone
the database administrator.
Read
the online Oracle documentation at http://technet.oracle.com.
SELECT
* FROM dictionary
WHERE table_name = 'USER_TABLES';
SELECT
* FROM dictionary
WHERE table_name = 'USER%';
The database administrator has granted the DROP ANY
PROCEDURE privilege to user KIM. This allows Kim to remove other users'
procedures and functions from the database. How would Kim now drop function
GET_EMP, which is owned by user MEHMET?
None of
these
DROP
FUNCTION mehmet.get_emp (*)
DROP
FUNCTION get_emp FROM mehmet
DROP
PROGRAM mehmet.get_emp
DROP
PROCEDURE mehmet.get_emp
Which view would you query to see
the detailed code of a procedure?
user_source
(*)
user_errors
user_objects
user_procedures
user_dependencies
The following function has been
created:
CREATE OR REPLACE FUNCTION upd_dept
(p_dept_id IN
departments.department_id%TYPE)
RETURN NUMBER IS
BEGIN
UPDATE departments
SET
department_name = 'Accounting'
WHERE
department_id = p_dept_id;
RETURN p_dept_id;
END;
Which of the following will execute successfully?
DELETE
FROM employees
WHERE department_id = upd_dept(80);
(*)
DELETE
FROM departments
WHERE department_id = upd_dept(department_id);
SELECT
upd_dept(department_id)
FROM employees;
SELECT
upd_dept(80)
FROM dual;
Why will the following statement
fail?
SELECT employee_id, tax(p_value => salary)
FROM employees;
User-defined
functions are not allowed in the SELECT clause.
The
statement will execute and not fail.
The
data type for the tax variable does not match the data type for salary.
Name
notation is not allowed. (*)
A benefit of user-defined
functions is that the function can accept any SQL or PL/SQL data type. True or
False?
True
False
(*)
No comments:
Post a Comment