Oracle Certification Exam Quiz: MCQ!

1. While creating a package, you placed the function name in the specification and the body. Which type of construct have you created?

PUBLIC

Illegal

Private

One-time only

By placing the function name in both the specification and the body, you have created a PUBLIC construct. This means that the function can be accessed and used by other parts of the code or other packages.

Explanation

By placing the function name in both the specification and the body, you have created a PUBLIC construct. This means that the function can be accessed and used by other parts of the code or other packages.

2. The GET_BUDGET function is no longer needed and should be removed. Which command will successfully remove this function from the database?

DROP get_budget;

REMOVE get_budget;

DROP FUNCTION get_budget;

ALTER get_budget DROP FUNCTION;

The correct answer is "DROP FUNCTION get_budget;". This command is used to remove a function from the database. The "DROP FUNCTION" statement is specifically designed to drop functions, while the other options are not valid commands for removing a function.

Explanation

The correct answer is "DROP FUNCTION get_budget;". This command is used to remove a function from the database. The "DROP FUNCTION" statement is specifically designed to drop functions, while the other options are not valid commands for removing a function.

3. Examine this package specification: CREATE OR REPLACE PACKAGE theater_package IS PROCEDURE find_cpt (v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER); PROCEDURE update_theater (v_name IN VARCHAR2); PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); PROCEDURE add_theater; END theater_package; Which statement about the procedures in this specification is true?

They are public procedures

They are private procedures

Each procedure is missing code and, therefore, is illegal.

Each procedure contains an argument list and, therefore, is illegal.

The procedures in this package specification are considered public procedures because they are declared without any access modifiers. Public procedures can be accessed and called by other programs or packages.

Explanation

The procedures in this package specification are considered public procedures because they are declared without any access modifiers. Public procedures can be accessed and called by other programs or packages.

4. When creating a function in SQL*Plus, you receive an error message stating that the function created with compilation errors. What must you do to see the compilation errors?

Issue the SHOW ERRORS command.

Issue the DISPLAY ERRORS command.

Query the FUNCTION_ERRORS data dictionary view.

Do nothing. The errors will display after a five-second delay.

To see the compilation errors when creating a function in SQL*Plus, you need to issue the SHOW ERRORS command. This command will display the specific errors encountered during the compilation process, allowing you to identify and resolve them. It is important to review these errors to ensure the function is created correctly and functions as intended.

Explanation

To see the compilation errors when creating a function in SQL*Plus, you need to issue the SHOW ERRORS command. This command will display the specific errors encountered during the compilation process, allowing you to identify and resolve them. It is important to review these errors to ensure the function is created correctly and functions as intended.

5. The AUDIT_THEATER trigger on the THEATER table is no longer needed and must be removed. Which command will successfully remove this trigger from the database?

DROP audit_theater;

DROP TRIGGER audit_theater;

ALTER TRIGGER audit_theater DROP;

ALTER TABLE theater DROP TRIGGER audit_theater;

The correct answer is "DROP TRIGGER audit_theater;". This command is used to remove a trigger from the database. The "DROP" keyword is used to indicate that the trigger is being dropped, and "audit_theater" specifies the name of the trigger that needs to be removed.

Explanation

The correct answer is "DROP TRIGGER audit_theater;". This command is used to remove a trigger from the database. The "DROP" keyword is used to indicate that the trigger is being dropped, and "audit_theater" specifies the name of the trigger that needs to be removed.

6. For which purpose are formal parameters used when creating functions?

Restricting pragma references

Passing values to the function

Bypassing directives to the compiler

Prompting the end user for information

Formal parameters are used when creating functions to pass values to the function. These parameters act as placeholders for the values that will be provided when the function is called. By passing values to the function through formal parameters, the function can perform operations on these values and return a result or perform a specific task based on the provided values. This allows for the reuse and flexibility of functions, as different values can be passed to achieve different outcomes.

Explanation

Formal parameters are used when creating functions to pass values to the function. These parameters act as placeholders for the values that will be provided when the function is called. By passing values to the function through formal parameters, the function can perform operations on these values and return a result or perform a specific task based on the provided values. This allows for the reuse and flexibility of functions, as different values can be passed to achieve different outcomes.

7. Evaluate this statement: DROP PACKAGE dept_pack; Which statement is true?

This statement removes only the package body.

The statement removes only the package specification.

The statement removes the package specification and the package body.

This statement contains a syntax error.

The given statement "DROP PACKAGE dept_pack;" is used to remove a package in a database. In this case, it removes both the package specification and the package body for the package named "dept_pack". Therefore, the correct answer is "The statement removes the package specification and the package body."

Explanation

The given statement "DROP PACKAGE dept_pack;" is used to remove a package in a database. In this case, it removes both the package specification and the package body for the package named "dept_pack". Therefore, the correct answer is "The statement removes the package specification and the package body."

8. Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; 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 get_budget; END theater_pck; Which type of variable is CURRENT_AVG_COST_PER_TICKET?

Public

Private

Invalid

Constant

In the given package body, the variable CURRENT_AVG_COST_PER_TICKET is declared before the PROCEDURE find_seats_sold. It is not declared within any procedure or function, making it a private variable. Private variables can only be accessed within the package body and are not visible to other packages or outside the package.

Explanation

In the given package body, the variable CURRENT_AVG_COST_PER_TICKET is declared before the PROCEDURE find_seats_sold. It is not declared within any procedure or function, making it a private variable. Private variables can only be accessed within the package body and are not visible to other packages or outside the package.

9. For which trigger timing can you reference the NEW and OLD qualifiers?

Row only

Statement only

Statement and row

Oracle Forms trigger

The NEW and OLD qualifiers can be referenced for the "row only" trigger timing. This means that the qualifiers can be used when a trigger is fired for each row affected by a DML statement. In this case, the trigger can access the old and new values of the affected row.

Explanation

The NEW and OLD qualifiers can be referenced for the "row only" trigger timing. This means that the qualifiers can be used when a trigger is fired for each row affected by a DML statement. In this case, the trigger can access the old and new values of the affected row.

10. Examine this procedure: CREATE OR REPLACE PROCEDURE find_cpt (v_movie_id {argument mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER) IS BEGIN IF v_cost_per_ticket > 8.50 THEN SELECT cost_per_ticket INTO v_cost_per_ticket FROM gross_receipt WHERE movie_id = v_movie_id; END IF; END; Which argument mode should be used for V_MOVIE_ID?

IN

OUT

IN OUT

IN RETURN

The argument mode for V_MOVIE_ID should be IN. This is because the procedure is using the value of V_MOVIE_ID to perform a SELECT query in the IF statement. It is not modifying the value of V_MOVIE_ID, so there is no need for it to be an OUT or IN OUT parameter. Additionally, there is no need for it to be a RETURN parameter, as the procedure is not returning any value related to V_MOVIE_ID. Therefore, the most appropriate argument mode for V_MOVIE_ID in this procedure is IN.

Explanation

The argument mode for V_MOVIE_ID should be IN. This is because the procedure is using the value of V_MOVIE_ID to perform a SELECT query in the IF statement. It is not modifying the value of V_MOVIE_ID, so there is no need for it to be an OUT or IN OUT parameter. Additionally, there is no need for it to be a RETURN parameter, as the procedure is not returning any value related to V_MOVIE_ID. Therefore, the most appropriate argument mode for V_MOVIE_ID in this procedure is IN.

11. The CALC_COMM procedure is no longer needed and should be removed. Which command will successfully remove this procedure from the database?

DROP calc_comm;

REMOVE calc_comm;

DROP PROCEDURE calc_comm;

ALTER calc_comm DROP PROCEDURE;

The correct answer is "DROP PROCEDURE calc_comm;". This command is used to remove a procedure from the database. In this case, it specifically removes the "calc_comm" procedure. The other options, "DROP calc_comm;", "REMOVE calc_comm;", and "ALTER calc_comm DROP PROCEDURE;", are not valid commands for removing a procedure.

Explanation

The correct answer is "DROP PROCEDURE calc_comm;". This command is used to remove a procedure from the database. In this case, it specifically removes the "calc_comm" procedure. The other options, "DROP calc_comm;", "REMOVE calc_comm;", and "ALTER calc_comm DROP PROCEDURE;", are not valid commands for removing a procedure.

12. Which command must you issue in SQL*Plus to display the result of the DBMS_OUTPUT package?

SET ECHO ON

SET OUTPUT ON

SET FEEDBACK ON

SET SERVEROUTPUT ON

The correct answer is SET SERVEROUTPUT ON. This command enables the display of the result of the DBMS_OUTPUT package in SQL*Plus. It allows the output from procedures, functions, and triggers to be shown in the SQL*Plus command-line interface.

Explanation

The correct answer is SET SERVEROUTPUT ON. This command enables the display of the result of the DBMS_OUTPUT package in SQL*Plus. It allows the output from procedures, functions, and triggers to be shown in the SQL*Plus command-line interface.

13. Examine this code:
CREATE OR REPLACE PACKAGE prod_pack IS g_tax_rate NUMBER := .08; END prod_pack; Which statement about this code is true?

This package specification can exist without a body.

This package body can exist without a specification.

This package body cannot exist without a specification.

This package specification cannot exist without a body.

The given code is a package specification, which defines the interface of a package. In Oracle PL/SQL, a package specification can exist without a body. The package specification contains declarations of variables, constants, types, cursors, and subprograms that are accessible to other program units. The package body, on the other hand, contains the implementation of the subprograms declared in the specification. However, it is not necessary to have a package body for a package specification to exist.

Explanation

The given code is a package specification, which defines the interface of a package. In Oracle PL/SQL, a package specification can exist without a body. The package specification contains declarations of variables, constants, types, cursors, and subprograms that are accessible to other program units. The package body, on the other hand, contains the implementation of the subprograms declared in the specification. However, it is not necessary to have a package body for a package specification to exist.

14. Due to a disk failure, the AUDIT_THEATER table is unavailable until further notice. The CHECK_THEATER database trigger references this table when a DML operation is performed on the THEATER table. Which command should you issue to prevent this database trigger from executing until this problem is resolved?

ALTER TRIGGER check_theater DROP;

MODIFY TRIGGER check_theater DROP;

ALTER TRIGGER check_theater DISABLE;

MODIFY TRIGGER check_theater DISABLE;

The correct answer is "ALTER TRIGGER check_theater DISABLE." This command will disable the "check_theater" trigger, preventing it from executing until the problem with the AUDIT_THEATER table is resolved.

Explanation

The correct answer is "ALTER TRIGGER check_theater DISABLE." This command will disable the "check_theater" trigger, preventing it from executing until the problem with the AUDIT_THEATER table is resolved.

15. You created a database trigger that will be executed for all data manipulation statements on the THEATER table. Within the code, you will determine which type of manipulation has caused the trigger to execute. Which would you use to test for the type of manipulation being performed?

DBMS_SQL

DBMS_TRIGGER

DBMS_CHECK_MANIPULATION_TYPE

DELETING, UPDATING, and INSERTING

To test for the type of manipulation being performed, you would use the options DELETING, UPDATING, and INSERTING. These options represent the different types of data manipulation statements that can be executed on the THEATER table. By checking which of these options is true within the trigger code, you can determine the type of manipulation that caused the trigger to execute.

Explanation

To test for the type of manipulation being performed, you would use the options DELETING, UPDATING, and INSERTING. These options represent the different types of data manipulation statements that can be executed on the THEATER table. By checking which of these options is true within the trigger code, you can determine the type of manipulation that caused the trigger to execute.

16. Examine this function: CREATE OR REPLACE FUNCTION set_budget (v_studio_id IN NUMBER, v_new_budget IN NUMBER) RETURN number IS BEGIN UPDATE studio SET yearly_budget = v_new_budget WHERE id = v_studio_id; COMMIT; RETURN SQL%ROWCOUNT; END; While executing this in SQL*Plus, you want to see the value of SQL%ROWCOUNT displayed on the screen. Which line of code will accomplish this?

OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));

DBMS_DEBUG.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));

DBMS_OUTPUT.DISPLAY(TO_CHAR(SQL%ROWCOUNT));

DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));

The correct answer is DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));. This line of code will display the value of SQL%ROWCOUNT on the screen using the DBMS_OUTPUT.PUT_LINE function. The TO_CHAR function is used to convert the value of SQL%ROWCOUNT to a character string before displaying it.

Explanation

The correct answer is DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));. This line of code will display the value of SQL%ROWCOUNT on the screen using the DBMS_OUTPUT.PUT_LINE function. The TO_CHAR function is used to convert the value of SQL%ROWCOUNT to a character string before displaying it.

17. Examine this database trigger: CREATE OR REPLACE TRIGGER audit_gross_modification AFTER INSERT OR DELETE ON gross_receipt BEGIN INSERT INTO audit_gross VALUES (USER, SYSDATE); END; To test this trigger, you delete 30 rows from the GROSS_RECEIPT table. How many rows are inserted into the AUDIT_GROSS table due to this event?

1

30

31

None

When the trigger is fired after deleting 30 rows from the GROSS_RECEIPT table, only one row is inserted into the AUDIT_GROSS table. This is because the trigger is designed to insert a single row into the AUDIT_GROSS table every time it is fired, regardless of the number of rows affected by the DELETE statement. Therefore, regardless of the number of rows deleted, only one row will be inserted into the AUDIT_GROSS table.

Explanation

When the trigger is fired after deleting 30 rows from the GROSS_RECEIPT table, only one row is inserted into the AUDIT_GROSS table. This is because the trigger is designed to insert a single row into the AUDIT_GROSS table every time it is fired, regardless of the number of rows affected by the DELETE statement. Therefore, regardless of the number of rows deleted, only one row will be inserted into the AUDIT_GROSS table.

18. Examine this procedure: CREATE PROCEDURE add_theater IS BEGIN INSERT INTO theater VALUES (35, 'Riverplace Theatre', '1222 River Drive, Austin, Tx.'); END; This procedure already exists in the database. You have made a change to the code and want to recreate the procedure. Which command must you now use to prevent an error?

REPLACE PROCEDURE

RECREATE PROCEDURE

UPDATE PROCEDURE WITH

CREATE OR REPLACE PROCEDURE

The correct answer is "CREATE OR REPLACE PROCEDURE". This command allows you to recreate the procedure without causing an error. By using the "CREATE OR REPLACE" syntax, you can replace the existing procedure with the updated code, ensuring that any changes you have made are implemented correctly.

Explanation

The correct answer is "CREATE OR REPLACE PROCEDURE". This command allows you to recreate the procedure without causing an error. By using the "CREATE OR REPLACE" syntax, you can replace the existing procedure with the updated code, ensuring that any changes you have made are implemented correctly.

19. Procedures and functions are very similar. For which reason would you choose a function over a procedure?

A function allows Oracle group functions.

A function can be used in a SQL statement.

A function guarantees read consistency, while a procedure cannot.

A function can only be executed from a previously created procedure.

A function can be used in a SQL statement because it returns a value that can be used in a query. This allows for more flexibility in manipulating and retrieving data from the database. In contrast, a procedure does not return a value and is typically used for performing a series of actions or tasks without returning a result.

Explanation

A function can be used in a SQL statement because it returns a value that can be used in a query. This allows for more flexibility in manipulating and retrieving data from the database. In contrast, a procedure does not return a value and is typically used for performing a series of actions or tasks without returning a result.

20. The auditing utility in Oracle records the type of data manipulation operation and not the actual changed values. To enhance auditing by capturing the new and old values, you create which type of trigger?

Row only

Statement only

Either statement or row

Neither statement or row

To enhance auditing by capturing the new and old values, you create a "row only" trigger. This type of trigger is specifically designed to capture the changes made to individual rows in a table. It allows you to track the old and new values of the modified data, providing more detailed information for auditing purposes. By using a "row only" trigger, you can ensure that the actual changed values are recorded along with the type of data manipulation operation.

Explanation

To enhance auditing by capturing the new and old values, you create a "row only" trigger. This type of trigger is specifically designed to capture the changes made to individual rows in a table. It allows you to track the old and new values of the modified data, providing more detailed information for auditing purposes. By using a "row only" trigger, you can ensure that the actual changed values are recorded along with the type of data manipulation operation.

21. When a database trigger routine does not have to take place before the triggering event, which timing should you assign to the trigger?

On

Off

After

Before

When a database trigger routine does not have to take place before the triggering event, the timing that should be assigned to the trigger is "after". This means that the trigger will be executed after the triggering event has occurred.

Explanation

When a database trigger routine does not have to take place before the triggering event, the timing that should be assigned to the trigger is "after". This means that the trigger will be executed after the triggering event has occurred.

22. When declaring arguments within a procedure, which specification is NOT allowed?

%TYPE

Data type

%ROWTYPE

Maximum length

The specification "maximum length" is not allowed when declaring arguments within a procedure. The other options, "%TYPE", "data type", and "%ROWTYPE" are all valid specifications that can be used when declaring arguments.

Explanation

The specification "maximum length" is not allowed when declaring arguments within a procedure. The other options, "%TYPE", "data type", and "%ROWTYPE" are all valid specifications that can be used when declaring arguments.

23. CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; 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 get_budget; END theater_pck; Which will successfully invoke the FIND_SEATS_SOLD procedure within SQL*Plus?

EXECUTE find_seats_sold (500,11);

Theater_pck.find_seats_sold (500,11);

EXECUTE theater_pck.find_seats_sold (500,11);

SELECT find_seats_sold(movie_id, theater_id)FROM gross_receipt;

The correct answer is "EXECUTE theater_pck.find_seats_sold (500,11);" because it uses the correct syntax to invoke a procedure within SQL*Plus. The EXECUTE keyword is used to execute a stored procedure, followed by the fully qualified name of the procedure, which includes the package name "theater_pck" and the procedure name "find_seats_sold". The procedure is then passed the arguments 500 and 11.

Explanation

The correct answer is "EXECUTE theater_pck.find_seats_sold (500,11);" because it uses the correct syntax to invoke a procedure within SQL*Plus. The EXECUTE keyword is used to execute a stored procedure, followed by the fully qualified name of the procedure, which includes the package name "theater_pck" and the procedure name "find_seats_sold". The procedure is then passed the arguments 500 and 11.

24. You decide to use packages to logically group related programming constructs. Which two types of constructs can be grouped within a package? (Choose two.)

View

Cursor

Variable

Database trigger

Application trigger

Packages in programming are used to logically group related programming constructs. Two types of constructs that can be grouped within a package are cursors and variables. Cursors are used to retrieve and manipulate data from a database, while variables are used to store and manipulate data within a program. By grouping these constructs within a package, it allows for better organization, modularity, and reusability of code.

Explanation

Packages in programming are used to logically group related programming constructs. Two types of constructs that can be grouped within a package are cursors and variables. Cursors are used to retrieve and manipulate data from a database, while variables are used to store and manipulate data within a program. By grouping these constructs within a package, it allows for better organization, modularity, and reusability of code.

Submit

25. Examine this procedure: PROCEDURE find_cpt (v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER) IS BEGIN IF v_cost_per_ticket > 8.50 THEN SELECT cost_per_ticket INTO v_cost_per_ticket FROM gross_receipt WHERE movie_id = v_movie_id; END IF; END; You decide to create this procedure within the THEATER_PCK package. It will be accessible outside of the package. What will you add to the package specification?

PROCEDURE find_cpt(v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER);

PUBLIC PROCEDURE find_cpt(v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER);

PROCEDURE find_cpt(v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER)ISBEGINIF v_cost_per_ticket > 8.50 THENSELECT cost_per_ticketINTO v_cost_per_ticketFROM gross_receiptWHERE movie_id = v_movie_id;END IF;END;

PUBLIC PROCEDURE find_cpt(v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER)ISBEGINIF v_cost_per_ticket > 8.50 THENSELECT cost_per_ticketINTO v_cost_per_ticketFROM gross_receiptWHERE movie_id = v_movie_id;END IF;END;

The correct answer is "PROCEDURE find_cpt (v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER);". This is the correct syntax for adding a procedure to the package specification. It includes the procedure name, the input and output parameters, and their data types.

Explanation

The correct answer is "PROCEDURE find_cpt (v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER);". This is the correct syntax for adding a procedure to the package specification. It includes the procedure name, the input and output parameters, and their data types.

26. Each month a SQL*Loader application is executed to insert approximately 1 million rows into the GROSS_RECEIPT table. This table has three database triggers that execute for each row inserted. Which command can you issue immediately before the SQL*Loader operation to improve performance?

DISABLE TRIGGERS ON gross_receipt;

DISABLE ALL TRIGGERS ON gross_receipt;

ALTER TRIGGER ALL DISABLE ON gross_receipt;

ALTER TABLE gross_receipt DISABLE ALL TRIGGERS;

Disabling all triggers on the "gross_receipt" table using the "ALTER TABLE" command can improve performance during the SQL*Loader operation. By disabling the triggers, the database will not have to execute them for each row inserted, which can reduce the overall processing time and improve performance. This allows the SQL*Loader application to insert the rows more efficiently into the table.

Explanation

Disabling all triggers on the "gross_receipt" table using the "ALTER TABLE" command can improve performance during the SQL*Loader operation. By disabling the triggers, the database will not have to execute them for each row inserted, which can reduce the overall processing time and improve performance. This allows the SQL*Loader application to insert the rows more efficiently into the table.

27. Examine this procedure: CREATE PROCEDURE update_theater IS BEGIN UPDATE theater SET name = v_name WHERE id = 34; END; Because a value for the new theater name must be passed to this procedure upon invocation, you decide to create a parameter called V_NAME to hold the value. To be successful, which additional change must you make to this procedure?

Add (v_name IN VARCHAR2) immediately after the IS keyword.

Add v_name VARCHAR2(30); immediately after the IS keyword.

Add (v_name IN VARCHAR2) immediately before the IS keyword.

Add (v_name IN VARCHAR2) immediately after the BEGIN keyword.

To be successful, an additional change that must be made to this procedure is to add (v_name IN VARCHAR2) immediately before the IS keyword. This indicates that a parameter called V_NAME of type VARCHAR2 should be passed to the procedure when it is invoked.

Explanation

To be successful, an additional change that must be made to this procedure is to add (v_name IN VARCHAR2) immediately before the IS keyword. This indicates that a parameter called V_NAME of type VARCHAR2 should be passed to the procedure when it is invoked.

28. Which statement about packages is true?

A package can be called.

A package can be nested.

A package can be parameterized.

Package contents can be shared by multiple applications.

Packages in programming languages like Java allow for better organization and encapsulation of code. They can contain classes, interfaces, and other packages. One of the main advantages of using packages is that their contents can be shared by multiple applications. This means that different programs can access and use the classes and resources within a package, promoting code reuse and modularity. This helps in avoiding code duplication and makes development more efficient. Therefore, the statement "Package contents can be shared by multiple applications" is true.

Explanation

Packages in programming languages like Java allow for better organization and encapsulation of code. They can contain classes, interfaces, and other packages. One of the main advantages of using packages is that their contents can be shared by multiple applications. This means that different programs can access and use the classes and resources within a package, promoting code reuse and modularity. This helps in avoiding code duplication and makes development more efficient. Therefore, the statement "Package contents can be shared by multiple applications" is true.

29. A stored function can be invoked in many different ways. Which invocation example is NOT valid?

Executing the stored function within an INSERT statement

Executing the stored function within a server-side function

Executing the stored function within a client-side function

Executing the stored function within the DEFAULT clause of the CREATE TABLE statement

not-available-via-ai

Explanation

not-available-via-ai

30. CREATE OR REPLACE PACKAGE theater_pck IS v_total_budget NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; 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 get_budget; END theater_pck; Which statement about the V_TOTAL_BUDGET variable is true?

It must also be declared in the body.

It can only be referenced from inside the package

It can only be referenced from outside the package.

It can be referenced from inside and outside the package.

The V_TOTAL_BUDGET variable can be referenced from both inside and outside the package. This is because it is declared in the package specification, which allows it to be accessed by any program that uses the package. It does not need to be declared again in the package body.

Explanation

The V_TOTAL_BUDGET variable can be referenced from both inside and outside the package. This is because it is declared in the package specification, which allows it to be accessed by any program that uses the package. It does not need to be declared again in the package body.

31. Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; 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 get_budget; END theater_pck; Which statement about the GET_BUDGET function is true?

It can be referenced within a SQL statement.

It can be referenced from outside of the package.

It can only be referenced from within the package.

It must also be specified in the specification because it is specified in the body.

The GET_BUDGET function can only be referenced from within the package. This means that it cannot be called or used outside of the package. It is only accessible within the package's body, and cannot be accessed or called from any other part of the code or program.

Explanation

The GET_BUDGET function can only be referenced from within the package. This means that it cannot be called or used outside of the package. It is only accessible within the package's body, and cannot be accessed or called from any other part of the code or program.

32. Procedures and functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?

When the transaction is committed

During a data manipulation statement

When it is explicitly invoked by another construct

When an Oracle supplied package references the trigger

A database trigger is executed during a data manipulation statement. This means that whenever a specific action, such as an insert, update, or delete, is performed on the data in a table, the trigger associated with that table will be automatically executed. The trigger can be used to perform additional actions or enforce certain rules before or after the data manipulation statement is executed.

Explanation

A database trigger is executed during a data manipulation statement. This means that whenever a specific action, such as an insert, update, or delete, is performed on the data in a table, the trigger associated with that table will be automatically executed. The trigger can be used to perform additional actions or enforce certain rules before or after the data manipulation statement is executed.

33. Examine this trigger: CREATE OR REPLACE TRIGGER audit_gross_receipt AFTER DELETE OR UPDATE OF seats_sold, cost_per_ticket ON gross_receipt BEGIN ... END; How many times will the trigger body execute upon invocation?

Once

Twice

Once for each row deleted or updated

Once for each row deleted or seats_sold_cost_per_ticket updated

The trigger body will execute once upon invocation. This is because the trigger is defined as an "AFTER" trigger, which means it will only execute after the specified event (in this case, a delete or update of seats_sold or cost_per_ticket on the gross_receipt table) has occurred. Therefore, regardless of the number of rows affected by the delete or update statement, the trigger body will only execute once.

Explanation

The trigger body will execute once upon invocation. This is because the trigger is defined as an "AFTER" trigger, which means it will only execute after the specified event (in this case, a delete or update of seats_sold or cost_per_ticket on the gross_receipt table) has occurred. Therefore, regardless of the number of rows affected by the delete or update statement, the trigger body will only execute once.

34. CREATE OR REPLACE TRIGGER update_studio BEFORE UPDATE OF yearly_budget ON STUDIO FOR EACH ROW BEGIN ... END; Which event will invoke this trigger?

STUDIO table update

YEARLY_BUDGET column update

STUDIO table insert, update, or delete

Any column update other than YEARLY_BUDGET

This trigger will be invoked when there is an update specifically on the "YEARLY_BUDGET" column of the "STUDIO" table. It will not be triggered by any other column updates or by insert, update, or delete operations on the "STUDIO" table.

Explanation

This trigger will be invoked when there is an update specifically on the "YEARLY_BUDGET" column of the "STUDIO" table. It will not be triggered by any other column updates or by insert, update, or delete operations on the "STUDIO" table.

35. 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; END; To execute this function successfully, what additional code must be added to the executable section?

RETURN

RETURN get_budget;

OUT v_yearly_budget;

RETURN v_yearly_budget;

The additional code that must be added to the executable section is "RETURN v_yearly_budget;". This is because the function is declared to return a number, and the variable v_yearly_budget holds the value that should be returned. By adding "RETURN v_yearly_budget;", the function will successfully return the value of v_yearly_budget when executed.

Explanation

The additional code that must be added to the executable section is "RETURN v_yearly_budget;". This is because the function is declared to return a number, and the variable v_yearly_budget holds the value that should be returned. By adding "RETURN v_yearly_budget;", the function will successfully return the value of v_yearly_budget when executed.

36. Which statement about declaring parameters is true?

Only data type is required.

Data type and maximum length are required.

Data type and maximum length are not required.

Only maximum length is required for the VARCHAR2 data type.

When declaring parameters, only the data type is required. The maximum length is not necessary to specify when declaring parameters.

Explanation

When declaring parameters, only the data type is required. The maximum length is not necessary to specify when declaring parameters.

37. CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; 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 get_budget; END theater_pck; Which statement about the CURRENT_AVG_COST_PER_TICKET variable is true?

It can only be referenced from outside the package

It can be referenced from outside and within the package.

It can be referenced by all constructs within the package.

It must be moved to the specification to compile successfully

The CURRENT_AVG_COST_PER_TICKET variable can be referenced by all constructs within the package. This means that it can be accessed and used by any procedure or function within the package, including the find_seats_sold procedure and the get_budget function. It does not need to be moved to the package specification in order to compile successfully.

Explanation

The CURRENT_AVG_COST_PER_TICKET variable can be referenced by all constructs within the package. This means that it can be accessed and used by any procedure or function within the package, including the find_seats_sold procedure and the get_budget function. It does not need to be moved to the package specification in order to compile successfully.

38. Examine this database trigger: CREATE OR REPLACE TRIGGER update_show_gross {additional trigger information} BEGIN {additional code} END; This trigger should execute for each row when the SEATS_SOLD or COST_PER_TICKET columns are updated and when a row is inserted into the GROSS_RECEIPT table. Which trigger information must you add?

BEFORE (gross_receipt, seats_sold, cost_per_ticket) INSERT, UPDATE

BEFORE INSERT OR UPDATE OF seats_sold, cost_per_ticket ON gross_receipt

BEFORE INSERT OR UPDATE(seats_sold, cost_per_ticket) ON gross_receiptFOR EVERY ROW

BEFORE INSERT OR UPDATE OF seats_sold, cost_per_ticket ON gross_receiptFOR EACH ROW

The trigger should execute for each row when the SEATS_SOLD or COST_PER_TICKET columns are updated and when a row is inserted into the GROSS_RECEIPT table. Therefore, the trigger information must specify that the trigger should be executed before an insert or update operation on the SEATS_SOLD and COST_PER_TICKET columns of the GROSS_RECEIPT table. Additionally, the trigger should be executed for each row affected by the insert or update operation.

Explanation

The trigger should execute for each row when the SEATS_SOLD or COST_PER_TICKET columns are updated and when a row is inserted into the GROSS_RECEIPT table. Therefore, the trigger information must specify that the trigger should be executed before an insert or update operation on the SEATS_SOLD and COST_PER_TICKET columns of the GROSS_RECEIPT table. Additionally, the trigger should be executed for each row affected by the insert or update operation.

39. 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; This function is owned by the account, PROD. The user, JSMITH, must execute this function. Which GRANT command(s) should be issued?

GRANT EXECUTE ON get_budget TO jsmith;

GRANT EXECUTE, SELECT ON studio TO jsmith;

GRANT EXECUTE, SELECT ON get_budget TO jsmith;

GRANT SELECT ON studio TO jsmith;GRANT EXECUTE ON get_budget TO jsmith;

The function "get_budget" is owned by the account PROD and the user JSMITH needs to execute this function. To grant JSMITH the permission to execute the function, the GRANT command "GRANT EXECUTE ON get_budget TO jsmith;" should be issued. This command specifically grants the EXECUTE privilege on the "get_budget" function to the user JSMITH.

Explanation

The function "get_budget" is owned by the account PROD and the user JSMITH needs to execute this function. To grant JSMITH the permission to execute the function, the GRANT command "GRANT EXECUTE ON get_budget TO jsmith;" should be issued. This command specifically grants the EXECUTE privilege on the "get_budget" function to the user JSMITH.

40. Examine this code: BEGIN theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year; END; For this code to be successful, what must be true?

Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCKpackage.

Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of theTHEATER_PCK package.

Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function mustexist only in the body of the THEATER_PCK package.

Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function mustexist in the specification of the THEATER_PCK package.

For the code to be successful, both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package. This means that both the variable and the function must be declared and defined in the package specification, allowing them to be accessed and used in the code. If either the variable or the function is missing from the package specification, the code will not be able to compile and execute successfully.

Explanation

For the code to be successful, both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package. This means that both the variable and the function must be declared and defined in the package specification, allowing them to be accessed and used in the code. If either the variable or the function is missing from the package specification, the code will not be able to compile and execute successfully.

41. Examine this procedure: CREATE OR REPLACE PROCEDURE find_seats_sold (v_movie_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id; END; The value of V_SEATS_SOLD must be returned to the calling environment. Which change should you make to the code?

Declare V_SEATS_SOLD as an OUT argument.

Declare V_SEATS_SOLD as a RETURN argument.

Add RETURN V_SEATS_SOLD immediately before the IS keyword.

Add RETURN V_SEATS_SOLD immediately before the END keyword.

To return the value of V_SEATS_SOLD to the calling environment, it should be declared as an OUT argument. An OUT argument allows the procedure to pass a value back to the calling environment. This can be done by adding the OUT keyword before the parameter declaration of V_SEATS_SOLD in the procedure declaration.

Explanation

To return the value of V_SEATS_SOLD to the calling environment, it should be declared as an OUT argument. An OUT argument allows the procedure to pass a value back to the calling environment. This can be done by adding the OUT keyword before the parameter declaration of V_SEATS_SOLD in the procedure declaration.

42. Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; 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 get_budget; END theater_pck; Which statement about the FIND_SEATS_SOLD procedure is true?

It can be referenced within a SQL statement.

It can only be referenced from within the package.

It can be referenced from within and outside of the package.

It cannot also be specified in the specification because it is specified in the body.

The FIND_SEATS_SOLD procedure can be referenced from both within and outside of the package. This means that the procedure can be called and used in SQL statements as well as in other parts of the code outside of the package.

Explanation

The FIND_SEATS_SOLD procedure can be referenced from both within and outside of the package. This means that the procedure can be called and used in SQL statements as well as in other parts of the code outside of the package.

43. Which subprogram type can be invoked from within a SQL statement?

Function

Procedure

Public packaged procedure

Private packaged procedure

Private packaged function

A function can be invoked from within a SQL statement. Functions are subprograms that can be called within SQL statements to perform a specific task and return a value. They can be used to manipulate data, perform calculations, or retrieve specific information from the database. Unlike procedures, functions always return a value and can be used in expressions or as part of a query. Therefore, a function is the correct subprogram type that can be invoked from within a SQL statement.

Explanation

A function can be invoked from within a SQL statement. Functions are subprograms that can be called within SQL statements to perform a specific task and return a value. They can be used to manipulate data, perform calculations, or retrieve specific information from the database. Unlike procedures, functions always return a value and can be used in expressions or as part of a query. Therefore, a function is the correct subprogram type that can be invoked from within a SQL statement.

44. Which statement about error propagation is true?

An exception can propagate across remote procedure calls.

The RAISE; statement in an exception handler ends the current exception.

An exception raised inside an exception handler will not propagate to the enclosing block.

When an exception is raised in a called procedure, control goes to the exception section of that block.

When an exception is raised in a called procedure, control goes to the exception section of that block. This means that if an exception occurs within a procedure or function that is called by another block of code, the control will be transferred to the exception handling section of the called procedure. This allows for proper handling and management of exceptions, ensuring that the appropriate actions are taken to handle the error and prevent program termination.

Explanation

When an exception is raised in a called procedure, control goes to the exception section of that block. This means that if an exception occurs within a procedure or function that is called by another block of code, the control will be transferred to the exception handling section of the called procedure. This allows for proper handling and management of exceptions, ensuring that the appropriate actions are taken to handle the error and prevent program termination.

45. Modifications to the THEATER table are not allowed during the last week in December. When creating a database trigger to enforce this rule, which timing will you use to be most efficient?

After

Weekly

Before

Monthly

Using the "before" timing for the trigger will be most efficient in enforcing the rule that modifications to the THEATER table are not allowed during the last week in December. This is because the "before" timing allows the trigger to be executed before the modification takes place, effectively preventing any modifications from occurring during the specified time period. By using the "before" timing, the trigger can quickly and efficiently check the date and prevent any unauthorized modifications, saving resources and ensuring that the rule is enforced effectively.

Explanation

Using the "before" timing for the trigger will be most efficient in enforcing the rule that modifications to the THEATER table are not allowed during the last week in December. This is because the "before" timing allows the trigger to be executed before the modification takes place, effectively preventing any modifications from occurring during the specified time period. By using the "before" timing, the trigger can quickly and efficiently check the date and prevent any unauthorized modifications, saving resources and ensuring that the rule is enforced effectively.

46. Which statement about packages is true?

Package constructs stored in the database are public.

Package constructs defined within the package specification are private.

Private and public package constructs are both declared and defined within the package body.

A private package construct can only be referenced only by other constructs within the same package.

Private package constructs are only accessible within the same package and cannot be referenced by any other constructs outside of the package. This means that they are not public and cannot be accessed by other packages or programs. Therefore, the statement "A private package construct can only be referenced only by other constructs within the same package" is true.

Explanation

Private package constructs are only accessible within the same package and cannot be referenced by any other constructs outside of the package. This means that they are not public and cannot be accessed by other packages or programs. Therefore, the statement "A private package construct can only be referenced only by other constructs within the same package" is true.

47. Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; 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 get_budget; END theater_pck; Which code will successfully invoke the GET_BUDGET function within SQL*Plus?

VARIABLE g_budget_amount NUMBEREXECUTE :g_budget_amount := get_budget(11);

VARIABLE g_budget_amount NUMBEREXECUTE theater_pck.get_budget(g_budget_amount, 11);

VARIABLE g_budget_amount NUMBEREXECUTE :g_budget_amount := theater_pck.get_budget(11);

This function cannot be referenced from outside the package.

The code to invoke the GET_BUDGET function within SQL*Plus is not provided. However, based on the given package specification and body, the GET_BUDGET function is not declared as a public function in the package. Therefore, it cannot be referenced or invoked from outside the package.

Explanation

The code to invoke the GET_BUDGET function within SQL*Plus is not provided. However, based on the given package specification and body, the GET_BUDGET function is not declared as a public function in the package. Therefore, it cannot be referenced or invoked from outside the package.

48. Examine this procedure: CREATE OR REPLACE PROCEDURE FIND_ORDERS (v_total IN sales_order.total%TYPE) IS CURSOR c1 IS SELECT order_id FROM sales_order WHERE total > v_total; BEGIN FOR sales_order_rec in c1 LOOP --process the row END LOOP; END; This procedure returns all orders with a total greater than an amount that is passed in the V_TOTAL parameter. Occasionally, a user might want to process all orders regardless of the total amount. They could do this by passing 0 in the V_TOTAL parameter, however, they would prefer not to pass anything. Which change can you make to the procedure to allow a user to process all orders in the SALES_ORDER table without having to pass a 0 total amount?

Remove only the V_TOTAL parameter.

Remove the NVL function from the WHERE clause.

Use (v_total IN sales_order.total%TYPE => 0) as the parameter definition.

Use (v_total IN sales_order.total%TYPE DEFAULT 0) as the parameter definition.

The correct answer is to use (v_total IN sales_order.total%TYPE DEFAULT 0) as the parameter definition. By using the DEFAULT keyword, the parameter will have a default value of 0 if no value is explicitly passed when calling the procedure. This allows the user to process all orders in the SALES_ORDER table without having to pass a 0 total amount.

Explanation

The correct answer is to use (v_total IN sales_order.total%TYPE DEFAULT 0) as the parameter definition. By using the DEFAULT keyword, the parameter will have a default value of 0 if no value is explicitly passed when calling the procedure. This allows the user to process all orders in the SALES_ORDER table without having to pass a 0 total amount.

49. You have just successfully dropped the CALC_COMM procedure and deleted the script file containing the source code. Which command can you execute to recover this procedure?

ROLLBACK ;

ROLLBACK TO PROCEDURE calc_comm;

ALTER PROCEDURE calc_comm COMPILE;

Only the database administrator can recover this procedure using backups.

The correct answer states that only the database administrator can recover the CALC_COMM procedure using backups. This implies that there is no specific command mentioned in the given options that can be executed to recover the procedure. Instead, it suggests that the procedure can only be recovered by restoring the database from a backup, which can be done by the database administrator.

Explanation

The correct answer states that only the database administrator can recover the CALC_COMM procedure using backups. This implies that there is no specific command mentioned in the given options that can be executed to recover the procedure. Instead, it suggests that the procedure can only be recovered by restoring the database from a backup, which can be done by the database administrator.

50. CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; 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 get_budget; BEGIN current_avg_cost_per_ticket := 8.50; END theater_pck; Which statement about the value of CURRENT_AVG_COST_PER_TICKET is true?

It is 0 until explicitly referenced.

It is assigned 8.50 each time the package is referenced.

It is assigned 8.50 only when it is explicitly referenced.

It is assigned 8.50 when the package is first invoked within a session.

The value of CURRENT_AVG_COST_PER_TICKET is assigned 8.50 when the package is first invoked within a session.

Explanation

The value of CURRENT_AVG_COST_PER_TICKET is assigned 8.50 when the package is first invoked within a session.

51. Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; 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 get_budget; END theater_pck; Which code will successfully assign a value to the CURRENT_AVG_COST_PER_TICKET variable within SQL*Plus?

BEGINcurrent_avg_cost_per_ticket := 6.75;END;

BEGINtheater_pck.current_avg_cost_per_ticket := 6.75;END;

BEGINSELECT AVG(cost_per_ticket)INTO current_avg_cost_per_ticketFROM gross_receipt;END;

This variable is private to the package and cannot be directly assigned a value within SQL*Plus.

The explanation for the given correct answer is that the variable CURRENT_AVG_COST_PER_TICKET is declared within the package body and is not accessible outside of the package. Therefore, it cannot be directly assigned a value within SQL*Plus.

Explanation

The explanation for the given correct answer is that the variable CURRENT_AVG_COST_PER_TICKET is declared within the package body and is not accessible outside of the package. Therefore, it cannot be directly assigned a value within SQL*Plus.

52. Examine this trigger: CREATE OR REPLACE TRIGGER budget_trig AFTER INSERT ON studio FOR EACH ROW DECLARE v_sum NUMBER; BEGIN SELECT sum(yearly_budget) INTO v_sum FROM studio; UPDATE parent_company SET overall_budget = v_sum; END; You insert a row into the STUDIO table and receive this message: ORA-04091: table SCOTT.STUDIO is mutating, trigger/function may not see it How do you correct this error?

Change the timing to BEFORE.

Remove the AFTER INSERT ON clause.

Remove the SUM function; it is not allowed in database triggers.

Convert it to a statement level database trigger by removing FOR EACH ROW.

The error message "ORA-04091: table SCOTT.STUDIO is mutating, trigger/function may not see it" indicates that the trigger is trying to access the table it is defined on (STUDIO) while it is still being modified. To correct this error, the trigger should be converted to a statement level trigger by removing the "FOR EACH ROW" clause. This change ensures that the trigger operates on the entire statement rather than individual rows, avoiding the mutation error.

Explanation

The error message "ORA-04091: table SCOTT.STUDIO is mutating, trigger/function may not see it" indicates that the trigger is trying to access the table it is defined on (STUDIO) while it is still being modified. To correct this error, the trigger should be converted to a statement level trigger by removing the "FOR EACH ROW" clause. This change ensures that the trigger operates on the entire statement rather than individual rows, avoiding the mutation error.

53. You created a database trigger that will be executed for all data manipulation statements on the THEATER table. Within the code, you will determine which type of manipulation has caused the trigger to execute. Which would you use to test for the type of manipulation being performed?

DBMS_SQL

DBMS_TRIGGER

DBMS_CHECK_MANIPULATION_TYPE

DELETING, UPDATING, and INSERTING

To test for the type of manipulation being performed, you would use "DELETING, UPDATING, and INSERTING". These are the options that represent the different types of data manipulation statements that can be executed on the THEATER table. By checking which type of manipulation is being performed, you can determine the appropriate action to take within the trigger code.

Explanation

To test for the type of manipulation being performed, you would use "DELETING, UPDATING, and INSERTING". These are the options that represent the different types of data manipulation statements that can be executed on the THEATER table. By checking which type of manipulation is being performed, you can determine the appropriate action to take within the trigger code.

54. Examine this function: CREATE OR REPLACE FUNCTION get_budget 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; What additional code is needed to compile this function successfully?

Remove the first RETURN statement.

Change "RETURN number" to "OUTPUT number".

Add "(v_studio_id IN NUMBER)" right after the IS keyword.

Add "(v_studio_id IN NUMBER)" right before the RETURN statement of the header.

To compile the given function successfully, the additional code needed is to add "(v_studio_id IN NUMBER)" right before the RETURN statement of the header. This is necessary because the function is using the variable v_studio_id in the SELECT statement, but it is not declared in the function header. By adding this parameter in the header, the function will be able to compile and execute correctly.

Explanation

To compile the given function successfully, the additional code needed is to add "(v_studio_id IN NUMBER)" right before the RETURN statement of the header. This is necessary because the function is using the variable v_studio_id in the SELECT statement, but it is not declared in the function header. By adding this parameter in the header, the function will be able to compile and execute correctly.

55. Evaluate this procedure: CREATE OR REPLACE PROCEDURE remove_department (v_deptno IN NUMBER(9)) IS BEGIN DELETE dept WHERE deptno = v_deptno; END; Why does this statement fail when compiled?

The keyword AS is missing.

Specifying a precision for a formal parameter is not permitted.

V_DEPTNO should be declared as IN OUT.

The DELETE statement has a syntax error.

The statement fails when compiled because specifying a precision for a formal parameter is not permitted. In PL/SQL, formal parameters do not have a precision, as they are placeholders for values that will be passed into the procedure at runtime. Therefore, specifying a precision for v_deptno is not allowed and causes the compilation to fail.

Explanation

The statement fails when compiled because specifying a precision for a formal parameter is not permitted. In PL/SQL, formal parameters do not have a precision, as they are placeholders for values that will be passed into the procedure at runtime. Therefore, specifying a precision for v_deptno is not allowed and causes the compilation to fail.

56. Examine this procedure: CREATE OR REPLACE PROCEDURE find_cpt (v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER DEFAULT 0) IS BEGIN IF v_cost_per_ticket > 8.50 THEN SELECT cost_per_ticket INTO v_cost_per_ticket FROM gross_receipt WHERE movie_id = v_movie_id; END IF; END; Why does this statement fail when executed?

MOVIE_ID is not declared.

The precision of V_MOVIE_ID was not specified.

V_COST_PER_TICKET should be specified as OUT.

The SELECT INTO statement contains a syntax error.

The declaration of V_COST_PER_TICKET cannot have a DEFAULT value.

The statement fails because the declaration of V_COST_PER_TICKET cannot have a DEFAULT value. In the procedure, V_COST_PER_TICKET is declared as IN OUT parameter, which means it can be both input and output. However, when declaring an IN OUT parameter, it is not allowed to provide a default value.

Explanation

The statement fails because the declaration of V_COST_PER_TICKET cannot have a DEFAULT value. In the procedure, V_COST_PER_TICKET is declared as IN OUT parameter, which means it can be both input and output. However, when declaring an IN OUT parameter, it is not allowed to provide a default value.

57. Which two subprogram headers are correct? (Choose two.)

CREATE OR REPLACE PROCEDURE get_salIS(v_sal IN number)

CREATE OR REPLACE PROCEDURE get_sal(v_sal IN number)IS

CREATE OR REPLACE FUNCTION calc_commRETURN number(p_amnt IN number)

CREATE OR REPLACE FUNCTION calc_comm(p_amnt IN number)RETURN number

CREATE OR REPLACE FUNCTION calc_comm(p_amnt IN number(3,2))RETURN number

The two correct subprogram headers are "CREATE OR REPLACE PROCEDURE get_sal" with input parameter "v_sal" of type number, and "CREATE OR REPLACE FUNCTION calc_comm" with input parameter "p_amnt" of type number and return type number.

Explanation

The two correct subprogram headers are "CREATE OR REPLACE PROCEDURE get_sal" with input parameter "v_sal" of type number, and "CREATE OR REPLACE FUNCTION calc_comm" with input parameter "p_amnt" of type number and return type number.

Submit

58. When creating the ADD_PROD procedure in SQL*Plus, you receive this message: Warning: Procedure created with compilation errors. What was saved to the data dictionary?

Source code only

Compilation errors only

Source code and compilation errors

When creating the ADD_PROD procedure in SQL*Plus and receiving the message "Warning: Procedure created with compilation errors," it means that both the source code and compilation errors were saved to the data dictionary. The data dictionary is a collection of metadata that stores information about the database objects, including the source code and any errors encountered during compilation. Therefore, when the procedure is created with compilation errors, both the source code and these errors are saved in the data dictionary.

Explanation

When creating the ADD_PROD procedure in SQL*Plus and receiving the message "Warning: Procedure created with compilation errors," it means that both the source code and compilation errors were saved to the data dictionary. The data dictionary is a collection of metadata that stores information about the database objects, including the source code and any errors encountered during compilation. Therefore, when the procedure is created with compilation errors, both the source code and these errors are saved in the data dictionary.

59. Which statement about formal parameters is true?

You can assign a default value to an OUT formal parameter.

An IN OUT formal parameter does not require a value before returning to the calling environment.

You cannot assign a value to an IN formal parameter inside the procedure in which it is being used.

You cannot change the value of an OUT formal parameter inside the procedure in which it is being used.

The statement that is true about formal parameters is that you cannot assign a value to an IN formal parameter inside the procedure in which it is being used. IN parameters are used for passing values into a procedure, and they are read-only within the procedure. This means that you cannot modify the value of an IN parameter inside the procedure.

Explanation

The statement that is true about formal parameters is that you cannot assign a value to an IN formal parameter inside the procedure in which it is being used. IN parameters are used for passing values into a procedure, and they are read-only within the procedure. This means that you cannot modify the value of an IN parameter inside the procedure.

60. Which code successfully calculates commission returning it to the calling environment?

CREATE OR REPLACE FUNCTION calc_comm(v_emp_id IN NUMBER)RETURN numberISv_total NUMBER;BEGINSELECT SUM(ord.total)INTO v_totalFROM ord,customerWHERE ord.custid = customer.custidAND customer.repid = v_emp_id;END;

CREATE OR REPLACE FUNCTION calc_comm(v_emp_id IN NUMBER)ISv_total NUMBER;BEGINSELECT SUM(ord.total)INTO v_totalFROM ord,customerWHERE ord.custid = customer.custidAND customer.repid = v_emp_id;RETURN (v_total * .20);END;

The correct answer is the third option: CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER) IS RETURN number v_total NUMBER; BEGIN SELECT SUM(ord.total) INTO v_total FROM ord,customer WHERE ord.custid = customer.custid AND customer.repid = v_emp_id; RETURN (v_total * .20); END;

This option successfully calculates the commission by selecting the sum of the total orders for a specific employee ID and multiplying it by 0.20. It also includes the necessary syntax for creating a function in Oracle PL/SQL.

Explanation

The correct answer is the third option: CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER) IS RETURN number v_total NUMBER; BEGIN SELECT SUM(ord.total) INTO v_total FROM ord,customer WHERE ord.custid = customer.custid AND customer.repid = v_emp_id; RETURN (v_total * .20); END;

This option successfully calculates the commission by selecting the sum of the total orders for a specific employee ID and multiplying it by 0.20. It also includes the necessary syntax for creating a function in Oracle PL/SQL.

61. Which three statements about procedures are true? (Choose three.)

They are typically written in SQL.

They add functionality to SQL DML statements.

They promote reusability and maintainability.

They perform actions and can accept parameters.

They require at least one executable statement in the procedure body.

Procedures are typically written in SQL and are used to add functionality to SQL DML statements. They promote reusability and maintainability by allowing the same code to be used multiple times and making it easier to make changes to the code in one place. Procedures can perform actions and accept parameters, allowing for flexibility in their use. Additionally, procedures require at least one executable statement in the procedure body to define the actions to be performed.

Explanation

Procedures are typically written in SQL and are used to add functionality to SQL DML statements. They promote reusability and maintainability by allowing the same code to be used multiple times and making it easier to make changes to the code in one place. Procedures can perform actions and accept parameters, allowing for flexibility in their use. Additionally, procedures require at least one executable statement in the procedure body to define the actions to be performed.

Submit

62. How do functions simplify maintainability?

By limiting changes to logic to one location

By eliminating the need for an executable section

By eliminating the need for the storage of object code

By ignoring side effects when invoked from within a SQL statement

Functions simplify maintainability by limiting changes to logic to one location. This means that if there is a change needed in the logic of a function, it only needs to be made in that one location. This reduces the chances of introducing errors or inconsistencies, as the logic is centralized and can be easily managed. It also makes it easier to understand and debug the code, as the logic is contained within a specific function. By limiting changes to one location, functions promote modularity and encapsulation, making the code easier to maintain and update.

Explanation

Functions simplify maintainability by limiting changes to logic to one location. This means that if there is a change needed in the logic of a function, it only needs to be made in that one location. This reduces the chances of introducing errors or inconsistencies, as the logic is centralized and can be easily managed. It also makes it easier to understand and debug the code, as the logic is contained within a specific function. By limiting changes to one location, functions promote modularity and encapsulation, making the code easier to maintain and update.

63. Which two statements are true? (Choose two.)

A function must return a value.

Functions and procedures must return a value.

Functions and procedures must contain IN arguments.

A function can be invoked from within a PL/SQL expression.

A procedure must be invoked from within a PL/SQL expression.

A function must return a value because it is designed to perform a specific task and produce a result. It is expected to return a value to the caller.

A function can be invoked from within a PL/SQL expression because it can be used as part of a larger expression or calculation within a PL/SQL block. This allows the function to be used to manipulate data or perform calculations within the PL/SQL code.

Explanation

A function must return a value because it is designed to perform a specific task and produce a result. It is expected to return a value to the caller.

A function can be invoked from within a PL/SQL expression because it can be used as part of a larger expression or calculation within a PL/SQL block. This allows the function to be used to manipulate data or perform calculations within the PL/SQL code.

Submit

64. A stored function can be invoked in many different ways. Which invocation example is NOT valid?

Executing the stored function within an INSERT statement

Executing the stored function within a server-side function

Executing the stored function within a client-side function

Executing the stored function within a CHECK constraint of a table

not-available-via-ai

Explanation

not-available-via-ai

65. Examine this function: CREATE OR REPLACE FUNCTION set_budget (v_studio_id IN NUMBER, v_new_budget IN NUMBER) RETURN BOOLEAN IS BEGIN UPDATE studio SET yearly_budget = v_new_budget WHERE id = v_studio_id; IF SQL%FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; COMMIT; END; Which code will successfully invoke this function?

SELECT id, set_budget(11,500000000)FROM studio;

DECLAREv_updated_flag BOOLEAN;BEGINset_budget(11,500000000);END;

VARIABLE g_updated_flag BOOLEANBEGINg_updated_flag := set_budget(11,500000000);END;

DECLAREv_updated_flag BOOLEAN;BEGINv_updated_flag := set_budget(11,500000000);END;

The correct answer is the DECLARE block because it declares a variable v_updated_flag of type BOOLEAN and assigns the result of the set_budget function to it. This allows the function to be invoked and the result to be stored in the variable for further use.

Explanation

The correct answer is the DECLARE block because it declares a variable v_updated_flag of type BOOLEAN and assigns the result of the set_budget function to it. This allows the function to be invoked and the result to be stored in the variable for further use.

66. 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 set of statements will successfully invoke this function within SQL*Plus?

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

VARIABLE g_yearly_budget NUMBEREXECUTE g_yearly_budget := GET_BUDGET(11);

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

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

The correct set of statements to successfully invoke the function within SQL*Plus is to declare a variable "g_yearly_budget" of type NUMBER using the "VARIABLE" command, and then use the "EXECUTE" command to assign the result of the function call "GET_BUDGET(11)" to the variable ":g_yearly_budget".

Explanation

The correct set of statements to successfully invoke the function within SQL*Plus is to declare a variable "g_yearly_budget" of type NUMBER using the "VARIABLE" command, and then use the "EXECUTE" command to assign the result of the function call "GET_BUDGET(11)" to the variable ":g_yearly_budget".

67. Examine this function: CREATE OR REPLACE FUNCTION set_budget (v_studio_id IN NUMBER, v_new_budget IN NUMBER) IS BEGIN UPDATE studio SET yearly_budget = v_new_budget WHERE id = v_studio_id; IF SQL%FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; COMMIT; END; Which code must be added to successfully compile this function?

Add "RETURN;" immediately before the IS keyword.

Add "RETURN NUMBER" immediately before the IS keyword.

Add "RETURN BOOLEAN" immediately after the IS keyword.

Add "RETURN BOOLEAN" immediately before the IS keyword.

In order to successfully compile this function, the code "RETURN BOOLEAN" must be added immediately before the IS keyword. This is because the function is expected to return a boolean value, either TRUE or FALSE, based on the result of the UPDATE statement. Adding "RETURN BOOLEAN" before the IS keyword specifies the return type of the function.

Explanation

In order to successfully compile this function, the code "RETURN BOOLEAN" must be added immediately before the IS keyword. This is because the function is expected to return a boolean value, either TRUE or FALSE, based on the result of the UPDATE statement. Adding "RETURN BOOLEAN" before the IS keyword specifies the return type of the function.

68. The CHECK_THEATER trigger of the THEATER table has been disabled. Which command can you issue to enable this trigger?

ENABLE check_theater;

ENABLE TRIGGER check_theater;

ALTER TRIGGER check_theater ENABLE;

ALTER TABLE check_theater ENABLE check_theater;

The correct command to enable a trigger in this scenario is "ALTER TRIGGER check_theater ENABLE;". This command specifically enables the "check_theater" trigger on the table. The other options are not valid commands for enabling a trigger.

Explanation

The correct command to enable a trigger in this scenario is "ALTER TRIGGER check_theater ENABLE;". This command specifically enables the "check_theater" trigger on the table. The other options are not valid commands for enabling a trigger.

69. You have just created a PL/SQL user-defined function called CALC_COMM. Which statement will successfully test it?

EXECUTE calc_comm;

SELECT *FROM calc_comm(emp);

EXECUTE calc_comm(SELECT total FROM ord);

SELECT *FROM ordGROUP BY ordidHAVING calc_comm(total) > 5000;

The correct answer is "SELECT * FROM ord GROUP BY ordid HAVING calc_comm(total) > 5000;". This statement will successfully test the PL/SQL user-defined function CALC_COMM. It selects all rows from the ORD table, groups them by the ORDID column, and then applies the CALC_COMM function to the TOTAL column. It filters out the rows where the result of the CALC_COMM function is greater than 5000. This statement ensures that the function is being used correctly and returns the desired results.

Explanation

The correct answer is "SELECT * FROM ord GROUP BY ordid HAVING calc_comm(total) > 5000;". This statement will successfully test the PL/SQL user-defined function CALC_COMM. It selects all rows from the ORD table, groups them by the ORDID column, and then applies the CALC_COMM function to the TOTAL column. It filters out the rows where the result of the CALC_COMM function is greater than 5000. This statement ensures that the function is being used correctly and returns the desired results.

70. Examine this statement: SELECT id, theater_pck.get_budget(id) FROM studio; What must be true about the GET_BUDGET function for this statement to be successful?

It must be remote.

It must not modify the database.

It must also exist as a stand-alone function.

It must only exist in the body of THEATER_PCK.

The statement is selecting the ID and the budget of a theater from the studio table. In order for this statement to be successful, the GET_BUDGET function must not modify the database. This means that the function should only retrieve data and not make any changes to the underlying database.

Explanation

The statement is selecting the ID and the budget of a theater from the studio table. In order for this statement to be successful, the GET_BUDGET function must not modify the database. This means that the function should only retrieve data and not make any changes to the underlying database.

71. Examine this procedure: CREATE OR REPLACE PROCEDURE find_cpt (v_movie_id {argument mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER) IS BEGIN IF v_cost_per_ticket > 8.50 THEN SELECT cost_per_ticket INTO v_cost_per_ticket FROM gross_receipt WHERE movie_id = v_movie_id; END IF; END; Which mode should be used for V_COST_PER_TICKET?

IN

OUT

RETURN

IN OUT

The mode "IN OUT" should be used for V_COST_PER_TICKET because the procedure is designed to both accept a value for v_cost_per_ticket as an input (IN) and also modify the value of v_cost_per_ticket and return it as an output (OUT). This allows the procedure to both read and update the value of v_cost_per_ticket within the procedure.

Explanation

The mode "IN OUT" should be used for V_COST_PER_TICKET because the procedure is designed to both accept a value for v_cost_per_ticket as an input (IN) and also modify the value of v_cost_per_ticket and return it as an output (OUT). This allows the procedure to both read and update the value of v_cost_per_ticket within the procedure.

72. Examine this function: CREATE OR REPLACE FUNCTION get_budget (v_studio_id IN NUMBER, v_max_budget IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; IF v_yearly_budget > v_max_budget THEN RETURN v_max_budget; ELSE RETURN v_yearly_budget; END IF; END; Which set of statements will successfully invoke this function within SQL*Plus?

SELECT id, name, get_budget(id,200)FROM studio;

VARIABLE g_yearly_budget NUMBEREXECUTE g_yearly_budget := get_budget(11, 4000000000);

VARIABLE g_yearly_budget NUMBERRUN :g_yearly_budget := get_budget(v_studio_id => 11,v_max_budget => 4000000000);

The correct answer is the first option. This is because it correctly invokes the function "get_budget" within the SQL query by passing the "id" column from the "studio" table as the first argument and the value 200 as the second argument. It retrieves the "id" and "name" columns from the "studio" table and also includes the result of the "get_budget" function in the output.

Explanation

The correct answer is the first option. This is because it correctly invokes the function "get_budget" within the SQL query by passing the "id" column from the "studio" table as the first argument and the value 200 as the second argument. It retrieves the "id" and "name" columns from the "studio" table and also includes the result of the "get_budget" function in the output.

73. Examine this procedure: CREATE OR REPLACE PROCEDURE remove_department (v_deptno IN NUMBER) IS BEGIN DELETE dept WHERE deptno = v_deptno; END; After executing this procedure, you receive this message: ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found What must be added to the procedure to handle this error?

Create an EXCEPTION section, and add code to handle this PL/SQL predefined exception.

Add an IF statement immediately after the DELETE statement to check the value of SQL%NOTFOUND.

Nothing should be added to this procedure. It must only be executed using valid department numbers.

The correct answer is to declare a new exception and associate it with error code -2292. By creating an exception section and adding code to handle this non-predefined exception, the procedure can handle the ORA-02292 error. This allows for proper handling of the integrity constraint violation, ensuring that any child records are dealt with appropriately.

Explanation

The correct answer is to declare a new exception and associate it with error code -2292. By creating an exception section and adding code to handle this non-predefined exception, the procedure can handle the ORA-02292 error. This allows for proper handling of the integrity constraint violation, ensuring that any child records are dealt with appropriately.

74. Procedures and functions can be created and stored in the database or in an Oracle Developer application. How is performance improved when storing procedures and functions in the database?

Network roundtrips are reduced.

The object code is created during execution.

Network traffic is decreased by bundling commands.

The source code is stored externally, and the object code is stored internally.

Storing procedures and functions in the database improves performance by reducing network roundtrips. When the code is stored in the database, it can be executed directly on the server without the need to transmit data back and forth between the client and the server. This reduces the latency and overhead associated with network communication, resulting in faster execution times.

Explanation

Storing procedures and functions in the database improves performance by reducing network roundtrips. When the code is stored in the database, it can be executed directly on the server without the need to transmit data back and forth between the client and the server. This reduces the latency and overhead associated with network communication, resulting in faster execution times.

75. You created a database trigger that will be executed for all data manipulation statements on the THEATER table. Within the code, you will determine which type of manipulation has caused the trigger to execute. Which would you use to test for the type of manipulation being performed?

BEFORE (gross_receipt) DELETE, INSERT, UPDATE

AFTER DELETE OR INSERT OR UPDATE ON gross_receipt

BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt

BEFORE DELETE OR INSERT OR UPDATE ON gross_receiptFOR EACH ROW

The correct answer is "BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt". This is the correct syntax for creating a trigger that will be executed before any delete, insert, or update operation is performed on the gross_receipt table. The "BEFORE" keyword indicates that the trigger will be executed before the operation takes place, and the "DELETE OR INSERT OR UPDATE" keywords specify the types of operations that will trigger the execution of the trigger. The "ON gross_receipt" specifies the table on which the trigger will be applied.

Explanation

The correct answer is "BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt". This is the correct syntax for creating a trigger that will be executed before any delete, insert, or update operation is performed on the gross_receipt table. The "BEFORE" keyword indicates that the trigger will be executed before the operation takes place, and the "DELETE OR INSERT OR UPDATE" keywords specify the types of operations that will trigger the execution of the trigger. The "ON gross_receipt" specifies the table on which the trigger will be applied.