1. Examine the following anonymous block and choose the appropriate statement.

DECLARE

fname VARCHAR2(20);

lname VARCHAR2(15) DEFAULT 'fernandez';

BEGIN

DBMS_OUTPUT.PUT_LINE( FNAME ||' ' ||lname);

END;

A. The block will execute successfully and print fernandez.

B. The block will give an error because the fname variable is used without initializing.

C. The block will execute successfully and print null fernandez.

D. The block will give an error because you cannot use the DEFAULT keyword to initialize a variable of the VARCHAR2 type.

E. The block will give an error because the FNAME variable is not declared.

2. In Application Express:

A. Create the following function:

CREATE FUNCTION num_characters (p_string IN VARCHAR2)

RETURN INTEGER AS

v_num_characters INTEGER;

BEGIN

SELECT LENGTH(p_string) INTO v_num_characters

FROM dual;

RETURN v_num_characters;

END;

B. Create and execute the following anonymous block:

DECLARE

v_length_of_string INTEGER;

BEGIN

v_length_of_string := num_characters('Oracle Corporation');

DBMS_OUTPUT.PUT_LINE(v_length_of_string);

END;

C. Execute and observe the output. Try at least two more strings and show the output.

3. Evaluate the variables in the following code. Answer the following questions about each variable. Is it named well? Why or why not? If it is not named well, what would be a better name and why?

DECLARE

country_name VARCHAR2 (50);

median_age NUMBER(6,2);

BEGIN

SELECT country_name, median_age INTO country_name, median_age

FROM wf_countries

WHERE country_name = 'United States of America';

DBMS_OUTPUT.PUT_LINE(' The median age in '||country_name||' is '||median_age||'.');

END;

4. Examine the declarations in question 3. Change the declarations so that they use the %TYPE attribute.

5. In your own words, describe why using the %TYPE attribute is better than hard-coding data types. Can you explain how you could run into problems in the future by hard-coding the data types of the country_name and median_age variables in question 3?

TASK 3:

1. Create the following anonymous block:

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World');

END;

A. Add a declarative section to this PL/SQL block. In the declarative section, declare the following variables:

• A variable named TODAY of datatype DATE. Initialize TODAY with SYSDATE.
• A variable named TOMORROW with the same datatype as TODAY.

B. In the executable section, initialize the TOMORROW variable with an expression that calculates tomorrows date (add 1 to the value in TODAY). Print the value of TODAY and TOMORROW after printing Hello World.

2. Write an anonymous block that uses a country id as input and prints the country name and region. Use the countries table. Execute your block three times using AR, AU, and BE. (See example Task 2. Exercise No. 3). Note: You need to try using a normal scalar variable and using %Type attribute.

3. Write an anonymous block that uses a job id as input and prints the job title, maximum salary and minimum salary. Use the jobs table. Execute your block three times using AD_PRES, AD_VP, and AC_MGR. (See example Task 2. Exercise No. 3). Note: You need to try using a normal scalar variable and using %Type attribute.