 |
| Creating Functions |
01. Which statement is true?
a. Stored functions can be called from the SELECT and WHERE clauses only.
b. Stored functions do not permit calculations that involve database links in a distributed environment.
c. Stored functions cannot manipulate new types of data, such as longitude and latitude.
d. Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application.
Answer: D
02. To be callable form a SQL expression, a user-defined function must do what?
a. Be stored only in the database.
b. Have both IN and OUT parameters
c. Use the positional notation for parameters
d. Return a BOOLEAN or VARCHAR2 data type.
Answer: A
03. Which two program declarations are correct for stored program unit? (Choose two)
a. CREATE OR REPLACE FUNCTION tax_amt (b_id NUMBER) RETURN NUMBER
b. CREATE OR REPLACE PROCEDURE tax_amt (b_id NUMBER) RETURN NUMBER
c. CREATE OR REPLACE PROCEDURE tax_amt (b_id NUMBER, b_amount OUT NUMBER)
d. CREATE OR REPLACE FUNCTION tax_amt (b_id NUMBER) RETURN NUMBER (10,2)
e. CREATE OR REPLACE PROCEDURE tax_amt (b_id NUMBER, b_amount OUT NUMBER (10,2))
Answer: A, C
04. When creating a function in which section will you typically find a return key word?
a. Header only
b. Declarative
c. Executable and Header
d. Executable and Exception handling
Answer: C
05. Which two statements are true? (Choose two)
a. A function must return a value.
b. A procedure must return a value.
c. A function executes a PL/SQL statement.
d. A function is invoked as part of an expression.
e. A procedure must have a return data type specify in its declaration.
Answer: A, D
06. Which allows a PL/SQL user define a function?
a. NEXTVAL
b. FROM clause of the SELECT FOR UPDATE command
c. HAVING clause of the SELECT command
d. ALTER TABLE command
Answer: C
07. Function can be invoked—
a. As a part of pl/sql expression.
b. As a part of sql statement.
c. As a part of executable statement.
d. Can be anywhere.
Answer: A, B
08. The RETURN data type must not include a size specification—
a. TRUE
b. FALSE
Answer: A
09. The RETURN statement must be including in executable section.
a. TRUE
b. FALSE
Answer: A
10. Examine this code:
CREATE OR REPLACE FUNCTION gen_email_name
( p_first_name VARCHAR2, p_last_name VARHCAR2, p_id NUMBER )
RETURN VARCHAR2
IS
v_email_name VARCHAR2(19);
BEGIN
v_email_name := SUBSTR (p_first_name, 1,1,) ||
SUBSTR ( p_last_name, 1, 7) ||‘@Oracle,com’ ;
UPDATE employees
SET email = v_email_name
WHERE employee_id = p_id ;
RETURN v_email_name ;
END ;
/
You this select statement :
SELECT first_name, last_name,
gen_email_name ( first_name, last_name, 108 ) EMAIL
FROM employees ;
What occurs?
A. Employee 108 has his email name updated based on the return result of the function.
B. The statement fails because functions called from SQL expressions cannot perform DML
C. The statement fails because the function does not contain code to end the transaction.
D. The SQL statement executes successfully, because update and delete statements are ignoring in stored functions called from SQL expressions.
E. The SQL statement executes successfully and control is passed to the calling environment.
Answer: B
11. Examine this code:
CREATE OR REPLACE FUNCTION gen_email_name
( p_first_name VARCHAR2, p_last_name VARHCAR2, p_id NUMBER )
RETURN VARCHAR2 IS
v_email_name VARCHAR2(19);
BEGIN
v_email_name := SUBSTR (p_first_name, 1,1,) ||
SUBSTR ( p_last_name, 1, 7) ||‘@Oracle,com’ ;
UPDATE employees
SET email = v_email_name
WHERE employee_id = p_id ;
RETURN v_email_name ;
END ;
/
Which statement removes the function?
A. DROP FUNCTION gen_email_name;
B. REMOVE gen_email_name;
C. DELETE gen_email_name;
D. Missing
Answer: A
12. Examine this code:
CRATE OR REPLACE STORED FUNCTION GET_sal
( p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE )
RETURN NUMBER
IS
v_salary NUMBER ;
v_raise NUMBER(8,2) ;
BEGIN
SELECT salary
INTO v_salary
FROM employees
WHERE employee_id = p_employee_id ;
v_raise := p_raise_amt * v_salary ;
RETURN v_raisel ;
END ;
/
Which statement is true?
A. This statement creates a stored procedure named get_sal;
B. This statement returns a raise amount based on an employee id.
C. This statement creates a stored function named get_sal with a status of invalid.
D. This statement creates a stored function named get_sal.
E. This statement fails.
Answer: C
13. Examine this code:
CREATE OR REPLACE FUNCTION calc_sal ( p_salary NUMBER )
RETURN NUMBER
IS
v_raise NUMBER (4,2) DEFAULT 1.08 ;
BEGIN
RETURN v_raise * p_salary ;
END calc_sal ;
Which statement accurately call the stored function calc_sal: (Choose two)
A. UPDATE employees ( calc_sal ( salary ) )
SET salary = salary * calc_sal ( salary ) ;
B. INSERT calc_sal ( salary )
INTO employees
WHERE department_id = 60 ;
C. DELETE FROM employees ( calc_sal ( salary ) )
WHERE calc_sal ( salary ) > 1000 ;
D. SELECT salary, calc_sal ( salary )
FROM employees
WHERE department_id = 60 ;
E. SELECT last_name, salary, calc_sal ( salary )
FROM employees
ORDER BY calc ( sal ( salary ) ;
Answer: D
14. Which two program declarations are correct for stored program unit? (Choose two)
A. CREATE OR REPLACE FUNCTION tax_amt
(b_id NUMBER) RETURN NUMBER
B. CREATE OR REPLACE PROCEDURE tax_amt
(b_id NUMBER) RETURN NUMBER
C. CREATE OR REPLACE PROCEDURE tax_amt
(b_id NUMBER, b_amount OUT NUMBER)
D. CREATE OR REPLACE FUNCTION tax_amt
(b_id NUMBER) RETURN NUMBER (10,2)
E. CREATE OR REPLACE PROCEDURE tax_amt
(b_id NUMBER, b_amount OUT NUMBER (10,2))
Answer: A, C
15. Examine this function.
CREATE OR REPLACE FUNCTION calc_player_avg
( v_id IN player_bat_stat.player_id%TYPE )
RETURN NUMBER
IS
v_avg NUMBER ;
BEGIN
SELECT hits/at_bats
INTO v_avg
FROM player_bat_stat
WHERE player_id = v_id ;
RETURN (v_avg) ;
END ;
Which statement will successfully invoke this function in SQL Plus?
A. SELECT calc_player_avg (player_id)
FROM player_bat_stat ;
B. EXECUTE calc_player_avg (31) ;
C. calc_player (‘RUTH’) ;
D. calc_player_avg (31) ;
E. START calc_player_avg (31)
Given a function calctax :
Answer: B
16. CREATE OR REPLACE FUNCTION caltax (sal NUMBER) RETURN NUMBER
IS
BEGIN
RETURN (sal * 0.05);
END;
You want to run above function from the iSQL*Plus prompt.
Which statement is true?
A. You need to execute the command caltax (1000) ;
B. You need to execute the command EXECUTE FUNCTION caltax ;
C. You need to create a SQL*Plus environment variable x and issue the command :x := caltax (1000);
D. You need to create a SQL*Plus environment variable x and issue the command EXECUTE :x := caltax;
E. You need to create a SQL*Plus environment variable x and issue the command EXECUTE :x := caltax(1000);
Answer: E
17. Which code successfully calculates tax?
A. CREATE OR REPLACE PROCEDURE calc ( p_no IN NUMBER ) RETURN tax
IS
v_sal NUMBER ;
tax NUMBER ;
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = p_no ;
tax := v_sal * 0. 05 ;
END;
B. CREATE OR REPLACE FUNCTION calctax ( p_no NUMBER ) RETURN NUMBER
IS
v_sal NUMBER;
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = p_no ;
RETURN (v_sal * 0. 05) ;
END;
C. CRETAE OR REPLACE FUNCTION calctax ( p_no NUMBER ) RETURN NUMBER
IS
v_sal NUMBER ;
tax NUMBER ;
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = p_no ;
Tax := v_sal * 0. 05;
END;
D. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER)
IS
v_sal NUMBER ;
tax NUMBER ;
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = p_no ;
Tax := v_sal * 0. 05 ;
RETURN (tax) ;
END;
Answer: B
18. When creating a function in which section will you typically find a return key word?
A. Header only
B. Declarative
C. Executable and Header
D. Executable and Exception handling
Answer: C
19. Which two statements are true? (Choose two)
A. A function must return a value.
B. A procedure must return a value.
C. A function executes a PL/SQL statement.
D. A function is invoked as part of an expression.
E. A procedure must have a return data type specify in its declaration.
Answer: A, E
20. Which allows a PL/SQL user define a function?
A. NEXTVAL
B. HAVING clause of the SELECT command
C. ALTER TABLE command
D. FROM clause of the SELECT FOR UPDATE command
Answer: B
21. Examine this function:
CREATE FUNCTION get_budget ( v_studio_id IN NUMBER )
IS
v_yearly_budget NUMBER;
BEGIN
SELECT yearly_budget
INTO v_yearly_budget
FROM studio
WHERE id = v_studio_id;
END;
Which two must be added to this function for it to execute successfully? (Choose two.)
A. A RETURN clause in the header
B. An OUT parameter in the header
C. A REPLACE option in the header
D. A RETURN statement in the executable section
E.An exception handler in the executable section
Answer: A, D
22. When creating a function in a SQL*Plus environment. You receive this message :
“Warning: function created with compilation errors”.
Which command can you issue to see the actual error message?
A. SHOW FUNCTION_ERRORS
B. SHOW USER_ERRORS
C. SHOW ERRORS
D. SHOW ALL_ERRORS
Answer: C
23. Examine this function:
CREATE OR REPLACE FUNCTION get_budget
(v_studio_id IN NUMBER) RETURN NUMBER
IS
v_yearly_budget NUMBER ;
BEGIN
SELECT yearly_budget
INTO v_yearly_budget
FROM studio
WHERE id = v_studio_id ;
RETURN v_yearly_budget ;
END;
Which invokers-rights clause must you use to ensure that this function uses the security of the executing user when it is invoked?
A. AUTHID DEFINER
B. AUTHID CURRENT_USER
C. AUTHID DETERMINISTIC
D. AUTHID PARALLEL_ENABLE
Answer: B
24. Examine the code:
SET SERVER OUTPUT ON
DECLARE
v_char_val varchar2(100);
BEGIN
v_char_val:= ‘Hello World’,
DBMS_OUTPUT.PUT_LINE(v_char_val);
END
SET SERVER OUTPUT OFF
This code is stored in a script file name “myproc,sql”.
Which statement executes the code in the script file?
A. Myproc.sql
B. RUN myproc,sql
C. START myproc.sql
D. EXECUTE myproc.sql
E. BEGIN myproc.sql END;
Answer: C
25. Which statement is valid within the executable section of PL/SQL block?
A. BEGIN
emp_rec emp%ROWTYPE
END;
B. WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT.LINE(‘No records found’);
C. SELECT ename, sal
INTO v_ename, v_sal
FROM emp
WHERE empno = 101;
D. PROCEDURE cal_max (n1 NUMBER, n2 NUMBER, p_max OUT NUMBER)
IS
BEGIN
IF n1 > n2 THEN
p_max := n1;
ELSE
p_max := n2;
END IF;
END;
Answer: C
26. Which is false for anonymous block?
A. Anonymous block are unnamed block
B. You can embed an anonymous block within a precompiled program.
C. This block store in database for repeated execution
D. Triggers in oracle developer components consist of such block
Answer: C
27. Which is not a PL/SQL variable?
A. Scalar variable
B. Composite Variable
C. Bind Variable
D. Reference Variable
Answer: C
28. Which is not the naming rule of a variable?
A. The name of the variables must not be longer than 30 characters
B. The first character must be a letter
C. The first character may be number
Answer: C
29. In which section of a PL/SQL block could a new value be assigned to an initialized variable?
A. END
B. Header
C. Executable
D. Declarative
E. Exception handling
Answer: C
30. Developer Janet receives an error due to the following statement in the declaration section: Pi CONSTANT NUMBER;
The problem is because:
A. There is not enough memory in the program for the constant.
B. There is no value associated with the constant.
C. There is no datatype associated with the constant.
D. PI is reserved word.
Answer: B
31. There are ___ grid infrastructure products in the Oracle10g release:
a. One
b. Two
c. Three
d. Four
Answer: C
32. Evaluate this PL/SQL block:
DECLARE
v_id_number inventory.id_number%TYPE;
v_manufacturer_id inventory.manufacturer_id%TYPE;
c_quantity CONSTANT NUMBER := 500;
BEGIN
SELECT id_number, manufacturer_id, quantity
INTO v_id_number, v_manufacturer_id
FROM inventory
WHERE quantity > c_quantity;
END;
If you only needed to know the ID_NUMBER and MANUFACTURER_ID values of items with a QUANTITY value greater than 500, which clause contains an error?
A. From Inventory
B. Where Quanity>q_quantity
C. Into V_id_Number,V_manufacturer_id
D. Select Id_number, Manufacturer_id, Quantity
Answer: D
33. Evaluate this PL/SQL block:
DECLARE
v_quota BOOLEAN := FALSE;
v_stock BOOLEAN := NULL;
v_approval BOOLEAN;
BEGIN
v_approval := v_quota AND v_stock;
END;
Which value is assigned to the V_APPROVAL?
a. True
b. Null
c. False
d. None
Answer: C
34. A student is given a letter grade based on their numeric grade.
Evaluate this IF statement:
IF v_num_grade > 95 THEN
v_letter_grade := 'A';
ELSE
IF v_num_grade > 87 THEN
v_letter_grade := 'B';
ELSE
IF v_num_grade > 80 THEN
v_letter_grade := 'C';
ELSE
v_letter_grade := 'F';
END IF;
END IF;
END IF;
Which numeric grade value would cause V_LETTER_GRADE to be set to 'C'? A student
a. Any numeric grade less then 100 and greater than 95
b. Any numeric grade less then 96 and greater than 87
c. Any numeric grade less then 88 and greater than 80
d. Any numeric grade less then 87 and greater than 79
e. Any numeric grade greater than 80
Answer: C
35. Examine the following Part of PL/SQL Block.
Declare
v_salary employees.salray%TYPE NOT NULL;
v_job_id employees.job_id%TYPE;
v_raise v_salary%TYPE;
v_empno employees.employee_id%TYPE NOT NULL: = &p_empno;
Which Variable Declaration is wrong?
A. v_salary
B. v_job_id
C. v_raise
D. v_empno
Answer: A
36. Examine the following Declarations.
Declare
x NUMBER(2) : = 1;
y NUMBER(2) NOT NULL DEFAULT 2;
z y%TYPE;
Which Variable Declaration is wrong?
A. x
B. y
C. z
D. None Of Them
Answer: C
37. You have written the following SQL statement in executable section of a PL/SQL code.
Assuming that all the variables are properly declared, which statement is true for the following SQL statement?
BEGIN
SELECT salary, job_id, salary*0.10 as raise
INTO v_salary, v_job_id, v_raise
FROM employees;
END;
A. The Statement will generate an exception because wrong type of variables are used
B. The Statement will fail because an alias is used in a column
C. You cannot use a SELECT statement to populate variables
D. INTO clause can only refer to one variable only
Answer: A
38. Which Declaration is wrong in the following?
DECLARE
r NUMBER (17, 2) DEFAULT 10;
pi CONSTANT r%TYPE;
Area r%TYPE NOT NULL: = 0;
A. r
B. pi
C. area
D. None of Them.
Answer: B
39. Examine the following Declarations.
DECLARE
v_name VARCHAR2 (50): = ‘CNS LIMITED’;
v_flag BOOLEAN : = (v_name IS NOT NULL);
v_date DATE DEFAULT SYSDATE + TO_DATE(’01-JAN-04’);
v_warranty INTERVAL YEAR(2) TO MONTH : = TO_YMINTERVAL(’01-06’);
Which one is wrongly declared?
A. v_name
B. v_flag
C. v_date
D. v_warranty
Answer: C
0 Comments