PLSQL INTERVIEW QUESTIONS

13) Read the following code:

CREATE OR REPLACE FUNCTION get_budget

(v_studio_id IN NUMBER)RETURN number ISv_yearly_budget NUMBER; BEGINSELECT yearly_budgetINTO v_yearly_budgetFROM studioWHERE id = v_studio_id; RETURN v_yearly_budget;

END;

Which set of statements will successfully invoke this function within SQL*Plus? 1. VARIABLE g_yearly_budget NUMBEREXECUTE g_yearly_budget := GET_BUDGET(11);

2. VARIABLE g_yearly_budget NUMBEREXECUTE :g_yearly_budget := GET_BUDGET(11);

3. VARIABLE :g_yearly_budget NUMBEREXECUTE :g_yearly_budget := GET_BUDGET(11);

4. VARIABLE g_yearly_budget NUMBER:g_yearly_budget := GET_BUDGET(11);

14) Read the following code: CREATE OR REPLACE PROCEDURE update_theater (v_name IN VARCHAR v_theater_id IN NUMBER)

IS

BEGIN

UPDATE theater SET name = v_name WHERE id = v_theater_id; END update_theater;

When invoking this procedure, you encounter the error: ORA-000: Unique constraint (SCOTT.THEATER_NAME_UK) violated.

How should you modify the function to handle thiserror?

1. An user defined exception must be declared andassociated with the error code and handled in theEXCEPTION section.

2. Handle the error in EXCEPTION section byreferencing the error code directly.

3. Handle the error in the EXCEPTION section byreferencing the UNIQUE_ERROR predefined exception.

4. Check for success by checking the value ofSQL%FOUND immediately after the UPDATE statement.

15) Read the following code:

CREATE OR REPLACE PROCEDURE calculate_budget IS v_budget studio.yearly_budget%TYPE; BEGIN v_budget := get_budget(11); IF v_budget <> What effect will this have?

1. The GET_BUDGET function will be marked invalid andmust be recompiled before the next execution.

2. The SET_BUDGET function will be marked invalid andmust be recompiled before the next execution.

3. Only the CALCULATE_BUDGET procedure needs to berecompiled.

4. All three procedures are marked invalid and mustbe recompiled.