Oracle Certified Associate Practice Quiz!

1. Which data dictionary view can you query to examine all the dependencies between the objects that you own?

USER_OBJECTS

USER_RELATIONS

USER_DEPENDENCIES

USER_RELATIONSHIPS

The correct answer is USER_DEPENDENCIES. This data dictionary view allows you to query and examine all the dependencies between the objects that you own. It provides information about the dependent objects, such as procedures, functions, packages, and triggers, and the objects they depend on. This view is useful for understanding the relationships and dependencies between different database objects, which can be crucial for managing and maintaining the database effectively.

Explanation

The correct answer is USER_DEPENDENCIES. This data dictionary view allows you to query and examine all the dependencies between the objects that you own. It provides information about the dependent objects, such as procedures, functions, packages, and triggers, and the objects they depend on. This view is useful for understanding the relationships and dependencies between different database objects, which can be crucial for managing and maintaining the database effectively.

2. Examine this code: CREATE OR REPLACE PROCEDURE find_seats_sold (v_movie_id IN NUMBER) IS v_seats_sold NUMBER(3); BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id; END; You are concerned with future changes to the database, such as modifying the maximum length of the SEATS_SOLD column of the GROSS_RECEIPT table. Which change should you make to guarantee successful recompilation of this procedure in the event of such a change?

Define V_SEATS_SOLD as GROSS_RECEIPT.SEATS_SOLD%TYPE.

Define V_SEATS_SOLD using the PRAGMA EXCEPTION_INIT compiler directive.

Change V_SEATS_SOLD to be a formal argument rather than a local variable.

Define V_SEATS_SOLD using the PRAGMA RESTRICT_REFERENCES compiler directive.

To guarantee successful recompilation of the procedure in the event of future changes to the maximum length of the SEATS_SOLD column in the GROSS_RECEIPT table, it is recommended to define the variable V_SEATS_SOLD as GROSS_RECEIPT.SEATS_SOLD%TYPE. This ensures that the variable will always have the same data type and length as the SEATS_SOLD column, regardless of any modifications made to it in the future.

Explanation

To guarantee successful recompilation of the procedure in the event of future changes to the maximum length of the SEATS_SOLD column in the GROSS_RECEIPT table, it is recommended to define the variable V_SEATS_SOLD as GROSS_RECEIPT.SEATS_SOLD%TYPE. This ensures that the variable will always have the same data type and length as the SEATS_SOLD column, regardless of any modifications made to it in the future.

3. Examine this procedure: CREATE OR REPLACE 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; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END; Which command will successfully invoke this procedure in SQL*Plus?

RUN find_seats_sold (v_theater_id => 500);

EXECUTE find_seats_sold (v_movie_id => 34);

EXECUTE find_seats_sold (v_theater_id => 500);

RUN find_seats_sold (v_movie_id => DEFAULT, v_theater_id => 500);

The correct command to invoke this procedure in SQL*Plus is "EXECUTE find_seats_sold (v_theater_id => 500)". This command uses the EXECUTE keyword to execute the procedure "find_seats_sold" and passes the value 500 to the parameter "v_theater_id".

Explanation

The correct command to invoke this procedure in SQL*Plus is "EXECUTE find_seats_sold (v_theater_id => 500)". This command uses the EXECUTE keyword to execute the procedure "find_seats_sold" and passes the value 500 to the parameter "v_theater_id".

4. Which type(s) of objects does Oracle keep track of?

All object types

Database triggers only

Tables, procedures, and functions only

Packages, procedures, and functions only

Oracle keeps track of all object types in the database. This means that Oracle keeps records of tables, procedures, functions, packages, database triggers, and any other objects that are created within the database. By keeping track of all object types, Oracle ensures that it can manage and maintain the integrity and consistency of the database, allowing users to access and manipulate the data effectively.

Explanation

Oracle keeps track of all object types in the database. This means that Oracle keeps records of tables, procedures, functions, packages, database triggers, and any other objects that are created within the database. By keeping track of all object types, Oracle ensures that it can manage and maintain the integrity and consistency of the database, allowing users to access and manipulate the data effectively.

5. What should be placed after the IS keyword when creating a procedure?

Parameters

Local variables only

Global variables only

Global and local variables

When creating a procedure, the keyword "local variables only" should be placed after the IS keyword. This indicates that only local variables, which are variables declared within the procedure and can only be accessed within the procedure, will be used in the procedure. This means that any variables declared outside of the procedure, such as global variables, cannot be accessed or used within the procedure.

Explanation

When creating a procedure, the keyword "local variables only" should be placed after the IS keyword. This indicates that only local variables, which are variables declared within the procedure and can only be accessed within the procedure, will be used in the procedure. This means that any variables declared outside of the procedure, such as global variables, cannot be accessed or used within the procedure.

6. Which statement about the use of the DEFAULT clause in the declaration of a formal parameter is true?

IN parameters must be initialized with a default value.

OUT parameters must be initialized with a default value.

IN parameters cannot be initialized with a default value.

IN OUT parameters cannot be initialized with a default value.

The statement that is true about the use of the DEFAULT clause in the declaration of a formal parameter is that IN OUT parameters cannot be initialized with a default value.

Explanation

The statement that is true about the use of the DEFAULT clause in the declaration of a formal parameter is that IN OUT parameters cannot be initialized with a default value.

7. Examine this procedure: CREATE OR REPLACE PROCEDURE calculate_budget IS v_budget studio.yearly_budget%TYPE; BEGIN v_budget := get_budget (11); IF v_budget < 30000000 THEN set_budget (11, 300000000); END IF; END; For which reason would the recompilation of CALCULATE_BUDGET be unsuccessful?

A change to the code of SET_BUDGET

A change to the code of GET_BUDGET

A change to the arguments of SET_BUDGET or GET_BUDGET

A change to the maximum length of the YEARLY_SALARY column of the STUDIO table

If there is a change to the arguments of SET_BUDGET or GET_BUDGET, the recompilation of CALCULATE_BUDGET would be unsuccessful because the procedure is using these functions and any change in their arguments would cause a mismatch between the procedure and the functions it calls. This would result in a compilation error.

Explanation

If there is a change to the arguments of SET_BUDGET or GET_BUDGET, the recompilation of CALCULATE_BUDGET would be unsuccessful because the procedure is using these functions and any change in their arguments would cause a mismatch between the procedure and the functions it calls. This would result in a compilation error.

8. The MODIFY_PAYROLL procedure contains many SQL statements and will be executed from multiple client applications. Where should this procedure be stored?

Server only

System global area

Client applications only

Server and client applications

The MODIFY_PAYROLL procedure should be stored on the server only. Storing it on the server ensures that it can be accessed and executed by multiple client applications. Placing it in the system global area or on client applications only would limit its accessibility and make it difficult for all client applications to execute the procedure. Storing it on both the server and client applications would result in redundancy and potential synchronization issues.

Explanation

The MODIFY_PAYROLL procedure should be stored on the server only. Storing it on the server ensures that it can be accessed and executed by multiple client applications. Placing it in the system global area or on client applications only would limit its accessibility and make it difficult for all client applications to execute the procedure. Storing it on both the server and client applications would result in redundancy and potential synchronization issues.

9. You execute this code: CREATE OR REPLACE PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34) IS v_seats_sold gross_receipt.seats_sold%TYPE; BEGIN null; END; Which statement is true?

The statement compiles, and the procedure is created.

The statement complies with errors because there is no exception handling.

The statement complies with errors because the body does not contain a SQL statement.

The statement complies, and the procedure is stored as a data dictionary object in the database.

The given code is a valid PL/SQL code that creates a procedure named "find_seats_sold" with a parameter "v_movie_id" of type NUMBER and a local variable "v_seats_sold" of the same type. The code does not contain any executable statements inside the BEGIN-END block, but since the question does not specify any requirements for the procedure, it is still considered a valid procedure creation. Therefore, the statement compiles successfully and the procedure is created.

Explanation

The given code is a valid PL/SQL code that creates a procedure named "find_seats_sold" with a parameter "v_movie_id" of type NUMBER and a local variable "v_seats_sold" of the same type. The code does not contain any executable statements inside the BEGIN-END block, but since the question does not specify any requirements for the procedure, it is still considered a valid procedure creation. Therefore, the statement compiles successfully and the procedure is created.

10. Which statement about declaring arguments for procedures is true?

Precision must be specified for VARCHAR2 arguments.

Each declared argument must have a mode specified.

An IN argument passes a value from a procedure to the calling environment.

Formal arguments allow you to transfer values to and from the calling environment.

Formal arguments allow you to transfer values to and from the calling environment. This means that when a procedure is called, values can be passed to the procedure through its formal arguments, and the procedure can also return values back to the calling environment through these arguments. This allows for the exchange of data between the procedure and the calling code, making it a useful feature for passing and manipulating data within a program.

Explanation

Formal arguments allow you to transfer values to and from the calling environment. This means that when a procedure is called, values can be passed to the procedure through its formal arguments, and the procedure can also return values back to the calling environment through these arguments. This allows for the exchange of data between the procedure and the calling code, making it a useful feature for passing and manipulating data within a program.

11. Examine this procedure: 1. CREATE OR REPLACE PROCEDURE remove_department 2. (v_deptno IN NUMBER) 3. IS 4. BEGIN 5. DELETE dept 6. WHERE deptno = v_deptno; 7. EXCEPTION 8. WHEN DEPT_DELETE_EXCEPTION 9. THEN DBMS_OUTPUT.PUT_LINE ('Cannot delete this department.'); 10. END; Which line of this procedure creates an error when compiled?

1

2

5

6

8

Line 8 creates an error when compiled because "WHEN DEPT_DELETE_EXCEPTION" is not a valid exception in Oracle.

Explanation

Line 8 creates an error when compiled because "WHEN DEPT_DELETE_EXCEPTION" is not a valid exception in Oracle.

12. How does a local dependent object differ from a remote dependent object?

Local dependent objects are on the same node in the same database.

Remote dependent objects are on the same node in the same database.

Local dependent objects are on different nodes in the same database.

Remote dependent objects are on the same node in different databases.

A local dependent object refers to an object that is located on the same node in the same database as the object it depends on. This means that both the dependent object and the object it depends on are physically stored in the same location within the database. On the other hand, a remote dependent object is also located on the same node but in a different database. This means that the dependent object and the object it depends on are stored in separate databases, although they are still on the same node.

Explanation

A local dependent object refers to an object that is located on the same node in the same database as the object it depends on. This means that both the dependent object and the object it depends on are physically stored in the same location within the database. On the other hand, a remote dependent object is also located on the same node but in a different database. This means that the dependent object and the object it depends on are stored in separate databases, although they are still on the same node.

13. Which data dictionary table can you query to determine all stand-alone procedures that reference the THEATER_PCK package?

USER_OBJECTS

USER_DEPTREE

USER_PACKAGES

USER_DEPENDENCIES

The correct answer is USER_DEPENDENCIES because this data dictionary table contains information about dependencies between database objects. By querying this table, you can determine all stand-alone procedures that reference the THEATER_PCK package, as it will provide information about the dependencies between the procedures and packages in the database.

Explanation

The correct answer is USER_DEPENDENCIES because this data dictionary table contains information about dependencies between database objects. By querying this table, you can determine all stand-alone procedures that reference the THEATER_PCK package, as it will provide information about the dependencies between the procedures and packages in the database.

14. Examine this procedure: CREATE OR REPLACE PROCEDURE calculate_budget IS v_budget studio.yearly_budget%TYPE; BEGIN v_budget := get_budget@proddb(11); IF v_budget < 30000000 THEN set_budget (11, 300000000); END IF; END; You are about to add an argument to the local procedure, CALCULATE_BUDGET. What effect will this have on the remote procedure, GET_BUDGET?

It will be marked as invalid

It will be recompiled at the next execution.

It will be recompiled at the second execution.

There is no effect.

Adding an argument to the local procedure, CALCULATE_BUDGET, will have no effect on the remote procedure, GET_BUDGET. The remote procedure will not be marked as invalid or recompiled. The addition of an argument to the local procedure does not impact the remote procedure in any way.

Explanation

Adding an argument to the local procedure, CALCULATE_BUDGET, will have no effect on the remote procedure, GET_BUDGET. The remote procedure will not be marked as invalid or recompiled. The addition of an argument to the local procedure does not impact the remote procedure in any way.

15. 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; This function is executed from within a procedure called, CALCULATE_BUDGET. When executing CALCULATE_BUDGET, which relationships will be identified?

A direct dependency between SET_BUDGET and STUDIO only

A direct dependency between SET_BUDGET and CALCULATE_BUDGET only

A direct dependency between SET_BUDGET, CALCULATE_BUDGET, and STUDIO

A direct dependency between SET_BUDGET and CALCULATE_BUDGET, a direct dependencybetween SET_BUDGET and STUDIO, and an indirect dependency between CALCULATE_BUDGETand STUDIO

The correct answer is a direct dependency between SET_BUDGET and CALCULATE_BUDGET, a direct dependency between SET_BUDGET and STUDIO, and an indirect dependency between CALCULATE_BUDGET and STUDIO. This is because the function SET_BUDGET is called from within the procedure CALCULATE_BUDGET, creating a direct dependency between the two. Additionally, SET_BUDGET updates the table STUDIO, creating a direct dependency between SET_BUDGET and STUDIO. Finally, since CALCULATE_BUDGET uses the updated data from STUDIO, there is an indirect dependency between CALCULATE_BUDGET and STUDIO.

Explanation

The correct answer is a direct dependency between SET_BUDGET and CALCULATE_BUDGET, a direct dependency between SET_BUDGET and STUDIO, and an indirect dependency between CALCULATE_BUDGET and STUDIO. This is because the function SET_BUDGET is called from within the procedure CALCULATE_BUDGET, creating a direct dependency between the two. Additionally, SET_BUDGET updates the table STUDIO, creating a direct dependency between SET_BUDGET and STUDIO. Finally, since CALCULATE_BUDGET uses the updated data from STUDIO, there is an indirect dependency between CALCULATE_BUDGET and STUDIO.

16. Examine this procedure: CREATE OR REPLACE PROCEDURE update_employee (v_emp_id IN NUMBER) IS v_comm NUMBER; PROCEDURE calc_comm IS 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; v_comm := v_total * .20; END calc_comm; v_percentage NUMBER; BEGIN SELECT percentage INTO v_percentage FROM daily_figures WHERE TRUNC(figure_date) = TRUNC(SYSDATE); IF v_percentage > 33 THEN calc_comm; END IF; END; Why does this code cause an error when compiled?

The SUBPROGRAM keyword does not exist.

CALC_COMM must be invoked using the EXECUTE command.

CALC_COMM must be declared after all local variable declarations.

CALC_COMM must be declared before all local variable declarations.

The code causes an error when compiled because the procedure calc_comm is declared after the local variable v_comm. In PL/SQL, subprograms must be declared before any local variable declarations. Therefore, the correct order should be to declare calc_comm before declaring v_comm.

Explanation

The code causes an error when compiled because the procedure calc_comm is declared after the local variable v_comm. In PL/SQL, subprograms must be declared before any local variable declarations. Therefore, the correct order should be to declare calc_comm before declaring v_comm.

17. Examine this procedure: CREATE OR REPLACE PROCEDURE calculate_budget IS BEGIN v_budget := get_budget (11); IF v_budget < 30000000 THEN set_budget(11, 300000000); END IF; END; A table that SET_BUDGET references has been accidentally dropped. Until this problem is resolved, what is the status of these procedures?

Only SET_BUDGET is marked invalid.

Only GET_BUDGET and SET_BUDGET are marked invalid.

Only CALCULATE_BUDGET and SET_BUDGET are marked invalid.

All three are marked invalid.

The procedure CALCULATE_BUDGET is marked invalid because it references the SET_BUDGET procedure, which has been accidentally dropped. Since SET_BUDGET is no longer available, CALCULATE_BUDGET cannot execute successfully. Therefore, both CALCULATE_BUDGET and SET_BUDGET are marked invalid. GET_BUDGET is not affected by the dropped table and remains valid.

Explanation

The procedure CALCULATE_BUDGET is marked invalid because it references the SET_BUDGET procedure, which has been accidentally dropped. Since SET_BUDGET is no longer available, CALCULATE_BUDGET cannot execute successfully. Therefore, both CALCULATE_BUDGET and SET_BUDGET are marked invalid. GET_BUDGET is not affected by the dropped table and remains valid.

18. Evaluate this SELECT statement: SELECT * FROM user_dependencies WHERE referenced_name = 'EMPLOYEE'; The EMPLOYEE table is in your schema. Which result will this statement accomplish?

Displays all the direct dependencies on the EMPLOYEE table

Displays only the direct dependencies you created on the EMPLOYEE table

Displays the name of the indirect dependencies on the EMPLOYEE table

Displays the OWNER column to indicate the schema of any referenced objects

This SELECT statement will display all the direct dependencies on the EMPLOYEE table. It will retrieve all the objects that directly depend on the EMPLOYEE table, such as views, procedures, or functions.

Explanation

This SELECT statement will display all the direct dependencies on the EMPLOYEE table. It will retrieve all the objects that directly depend on the EMPLOYEE table, such as views, procedures, or functions.

19. When a change is made to a referenced database object, what can be said about existing dependencies?

Only direct dependent objects are affected.

Only indirect dependent objects are affected.

Only those dependencies that directly reference a database table are affected.

All direct and indirect dependent objects are affected.

When a change is made to a referenced database object, all direct and indirect dependent objects are affected. This means that any objects that directly or indirectly rely on the changed object will be impacted by the modification. This includes not only the objects that directly reference a database table, but also any other objects that depend on those directly dependent objects. Therefore, any change made to a referenced database object will have a cascading effect on all its direct and indirect dependencies.

Explanation

When a change is made to a referenced database object, all direct and indirect dependent objects are affected. This means that any objects that directly or indirectly rely on the changed object will be impacted by the modification. This includes not only the objects that directly reference a database table, but also any other objects that depend on those directly dependent objects. Therefore, any change made to a referenced database object will have a cascading effect on all its direct and indirect dependencies.

20. 321Examine this procedure: CREATE OR REPLACE PROCEDURE calculate_budget IS v_budget studio.yearly_budget%TYPE; BEGIN v_budget := get_budget (11); IF v_budget < 30000000 THEN set_budget (11, 300000000); END IF; END; If the GET_BUDGET function is currently marked as invalid, when will it be recompiled implicitly?

Never, but may be manually recompiled

The next time the SET_BUDGET procedure is analyzed

The second time the GET_BUDGET function is executed

The next time the CALCULATE_BUDGET procedure is executed

The GET_BUDGET function will be recompiled implicitly the next time the CALCULATE_BUDGET procedure is executed. This is because when a procedure is executed, all the objects it depends on, such as functions, are also checked for validity. Since the GET_BUDGET function is currently marked as invalid, it will be recompiled before the CALCULATE_BUDGET procedure is executed to ensure that it is up to date and can be used correctly.

Explanation

The GET_BUDGET function will be recompiled implicitly the next time the CALCULATE_BUDGET procedure is executed. This is because when a procedure is executed, all the objects it depends on, such as functions, are also checked for validity. Since the GET_BUDGET function is currently marked as invalid, it will be recompiled before the CALCULATE_BUDGET procedure is executed to ensure that it is up to date and can be used correctly.

21. Examine this procedure: CREATE PROCEDURE add_theater IS BEGIN INSERT INTO theater VALUES (35, 'Riverplace Theatre', '1222 River Drive, Austin, Tx.'); END; Which three statements about this procedure are true? (Choose three.)

The ADD_THEATER procedure is written in SQL.

The ADD_THEATER procedure can be shared by multiple programs.

The ADD_THEATER procedure will be stored in the database as a schema object.

The ADD_THEATER procedure will execute with the privileges of its owner, by default.

The ADD_THEATER procedure has three parts: the specification, the body, and the exception handler.

The given answer is correct because the procedure "add_theater" can be shared by multiple programs, meaning that different programs can call and use this procedure for inserting data into the theater table. Additionally, the procedure will be stored in the database as a schema object, which means it can be accessed and executed by any user with the necessary privileges. By default, the procedure will execute with the privileges of its owner, ensuring that the necessary permissions are granted for the insertion operation.

Explanation

The given answer is correct because the procedure "add_theater" can be shared by multiple programs, meaning that different programs can call and use this procedure for inserting data into the theater table. Additionally, the procedure will be stored in the database as a schema object, which means it can be accessed and executed by any user with the necessary privileges. By default, the procedure will execute with the privileges of its owner, ensuring that the necessary permissions are granted for the insertion operation.

Submit

22. When a local procedure that references a remote procedure is compiled, what is recorded in the p-code or object code?

Both timestamps of the local and remote procedures

The timestamp of the local procedure compilation only

The timestamp of the remote procedure compilation only

No timestamps, unless a reference is made to PRAGMA RESTRICT_REFERENCES

When a local procedure that references a remote procedure is compiled, both timestamps of the local and remote procedures are recorded in the p-code or object code. This allows for tracking and synchronization of the compilation times of both procedures.

Explanation

When a local procedure that references a remote procedure is compiled, both timestamps of the local and remote procedures are recorded in the p-code or object code. This allows for tracking and synchronization of the compilation times of both procedures.

23. Examine this procedure: CREATE OR REPLACE PROCEDURE calculate_budget IS v_budget studio.yearly_budget%TYPE; BEGIN v_budget := get_budget@proddb(11); IF v_budget < 30000000 THEN set_budget@proddb (11, 300000000); END IF; END; The dependency mode is set to TIMESTAMP. The local procedure, CALCULATE_BUDGET, was compiled yesterday at 8:00 a.m., after compiling the GET_BUDGET remote function. The GET_BUDGET remote function was recompiled at 4:00 p.m. today. What can be said about the subsequent executions of CALCULATE_BUDGET?

Examine this procedure:CREATE OR REPLACE PROCEDURE calculate_budgetISv_budget studio.yearly_budget%TYPE;BEGINv_budget := get_budget@proddb(11);IF v_budget < 30000000 THENset_budget@proddb (11, 300000000);END IF;END;The dependency mode is set to TIMESTAMP.The local procedure, CALCULATE_BUDGET, was compiled yesterday at 8:00 a.m., after compiling theGET_BUDGET remote function. The GET_BUDGET remote function was recompiled at 4:00 p.m. today.What can be said about the subsequent executions of CALCULATE_BUDGET?

The first execution attempt will result in a recompilation and, if successful, will re-execute.

Unless the formal arguments of GET_BUDGET have changed, CALCULATE_BUDGET will executewithout recompilation.

The first execution attempt will result in a runtime error. The second execution attempt will result in arecompilation and, if successful, will re-execute.

The subsequent executions of CALCULATE_BUDGET will result in a runtime error on the first attempt due to the recompilation of the GET_BUDGET remote function. This is because the local procedure depends on the remote function, and when the remote function is recompiled, the local procedure needs to be recompiled as well. However, on the second execution attempt, if the recompilation is successful, CALCULATE_BUDGET will be re-executed.

Explanation

The subsequent executions of CALCULATE_BUDGET will result in a runtime error on the first attempt due to the recompilation of the GET_BUDGET remote function. This is because the local procedure depends on the remote function, and when the remote function is recompiled, the local procedure needs to be recompiled as well. However, on the second execution attempt, if the recompilation is successful, CALCULATE_BUDGET will be re-executed.

24. Which statement about a dependent procedure is true?

A dependent procedure only directly references a view, sequence, procedure, or packaged procedure orfunction.

A dependent procedure only indirectly references a view, sequence, procedure, or packaged procedureor function.

A dependent procedure directly or indirectly references a view, sequence, procedure, or packagedprocedure or function.

A dependent procedure neither directly nor indirectly references a view, sequence, procedure, orpackaged procedure or function.

A dependent procedure directly or indirectly references a view, sequence, procedure, or packaged procedure or function. This means that the procedure can directly call or use these database objects, or it can indirectly reference them through other procedures or functions that it calls. In either case, the dependent procedure relies on these objects for its functionality.

Explanation

A dependent procedure directly or indirectly references a view, sequence, procedure, or packaged procedure or function. This means that the procedure can directly call or use these database objects, or it can indirectly reference them through other procedures or functions that it calls. In either case, the dependent procedure relies on these objects for its functionality.

25. Examine this package: 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; You are considering adding a call to the stand-alone function, SET_BUDGET. If you make this change, what should you be concerned with if the code of SET_BUDGET is modified in the future?

The entire package body will be invalidated and will recompile upon the next execution.

The entire package body will be invalidated and must be manually compiled before the next execution.

The entire package specification will be invalidated and will recompile upon the next execution.

The entire package specification will be invalidated and must be manually compiled before the nextexecution.

If the code of the SET_BUDGET function is modified in the future, it will invalidate the entire package body. This means that the package body will need to be recompiled upon the next execution. The package body contains the implementation details of the package, so any changes to the functions or procedures within the package can cause the package body to become invalid.

Explanation

If the code of the SET_BUDGET function is modified in the future, it will invalidate the entire package body. This means that the package body will need to be recompiled upon the next execution. The package body contains the implementation details of the package, so any changes to the functions or procedures within the package can cause the package body to become invalid.

26. Examine this procedure: CREATE OR REPLACE PROCEDURE calculate_budget IS v_budget studio.yearly_budget%TYPE; v_studio_rec studio%ROWTYPE; BEGIN SELECT * INTO v_studio_rec FROM studio WHERE NAME = 'Vince Productions'; v_budget := get_budget (v_studio_rec.id); IF v_budget < 30000000 THEN set_budget (11, 300000000); END IF; END; Which effect could you expect if a new column were added to the STUDIO table?

Since the code references %ROWTYPE, no affect will be noticed.

The CALCULATE_BUDGET procedure would be marked valid and would automatically compilesuccessfully upon the next execution.

The CALCULATE_BUDGET procedure would be marked invalid and would automatically compilesuccessfully upon the next execution.

The CALCULATE_BUDGET procedure would be marked invalid and would automatically compileunsuccessfully upon the next execution.

If a new column were added to the STUDIO table, the CALCULATE_BUDGET procedure would be marked invalid. However, it would automatically compile successfully upon the next execution. This is because the procedure references %ROWTYPE, which means it selects all columns from the STUDIO table. When a new column is added, the procedure would need to be recompiled to include the new column in its select statement.

Explanation

If a new column were added to the STUDIO table, the CALCULATE_BUDGET procedure would be marked invalid. However, it would automatically compile successfully upon the next execution. This is because the procedure references %ROWTYPE, which means it selects all columns from the STUDIO table. When a new column is added, the procedure would need to be recompiled to include the new column in its select statement.

27. Which two views, when created, are queried to display indirect dependencies? (Choose two.)

DEPTREE

IDEPTREE

USER_IDTREE

USER_OBJECTS

USER_DEPENDENCIES

The DEPTREE and IDEPTREE views are queried to display indirect dependencies. The DEPTREE view shows the dependencies between objects in a database, while the IDEPTREE view shows the dependencies between objects within a single schema. By querying these views, users can understand the relationships and dependencies between different objects, helping them to analyze and troubleshoot any potential issues or conflicts in the database.

Explanation

The DEPTREE and IDEPTREE views are queried to display indirect dependencies. The DEPTREE view shows the dependencies between objects in a database, while the IDEPTREE view shows the dependencies between objects within a single schema. By querying these views, users can understand the relationships and dependencies between different objects, helping them to analyze and troubleshoot any potential issues or conflicts in the database.

Submit

28. Which two statements about dependent objects are true?

A procedure that updates a table is a dependent object.

A procedure that calls another procedure is a dependent object.

A table that is modified by a trigger is a dependent object.

A function that is called by another function is a dependent object.

A view that is queried by a procedure is a dependent object.

The given answer is correct. A procedure that updates a table is considered a dependent object because it relies on the table for its functionality. Similarly, a procedure that calls another procedure is also considered a dependent object as it relies on the called procedure. The other statements are not true in the context of dependent objects.

Explanation

The given answer is correct. A procedure that updates a table is considered a dependent object because it relies on the table for its functionality. Similarly, a procedure that calls another procedure is also considered a dependent object as it relies on the called procedure. The other statements are not true in the context of dependent objects.

Submit

29. Which three tables or views could you use to help track dependencies? (Choose three.)

DEPTREE

IDEPTREE

DEPENDENCIES

USER_OBJECTS

USER_DEPENDENCIES

The three tables or views that could be used to help track dependencies are DEPTREE, IDEPTREE, and USER_DEPENDENCIES. DEPTREE and IDEPTREE are likely to be specific tables or views designed for tracking dependencies. USER_DEPENDENCIES is a system view that provides information about dependencies between objects owned by the current user.

Explanation

The three tables or views that could be used to help track dependencies are DEPTREE, IDEPTREE, and USER_DEPENDENCIES. DEPTREE and IDEPTREE are likely to be specific tables or views designed for tracking dependencies. USER_DEPENDENCIES is a system view that provides information about dependencies between objects owned by the current user.

Submit

30. Which statement will create the dependency structure of the DEPARTMENT table in the PROD schema?

EXECUTE deptree_fill ('PROD.DEPARTMENT')

EXECUTE deptree_fill ('PROD', 'DEPARTMENT')

EXECUTE deptree_fill ('TABLE', 'PROD.DEPARTMENT')

EXECUTE deptree_fill ('TABLE', 'PROD', 'DEPARTMENT')

The correct answer is "EXECUTE deptree_fill ('TABLE', 'PROD', 'DEPARTMENT')". This statement will create the dependency structure of the DEPARTMENT table in the PROD schema. The 'TABLE' parameter specifies that the dependency structure should be created for a table. The 'PROD' parameter specifies the schema name and the 'DEPARTMENT' parameter specifies the table name.

Explanation

The correct answer is "EXECUTE deptree_fill ('TABLE', 'PROD', 'DEPARTMENT')". This statement will create the dependency structure of the DEPARTMENT table in the PROD schema. The 'TABLE' parameter specifies that the dependency structure should be created for a table. The 'PROD' parameter specifies the schema name and the 'DEPARTMENT' parameter specifies the table name.