Oracle Certified Associate Exam: Quiz!

30 Questions | Total Attempts: 1362

SettingsSettingsSettings
OCA Exam Quizzes & Trivia

Questions and Answers
  • 1. 
    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.)
    • A. 

      The ADD_THEATER procedure is written in SQL.

    • B. 

      The ADD_THEATER procedure can be shared by multiple programs.

    • C. 

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

    • D. 

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

    • E. 

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

  • 2. 
    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?
    • A. 

      1

    • B. 

      2

    • C. 

      5

    • D. 

      6

    • E. 

      8

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

      Server only

    • B. 

      System global area

    • C. 

      Client applications only

    • D. 

      Server and client applications

  • 4. 
    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?
    • A. 

      The SUBPROGRAM keyword does not exist.

    • B. 

      CALC_COMM must be invoked using the EXECUTE command.

    • C. 

      CALC_COMM must be declared after all local variable declarations.

    • D. 

      CALC_COMM must be declared before all local variable declarations.

  • 5. 
    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?
    • A. 

      RUN find_seats_sold (v_theater_id => 500);

    • B. 

      EXECUTE find_seats_sold (v_movie_id => 34);

    • C. 

      EXECUTE find_seats_sold (v_theater_id => 500);

    • D. 

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

  • 6. 
    Which statement about declaring arguments for procedures is true?
    • A. 

      Precision must be specified for VARCHAR2 arguments.

    • B. 

      Each declared argument must have a mode specified.

    • C. 

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

    • D. 

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

  • 7. 
    What should be placed after the IS keyword when creating a procedure?
    • A. 

      Parameters

    • B. 

      Local variables only

    • C. 

      Global variables only

    • D. 

      Global and local variables

  • 8. 
    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?
    • A. 

      The statement compiles, and the procedure is created.

    • B. 

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

    • C. 

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

    • D. 

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

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

      IN parameters must be initialized with a default value.

    • B. 

      OUT parameters must be initialized with a default value.

    • C. 

      IN parameters cannot be initialized with a default value.

    • D. 

      IN OUT parameters cannot be initialized with a default value.

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

      Both timestamps of the local and remote procedures

    • B. 

      The timestamp of the local procedure compilation only

    • C. 

      The timestamp of the remote procedure compilation only

    • D. 

      No timestamps, unless a reference is made to PRAGMA RESTRICT_REFERENCES

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

      Only direct dependent objects are affected.

    • B. 

      Only indirect dependent objects are affected.

    • C. 

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

    • D. 

      All direct and indirect dependent objects are affected.

  • 12. 
    Which two statements about dependent objects are true? 
    • A. 

      A procedure that updates a table is a dependent object.

    • B. 

      A procedure that calls another procedure is a dependent object.

    • C. 

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

    • D. 

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

    • E. 

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

  • 13. 
    Examine this procedure: CREATE OR REPLACE PROCEDURE calculate_budget IS v_budget studio.yearly_budget%TYPE; BEGIN v_budget := [email protected](11); IF v_budget < 30000000 THEN [email protected] (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?
    • A. 

      Examine this procedure: CREATE OR REPLACE PROCEDURE calculate_budget IS v_budget studio.yearly_budget%TYPE; BEGIN v_budget := [email protected](11); IF v_budget < 30000000 THEN [email protected] (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?

    • B. 

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

    • C. 

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

    • D. 

      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.

  • 14. 
    How does a local dependent object differ from a remote dependent object?
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 15. 
    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?
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

  • 16. 
    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?
    • A. 

      Never, but may be manually recompiled

    • B. 

      The next time the SET_BUDGET procedure is analyzed

    • C. 

      The second time the GET_BUDGET function is executed

    • D. 

      The next time the CALCULATE_BUDGET procedure is executed

  • 17. 
    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?
    • A. 

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

    • B. 

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

    • C. 

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

    • D. 

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

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

      A direct dependency between SET_BUDGET and STUDIO only

    • B. 

      A direct dependency between SET_BUDGET and CALCULATE_BUDGET only

    • C. 

      A direct dependency between SET_BUDGET, CALCULATE_BUDGET, and STUDIO

    • D. 

      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

  • 19. 
    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?
    • A. 

      Only SET_BUDGET is marked invalid.

    • B. 

      Only GET_BUDGET and SET_BUDGET are marked invalid.

    • C. 

      Only CALCULATE_BUDGET and SET_BUDGET are marked invalid.

    • D. 

      All three are marked invalid.

  • 20. 
    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?
    • A. 

      Define V_SEATS_SOLD as GROSS_RECEIPT.SEATS_SOLD%TYPE.

    • B. 

      Define V_SEATS_SOLD using the PRAGMA EXCEPTION_INIT compiler directive.

    • C. 

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

    • D. 

      Define V_SEATS_SOLD using the PRAGMA RESTRICT_REFERENCES compiler directive.

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

      A change to the code of SET_BUDGET

    • B. 

      A change to the code of GET_BUDGET

    • C. 

      A change to the arguments of SET_BUDGET or GET_BUDGET

    • D. 

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

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

      USER_OBJECTS

    • B. 

      USER_DEPTREE

    • C. 

      USER_PACKAGES

    • D. 

      USER_DEPENDENCIES

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

      DEPTREE

    • B. 

      IDEPTREE

    • C. 

      DEPENDENCIES

    • D. 

      USER_OBJECTS

    • E. 

      USER_DEPENDENCIES

  • 24. 
    Which statement will create the dependency structure of the DEPARTMENT table in the PROD schema?
    • A. 

      EXECUTE deptree_fill ('PROD.DEPARTMENT')

    • B. 

      EXECUTE deptree_fill ('PROD', 'DEPARTMENT')

    • C. 

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

    • D. 

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

    • E. 

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

  • 25. 
    Examine this procedure: CREATE OR REPLACE PROCEDURE calculate_budget IS v_budget studio.yearly_budget%TYPE; BEGIN v_budget := [email protected](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?
    • A. 

      It will be marked as invalid

    • B. 

      It will be recompiled at the next execution.

    • C. 

      It will be recompiled at the second execution.

    • D. 

      There is no effect.

Related Topics
Back to Top Back to top