Oracle Certified Associate Exam: Quiz!

30 Questions | By Sunil.aketi | Last updated: Mar 22, 2022 | Total Attempts: 1362

Questions

Settings

Create your own Quiz

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.