1Z0-147 Exam Questions

What can you do with the DBMS_LOB package?

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID,LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT;...

This statement fails when executed: CREATE OR REPLACE TRIGGER CALC_TEAM_AVG AFTER INSERT ON PLAYER BEGIN INSERT INTO PLAYER_BAT_STAT (PLAYER_ID, SEASON_YEAR,AT_BATS,HITS) VALUES (N...

The upd_player, upd_player_stat, and upd_pitcher_stat procedures are grouped together in a package. A variable must be shared among only these procedures. Where should you declare...

Examine this package: CREATE OR REPLACE PACKAGE discounts IS g_id_NUMBER := 7839; discount_rate NUMBER := 0.00; PROCEDURE display_price (p_price NUMBER); END discounts; / CREATE OR...

Examine this code: CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(p_comm IN NUMBER) END comm_package; / User Jones executes the following code...

Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name VARCHAR2, p_id_NUMBER) RETURN VARCHAR2 IS v_email_name VARCHAR2(19); BEGIN v_email_...

Examine this code: CREATE OR REPLACE TRIGGER secure_emp BEFORE LOGON ON employees BEGIN IF (TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN')) OR (TO_CHAR(SYSDATE, 'HH24:MI') NOT BETWEEN '0...

What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?

You need to remove the database trigger BUSINESS_HOUR. Which command do you use to remove the trigger in the SQL*Plus environment?

Which three are valid ways to minimize dependency failure? (Choose three.)

Examine this package: CREATE OR REPLACE PACKAGE manage_emps IS tax_rate CONSTANT NUMBER(5,2) := .28; v_id NUMBER; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER); PROCEDURE de...

Procedure PROCESS_EMP references the table EMP. Procedure UPDATE_EMP updates rows in table EMP through procedure PROCESS_EMP. There is a remote procedure QUERY_EMP that queries the...

Which two statements about packages are true? (Choose two.)

Which statement about triggers is true?

Which two statements about the overloading feature of packages are true? (Choose two.)

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; / Which s...

Which part of a database trigger determines the number of times the trigger body executes?

Examine the code examples. Which one is correct?

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 wa...

Examine this code: CREATE OR REPLACE PACKAGE metric_converter IS c_height CONSTANT NUMBER := 2.54; c_weight CONSTANT NUMBER := .454; FUNCTION calc_height (p_height_in_inches NUMBER...

You have an AFTER UPDATE row-level trigger on the table EMP. This trigger queries the EMP table and inserts the updating user's information into the AUDIT_TABLE . What happens when...

Examine this code: CREATE OR REPLACE STORED FUNCTION get_sal (p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE) RETURN NUMBER IS v_salary NUMBER; v_raise NUMBER(8,2) ;...

Why do you use an INSTEAD OF trigger?

Given a function CALCTAX: CREATE OR REPLACE FUNCTION calctax (sal NUMBER) RETURN NUMBER IS BEGIN RETURN (sal * 0.05); END; / If you want to run the above function from the SQL*Plus...

What is the name of the private procedure in this package?

You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a SELECT statement on the EMP table to ensure that the new salary value falls within the minimum...

Mr. King is the president of a company. Five managers report to him. All other employees report to these managers. Examine the code: SELECT employee_ename FROM emp employee WHERE e...

Which statement about multiple column subqueries is true?

Which four triggering events can cause a trigger to fire? (Choose four.)

You create a DML trigger. For the timing information, which, are valid with a DML trigger? (Choose all that apply.)

Consider this scenario: A procedure X references a view Y that is based on a table Z. Which two statements are true? (Choose two.)

Examine this function: CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID IN PLAYER_BAT_STAT.PLAYER_ID%TYPE) RETURN NUMBER IS V_AVG NUMBER; BEGIN SELECT HITS / AT_BATS INTO V_AVG FRO...

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)...

How can you migrate from a LONG to a LOB data type for a column?

Which table should you query to determine when your procedure was last compiled?

When using a PL/SQL stored package, how is a side effect defined?

Examine this code: CREATE OR REPLACE PROCEDURE load_bfile (p_file_loc IN VARCHAR2) IS V_file BFILE; V_filename VARCHAR2(16); CURSOR emp_cursor IS SELECT employee_id FROM employees...

Which two describe a stored procedure? (Choose two.)

You need to create a trigger to ensure that information in the EMP table is only modified during business hours, Monday to Friday from 9:00am to 5:00pm. Which types of trigger do y...

You need to create a DML trigger. Which five pieces need to be identified? (Choose five.)

You disabled all triggers on the EMPLOYEES table to perform a data load. Now, you need to enable all triggers on the EMPLOYEES table. Which command accomplishes this?

Examine this package: CREATE OR REPLACE PACKAGE discounts IS g_id_number := 7829; g_discount_rate NUMBER := 0.00; PROCEDURE display_price (p_price NUMBER); END discounts; / CREATE...

You want to create procedures, functions and packages. Which privilege do you need?

The creation of which four database objects will cause a DDL trigger to fire? (Choose four.)

Examine this code: CREATE OR REPLACE PROCEDURE add_dept (p_dept_name VARCHAR2 DEFAULT 'placeholder', p_location VARCHAR2 DEFAULT 'Boston') IS BEGIN INSERT INTO departments VALUES (...

Which type of argument passes a value from a procedure to the calling environment?

When creating stored procedures and functions, which construct allows you to transfer values to and from the calling environment?

You need to implement a virtual private database (vpd). In order to have the vpd functionality, a trigger is required to fire when every user initiates a session in the database. W...

Examine the declaration section: DECLARE CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS SELECT empno, ename FROM emp WHERE deptno = p_deptno AND job = p_job; BEGIN ... Which...