A drop function genemailname b remove genemailname c

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: _id = p_id; RETURN v_email_name; END; Which statement removes the function? A. DROP FUNCTION gen_email_name; B. REMOVE gen_email_name; C. DELETE gen_email_name; D. TRUNCATE gen_email_name; E. ALTER FUNCTION gen_email_name REMOVE; Answer: A Explanation: Stored functions can be permanently removed from the database by dropping them. You use the following statement for dropping a stand-alone stored function: DROP FUNCTION ; Answers B, C, D & E are incorrect & will generate errors WWW.ExamMagic.COM - 48: Examine this procedure: CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID IN NUMBER DEFAULT 10, V_AB IN NUMBER DEFAULT 4) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB www.ExamMagic.com 1Z0-147 WHERE PLAYER_ID = V_ID; COMMIT; END; Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two) A. EXECUTE UPD_BAT_STAT; B. EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31); C. EXECUTE UPD_BAT_STAT(31, 'FOUR','TWO'); D. UPD_BAT_STAT(V_AB=>10, V_ID=>31); E. RUN UPD_BAT_STAT; Answer: A,B Explanation: A. This is the correct syntax for invoking a function from SQL *Plus. Actual parameters are not needed since the procedure has default values defined for each formal parameter. The EXECUTE or EXEC statement is used to invoke an independent procedure from SQL*Plus. The EXECUTE or EXEC keyword is followed by the name of the procedure and arguments. B. This is the correct syntax for invoking this function using positional notation. Incorrect Answers C. This would fail, too many parameters are passed. The procedure being called only has two parameters. D. If this statement was invoked from inside an anonymous PL/SQL Block or from another procedure you would omit the EXECUTE. E. Run is not a valid command for invoking a procedure. WWW.ExamMagic.COM - 49: Examine this code: CREATE OR REPLACE PROCEDURE audit_action (p_who VARCHAR2) AS BEGIN INSERT INTO audit(schema_user) VALUES(p_who); END audit_action; / CREATE OR REPLACE TRIGGER watch_it AFTER LOGON ON DATABASE CALL audit_action(ora_login_user) / What does this trigger do? www.ExamMagic.com 1Z0-147 A. The trigger records an audit trail when a user makes changes to the database. B. The trigger marks the user as logged on to the database before an audit statement is issued. C. The trigger invoked the procedure audit_action each time a user logs on to his/her schema and adds the username to the audit table. D. The trigger invokes the procedure audit_action each time a user logs on to the database and adds the username to the audit table. Answer: D Explanation: This trigger fires after a user connects to a database and inserts a record into the audit table. Incorrect Answers A. This Trigger is not defined as a DML Trigger B. This does not make any sense C. If you specified to AFTER LOGON ON SCHEMA this would be the correct answer. WWW.ExamMagic.COM - 50: Which view displays indirect dependencies, indenting each dependency? A. DEPTREE B. IDEPTREE C. INDENT_TREE D. I_DEPT_TREE Answer: B Explanation: The IDEPTREE displays the indirect dependencies with indentation. No indent is used on the first line for the object being analyzed; a single indent indicates a direct dependency, a double indent represents an indirect dependency. Incorrect Answers A. DEPTREE displaysthe indirect dependencies without indentation. This view uses a nested_level column with a value of 0,1 or 2 to indicate the nesting level. C & D are not valid dictionary views www.ExamMagic.com 1Z0-147 WWW.ExamMagi c.COM - 51: The OLD and NEW qualifiers can be used in which type of trigger? A. Row level DML trigger B. Row level system trigger C. Statement level DML trigger D. Row level application trigger E. Statement level system trigger F. Statement level application trigger Answer: A Explanation: The qualifiers :OLD and :NEW can only be used with row level DML triggers. Incorrect Answers B, C, D, E & F. ROW level triggers do not apply to application or system triggers. The :OLD & :NEW Qualifiers only apply to DML ROW level Triggers. WWW.ExamMagic.COM - 52: Which statement is true? A. Stored functions can be called from the SELECT and WHERE clauses only. B. Stored functions do not permit calculations that involve database links in a distributed environment. C. Stored functions cannot manipulate new types of data, such as longitude and latitude. D. Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application. Answer: D Explanation: User-defined functions increase the efficiency of queries by applying the functions in the query itself. This drastically improves the performance because the query is designed to use the function instead of using the query and functions separately from client-side tools when enforcing application logic. Incorrect Answers www.ExamMagic.com 1Z0-147 A. Functions can be invoked as part of SELECT statements (in the column list, WHERE, GROUP BY, HAVING and ORDER BY clauses), in the UPDATE statement (in the SET and WHERE clause), in t...
View Full Document

Ask a homework question - tutors are online