Free Oracle 1Z0-147 Exam Dumps Questions & Answers

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;
Which statement will successfully assign $75,000,000 to the V_MAX_TEAM_SALARY variable
from within a stand-alone procedure?

• A.  BB_PACK.V_MAX_TEAM_SALARY := 75000000;
• B.  This variable cannot be assigned a value from outside the package.
• C.  BB_PACK.ADD_PLAYER.V_MAX_TEAM_SALARY := 75000000;
• D.  V_MAX_TEAM_SALARY := 7500000;

Examine this code:
CREATE OR REPLACE PROCEDURE add_dept
( p_name departments.department_name%TYPE DEFAULT 'unknown',
p_loc departments.location_id%TYPE DEFAULT 1700)
IS
BEGIN
INSERT INTO departments(department_id, department_name,
loclation_id)
VALUES(dept_seq.NEXTVAL,p_name, p_loc);
END add_dept;
/
You created the add_dept procedure above, and you now invoke the procedure in SQL *Plus.
Which four are valid invocations? (Choose four)

• A.  EXECUTE add_dept(p_name=>'Education', 2500)
• B.  EXECUTE add_dept('Education', 2500)
• C.  EXECUTE add_dept('2500', p_loc =>2500)
• D.  EXECUTE add_dept(p_loc=>2500)
• E.  EXECUTE add_dept(p_loc=>2500, p_name=>'Education')

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

• A.  BFILES are stored in the database
• B.  All LOBs have read and write access
• C.  The Oracle9i server performs implicit conversions between CLOBs and VARCHAR2 data types
• D.  The Oracle9i server performs implicit conversions between BLOBs and NUMBER data types
• E.  NCLOB represents a multi-byte character object

Which three are true statements about dependent objects? (Choose three)

• A.  All schema objects have a status that is recorded in the data dictionary.
• B.  Invalid objects cannot be described.
• C.  The Oracle server automatically records dependencies among objects.
• D.  You can view whether an object is valid or invalid in the USER_STATUS data dictionary view.
• E.  An object with status of invalid cannot be a referenced object.
• F.  You can view whether an object is valid or invalid in the USER_OBJECTS data dictionary view.

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?

• A.  You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);
• B.  You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000);.
• C.  You need to execute the command CALCTAX(1000);.
• D.  You need to execute the command EXECUTE FUNCTION calctax;.
• E.  You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX;