ORACLE - FINAL EXAM

Keterangan
You have created the update_emp package in your database and want to access the procedures and functions present inside the package<br />	A package cannot exist without a package specification
Examine the package:<br /><br />CREATE OR REPLACE PACKAGE theater_pck<br />IS<br />current_avg_cost_per_ticket NUMBER;<br /><br />PROCEDURE find_seats_sold<br />(v_movie_id IN NUMBER DEFAULT 34, v_theather_id IN NUMBER);<br /><br />FUNCTION get_budget<br />(v_studio_id IN NUMBER)<br />RETURN NUMBER<br /><br />END theather_pck;<br /><br />You issue this statement in SQL*Plus;<br /><br />EXECUTE theather_pck.current_avg_cost_per_ticket:=10;<br /><br />What is true about the state of CURRENT_AVG_COST_PER_TICKET?<br />	It is 10 for the duration of the current session
Examine the code of the following procedure:<br /><br />CREATE OR REPLACE PROCEDURE update_sal<br />(v_emp_id NUMBER, v_increment NUMBER, v_salt NUMBER)<br />IS<br />BEGIN<br />UPDATE emp<br />SET<br />salary = v_sal + v_increment<br />WHERE employee_id = v_emp_id;<br />END update_sal;<br /><br />Which statement is NOT a valid call to the update_sal procedure?<br />	update_sal(v_emp_id := 20, v_increment := 1200, v_sal := 20000);
You decide to use packages to logically group related programming constructs. Which two types of constructs can be grouped within a package? (Choose two)<br />	cursor
	variable
Examine this database trigger:<br /><br />CREATE OR REPLACE TRIGGER audit_gross_modification<br />AFTER INSERT OR DELETE ON gross_receipt<br />BEGIN<br />INSERT INTO audit_gross<br />VALUES (USER, SYSDATE):<br />END;<br /><br />To test this trigger, you delete 30 rows from the GROSS_RECEIPT table. How many rows are inserted into the AUDIT_GROSS table due to this event?<br />	1
Evaluate this statement:<br /><br />DROP PACKAGE dept_pack;<br /><br />Which statement is true?<br />	The statement removes the package specification and the package body
Which data type is an external large object (LOB) data type?	BFILE
Which two statements about dependent objects are true? (Choose two.)	A procedure that updates a table is a dependent object
All users in the HR_EMP role have UPDATE privileges on the EMPLOYEE table. You create the<br />UPDATE_EMPLOYEE procedure. HR_EMP users should only be able to update the EMPLOYEE table using<br />this procedure.<br /><br />Which two statements should you execute? (Choose two.)	A procedure that calls another procedure is a dependent object
	REVOKE UPDATE ON employee FROM hr_emp;
	GRANT EXECUTE ON update_employee TO hr_emp;
When creating a function in SQL*Plus, you receive an error message stating that the function created with<br />compilation errors.<br /><br />What should you do to see the compilation errors?	Issue the SHOW ERRORS command
Which Oracle supplied package manages LOBs?	DBMS_LOB
Which command must you issue in SQL*Plus to display the result of the DBMS_OUTPUT package?	SET SERVEROUTPUT ON
The UPDATE_EMPLOYEE procedure contains an algorithm that calculates an employee\'s commission<br />multiple times throughout the program. If a change is made to the algorithm, the change must be made<br />multiple times.<br /><br />How can this procedure be modified to simplify the code and reduce duplicated code?	Add a local subprogram containing the algorithm
You have a stored procedure named PAYROLL_CALC that references the remote view named<br />EMP_ACTIVE. You re-create the EMP_ACTIVE view omitting several of its columns.<br /><br />Which statement is true about the result of this action?	Recompilation of the PAYROLL_CALC procedure will be unsuccessful if it references one of the omittedcolumns
Examine this procedure:<br />CREATE OR REPLACE PROCEDURE update_employee<br />(v_emp_id IN NUMBER)<br />IS<br />v_comm NUMBER;<br /><br />PROCEDURE calc_comm<br />IS<br />v_total NUMBER;<br />BEGIN<br />SELECT SUM(ord.total)<br />INTO v_total<br />FROM ord,customer<br />WHERE ord.custid = customer.custid<br />AND customer.repid = v_emp_id;<br />v_comm := v_total * .20;<br />END calc_comm;<br /><br />v_percentage NUMBER;<br />BEGIN<br />SELECT percentage<br />INTO v_percentage<br />FROM daily_figures<br />WHERE TRUNC(figure_date) = TRUNC(SYSDATE);<br /><br />IF v_percentage &gt; 33 THEN<br />calc_comm;<br />END IF;<br /><br />END;<br /><br />Why does this code cause an error when compiled?	CALC_COMM must be declared after all local variable declarations
Evaluate this SELECT statement:<br /><br />SELECT *<br />FROM user_dependencies<br />WHERE referenced_name = \'EMPLOYEE\';<br /><br />The EMPLOYEE table is in your schema. Which result will this statement accomplish?	displays all the direct dependencies in your schema on the EMPLOYEE table
Examine the following code:<br /><br />CREATE OR REPLACE PACKAGE bank_acct<br />IS<br />current_int_rate NUMBER := 0;<br />PROCEDURE acct_details (account_no IN NUMBER);<br />FUNCTION acct_balance (account_no IN NUMBER)<br />RETURN NUMBER;<br />END;<br /><br />John executed the following statement in his session:<br /><br />SQL&gt; EXECUTE bank_acct.current_int_rate := 2.5;<br /><br />Which statement is true about the current_int_rate variable?	The value of the the current_int_rate variable, 2.5, will persist for John\'s current session
Which three statements about procedures are true? (Choose three.)	They promote reusability and maintainability
	They perform actions and can accept parameters
	They require at least one executable statement in the procedure body
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE update_theater<br />(v_name IN VARCHAR2)<br />IS<br />BEGIN<br />DELETE theater<br />WHERE id = 34;<br />END update_theater;<br /><br />This procedure is owned by PROD. The user JSMITH must execute this procedure.<br /><br />Which statement(s) must PROD execute to grant the necessary privileges to JSMITH?	GRANT EXECUTE ON update_theater TO jsmith;
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE FIND_ORDERS<br />(v_total IN sales_order.total%TYPE)<br />IS<br />CURSOR c1 IS SELECT order_id<br />FROM sales_order<br />WHERE total &gt; v_total;<br />BEGIN<br />FOR sales_order_rec in c1 LOOP<br />--process the row<br />END LOOP;<br />END;<br /><br />This procedure returns all orders with a total greater than an amount that is passed in the V_TOTAL<br />parameter. Occasionally, a user might want to process all orders regardless of the total amount. They could<br />do this by passing 0 in the V_TOTAL parameter, however, they would prefer not to pass anything.<br /><br />Which change can you make to the procedure to allow a user to process all orders in the SALES_ORDER<br />table without having to pass a 0 total amount?	Use (v_total IN sales_order.total%TYPE DEFAULT 0) as the parameter definition
Which statement about packages is true?	Package contents can be shared by multiple applications.
Which statement correctly identifies the differences between large objects (LOBS) and LONG and LONG<br />RAW data types?	A table containing a LOB data type can be replicated, but a table containing a LONG data type cannot bereplicated
Procedures and functions are very similar. For which reason would you choose a function over a procedure?	A function can be used in a SQL statement
When using a PL/SQL stored package, which statement about side effects is true?	Side effects are changes to database tables or public packaged variables declared in the packagespecification
Examine this package:<br /><br />CREATE OR REPLACE PACKAGE prod_pack<br />IS<br />CURSOR c1 is<br />SELECT *<br />FROM product;<br /><br />PROCEDURE order_product<br />(p1 IN NUMBER, p2 IN NUMBER);<br />END prod_pack;<br /><br />CREATE OR REPLACE PACKAGE BODY prod_pack<br />IS<br />CURSOR c2 IS<br />SELECT *<br />FROM ord;<br /><br />PROCEDURE order_product<br />(p1 IN NUMBER, p2 IN NUMBER)<br />IS<br />BEGIN<br />OPEN c2;<br />...<br />END;<br />END prod_pack;<br /><br />You execute these commands in SQL*Plus:<br /><br />EXECUTE OPEN prod_pack.c1;<br />EXECUTE prod_pack.order_product(100,1);<br /><br />Which cursor or cursors will be opened for the duration of the session, unless it is explicitly closed?	both C1 and C2
Examine this package specification and body:<br /><br />CREATE OR REPLACE PACKAGE theater_pck<br />IS<br />PROCEDURE find_seats_sold<br />(v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER);<br />END theater_pck;<br /><br />CREATE OR REPLACE PACKAGE BODY theater_pck<br />IS<br />current_avg_cost_per_ticket NUMBER;<br /><br />PROCEDURE find_seats_sold<br />(v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER)<br />IS<br />v_seats_sold gross_receipt.seats_sold%TYPE;<br />v_budget studio.yearly_budget%TYPE;<br />BEGIN<br />SELECT seats_sold<br />INTO v_seats_sold<br />FROM gross_receipt<br />WHERE movie_id = v_movie_id<br />AND theater_id = v_theater_id;<br />END find_seats_sold;<br /><br />FUNCTION get_budget<br />(v_studio_id IN NUMBER)<br />RETURN NUMBER<br />IS<br />v_yearly_budget NUMBER;<br />BEGIN<br />SELECT yearly_budget<br />INTO v_yearly_budget<br />FROM studio<br />WHERE id = v_studio_id;<br />RETURN v_yearly_budget;<br />END get_budget;<br /><br />END theater_pck;<br /><br />Which type of variable is CURRENT_AVG_COST_PER_TICKET?	private
Which two subprogram headers are correct? (Choose two.)	CREATE OR REPLACE PROCEDURE get_sal(v_sal IN NUMBER)IS
	CREATE OR REPLACE FUNCTION calc_comm(p_amnt IN NUMBER)RETURN NUMBER
Examine this code:<br /><br />CREATE OR REPLACE PROCEDURE find_seats_sold<br />(v_movie_id IN NUMBER)<br />IS<br />v_seats_sold NUMBER(3);<br />BEGIN<br />SELECT seats_sold<br />INTO v_seats_sold<br />FROM gross_receipt<br />WHERE movie_id = v_movie_id;<br />END;<br /><br />You are concerned with future changes to the database, such as modifying the maximum length of the<br />SEATS_SOLD column of the GROSS_RECEIPT table.<br /><br />Which change should you make to guarantee successful recompilation of this procedure in the event of such<br />a change?	Define V_SEATS_SOLD as GROSS_RECEIPT.SEATS_SOLD%TYPE
Examine this function:<br /><br />CREATE OR REPLACE FUNCTION get_budget<br />(v_studio_id IN NUMBER)<br />RETURN NUMBER<br />IS<br />v_yearly_budget NUMBER;<br />BEGIN<br />SELECT yearly_budget<br />INTO v_yearly_budget<br />FROM studio<br />WHERE id = v_studio_id;<br />END;<br /><br />To create this function successfully, what additional code must be added to the executable section?	RETURN v_yearly_budget;
Which three tables or views could you use to help track dependencies? (Choose three.)	DEPTREE
	IDEPTREE
	USER_DEPENDENCIES
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE calculate_budget<br />IS<br />v_budget studio.yearly_budget%TYPE;<br />BEGIN<br />v_budget := get_budget (11);<br />IF v_budget &lt; 30000000 THEN<br />set_budget (11, 300000000);<br />END IF;<br />END;<br /><br />If the GET_BUDGET function is currently marked as invalid, when will it be recompiled implicitly?	the next time the CALCULATE_BUDGET procedure is executed
Which statement is true regarding large object (LOB) data types?	The BFILE data type is an external LOB data type
When a database trigger routine does not have to take place before the triggering event, which timing should<br />you assign to the trigger?	AFTER
Examine this package specification:<br /><br />CREATE OR REPLACE PACKAGE theater_pck<br />IS<br />PROCEDURE find_seats_sold<br />(v_movie_id IN NUMBER, v_theater_id IN NUMBER);<br />END theater_pck;<br /><br />You want to take advantage of package overloading. Which procedure header fails the rules of overloading?	PROCEDURE find_seats_sold(v_movie_id IN BINARY_INTEGER, v_theater_id IN NUMBER);
Examine this package specification:<br /><br />CREATE OR REPLACE PACKAGE theater_package<br />IS<br />PROCEDURE find_cpt<br />(v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER);<br />PROCEDURE update_theater (v_name IN VARCHAR2);<br />PROCEDURE find_seats_sold<br />(v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER);<br />PROCEDURE add_theater;<br />END theater_package;<br /><br />Which statement about the procedures in this specification is true?	They are public procedures
Which view can you query to determine the validity of a particular procedure?	USER_OBJECTS
While creating a package, you placed the function name in the specification and the body. Which type of<br />construct have you created?	public
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE calculate_budget<br />IS<br />v_budget studio.yearly_budget%TYPE;<br />BEGIN<br />v_budget := get_budget@proddb(11);<br />IF v_budget &lt; 30000000 THEN<br />set_budget@proddb (11, 300000000);<br />END IF;<br />END;<br /><br />The dependency mode is set to TIMESTAMP.<br /><br />The local procedure, CALCULATE_BUDGET, was compiled yesterday at 8:00 a.m., after compiling the<br />GET_BUDGET remote function. The GET_BUDGET remote function was recompiled at 4:00 p.m. today.<br /><br />What can be said about the subsequent executions of CALCULATE_BUDGET?	The first execution attempt will result in a runtime error. The second execution attempt will result in arecompilation and, if successful, will re-execute.
You have created a procedure, A, which references a remote table, B. Structural changes to B, affect the<br />dependent procedure, A, making A invalid.<br /><br />Which actions will NOT enable you to prevent the problem? (Choose all that apply.)	creating a view on the remote table and accessing the view
	not modifying the remote table when the procedure is being used
	explicitly specifying the column names while selecting data from the remote table
Which two statements are true about temporary LOBs? (Choose two.)	The lifetime of a temporary LOB is a session
	Temporary LOBs are used to perform transformation on persistent LOB data
You have issued a SQL statement that fires the following triggers:<br /><br />1. AFTER row triggers<br />2. BEFORE row triggers<br />3. AFTER statement triggers<br />4. BEFORE statement triggers<br /><br />Which option represents the sequence in which these triggers will be fired?	4, 2, 1, 3
When creating the ADD_PROD procedure in SQL*Plus, you receive this message:<br /><br />Warning: Procedure created with compilation errors.<br /><br />What was saved to the data dictionary?	source code and compilation errors
Which type of LOB represents a multibyte character object?	NCLOB
Examine this function:<br /><br />CREATE OR REPLACE FUNCTION set_budget<br />(v_studio_id IN NUMBER, v_new_budget IN NUMBER)<br />RETURN NUMBER<br />IS<br />BEGIN<br />UPDATE studio<br />SET yearly_budget = v_new_budget<br />WHERE id = v_studio_id;<br />COMMIT;<br />RETURN SQL%ROWCOUNT;<br />END;<br /><br />This function is executed from within a procedure called CALCULATE_BUDGET. The database administrator<br />has just informed you that a new column has been added to the STUDIO table.<br /><br />What effect will this have?	SET_BUDGET and CALCULATE_BUDGET will be marked invalid.
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE find_seats_sold<br />(v_movie_id IN NUMBER)<br />IS<br />v_seats_sold gross_receipt.seats_sold%TYPE;<br />BEGIN<br />SELECT seats_sold<br />INTO v_seats_sold<br />FROM gross_receipt<br />WHERE movie_id = v_movie_id;<br />END;<br /><br />Which command will successfully invoke this procedure in SQL*Plus?	EXECUTE find_seats_sold (34);
Which type of construct should you create to solely perform an action without returning a value?	procedure
Which procedure of the DBMS_OUTPUT supplied package allows you to place messages in a buffer to be<br />displayed at a later time?	PUT
You have created an update_emp package as follows:<br /><br />CREATE OR REPLACE PACKAGE update_emp<br />sal_increase NUMBER := 2000;<br />FUNCTION inc_sal(emp_id IN NUMBER)<br />RETURN NUMBER;<br />END;<br /><br />CREATE OR REPLACE PACKAGE BODY update_emp<br />AS<br />FUNCTION inc_sal(emp_id)<br />RETURN NUMBER<br />IS<br />v_salary NUMBER;<br />BEGIN<br />UPDATE emp SET salary = salary + sal_increase<br />WHERE employee_id = emp_id;<br />END inc_sal;<br /><br />Several users are concurrently using this package, and the user ADAM issues the following statement:<br /><br />EXECUTE update_emp.sal_increase = 5000;<br /><br />Which statement is true about the sal_increase variable?	The sal_increase variable will have the value 5000 for ADAM\'s current session.
When a change is made to a referenced database object, what can be said about existing dependencies?	All direct and indirect dependent objects are affected.
Which statement about declaring parameters is true?	Only data type is required.
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE find_cpt<br />(v_movie_id {argument mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER)<br />IS<br />BEGIN<br />IF v_cost_per_ticket &gt; 8.50 THEN<br />SELECT cost_per_ticket<br />INTO v_cost_per_ticket<br />FROM gross_receipt<br />WHERE movie_id = v_movie_id;<br />END IF;<br />END;<br /><br />Which argument mode should be used for V_MOVIE_ID?	IN
Which are NOT valid system events for a trigger? (Choose all that apply.)	BEFORE STARTUP
	BEFORE LOGON ON DATABASE
Which type of trigger should you create to track the time at which users log off from a database?	a system event trigger on a database
Examine the following code:<br /><br />CREATE OR REPLACE TRIGGER acct_update<br />AFTER UPDATE OF acct_balance, acct_det ON accounts<br />FOR EACH ROW<br />BEGIN<br />INSERT INTO acct_log (new_bal, new_details, acct_number)<br />VALUES(:new.acct_balance, :new.acct_det, acct_number);<br />END acct_update;<br /><br />When will the acct_update trigger be fired?	when either the acct_balance column or the acct_det column is updated
Which data dictionary table can you query to determine all stand-alone procedures that reference the<br />THEATER_PCK package?	USER_DEPENDENCIES
The auditing utility in Oracle records the type of data manipulation operation and not the actual changed<br />values. To enhance auditing by capturing the new and old values, you create which type of trigger?	row only
For which trigger timing can you reference the NEW and OLD qualifiers?	row only
CREATE OR REPLACE TRIGGER update_studio<br /><br />BEFORE UPDATE OF yearly_budget ON STUDIO<br />FOR EACH ROW<br />BEGIN<br />...<br />END;<br /><br />Which event will invoke this trigger?	YEARLY_BUDGET column update
When a local procedure that references a remote procedure is compiled, what is recorded in the p-code or<br />object code?	both timestamps of the local and remote procedures
Examine this code:<br /><br />BEGIN<br />theater_pck.v_total_seats_sold_overall := theater_pck.get_total_for_year;<br />END;<br /><br />For this code to be successful, what must be true?	Both the V_TOTAL_SEATS_SOLD_OVERALL variable and the GET_TOTAL_FOR_YEAR function mustexist in the specification of the THEATER_PCK package.
In which situations should you NOT use a trigger? (Choose all that apply.)	to check whether the salary of an employee is within a specific range
	to ensure that the identification numbers of all the items in a table are unique
	to ensure that the hire date of an employee is not later than the current date
Which two views, when created, are queried to display indirect dependencies? (Choose two.)	DEPTREE
	IDEPTREE
How does a local dependent object differ from a remote dependent object?	Local dependent objects are on the same node in the same database.
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE find_cpt<br />(v_movie_id IN NUMBER, v_cost_per_ticket IN OUT NUMBER DEFAULT 0)<br />IS<br />BEGIN<br />IF v_cost_per_ticket &gt; 8.50 THEN<br />SELECT cost_per_ticket<br />INTO v_cost_per_ticket<br />FROM gross_receipt<br />WHERE movie_id = v_movie_id;<br />END IF;<br />END;<br /><br />Why does this statement fail when executed?	The declaration of V_COST_PER_TICKET cannot have a DEFAULT value.
Modifications to the THEATER table are not allowed during the last week in December. When creating a<br />database trigger to enforce this rule, which timing will you use to be most efficient?	BEFORE
Which data dictionary view must you query to determine when a particular procedure or function was created?	USER_OBJECTS
For which type(s) of objects does Oracle keep track of dependencies?	all object types
Which procedure would you use to close a BFILE that is being accessed?	DBMS_LOB.FILECLOSE
You are in a process of creating a DML trigger. While compiling the trigger, the compilation is completed but<br />some errors are generated.<br /><br />Which two statements are true in this scenario? (Choose two.)	The trigger will be created.
	The errors can be viewed using the USER_ERRORS view.
When declaring arguments within a procedure, which specification is NOT allowed?	maximum length
Which function of the DBMS_SQL package returns the total number of rows affected by the last operation?	EXECUTE
When invoking a procedure, you can specify the arguments using the positional method by listing the values<br />in the order of the argument list.<br /><br />Which method would you use to list values in an arbitrary order?	named
Due to a disk failure, the AUDIT_THEATER table is unavailable until further notice. The CHECK_THEATER database trigger references this table when a DML operation is performed on the THEATER table.<br /><br />Which statement should you issue to prevent this database trigger from executing until this problem is resolved?	ALTER TRIGGER check_theater DISABLE;
Which two statements are true about the IDEPTREE view? (Choose two.)	The view displays the indirect dependency tree.
	The view displays the indented dependency tree.
You have lost the script file that contains the source code for the THEATER_PCK package. Which statement will produce the source code stored in the database?	SELECT textFROM user_sourceWHERE name = \'THEATER_PCK\';
Examine this package specification:<br /><br />CREATE OR REPLACE PACKAGE prod_pack<br />IS<br />PROCEDURE order_product<br />(p1 IN NUMBER, p2 IN NUMBER);<br />PROCEDURE order_product<br />(p1 IN NUMBER, p2 IN VARCHAR2);<br />PROCEDURE order_product;<br />END prod_pack;<br /><br />Which header can be added to this package specification without violating the rules of package overloading?	PROCEDURE order_product (p1 VARCHAR2);
Evaluate this code:<br /><br />CREATE OR REPLACE TRIGGER update_studio<br />BEFORE UPDATE OF yearly_budget ON STUDIO ON THEATER<br />FOR EACH ROW<br />BEGIN<br />...<br />END;<br /><br />Why does this trigger return an error upon compilation?	A database trigger cannot be created on more than one table
Which statement about declaring arguments for procedures is true?	Formal arguments allow you to transfer values to and from the calling environment
Examine this package:<br /><br />CREATE OR REPLACE PACKAGE theater_pck<br />IS<br />current_avg_cost_per_ticket NUMBER := 0;<br /><br />PROCEDURE find_seats_sold<br />(v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER);<br /><br />FUNCTION get_budget<br />(v_studio_id IN NUMBER)<br />RETURN NUMBER;<br />END theater_pck;<br />You issue these statements in SQL*Plus:<br />EXECUTE theater_pck.current_avg_cost_per_ticket := 10;<br />ROLLBACK;<br /><br />What is true about the state of CURRENT_AVG_COST_PER_TICKET?	It is 10 for the remainder of the session
Examine this database trigger:<br /><br />CREATE OR REPLACE TRIGGER prevent_gross_modification<br />{additional trigger code}<br />BEGIN<br />IF TO_CHAR(sysdate,\'DY\') = \'MON\' THEN<br />RAISE_APPLICATION_ERROR(-20000, \'Gross receipts cannot be entered on Monday\');<br />END IF;<br />END;<br /><br />This trigger must fire before each DELETE, INSERT, and UPDATE of the GROSS_RECEIPT table. It should<br />fire only once for the entire data manipulation statement.<br /><br />Which additional trigger code must you add?	BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt
Which statement will create the dependency structure of the DEPARTMENT table in the PROD schema?	EXECUTE deptree_fill (\'TABLE\', \'PROD\', \'DEPARTMENT\')
Which data dictionary view can you query to examine all the dependencies between the objects that you own?	USER_DEPENDENCIES
You created a database trigger that will be executed for all data manipulation statements on the THEATER<br />table. Within the code, you will determine which type of manipulation has caused the trigger to execute.<br /><br />Which would you use to test for the type of manipulation being performed?	DELETING, UPDATING, and INSERTING
Which statements represent benefits of using the EXECUTE IMMEDIATE statement over the DBMS_SQL<br />package? (Choose all that apply.)	EXECUTE IMMEDIATE is simpler to use
	EXECUTE IMMEDIATE is faster than DBMS_SQL
	Unlike DBMS_SQL, EXECUTE IMMEDIATE supports user-defined types
Which statements represent the difference between triggers and procedures? (Choose all that apply.)	Triggers are invoked automatically, but procedures must be invoked explicitly
How do functions simplify maintainability?	by limiting changes to logic to one location
What should be placed after the IS keyword when creating a procedure?	local variables only
A stored function can be invoked in many different ways. Which invocation example is NOT valid?	executing the stored function within a CHECK constraint of a table
Due to a change to a particular table, you are concerned with the number of stored procedures and functions<br />that may have been affected.<br /><br />Which view can you query to check the status of each subprogram and determine which procedures and<br />functions must be recompiled?	USER_OBJECTS
You have just moved a stand-alone function into a package. What could you add to the package specification<br />to check the purity level of this function?	PRAGMA RESTRICT_REFERENCES
You have created the following function:<br /><br />CREATE OR REPLACE FUNCTION get_salary<br />(v_emp_id NUMBER)<br />RETURN NUMBER<br />IS<br />v_salary NUMBER;<br />BEGIN<br />SELECT salary<br />INTO v_salary<br />FROM emp<br />WHERE employee_id = v_emp_id;<br />RETURN v_salary;<br />END get_salary;<br /><br />Which three statements will successfully invoke the get_salary function? (Choose three.)	SELECT get_salary(150) from emp;
	SELECT get_salary(205) from dual;
	UPDATE emp SET SALARY = get_salary(200) WHERE employee_id = 250;
You execute this code:<br /><br />CREATE OR REPLACE PROCEDURE find_seats_sold<br />(v_movie_id IN NUMBER DEFAULT 34)<br />IS<br />v_seats_sold gross_receipt.seats_sold%TYPE;<br />BEGIN<br />null;<br />END;<br /><br />Which statement is true?	The statement compiles, and the procedure is created
Examine this database trigger:<br /><br />CREATE OR REPLACE TRIGGER update_show_gross<br />{additional trigger information}<br />BEGIN<br />{additional code}<br />END;<br /><br />This trigger should execute for each row when the SEATS_SOLD or COST_PER_TICKET columns are<br />updated and when a row is inserted into the GROSS_RECEIPT table.<br /><br />Which trigger information must you add?	BEFORE INSERT OR UPDATE OF seats_sold, cost_per_ticket ON gross_receiptFOR EACH ROW
To easily determine indirect dependencies between specified objects, additional views can be created. Which<br />script file, provided by Oracle, will create these views when executed?	utldtree.sql
The TOTAL_GROSS column of the THEATER table can be derived from the GROSS_RECEIPT table. To<br />keep the value of this column consistent during modifications to the GROSS_RECEIPT table, which type of<br />construct will you create?	database trigger
Your database administrator has granted you the CREATE ANY PROCEDURE privilege. Which operations<br />can you successfully perform in the database with this privilege? (Choose all that apply.)	You can create a function in any schema
	You can create a procedure in any schema
	You can create a package in your own schema
Users in the database want to call a user-defined function from a SQL expression.<br /><br />Which two are prerequisites for these users\' actions to be successful? (Choose two.)	The function must be a stored function
	The formal parameter data types of the function must not be PL/SQL types
You own a table named GROSS_RECEIPT that contains sales data. You have disabled certain database<br />triggers on the GROSS_RECEIPT table.<br /><br />Which two data dictionary views can you query to determine the status of all the triggers you have created on<br />this table? (Choose two.)	USER_OBJECTS
	USER_TRIGGERS
Which two statements about package overloading are true? (Choose two.)	The subprograms must be local
	Two subprograms with the same name and number of formal parameters must have at least oneparameter defined with a different data type
Examine this trigger:<br /><br />CREATE OR REPLACE TRIGGER budget_trig<br />AFTER INSERT ON studio<br />FOR EACH ROW<br />DECLARE<br />v_sum NUMBER;<br />BEGIN<br />SELECT sum(yearly_budget)<br />INTO v_sum<br />FROM studio;<br /><br />UPDATE parent_company<br />SET overall_budget = v_sum;<br />END;<br /><br />You insert a row into the STUDIO table and receive this message:<br /><br />ORA-04091: table SCOTT.STUDIO is mutating, trigger/function may not see it<br /><br />How do you correct this error?	Convert it to a statement level database trigger by removing FOR EACH ROW
Procedures and functions can be created and stored in the database or in an Oracle Developer application.<br />How is performance improved when storing procedures and functions in the database?	Network roundtrips are reduced
Which statement about the use of the DEFAULT clause in the declaration of a formal parameter is true?	IN OUT parameters cannot be initialized with a default value
Which trigger timing can be used when creating a trigger on a view which does not allow DML?	INSTEAD OF only
The four phases that indicate the different phases of SQL statement execution are as follows:<br /><br />1. execute<br />2. fetch<br />3. parse<br />4. bind<br /><br />Which option represents the correct sequence of events while executing a statement?	3, 4, 1, 2
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE calculate_budget<br />IS<br />BEGIN<br />v_budget := get_budget (11);<br />IF v_budget &lt; 30000000 THEN<br />set_budget(11, 300000000);<br />END IF;<br />END;<br /><br />A table that SET_BUDGET references has been accidentally dropped. Until this problem is resolved, what is<br />the status of these procedures?	Only CALCULATE_BUDGET and SET_BUDGET are marked invalid
Which is a true statement regarding a bodiless package?	A bodiless package is used to initialize global variables
The MODIFY_PAYROLL procedure contains many SQL statements and will be executed from multiple client<br />applications. Where should this procedure be stored?	server only
Which two statements are true? (Choose two.)	A function must return a value
	A function can be invoked from within a PL/SQL expression
Which statement would you use to migrate a LONG data type to a LOB data type?	ALTER TABLE
Examine this package:<br /><br />CREATE OR REPLACE PACKAGE prod_pack<br />IS<br />CURSOR c1 is<br />SELECT *<br />FROM product;<br />PROCEDURE order_product<br />(p1 IN NUMBER, p2 IN NUMBER);<br />END prod_pack;<br /><br />CREATE OR REPLACE PACKAGE BODY prod_pack<br />IS<br /><br />PROCEDURE order_product<br />(p1 IN NUMBER, p2 IN NUMBER)<br />IS<br />CURSOR c2 IS<br />SELECT *<br />FROM ord;<br />BEGIN<br />OPEN c2;<br />...<br />END;<br />END prod_pack;<br /><br />After being opened, which cursor or cursors persist for the entire user session?	C1 only
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE find_seats_sold<br />(v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER)<br />IS<br />v_seats_sold gross_receipt.seats_sold%TYPE;<br />BEGIN<br />SELECT seats_sold<br />INTO v_seats_sold<br />FROM gross_receipt<br />WHERE movie_id = v_movie_id<br />AND theater_id = v_theater_id;<br />END;<br /><br />Which command will successfully invoke this procedure in SQL*Plus?	EXECUTE find_seats_sold (v_theater_id =&gt; 500);
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE calculate_budget<br />IS<br />v_budget studio.yearly_budget%TYPE;<br />v_studio_rec studio%ROWTYPE;<br />BEGIN<br />SELECT *<br />INTO v_studio_rec<br />FROM studio<br />WHERE NAME = \'Vince Productions\';<br /><br />v_budget := get_budget (v_studio_rec.id);<br />IF v_budget &lt; 30000000 THEN<br />set_budget (11, 300000000);<br />END IF;<br />END;<br /><br />Which effect could you expect if a new column were added to the STUDIO table?	The CALCULATE_BUDGET procedure would be marked invalid and would automatically compilesuccessfully upon the next execution
For which purpose are formal parameters used when creating functions?	passing values to the function
Which statement concerning the use of a procedure is true?	A user needs only the privilege to execute the procedure and does not need privileges on the underlying tables
Examine this database trigger:<br /><br />CREATE OR REPLACE TRIGGER cascade_updates<br />AFTER UPDATE OF id ON studio<br />FOR EACH ROW<br />BEGIN<br />UPDATE producer<br />SET studio_id = :new.id<br />WHERE studio_id = :old.id;<br />END;<br /><br />For this database trigger to execute successfully, which type of constraint must NOT exist?	a foreign key constraint on the STUDIO_ID column of the PRODUCER table referencing the ID column of the STUDIO table
Evaluate this procedure:<br /><br />CREATE OR REPLACE PROCEDURE remove_department<br />(v_deptno IN NUMBER(9))<br />IS<br />BEGIN<br />DELETE dept<br />WHERE deptno = v_deptno;<br />END;<br /><br />Why does this statement fail when compiled?	Specifying a precision for a formal parameter is not permitted
Which function or procedure would you use to initialize a BFILE column for inserting data?	BFILENAME
Which subprogram type can be invoked from within a SQL statement?	function
Which statement about error propagation is true?	When an exception is raised in a called procedure, control goes to the exception section of that block
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE calculate_budget<br />IS<br />v_budget studio.yearly_budget%TYPE;<br />BEGIN<br />v_budget := get_budget@proddb(11);<br />IF v_budget &lt; 30000000 THEN<br />set_budget (11, 300000000);<br />END IF;<br />END;<br /><br />You are about to add an argument to the local procedure, CALCULATE_BUDGET. What effect will this have<br />on the remote procedure, GET_BUDGET?	There is no effect
Examine this function:<br /><br />CREATE OR REPLACE FUNCTION set_budget<br />(v_studio_id IN NUMBER, v_new_budget IN NUMBER)<br />RETURN NUMBER<br />IS<br />BEGIN<br />UPDATE studio<br />SET yearly_budget = v_new_budget<br />WHERE id = v_studio_id;<br />COMMIT;<br />RETURN SQL%ROWCOUNT;<br />END;<br /><br />While executing this in SQL*Plus, you want to see the value of SQL%ROWCOUNT displayed on the screen.<br /><br />Which line of code will accomplish this?	DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT));
Which types of constructs can be overloaded within a package?	procedures and functions only
For which reason might you create a subprogram within a procedure?	to store a repeating block of code once without creating a separate construct
Which statement about a dependent procedure is true?	A dependent procedure directly or indirectly references a view, sequence, procedure, or packaged procedure or function
You have created a function as follows:<br /><br />CREATE OR REPLACE FUNCTION get_salary<br />(v_emp_id IN NUMBER)<br />RETURN NUMBER(10,2)<br />IS<br />v_salary NUMBER;<br />BEGIN<br />SELECT salary<br />INTO v_salary<br />FROM emp<br />WHERE id = v_emp_id;<br />RETURN v_salary;<br />END get_salary;<br /><br />After compilation, the function returned an error.<br /><br />What should you do to rectify the error?	Correct the return data type specification
Examine the following code:<br /><br />CREATE OR REPLACE TRIGGER audit_upd_emp<br />AFTER UPDATE OR DELETE<br />OF salary ON emp<br />FOR EACH ROW<br />BEGIN<br />INSERT INTO emp_update (emp_id, sal)<br />VALUES(employee_id, :new.salary);<br />END;<br /><br />Which portion of this statement in the trigger determines the number of times the body of the audit_upd_emp<br />trigger will be executed?	FOR EACH ROW
Due to modifications to certain database objects, you are facing problems with the dependent procedures<br />accessing these objects in your schema.<br /><br />Which data dictionary view can you query to display the statuses of these procedures?	USER_OBJECTS
In dynamic SQL that performs DML operations, in which phase are the processed rows returned?	execute
The code logic of the PREVENT_GROSS_MODIFICATION trigger seems to be incorrect. You have lost the<br />script file that contains the code for this trigger.<br /><br />Which data dictionary view can you query to examine the code for this trigger?	USER_TRIGGERS
Examine this procedure:<br /><br />CREATE OR REPLACE PROCEDURE find_seats_sold<br />(v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER)<br />IS<br />v_seats_sold gross_receipt.seats_sold%TYPE;<br />BEGIN<br />SELECT seats_sold<br />INTO v_seats_sold<br />FROM gross_receipt<br />WHERE movie_id = v_movie_id<br />AND theater_id = v_theater_id;<br />END;<br /><br />Which command will successfully invoke this procedure in SQL*Plus?	EXECUTE find_seats_sold (v_theater_id =&gt; 500, v_movie_id =&gt; 34);
Examine this function:<br /><br />CREATE OR REPLACE FUNCTION get_budget<br />RETURN NUMBER<br />IS<br />v_yearly_budget NUMBER;<br />BEGIN<br />SELECT yearly_budget<br />INTO v_yearly_budget<br />FROM studio<br />WHERE id = v_studio_id;<br />RETURN v_yearly_budget;<br />END;<br /><br />What additional action is needed to compile this function successfully?	Add (v_studio_id IN NUMBER) immediately before the RETURN clause of the header
You have created the set_budget procedure as follows:<br />SQL&gt; CREATE OR REPLACE PROCEDURE set_budget<br />2 (v_studio_id studio.studio_id%TYPE, v_new_budget NUMBER)<br />3 IS<br />4 BEGIN<br />5 UPDATE studio<br />6 SET yearly_budget = v_new_budget<br />7 WHERE id = v_studio_id;<br />8 EXECUTE get_budget(v_new_budget);<br />9 END;<br />10 /<br /><br />During compilation of the procedure, the compiler returns an error.<br /><br />Which line should you modify to successfully compile the procedure?	8
Examine this code:<br /><br />CREATE OR REPLACE PACKAGE prod_pack<br />IS<br />g_tax_rate NUMBER := .08;<br />END prod_pack;<br /><br />Which statement about this code is true?	This package specification can exist without a body