1. Function DOUBLE_SAL has been created as follows: CREATE OR REPLACE FUNCTION double_sal (p_salary IN employees.salary%TYPE) RETURN NUMBER IS BEGIN RETURN(p_salary * 2); END; Which of the following calls to DOUBLE_SAL will NOT work?
- SELECT * FROM employees WHERE double_sal(salary) > 20000;
- UPDATE employees SET salary = double_sal(salary);
- SELECT * FROM employees ORDER BY double_sal(salary) DESC;
- SELECT last_name, double_sal(salary) FROM employees;
- None, they will all work (*)
2. Which of the following is a benefit of user-defined functions? (Choose 3)
(Choose all correct answers)
- They can do the same job as built-in system functions such as UPPER and ROUND.
- They can be used in a WHERE clause to filter data and thereby increase efficiency. (*)
- They can add business rules to the database and can be reused many times. (*)
- They can often be used inside SQL statements. (*)
3. You want to create a function which can be used in a SQL statement. Which one of the following can be coded within your function?
- COMMIT;
- An OUT parameter
- RETURN BOOLEAN
- One or more IN parameters (*)
4. Which of the following is NOT a benefit of the Data Dictionary?
- It allows the PL/SQL compiler to check for object existence; for example, when creating a procedure which references a table, the PL/SQL compiler can check that the table exists.
- It allows us to check which system privileges have been granted to us.
- It will speed up the execution of SELECT statements in which the WHERE clause column is not indexed. (*)
- It allows us to remind ourselves of the names of our tables, in case we have fogotten them.
5. What is one of the main purposes of the Data Dictionary?
- 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 prevent users from accidentally dropping tables
- To provide a structured list of all objects in the database (*)
- To ensure correct spelling of the values in VARCHAR2 table columns
6. A user executes the following statement:
CREATE INDEX fn_index ON employees(first_name);
What output will the following statement now display:
SELECT index_name
FROM user_indexes
WHERE index_name LIKE 'fn%';
- fn_index FN_INDEX
- fn_index
- FN_INDEX
- No output will be displayed (*)
7. Which dictionary view will list all the PL/SQL subprograms in your schema?
- user_dependencies
- user_objects (*)
- user_source
- user_procedures
- user_subprograms
8. Procedure ins_emp accepts an employee_id as an IN parameter and attempts to insert a row with that employee_id into the EMPLOYEES table. Ins_emp does not contain an exception section. A second procedure is created as follows:
CREATE OR REPLACE PROCEDURE call_ins_emp IS
BEGIN
ins_emp(99); -- this employee does not exist
ins_emp(100); -- this employee already exists
ins_emp(999); -- this employee does not exist
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An exception occurred');
END;
When call_ins_emp is executed, (assuming Auto Commit is turned on), which rows will be inserted into the EMPLOYEES table?
- No rows will be inserted
- All three rows will be inserted
- 99 and 999
- 99 only (*)
- 999 only
9. Function GET_JOB accepts an employee id as input and returns that employee's job id. Which of the following calls to the function will NOT work?
- v_job_id := get_job(100);
- IF get_job(100) = 'IT_PROG' THEN ...
- DBMS_OUTPUT.PUT_LINE(get_job(100));
- get_job(100,v_job_id); (*)
10. Examine the following code:
CREATE OR REPLACE FUNCTION add_func
(p_param1 NUMBER, p_param2 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (p_param1 + p_param2);
END;
What will be displayed when the following SQL statement is executed?
SELECT add_func(6, add_func(3,8)) FROM dual;
- 17 (*)
- 11
- 23
- An error message will be displayed because you cannot nest user-defined functions.
- 66
11. Based on the following function definition:
Create function annual_comp
(sal employees.salary%type,
comm_pct IN employees.commission%type)
...
Which one of the following is an incorrect call for annual_comp?
- Select employee_id, annual_comp(salary, commission_pct)from employees;
- Select employee_id, annual_comp(salary)from employees; (*)
- Execute dbms_output.put_line(annual_comp (1000,.2));
- DeclareAnn_comp number (6,2);Begin...
- Ann_comp := annual_comp(1000,.2);...End;
12. How do you specify that you want a procedure MYPROCA to use Invoker's Rights?
- CREATE OR REPLACE PROCEDURE myproca
- AUTHID CURRENT_USER IS... (*)
- CREATE OR REPLACE PROCEDURE myproca
- AUTHID OWNER IS...
- GRANT INVOKER TO myprocA;
- ALTER PROCEDURE myproca TO INVOKER;
- Invoker's Rights are the default, therefore no extra code is needed.
13. What will happen when the following procedure is executed?
PROCEDURE log_usage (p_card_id NUMBER, p_loc NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
INSERT INTO log_table (card_id, location, tran_date)
VALUES (p_card_id, p_loc, SYSDATE);
COMMIT;
END log_usage;
- The subprogram will fail because the PRAGMA statement must be before IS.
- The program will compile successfully.
- The subprogram will fail because it is missing AUTHID CURRENT_USER before IS.
- The compilation will fail because a semicolon after AUTONOMOUS_TRANSACTION is required. (*)
14. USERB creates a function called SEL_PROC which includes the statement:
SELECT ... FROM usera.employees ...;
USERC needs to execute UserB's procedure. What privileges are needed for this to work correctly? (Choose two.)
(Choose all correct answers)
- UserA needs EXECUTE on userB.sel_proc
- UserB needs SELECT on userA.employees (*)
- UserC needs EXECUTE on userB.sel_proc (*)
- UserC needs EXECUTE on Userb
- UserC needs SELECT on userA.employees
15. You have created a function called USEFUL. You want every database user to be able to invoke the function. Which command would you use to do this?
- GRANT EXECUTE ON useful TO *;
- GRANT EXECUTE ON useful TO PUBLIC; (*)
- GRANT useful TO PUBLIC;
- GRANT TO PUBLIC EXECUTE ON useful;
- GRANT useful TO WORLD;
16. To be able to invoke a package subprogram from outside the package, it must be declared in the package:
- Body
- Specification
- Body and the specification (*)
- None of these.
17. In which component of a package is the full definition of a public procedure written?
- Neither the body nor the specification
- Both the body and the specification
- Specification
- Body (*)
18. 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.)
- CREATE PROCEDURE dept_proc IS
BEGIN
v_deptname := dept_pack.get_dept(40);
END;
(*)
- BEGIN
END;
BEGIN
dept_pack.ins_dept(20);
END;
(*)
- BEGIN
END;
- DECLARE
BEGIN
v_deptname := get_dept(50);
END;
19. Every subprogram which has been declared in a package specification must also be included in the package body. Triue or False?
- True (*)
- False
20. The two parts of a package are stored as separate objects in the database. True or False?
- True (*)
- False
21. 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 (*)
22. 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 open the cursor.
- TAXPROC can invoke TAXFUNC if TAXPROC is coded before TAXFUNC.
- The procedure can be invoked by:
taxpack.taxproc;
END;
.
23. What will be displayed when a user executes the following statement?
SELECT object_name FROM user_objects
WHERE object_type LIKE 'PACK%';
- The names of all package specifications in the user's schema
- The detailed code of all packages in the user's schema
- The names of all packages which can be invoked by the user
- The parameters which must be used when invoking all packaged subprograms in the user's schema
- The names of all package specifications and package bodies in the user's schema (*)
24. 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 (*)
25. Package OLDPACK is in your schema. What will happen when the following statement is executed?
DROP PACKAGE oldpack;
- Both the specification and the body will be dropped. (*)
- The statement will fail because you must drop the body before you can drop the specification.
- The specification will be dropped but the body will be retained.
- The body will be dropped but the specification will be retained.
26. 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 (*)
27. 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 open the cursor.
TAXPROC can invoke TAXFUNC if TAXPROC is coded before TAXFUNC.
The procedure can be invoked by:
BEGIN
taxpack.taxproc;
END;
28. What will be displayed when a user executes the following statement?
SELECT object_name FROM user_objects
WHERE object_type LIKE 'PACK%';
- The names of all package specifications in the user's schema
- The detailed code of all packages in the user's schema
- The names of all packages which can be invoked by the user
- The parameters which must be used when invoking all packaged subprograms in the user's schema
- The names of all package specifications and package bodies in the user's schema (*)
29. 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 (*)
30. Package OLDPACK is in your schema. What will happen when the following statement is executed?
DROP PACKAGE oldpack;
- Both the specification and the body will be dropped. (*)
- The statement will fail because you must drop the body before you can drop the specification.
- The specification will be dropped but the body will be retained.
- The body will be dropped but the specification will be retained.
31. Which general exceptions may be handled by the UTL_FILE package? (Choose 2)
(Choose all correct answers)
- VALUE_ERROR (*)
- ZERO_DIVIDE
- NO_DATA_FOUND (*)
- TOO_MANY_ROWS
32. Which DBMS_OUTPUT package subprogram places text into the buffer at Line 1? (Choose one)
IF v_bool1 AND NOT v_bool2 AND v_number < 25 THEN
--Line 1
ELSE
...
END IF;
DBMS_OUTPUT.NEW_LINE;
- DBMS_OUTPUT.PUT('IF branch was executed'); (*)
- DBMS_OUTPUT.PUT_LINE('IF branch was executed');
- DBMS_OUTPUT.GET_LINE('IF branch was executed');
- DBMS_OUTPUT.NEW_LINE('IF branch was executed');
33. 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
34. What will be displayed when the following code is executed?
BEGIN
DBMS_OUTPUT.PUT('I do like');
DBMS_OUTPUT.PUT_LINE('to be');
DBMS_OUTPUT.PUT('beside the seaside');
END;
I do like
to be
beside the seaside
I do liketo be
(*)
I do like to be beside the seaside
I do like to be
I do like to be
beside the seaside
35. The UTL_FILE package can be used to read and write binary files such as JPEGs as well as text files. True or False?
- True
- False (*)
36. Which of the following procedures is not valid for the UTL_MAIL package
- SEND_ATTACH_RAW
- SEND_ATTACH_BOOLEAN (*)
- SEND_ATTACH_VARCHAR2
- All are valid.
- SEND
37. DBMS_OUTPUT.PUT_LINE can be invoked from inside a private packaged function. True or False?
- True (*)
- False
38. Why is it better to use DBMS_OUTPUT only in anonymous blocks, not inside stored subprograms such as procedures?
- Because DBMS_OUTPUT cannot be used inside procedures
- Because anonymous blocks display messages while the block is executing, while procedures do not display anything until their execution has finished
- Because DBMS_OUTPUT should be used only for testing and debugging PL/SQL code (*)
- Because DBMS_OUTPUT can raise a NO_DATA_FOUND exception if used inside a packaged procedure
39. Which of the following best describes the purpose of the UTL_FILE package?
- It is used to read and write text files stored outside the database. (*)
- It is used to load binary files such as employees' photos into the database.
- It is used to find out how much free space is left on an operating system disk.
- It is used to query CHAR and VARCHAR2 columns in tables.
40. The SEND procedure is for sending messages without attachments. True or False?
- True (*)
- False
41. 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 (*)
42. Users A and B call the same procedure in a package to initialize a global variable my_pkg.g_var. What will be the value of my_pkg.g_var for User A at Point A?
User A: my_pkg.g_var is 10
User B: my_pkg.g_var is 10
User A: my_pkg.g_var is 50
User B: my_pkg.g_var is 25
Point A
- 25
- 50 (*)
- 10
43. 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 (*)
44. 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.close_curs;
- curs_pkg.emp_curs%ISOPEN;
- EXIT WHEN curs_pkg.emp_curs%NOTFOUND;
- curs_pkg.open_curs; (*)
45. A package's state is initialized when the package is first loaded. True or False?
- True (*)
- False
0 Komentar