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


certifyforall.com

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