Unformatted text preview: v_raise := p_raise_amt * v_salary;
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.
This statement will fail. Remove the STORED from CREATE OR REPLACE STORED
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
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
B. ALTER TRIGGERS ON TABLE employees DISABLE;
C. ALTER employees DISABLE ALL TRIGGERS;
D. ALTER TABLE employees DISABLE ALL TRIGGERS;
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
A. This is not true, you can disable all triggers on a table using the ALTER TABLE
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
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.
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)
v_raise NUMBER(4,2) DEFAULT 1.08;
RETURN v_raise * p_salary;
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)
WHERE department_id = 60;
E. SELECT last_name, salary, calc_sal(salary)
FROM employees ORDER BY
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.
A. Functions can only be invoked in the in the SET and WHERE clause of an UPDATE
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:
CREATE OR REPLACE TRIGGER CALC_TEAM_AVG
AFTER INSERT ON PLAYER
INSERT INTO PLAYER_BATSTAT (PLAYER_ID,
VALUES (:NEW.ID, 1997, 0,0);
To which type must you convert the trigger to correct the
C. ORACLE FORM trigger
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
B. This triggers is a Statement level trigger by default and that is why it is throwing an
C. This needs to be a database trigger ROW level Trigger not a Application client-side
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)
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.
- Spring '10