Oracle Certified Associate Practice Quiz!

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Sunil.aketi
S
Sunil.aketi
Community Contributor
Quizzes Created: 2 | Total Attempts: 3,950
| Attempts: 1,768
SettingsSettings
Please wait...
  • 1/30 Questions

    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
Please wait...
About This Quiz

Do you want to become an Oracle Certified Associate (OCA)? Want to check how good is your practice level? The quiz below will help you to measure your competency level. So, what are you waiting for? Take this practice test and evaluate how strong your understanding of Oracle technologies is. The Oracle Certification Program provides professional certification to an individual who has successfully passed all the tests offered by Oracle. Play this quiz, and your scores will tell you if you are ready to appear in this test or if you still need to work hard.

Oracle Certified Associate Practice Quiz! - Quiz

Quiz Preview

  • 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.

    Correct Answer
    A. Define V_SEATS_SOLD as GROSS_RECEIPT.SEATS_SOLD%TYPE.
    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.

    Rate this question:

  • 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);

    Correct Answer
    A. EXECUTE find_seats_sold (v_theater_id => 500);
    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".

    Rate this question:

  • 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

    Correct Answer
    A. All object types
    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.

    Rate this question:

  • 5. 

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

    • Parameters

    • Local variables only

    • Global variables only

    • Global and local variables

    Correct Answer
    A. Local variables only
    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.

    Rate this question:

  • 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.

    Correct Answer
    A. 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.

    Rate this question:

  • 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

    Correct Answer
    A. A change to the arguments of SET_BUDGET or GET_BUDGET
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Server only
    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.

    Rate this question:

  • 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.

    Correct Answer
    A. The statement compiles, 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.

    Rate this question:

  • 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.

    Correct Answer
    A. Formal arguments allow you to transfer values to and from the calling environment.
    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.

    Rate this question:

  • 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

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

    Rate this question:

  • 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.

    Correct Answer
    A. Local dependent objects are on the same node in the same database.
    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.

    Rate this question:

  • 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

    Correct Answer
    A. USER_DEPENDENCIES
    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.

    Rate this question:

  • 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.

    Correct Answer
    A. There is no effect.
    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.

    Rate this question:

  • 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 dependency between SET_BUDGET and STUDIO, and an indirect dependency between CALCULATE_BUDGET and STUDIO

    Correct Answer
    A. 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
    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.

    Rate this question:

  • 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.

    Correct Answer
    A. CALC_COMM must be declared after all local variable declarations.
    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.

    Rate this question:

  • 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.

    Correct Answer
    A. Only CALCULATE_BUDGET and SET_BUDGET are marked invalid.
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Displays all the direct dependencies on the EMPLOYEE table
    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.

    Rate this question:

  • 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.

    Correct Answer
    A. All direct and indirect dependent objects are affected.
    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.

    Rate this question:

  • 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

    Correct Answer
    A. The next time the CALCULATE_BUDGET procedure is executed
    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.

    Rate this question:

  • 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.

    Correct Answer(s)
    A. The ADD_THEATER procedure can be shared by multiple programs.
    A. The ADD_THEATER procedure will be stored in the database as a schema object.
    A. The ADD_THEATER procedure will execute with the privileges of its owner, by default.
    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.

    Rate this question:

  • 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

    Correct Answer
    A. Both timestamps of the local and remote 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.

    Rate this question:

  • 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_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?

    • 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 execute without recompilation.

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

    Correct Answer
    A. The first execution attempt will result in a runtime error. The second execution attempt will result in a recompilation and, if successful, will re-execute.
    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.

    Rate this question:

  • 24. 

    Which statement about a dependent procedure is true?

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

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

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

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

    Correct Answer
    A. A dependent procedure directly or indirectly references a view, sequence, procedure, or packaged procedure or function.
    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.

    Rate this question:

  • 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 next execution.

    Correct Answer
    A. The entire package body will be invalidated and will recompile upon the next execution.
    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.

    Rate this question:

  • 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 compile successfully upon the next execution.

    • The CALCULATE_BUDGET procedure would be marked invalid and would automatically compile successfully upon the next execution.

    • The CALCULATE_BUDGET procedure would be marked invalid and would automatically compile unsuccessfully upon the next execution.

    Correct Answer
    A. The CALCULATE_BUDGET procedure would be marked invalid and would automatically compile successfully upon the next execution.
    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.

    Rate this question:

  • 27. 

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

    • DEPTREE

    • IDEPTREE

    • USER_IDTREE

    • USER_OBJECTS

    • USER_DEPENDENCIES

    Correct Answer(s)
    A. DEPTREE
    A. IDEPTREE
    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.

    Rate this question:

  • 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.

    Correct Answer(s)
    A. A procedure that updates a table is a dependent object.
    A. A procedure that calls another procedure is a dependent object.
    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.

    Rate this question:

  • 29. 

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

    • DEPTREE

    • IDEPTREE

    • DEPENDENCIES

    • USER_OBJECTS

    • USER_DEPENDENCIES

    Correct Answer(s)
    A. DEPTREE
    A. IDEPTREE
    A. USER_DEPENDENCIES
    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.

    Rate this question:

  • 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')

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

    Correct Answer
    A. EXECUTE deptree_fill ('TABLE', 'PROD', 'DEPARTMENT')
    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.

    Rate this question:

Quiz Review Timeline (Updated): Aug 21, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Aug 21, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Mar 24, 2009
    Quiz Created by
    Sunil.aketi
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.