The source code appears in the text column and the

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: r all procedures created by the owner. The source code appears in the TEXT column, and the name of the procedure appears in the NAME column. WWW.ExamMagic.COM - 34: A CALL statement inside the trigger body enables you to call ______. A. A package. B. A stored function. 1Z0-147 C. A stored procedure. D. Another database trigger. Answer: C Explanation: Incorrect Answers: A. Package can't be called, we call a procedure inside the package. B. We can't call a function use CALL statement because function must return a value. D. Trigger can't be called, they are execute automatically when the trigger event occure. WWW.ExamMagic.COM - 35: You need to remove the database triggerBUSINESS_HOUR. Which command do you use to remove the trigger in the SQL *Plus environment? A. DROP TRIGGER business_hour; B. DELETE TRIGGER business_hour; C. REMOVE TRIGGER business_hour; D. ALTER TRIGGER business_hour REMOVE; E. DELETE FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'BUSINESS_HOUR'; Answer: A Explanation: To permanently remove a trigger from a database, you can use the DROP TRIGGER statement. The syntax of this statement is: DROP TRIGGER ; B, C & D. Are invalid and will return an error E. This statement would attempt to delete a record from the USER_Triggers View WWW.ExamMagic.COM - 36: How can you migrate from a LONG to a LOB data type for a column? A. Use the DBMS_MANAGE_LOB.MIGRATE procedure. B. Use the UTL_MANAGE_LOB.MIGRATE procedure. C. Use the DBMS_LOB.MIGRATE procedure. D. Use the ALTER TABLE command. 1Z0-147 E. You cannot migrate from a LONG to a LOB date type for a column. Answer: D Explanation: In Oracle9i, a LONG column in a table can be migrated to a LOB column using the ALTER TABLE statement. The syntax is: ALTER TABLE <schema>.<table name> MODIFY <long column name> {CLOB | BLOB |NCLOB} Incorrect Answers: A & B These are invalid packages and there is not a Migrate Procedure C. This is not a valid procedure E. LONG Data Types are migrated to LOB's using the ALTER TABLE Statement WWW.ExamMagic.COM - 37: Examine this procedure: CREATE OR REPLACE PROCEDURE INSERT_TEAM (V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT 'AUSTIN', V_NAME in VARCHAR2) IS BEGIN INSERT INTO TEAM (id, city, name) VALUES (v_id, v_city, v_name); COMMIT; END Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two) A. EXECUTE INSERT_TEAM; B. EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN'); C. EXECUTE INSERT_TEAM(3, 'AUSTIN','LONGHORNS'); D. EXECUTE INSERT_TEAM (V_ID := V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN'); E. EXECUTE INSERT_TEAM (3, 'LONGHORNS'); Answer: B,C Explanation: B: This statement correctly uses mixed notation. The following example uses named notation for passing actual parameters to a procedure: EXECUTE my_procedure (p_deptcode=>10, p_empid => 1); 1Z0-147 C. This statement correctly uses positional notation Incorrect Answers: A. This statement will fail because parameters are not specified the for V_ID and V_NAMME arguments and there are no default values specified in the procedure. Formal parameters that do not have any default values assigned in the parameter list must be providedthe actual values when the procedure is invoked. When invoking a procedure, you cannot omit the actual value of a formal parameter that does not have a default value. D. This is incorrect syntax for named notation. The following example demonstrates using named notation for passing actual parameters to the above created procedure my_procedure: EXECUTE my_procedure (p_deptcode=>10, p_empid => 1); E. This statement will fail because a parameter is not supplied to the V_Name argument and a default value is not specified in the procedure. WWW.ExamMagic.COM - 38: To be callable from a SQL expression, a user-defined function must do what? A. Be stored only in the database. B. Have both IN and OUT parameters. C. Use the positional notation for parameters. D. Return a BOOLEAN or VARCHAR2 data type. Answer: C Explanation: Functions called from SQL statements must use positional notation to pass values to the IN parameters. Incorrect Answers: A.Functions invoked using SQL statements must be stored in the database, however if they are stored on the client this alone will not cause the statement to fail. B. The function must only have parameters of the IN Mode for them to be used in a SQL Expression. D. You cannot use PL/SQL data types, such as BOOLEAN, RECORD, or TABLE for returning values for the function to be callable from a SQL expression. WWW.ExamMagic.COM - 39: Which two describe a stored procedure? (Choose two) A. A stored procedure is typically written in SQL. B. A stored procedure is a named PL/SQL block that can accept parameters. 1Z0-147 C. A stored procedure is a type of PL/SQL subprogram that performs an action. D. A stored procedure has three parts: the specification, the body, and the exception handler part. E. The executable section of a stored procedure contains statements that assigns...
View Full Document

This note was uploaded on 01/05/2011 for the course COMS 131 taught by Professor Dad during the Spring '10 term at University of Mumbai.

Ask a homework question - tutors are online