1Z0-147 - Program with PL/SQL

Go back to Oracle

Example Questions

How can you migrate from a LONG to a LOB data type for a column? What is a condition predicate in a DML trigger? Examine this code CREATE OR REPLACE PROCEDURE load bfile (p_flle_loc IN VARCHAR2) IS V_file BFILE; v_filename VARCHAR2(16); CURSOR emp_cursor IS SELECT employee_id FROM employees WHERE Job_id = 'IT_PROG' FROM UPDATE BEGIN FOR emp_record IN emp_cursor LOOP v_filename:=emp_record.emplyee_id||;GIF'; V_file:=BFILENMAE(p_file_loc,v_filename); END LOOP; END; / What does the BFILENAME function do? 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 and maximum salary for a given job title. What happens when you try to update a salary value in the EMP table? You create a DML trigger. For the timing information, which are valid with a DML trigger? Which two tables or views track object dependencies? (Choose two). The add_player procedure inserts rows into the PLAYER table. Which command will show this direct dependency? Which part of a database trigger determines the number of times the trigger body executes? 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 delete_emp; PROCEDURE update_emp; FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER; END manage_emps; / CREATE OR REPLACE PACKAGE BODY manage_emps IS PROCEDURE update_sal (p_raise_amt NUMBER) IS BEGIN UPDATE emp SET sal = (sal * p_raise_emt) + sal WHERE empno = v_id; END; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS BEGIN INSERT INTO emp(empno, deptno, sal) VALYES(v_id, p_depntno, p_sal); END insert_emp; PROCEDURE delete_emp IS BEGIN DELETE FROM emp WHERE empno = v_id; END delete_emp; PROCEDURE update_emp IS v_sal NUMBER(10, 2); v_raise NUMBER(10, 2); BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = v_id; IF v_sal < 500 THEN v_raise := .05; ELSIP v_sal < 1000 THEN v_raise := .07; ELSE v_raise := .04; END IF; update_sal(v_raise); END update_emp; FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER IS BEGIN RETURN p_sal * tax_rate; END calc_tax; END manage_emps; / How many public procedures are in the MANAGE_EMPS package? 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 prompt, which statement is true? When creating a function in SQL *Plus, you receive this message: "Warning: Function created with compilation errors." Which command can you issue to see the actual error message? Which three describe a stored procedure? (Choose three.) Which two statements about functions are true? (Choose two.) Examine this package: CREATE OR REPLACE PACKAGE discounts IS g_id NUMBER := 7829; discount_rate NUMBER := 0.00; PROCEDURE display_price (p_price NUMBER); END discounts; / CREATE OR REPLACE PACKAGE BODY discounts IS PROCEDURE display_price (p_price NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE('Discounted '|| TO_CHAR(p_price*NVL(discount_rate, 1))); END display_price; BEGIN / discount_rate := 0.10; END discounts; Which statement is true? Examine this package: CREATE OR REPLACE PACKAGE discounts IS BEGIN g_id NUMBER := 7829; discount_rate NUMBER := 0.00; PROCEDURE display_price(p_price_NUMBER); END discounts; / CREATE OR REPLACE PACKAGE BODY discounts IS PROCEDURE display_price(p_price NUMBER) IS BEGIN DBMS.OUTPUT.PUT LINE('Discounted ' || TO_CHAR(p_price * NVL(discount_rate, 1))); END display_price; BEGIN discount_rate := 0.10; END discounts; / Which statement is true? Examine the trigger heading: CREATE OR REPLACE TRIGGER salary_check BEFORE UPDATE OF sal, job ON emp FOR EACH ROW Under which condition does this trigger fire? There is a CUSTOMER table in a schema that has a public synonym CUSTOMER and you are granted all object privileges on it. You have a procedure PROCESS_CUSTOMER that processes customer information that is in the public synonym CUSTOMER table. You have just created a new table called CUSTOMER within your schema. Which statement is true? Which two statements are true about LOBs? (Choose two.) Examine this package: CREATE OR REPLACE PACKAGE pack_cur IS CURSOR c1 IS SELECT prodid FROM poduct ORDER BY prodid DESC; PROCEDURE proc1; PROCEDURE proc2; END pack_cur; / CREATE OR REPLACE PACKAGE BODY pack_cur IS v_prodid NUMBER; PROCEDURE proc1 IS BEGIN OPEN C1 LOOP FETCH C1 INTO v_prodid; DBMS_OUTPUT.PUT_LINE ( 'Row is: '| | c1%ROWCOUNT); EXIT WHEN c1%ROWCONT >= 3; END LOOP; END procl; PROCEDURE proc2 IS BEGIN LOOP FETCH C1 INTO v_prodid; DBMS_OUTPUT.PUT_LINE ( 'Row is: '| | c1%ROWCOUNT); EXIT WHEN c1%ROWCONT >= 6; END LOOP; CLOSE C1; END proc2; END pack_cur; / The product table has more than 1000 rows. The SQL *Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure PROC1 from SQL *Plus with the command: EXECUTE pack_cur.proc1 What is the output in your session? Which table should you query to determine when your procedure was last compiled? To be callable from a SQL expression, a user-defined function must do what? 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 discount; / CREATE OR REPLACE PACKAGE BODY discounts IS PROCEDURE display_price (p_price NUMBERI) IS BEGIN DBMS_OUTPUT.PUT LINE ( 'Discounted '|| TO_CHAR(p_price*NVL(discount_rate,1))); END discount; BEGIN Discount_rate=0.10; END discounts; / The SOL*Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure DISPLAY_PRICE from SOL*Plus with the command EXECUTE discount.display_price(100); What is the result? 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) Which three statements are true regarding database triggers? (Choose three.) Examine this code: CREATE OR REPLACE PROCEDURE Insert_Dept(p_Location_Id NUMBER) IS v_Dept_Id NUMBER(4); BEGIN INSERT INTO Departments VALUES (5, 'Education', 150, p_Location_Id); SELECT Department_Id INTO v_Dept_Id FROM Employees WHERE Employee_Id = 99999; END Insert_Dept; / CREATE OR REPLACE PROCEDURE insert_location (p_location_id NUMBER, p_city VARCHAR2) IS BEGIN INSERT INTO locations(location_id, city) VALUES (p_location_id, p_city); insert_dept(p_location_id); END insert_location; / You just created the departments, the locations, and the employees table. You did not insert any rows. Next you created both procedures. You new invoke the insert_location procedure using the following command: EXECUTE insert_location (19, 'San Francisco'); What is the result in this EXECUTE command? You want to create procedures, functions and packages. Which privilege do you need? Examine this code: CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT 'placeholder', p_location VARCHAR2 DEFAULT 'Boston') IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept; / Which three are valid calls to the add_dep procedure? (Choose three) Which two tables or views track object dependencies? (Choose two) You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE. What type of trigger do you create? 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 500pm Which types of trigger do you create? (Choose two.) The add_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 this variable? You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a table in your schema. You have granted the EXECUTE privilege to user A on this procedure. When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the operations performed by default? When creating a function, in which section will you typically find the RETURN keyword? 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.) 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 statement accurately call the stored function CALC_SAL? (Choose two) You want to create procedures, functions and packages Which privilege do you need? An internal LOB is _____. Examine this code: CREATE OR REPLACE PACKAGE bonus IS g_max_bonus NUMBER := .99; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER; FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY bonus IS v_salary employees.salary%TYPE; v_bonus employees.commission_pct%TYPE; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employee_id = p_emp_id; RETURN v_bonus * v_salary; END calc_bonus FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employees RETURN v_bonus * v_salary + v_salary; END cacl_salary; END bonus; / Which statement is true? Which two statements about packages are true? (Choose two.) Examine this package: CREATE OR REPLACE PACKAGE BB_PACK IS V_MAX_TEAM_SALARY NUMBER(12,2); PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER); END BB_PACK; / CREATE OR REPLACE PACKAGE BODY BB_PACK IS V_PLAYER_AVG NUMBER(4, 3); PROCEDURE UPD_PLAYER_STAT(V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS WHERE PLAYER_ID = V_ID; COMMIT; VALIDATE_PLAYER_STAT(V_ID); END UPD_PLAYER_STAT; PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER) IS BEGIN INSERT INTO PLAYER (ID, LAST_NAME, SALARY) VALUES (V_ID, V_LAST_NAME, V_SALARY); UPD_PLAYER_STAT(V_ID, 0, 0); END ADD_PLAYER; END BB_PACK; Which statement will successfully assign .333 to the V_PLAYER_AVG variable from a procedure outside the package? Which view displays indirect dependencies, indenting each dependency? 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; END; This procedure must invoke the UPD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure, will successfully invoke the UPD_BAT_STAT procedure? Examine this package: CREATE OR REPLACE PACKAGE pack_cur IS CURSOR c1 IS SELECT prodid FROM product ORDER BY Prodid DESC; PROCEDURE Proc1; PROCEDURE Proc2; END pack_cur; / CREATE OR REPLACE PACKAGE BODY pack_cur IS v_prodif NUMBER; PROCEDURE proc1 IS BEGIN OPEN C1; LOOP FETCH c1 INTO V_prodid; DBMS_OUTPUT.PUT_LINE('Row is ;'||C1% ROWCOUNT); EXIT WHEN C1% ROWCOUNT>=3; END LOOP; END PROC1; PROCEDURE proc2 IS BEGIN LOOP FETCH C1 INTO v_prodid; DBMS_OUTPUT-PUT_LINE ( ' Row is: ' ll c1 %ROWCOUNT); EXIT WHEN C1%ROWCOUNT >= 3; END LOOP; END Procl; / The product table has more than 1000 rows. The SQL*Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure PROC1 from SQL *Plus with the command: EXECUTE pack_cur. PROC1; You then execute the procedure PROC2 from SQL *Plus with the command: EXECUTE pack_cur. PROC2; What is the output in your session from the PROC2 procedure? Which two statements about packages are true? (Choose two) Which type of argument passes a value from a procedure to the calling environment? Which two statements describe the state of a package variable after executing the package in which it is declared? (Choose two) A CALL statement inside the trigger body enables you to call ______. Which statement about triggers is true? When using a packaged function in a query, what is true? 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 (user_id, log_delete) VALUES (USER, SYSDATE); END log_exec; v_name VARCHAR2(20); BEGIN DELETE FROM emp WHERE empno = p_id; log_exec; SELECT ename, empno INTO v_name, v_id FROM emp WHERE empno = p_id; END audit_emp; Why does this code cause an error when compiled?

Study Guides