Oracle Certification Exam Quiz: MCQ!

75 Questions | By Sunil.aketi | Last updated: Feb 25, 2021 | Total Attempts: 1204

Questions

Settings

or Create Online Exam

What do you know about the Oracle Certification Exam? Would you like to put your knowledge to the test? The Oracle Certification Program certifies candidates on skills and experience pertaining to Oracle manufactured goods and technologies. Credentials are presented based on a combination of passing exams, training, and performance-based assignments, depending upon the level of certification. If you intend to learn more about the Oracle Certification Exam, look no further than this quiz.

Questions and Answers

1.

CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END get_budget; END theater_pck; Which statement about the CURRENT_AVG_COST_PER_TICKET variable is true?
- A.
  
  It can only be referenced from outside the package
- B.
  
  It can be referenced from outside and within the package.
- C.
  
  It can be referenced by all constructs within the package.
- D.
  
  It must be moved to the specification to compile successfully
2.

While creating a package, you placed the function name in the specification and the body. Which type of construct have you created?
- A.
  
  PUBLIC
- B.
  
  Illegal
- C.
  
  Private
- D.
  
  One-time only
3.

Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END get_budget; END theater_pck;Which statement about the FIND_SEATS_SOLD procedure is true?
- A.
  
  It can be referenced within a SQL statement.
- B.
  
  It can only be referenced from within the package.
- C.
  
  It can be referenced from within and outside of the package.
- D.
  
  It cannot also be specified in the specification because it is specified in the body.
4.

Examine this package specification: CREATE OR REPLACE PACKAGE theater_package IS PROCEDURE find_cpt (v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER); PROCEDURE update_theater (v_name IN VARCHAR2); PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); PROCEDURE add_theater; END theater_package; Which statement about the procedures in this specification is true?
- A.
  
  They are public procedures
- B.
  
  They are private procedures
- C.
  
  Each procedure is missing code and, therefore, is illegal.
- D.
  
  Each procedure contains an argument list and, therefore, is illegal.
5.

CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END get_budget; END theater_pck; Which will successfully invoke the FIND_SEATS_SOLD procedure within SQL*Plus?
- A.
  
  EXECUTE find_seats_sold (500,11);
- B.
  
  Theater_pck.find_seats_sold (500,11);
- C.
  
  EXECUTE theater_pck.find_seats_sold (500,11);
- D.
  
  SELECT find_seats_sold(movie_id, theater_id) FROM gross_receipt;
6.

CREATE OR REPLACE PACKAGE theater_pck IS v_total_budget NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END get_budget; END theater_pck; Which statement about the V_TOTAL_BUDGET variable is true?
- A.
  
  It must also be declared in the body.
- B.
  
  It can only be referenced from inside the package
- C.
  
  It can only be referenced from outside the package.
- D.
  
  It can be referenced from inside and outside the package.
7.

CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END get_budget; BEGIN current_avg_cost_per_ticket := 8.50; END theater_pck; Which statement about the value of CURRENT_AVG_COST_PER_TICKET is true?
- A.
  
  It is 0 until explicitly referenced.
- B.
  
  It is assigned 8.50 each time the package is referenced.
- C.
  
  It is assigned 8.50 only when it is explicitly referenced.
- D.
  
  It is assigned 8.50 when the package is first invoked within a session.
8.

Examine this function: CREATE OR REPLACE FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END; This function is owned by the account, PROD. The user, JSMITH, must execute this function. Which GRANT command(s) should be issued?
- A.
  
  GRANT EXECUTE ON get_budget TO jsmith;
- B.
  
  GRANT EXECUTE, SELECT ON studio TO jsmith;
- C.
  
  GRANT EXECUTE, SELECT ON get_budget TO jsmith;
- D.
  
  GRANT SELECT ON studio TO jsmith; GRANT EXECUTE ON get_budget TO jsmith;
9.

Evaluate this statement: DROP PACKAGE dept_pack; Which statement is true?
- A.
  
  This statement removes only the package body.
- B.
  
  The statement removes only the package specification.
- C.
  
  The statement removes the package specification and the package body.
- D.
  
  This statement contains a syntax error.
10.

Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END get_budget; END theater_pck; Which statement about the GET_BUDGET function is true?
- A.
  
  It can be referenced within a SQL statement.
- B.
  
  It can be referenced from outside of the package.
- C.
  
  It can only be referenced from within the package.
- D.
  
  It must also be specified in the specification because it is specified in the body.
11.

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

Which command must you issue in SQL*Plus to display the result of the DBMS_OUTPUT package?
- A.
  
  SET ECHO ON
- B.
  
  SET OUTPUT ON
- C.
  
  SET FEEDBACK ON
- D.
  
  SET SERVEROUTPUT ON
13.

Which statement about packages is true?
- A.
  
  A package can be called.
- B.
  
  A package can be nested.
- C.
  
  A package can be parameterized.
- D.
  
  Package contents can be shared by multiple applications.
14.

Which statement about packages is true?
- A.
  
  Package constructs stored in the database are public.
- B.
  
  Package constructs defined within the package specification are private.
- C.
  
  Private and public package constructs are both declared and defined within the package body.
- D.
  
  A private package construct can only be referenced only by other constructs within the same package.
15.

You decide to use packages to logically group related programming constructs. Which two types of constructs can be grouped within a package? (Choose two.)
- A.
  
  View
- B.
  
  Cursor
- C.
  
  Variable
- D.
  
  Database trigger
- E.
  
  Application trigger
16.

Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END get_budget; END theater_pck; Which code will successfully assign a value to the CURRENT_AVG_COST_PER_TICKET variable within SQL*Plus?
- A.
  
  BEGIN current_avg_cost_per_ticket := 6.75; END;
- B.
  
  BEGIN theater_pck.current_avg_cost_per_ticket := 6.75; END;
- C.
  
  BEGIN SELECT AVG(cost_per_ticket) INTO current_avg_cost_per_ticket FROM gross_receipt; END;
- D.
  
  This variable is private to the package and cannot be directly assigned a value within SQL*Plus.
17.

Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END get_budget; END theater_pck; Which code will successfully invoke the GET_BUDGET function within SQL*Plus?
- A.
  
  VARIABLE g_budget_amount NUMBER EXECUTE :g_budget_amount := get_budget(11);
- B.
  
  VARIABLE g_budget_amount NUMBER EXECUTE theater_pck.get_budget(g_budget_amount, 11);
- C.
  
  VARIABLE g_budget_amount NUMBER EXECUTE :g_budget_amount := theater_pck.get_budget(11);
- D.
  
  This function cannot be referenced from outside the package.
18.

Examine this code: CREATE OR REPLACE PACKAGE prod_pack IS g_tax_rate NUMBER := .08; END prod_pack; Which statement about this code is true?
- A.
  
  This package specification can exist without a body.
- B.
  
  This package body can exist without a specification.
- C.
  
  This package body cannot exist without a specification.
- D.
  
  This package specification cannot exist without a body.
19.

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

Examine this code: BEGIN theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year; END; For this code to be successful, what must be true?
- A.
  
  Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.
- B.
  
  Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.
- C.
  
  Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist only in the body of the THEATER_PCK package.
- D.
  
  Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function must exist in the specification of the THEATER_PCK package.
21.

Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END get_budget; END theater_pck; Which type of variable is CURRENT_AVG_COST_PER_TICKET?
- A.
  
  Public
- B.
  
  Private
- C.
  
  Invalid
- D.
  
  Constant
22.

Procedures and functions are explicitly executed. This is different from a database trigger. When is a database trigger executed?
- A.
  
  When the transaction is committed
- B.
  
  During a data manipulation statement
- C.
  
  When it is explicitly invoked by another construct
- D.
  
  When an Oracle supplied package references the trigger
23.

Examine this trigger: CREATE OR REPLACE TRIGGER audit_gross_receipt AFTER DELETE OR UPDATE OF seats_sold, cost_per_ticket ON gross_receipt BEGIN ... END; How many times will the trigger body execute upon invocation?
- A.
  
  Once
- B.
  
  Twice
- C.
  
  Once for each row deleted or updated
- D.
  
  Once for each row deleted or seats_sold_cost_per_ticket updated
24.

When a database trigger routine does not have to take place before the triggering event, which timing should you assign to the trigger?
- A.
  
  On
- B.
  
  Off
- C.
  
  After
- D.
  
  Before
25.

Modifications to the THEATER table are not allowed during the last week in December. When creating a database trigger to enforce this rule, which timing will you use to be most efficient?
- A.
  
  After
- B.
  
  Weekly
- C.
  
  Before
- D.
  
  Monthly