A this statement creates a stored procedure named

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: v_raise := p_raise_amt * v_salary; RETURN v_raise; END; Which statement is true? A. This statement creates a stored procedure named get_sal. B. This statement returns a raise amount based on an employee id. C. This statement creates a stored function named get_sal with a status of invalid. D. This statement creates a stored function named get_sal. E. This statement fails. Answer: E Explanation: This statement will fail. Remove the STORED from CREATE OR REPLACE STORED FUNCTION Incorrect Answers A. This statement is attempting to create a Function B. If there was not a syntax error, this function would return a raise amount based on the raise amount & employee id passed to the function. C. The incorrect syntax will prevent the function from being created with a status of invalid. D. The incorrect syntax will prevent the function from being. WWW.ExamMagic.COM - 61: You need to disable all triggers on the EMPLOYEES table. Which command accomplishes this? A. None of these commands; you cannot disable multiple triggers on a table in one command. B. ALTER TRIGGERS ON TABLE employees DISABLE; C. ALTER employees DISABLE ALL TRIGGERS; D. ALTER TABLE employees DISABLE ALL TRIGGERS; Answer: D www.ExamMagic.com 1Z0-147 Explanation: You can disable all triggers using the ALTER TABLE command. The syntax to disable or re-enable all triggers on a particular table is: ALTER TABLE <table name> DISABLE | ENABLE ALL TRIGGERS Incorrect Answers A. This is not true, you can disable all triggers on a table using the ALTER TABLE command B & C are incorrect syntax for disabling a Trigger WWW.ExamMagic.COM - 62: An internal LOB is _____. A. A table. B. A column that is a primary key. C. Stored in the database. D. A file stored outside of the database, with an internal pointer to it from a database column. Answer: C Explanation: Internal LOBs are stored inside the database. To access the internal LOBs, Oracle provides the DBMS_LOB package, which uses the locator to access the LOB values. Incorrect Answers A. An Internal LOB is a Data Type not a Table B. An internal LOB is not a column that is a primary key D. External LOBs are stored outside the database. WWW.ExamMagic.COM - 63: Examine this code: CREATE OR REPLACE FUNCTION calc_sal(p_salary NUMBER) RETURN NUMBER IS v_raise NUMBER(4,2) DEFAULT 1.08; BEGIN RETURN v_raise * p_salary; END calc_sal; / www.ExamMagic.com 1Z0-147 Which statement accurately call the stored function CALC_SAL? (Choose two) A. UPDATE employees (calc_sal(salary)) SET salary = salary * calc_sal(salary); B. INSERT calc_sal(salary) INTO employees WHERE department_id = 60; C. DELETE FROM employees(calc_sal(salary)) WHERE calc_sal(salary) > 1000; D. SELECT salary, calc_sal(salary) FROM employees WHERE department_id = 60; E. SELECT last_name, salary, calc_sal(salary) FROM employees ORDER BY calc_sal(salary); Answer: D,E Explanation: D. This function is using the function in the column list of the select statement. This function does not perform any DML or violate any of the restriction on functions that are invoked as part of a SQL expression. E. This function is correctly using the function in the column list and the ORDER BY clause of the select statement. Functions can be invoked in the column list, WHERE, GROUP BY, HAVING and ORDER BY clauses of a SELECT statement. Restrictions on functions that are invoked as part of SQL expressions are: Functions should be stored in the database. Functions should retrun data types accepted by SQL. Functions should not include any transaction,session , or system control statments. Functions should have formal parameters of the IN mode. Functions should not modify any database tables,if called as part of a SELECT statment. Functions should not modify or read the same table,if called as part of an UPDATE or DELETE. Functions should use only positional to pass values to the formal parameters. Incorrect Answers A. Functions can only be invoked in the in the SET and WHERE clause of an UPDATE statement. B. Functions can only be invoked in the VALUES clause of an INSERT statement. C. Functions can only be invoked in the WHERE clause of a DELETE statement. WWW.ExamMagic.COM - 64: This statement fails when executed: www.ExamMagic.com 1Z0-147 CREATE OR REPLACE TRIGGER CALC_TEAM_AVG AFTER INSERT ON PLAYER BEGIN INSERT INTO PLAYER_BATSTAT (PLAYER_ID, SEASON_YEAR,AT_BATS,HITS) VALUES (:NEW.ID, 1997, 0,0); END; To which type must you convert the trigger to correct the error? A. Row B. Statement C. ORACLE FORM trigger D. Before Answer: A Explanation: The qualifiers :OLD and :NEW can only be used with row triggers. If you attempt to create a statement level trigger using the qualifiers, Oracle generates the following error message at compile time: ORA-01912: ROW keyword expected Incorrect Answers B. This triggers is a Statement level trigger by default and that is why it is throwing an error. C. This needs to be a database trigger ROW level Trigger not a Application client-side Trigger. D. The Trigger timing is not relevant to the problem. WWW.ExamMagic.COM - 65: Examine this code: CREATE OR REPLACE PROCEDURE audit_emp (p_id IN emp_empno%TYPE) IS v_id NUMBER; PROCEDURE log_exec IS BEGIN INSERT INTO log_table (us...
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