Oracle Certification Exam Quiz: MCQ!

It can only be referenced from outside the package

It can be referenced from outside and within the package.

It can be referenced by all constructs within the package.

It must be moved to the specification to compile successfully

PUBLIC

Illegal

Private

One-time only

It can be referenced within a SQL statement.

It can only be referenced from within the package.

It can be referenced from within and outside of the package.

It cannot also be specified in the specification because it is specified in the body.

They are public procedures

They are private procedures

Each procedure is missing code and, therefore, is illegal.

Each procedure contains an argument list and, therefore, is illegal.

EXECUTE find_seats_sold (500,11);

Theater_pck.find_seats_sold (500,11);

EXECUTE theater_pck.find_seats_sold (500,11);

SELECT find_seats_sold(movie_id, theater_id) FROM gross_receipt;

It must also be declared in the body.

It can only be referenced from inside the package

It can only be referenced from outside the package.

It can be referenced from inside and outside the package.

It is 0 until explicitly referenced.

It is assigned 8.50 each time the package is referenced.

It is assigned 8.50 only when it is explicitly referenced.

It is assigned 8.50 when the package is first invoked within a session.

GRANT EXECUTE ON get_budget TO jsmith;

GRANT EXECUTE, SELECT ON studio TO jsmith;

GRANT EXECUTE, SELECT ON get_budget TO jsmith;

GRANT SELECT ON studio TO jsmith; GRANT EXECUTE ON get_budget TO jsmith;

This statement removes only the package body.

The statement removes only the package specification.

The statement removes the package specification and the package body.

This statement contains a syntax error.

It can be referenced within a SQL statement.

It can be referenced from outside of the package.

It can only be referenced from within the package.

It must also be specified in the specification because it is specified in the body.

OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));

DBMS_DEBUG.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));

DBMS_OUTPUT.DISPLAY(TO_CHAR(SQL%ROWCOUNT));

DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));

SET ECHO ON

SET OUTPUT ON

SET FEEDBACK ON

SET SERVEROUTPUT ON

A package can be called.

A package can be nested.

A package can be parameterized.

Package contents can be shared by multiple applications.

Package constructs stored in the database are public.

Package constructs defined within the package specification are private.

Private and public package constructs are both declared and defined within the package body.

A private package construct can only be referenced only by other constructs within the same package.

BEGIN current_avg_cost_per_ticket := 6.75; END;

BEGIN theater_pck.current_avg_cost_per_ticket := 6.75; END;

BEGIN SELECT AVG(cost_per_ticket) INTO current_avg_cost_per_ticket FROM gross_receipt; END;

This variable is private to the package and cannot be directly assigned a value within SQL*Plus.

VARIABLE g_budget_amount NUMBER EXECUTE :g_budget_amount := get_budget(11);

VARIABLE g_budget_amount NUMBER EXECUTE theater_pck.get_budget(g_budget_amount, 11);

VARIABLE g_budget_amount NUMBER EXECUTE :g_budget_amount := theater_pck.get_budget(11);

This function cannot be referenced from outside the package.

This package specification can exist without a body.

This package body can exist without a specification.

This package body cannot exist without a specification.

This package specification cannot exist without a body.

PROCEDURE find_cpt (v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER);

PUBLIC PROCEDURE find_cpt (v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER);

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;

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;

Only the GET_TOTAL_FOR_YEAR variable must exist in the specification of the THEATER_PCK package.

Only the V_TOTAL_SEATS_SOLD_OVERALL variable must exist in the specification of the THEATER_PCK package.

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.

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.

Public

Private

Invalid

Constant

When the transaction is committed

During a data manipulation statement

When it is explicitly invoked by another construct

When an Oracle supplied package references the trigger

Once

Twice

Once for each row deleted or updated

Once for each row deleted or seats_sold_cost_per_ticket updated

On

Off

After

Before

After

Weekly

Before

Monthly

STUDIO table update

YEARLY_BUDGET column update

STUDIO table insert, update, or delete

Any column update other than YEARLY_BUDGET

1

30

31

None

Row only

Statement only

Either statement or row

Neither statement or row

Row only

Statement only

Statement and row

Oracle Forms trigger

Change the timing to BEFORE.

Remove the AFTER INSERT ON clause.

Remove the SUM function; it is not allowed in database triggers.

Convert it to a statement level database trigger by removing FOR EACH ROW.

BEFORE (gross_receipt, seats_sold, cost_per_ticket) INSERT, UPDATE

BEFORE INSERT OR UPDATE OF seats_sold, cost_per_ticket ON gross_receipt

BEFORE INSERT OR UPDATE(seats_sold, cost_per_ticket) ON gross_receipt FOR EVERY ROW

BEFORE INSERT OR UPDATE OF seats_sold, cost_per_ticket ON gross_receipt FOR EACH ROW

DISABLE TRIGGERS ON gross_receipt;

DISABLE ALL TRIGGERS ON gross_receipt;

ALTER TRIGGER ALL DISABLE ON gross_receipt;

ALTER TABLE gross_receipt DISABLE ALL TRIGGERS;

ENABLE check_theater;

ENABLE TRIGGER check_theater;

ALTER TRIGGER check_theater ENABLE;

ALTER TABLE check_theater ENABLE check_theater;

DROP audit_theater;

DROP TRIGGER audit_theater;

ALTER TRIGGER audit_theater DROP;

ALTER TABLE theater DROP TRIGGER audit_theater;

ALTER TRIGGER check_theater DROP;

MODIFY TRIGGER check_theater DROP;

ALTER TRIGGER check_theater DISABLE;

MODIFY TRIGGER check_theater DISABLE;

DBMS_SQL

DBMS_TRIGGER

DBMS_CHECK_MANIPULATION_TYPE

DELETING, UPDATING, and INSERTING

BEFORE (gross_receipt) DELETE, INSERT, UPDATE

AFTER DELETE OR INSERT OR UPDATE ON gross_receipt

BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt

BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt FOR EACH ROW

DBMS_SQL

DBMS_TRIGGER

DBMS_CHECK_MANIPULATION_TYPE

DELETING, UPDATING, and INSERTING

SELECT id, set_budget(11,500000000) FROM studio;

DECLARE v_updated_flag BOOLEAN; BEGIN set_budget(11,500000000); END;

VARIABLE g_updated_flag BOOLEAN BEGIN g_updated_flag := set_budget(11,500000000); END;

DECLARE v_updated_flag BOOLEAN; BEGIN v_updated_flag := set_budget(11,500000000); END;

VARIABLE g_yearly_budget NUMBER :g_yearly_budget := GET_BUDGET(11);

VARIABLE g_yearly_budget NUMBER EXECUTE g_yearly_budget := GET_BUDGET(11);

VARIABLE g_yearly_budget NUMBER EXECUTE :g_yearly_budget := GET_BUDGET(11);

VARIABLE :g_yearly_budget NUMBER EXECUTE :g_yearly_budget := GET_BUDGET(11);

SELECT id, name, get_budget(id,200) FROM studio;

VARIABLE g_yearly_budget NUMBER EXECUTE g_yearly_budget := get_budget(11, 4000000000);

VARIABLE g_yearly_budget NUMBER RUN :g_yearly_budget := get_budget(v_studio_id => 11,v_max_budget => 4000000000);

VARIABLE g_yearly_budget NUMBER RUN :g_yearly_budget := get_budget(v_studio_id => 11,v_max_budget => 4000000000);

Restricting pragma references

Passing values to the function

Bypassing directives to the compiler

Prompting the end user for information

Executing the stored function within an INSERT statement

Executing the stored function within a server-side function

Executing the stored function within a client-side function

Executing the stored function within a CHECK constraint of a table

By limiting changes to logic to one location

By eliminating the need for an executable section

By eliminating the need for the storage of object code

By ignoring side effects when invoked from within a SQL statement

Executing the stored function within an INSERT statement

Executing the stored function within a server-side function

Executing the stored function within a client-side function

Executing the stored function within the DEFAULT clause of the CREATE TABLE statement

It must be remote.

It must not modify the database.

It must also exist as a stand-alone function.

It must only exist in the body of THEATER_PCK.

Remove the first RETURN statement.

Change "RETURN number" to "OUTPUT number".

Add "(v_studio_id IN NUMBER)" right after the IS keyword.

Add "(v_studio_id IN NUMBER)" right before the RETURN statement of the header.

CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER) RETURN number 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; END;

CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER) 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; RETURN (v_total * .20); END;

CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER) IS RETURN number 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; RETURN (v_total * .20); END;

CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER) RETURN number 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; RETURN (v_total * .20); END;

EXECUTE calc_comm;

SELECT * FROM calc_comm(emp);

EXECUTE calc_comm(SELECT total FROM ord);

SELECT * FROM ord GROUP BY ordid HAVING calc_comm(total) > 5000;

Function

Procedure

Public packaged procedure

Private packaged procedure

Private packaged function