You will get the detailed explanation whenever you encounter troubles in your 1z0-147 study. Testking supply you the 1z0-147 oracle9i program with pl/sql tips legibly inside the 1z0-147 dumps. All these are significantly conserving your own expenditures to get familiar with the Oracle classes.
2021 Jun 1z0-147 exam question
Q41. 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)
A. add_dept;
B. add_dept('Accounting');
C. add_dept(, 'New York');
D. add_dept(p_location=>'New York');
Answer: ABD
Q42. 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?
A. An insert statement is not allowed in a subprogram declaration.
B. Procedure LOG_EXEC should be declared before any identifiers.
C. Variable v_name should be declared before declaring the LOG_EXEC procedure.
D. The LOG_EXEC procedure should be invoked as EXECUTE log_exec with the AUDIT_EMP procedure.
Answer: C
Q43. Which statement is true about removing packages?
A. You must remove the package body first
B. Removing a package specification removes the body too
C. Removing the package body removes the specification too
D. You must remove both the package body and the specification separately
E. Removing a package specification removes all stand alone stored functions named in the specification
Answer: B
Q44. Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased?
A. ALTER TABLE emp ADD
CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1);
B. CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN (new.sal < old.sal OR
new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease
salary not increase by more than 10%');
END;
C. CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal ON emp
WHEN (new.sal < old.sal OR
new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease
salary not increase by more than 10%');
END;
D. CREATE OR REPLACE TRIGGER check_sal
AFTER UPDATE OR sal ON emp
WHEN (new.sal < old.sal OR
-new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease
salary not increase by more than 10%');
END;
Answer: B
Q45. You want to create procedures, functions and packages Which privilege do you need?
A. EXECUTE CODE object privilege
B. CREATE ANY CODE object privilege
C. CREATE PACKAGE system privilege
D. CREATE PROCEDURE system privilege
E. CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE system privileges
Answer: D
Far out 1z0-147 test questions:
Q46. 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
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;
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;
You make a change to the body of the BB_PACK package. The BB_PACK body is recompiled.
What happens if the stand alone procedure VALIDATE_PLAYER_STAT references this package?
A. VALIDATE_PLAYER_STAT cannot recompile and must be recreated.
B. VALIDATE_PLAYER_STAT is not invalidated.
C. VALDIATE_PLAYER_STAT is invalidated.
D. VALIDATE_PLAYER_STAT and BB_PACK are invalidated.
Answer: B
Q47. Which two statements about packages are true? (Choose two)
A. Both the specification and body are required components of a package.
B. The package specification is optional, but the package body is required.
C. The package specification is required, but the package body is optional.
D. The specification and body of the package are stored together in the database.
E. The specification and body of the package are stored separately in the database.
Answer: CE
Q48. You create a DML trigger. For the timing information, which is valid with a DML trigger?
A. DURING
B. INSTEAD
C. ON SHUTDOWN
D. BEFORE
E. ON STATEMENT EXECUTION
Answer: D
Q49. 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?
A. SYS privileges
B. Your privileges
C. Public privileges
D. User A's privileges
E. User A cannot execute your procedure that has dynamic SQL.
Answer: B
Q50. Which two statements about object dependencies are accurate? (Choose two.)
A. When referencing a package procedure or function from a stand-alone procedure or function, if the package specification changes, the package body remains valid but the stand-alone procedure becomes invalid
B. When referencing a package procedure or function from a stand-alone procedure or function, if the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct remains valid.
C. When referencing a package procedure or function from a stand-alone procedure or function, if the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct becomes invalid
D. When referencing a package procedure or function from a stand-alone procedure or function, If the package specification changes, the stand-alone procedure referencing a package construct as well as the package body become invalid
Answer: BD