OCA And OCP Exam Quiz!

96 Questions | Total Attempts: 1545

SettingsSettingsSettings
OCA And OCP Exam Quiz! - Quiz

Are you familiar with OCA and OCP? Would you like to try this quiz? OCA stands for Oracle Certified Associate, while OCP stands for Oracle Certification Program. OCA is a beginner’s exam, while OCP is the next step toward certification, and this exam is more in-depth when it comes to knowledge of technology. If you want to learn more about OCA and OCP, this is the quiz for you.


Questions and Answers
  • 1. 
    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 FROM PLAYER_BAT_STAT WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which statement will successfully invoke this function in SQL *Plus?
    • A. 

      SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

    • B. 

      EXECUTE CALC_PLAYER_AVG(31);

    • C. 

      CALC_PLAYER('RUTH');

    • D. 

      CALC_PLAYER_AVG(31);

    • E. 

      START CALC_PLAYER_AVG(31)

  • 2. 
    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');

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

      Packages can be nested.

    • B. 

      You can pass parameters to packages.

    • C. 

      A package is loaded into memory each time it is invoked

    • D. 

      The contents of packages can be shared by many applications.

    • E. 

      You can achieve information hiding by making package constructs private

  • 4. 
    Which two programming constructs can be grouped within a package? (Choose two)
    • A. 

      Cursor

    • B. 

      Constant

    • C. 

      Trigger

    • D. 

      Sequence

    • E. 

      View

  • 5. 
    Which two statements describe the state of a package variable after executing the package in which it is declared? (Choose two)
    • A. 

      It persists across transactions within a session.

    • B. 

      It persists from session to session for the same user.

    • C. 

      It does not persist across transaction within a session

    • D. 

      It persists from user to user when the package is invoked

    • E. 

      It does not persist from session to session for the same user.

  • 6. 
    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;

  • 7. 
    Which statement is valid when removing procedures?
    • A. 

      Use a drop procedure statement to drop a standalone procedure.

    • B. 

      Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package specification.

    • C. 

      Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package body

    • D. 

      For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.

  • 8. 
    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_HITSWHERE 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

  • 9. 
    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?
    • A. 

      FOR EACH ROW trigger on the EMP table.

    • B. 

      Statement-level trigger on the EMP table.

    • C. 

      FOR EACH ROW trigger on the AUDIT_TABLE table.

    • D. 

      Statement-level trigger on the AUDIT_TABLE table

    • E. 

      FOR EACH ROW statement-level trigger on the EMP table.

  • 10. 
    Which statements are true? (Choose all that apply)
    • A. 

      If errors occur during the compilation of a trigger, the trigger is still created.

    • B. 

      If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_TRIGGERS data dictionary view to see the compilation errors

    • C. 

      If errors occur during the compilation of a trigger you can use the SHOW ERRORS command within iSQL *Plus to see the compilation errors

    • D. 

      If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_ERRORS data dictionary view to see compilation errors.

  • 11. 
    Which two dictionary views track dependencies? (Choose two)
    • A. 

      USER_SOURCE

    • B. 

      UTL_DEPTREE

    • C. 

      USER_OBJECTS /DBA_DEPENDENT_OBJECTS

    • D. 

      DEPTREE_TEMPTAB

    • E. 

      USER_DEPENDENCIES

  • 12. 
    Given a function CALCTAX: CREATE OR REPLACE FUNCTION calctax (sal NUMBER) RETURN NUMBER IS BEGIN 1z0-147 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 execute the command CALCTAX(1000);.

    • B. 

      You need to execute the command EXECUTE FUNCTION calctax;.

    • C. 

      You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000);.

    • D. 

      You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX;

    • E. 

      You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);

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

      The rows are selected and ordered

    • B. 

      The validity of the SQL statement is established

    • C. 

      An area of memory is established to process the SQL statement.

    • D. 

      The SQL statement is run and the number of rows processed is returned.

    • E. 

      The area of memory established to process the SQL statement is released

  • 14. 
    What part of a database trigger determines the number of times the trigger body executes?
    • A. 

      Trigger type

    • B. 

      Trigger body

    • C. 

      Trigger event

    • D. 

      Trigger timing

  • 15. 
    Examine this code: CREATE OR REPLACE FUNCTION gen_email_name(p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2is v_email_name VARCHAR2(19); BEGIN v_email_home := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) || '@Oracle.com'; UPDATE employees SET email = v_email_nameWHERE employee_id = p_id; RETURN v_email_name; END; You run this SELECT statement: SELECT first_name, last_namegen_email_name(first_name, last_name, 108) EMAIL FROM employees; What occurs?
    • A. 

      Employee 108 has his email name updated based on the return result of the function.

    • B. 

      The statement fails because functions called from SQL expressions cannot perform DML.

    • C. 

      The statement fails because the functions does not contain code to end the transaction.

    • D. 

      The SQL statement executes successfully, because UPDATE and DELETE statements are ignoring in stored functions called from SQL expressions.

    • E. 

      The SQL statement executes successfully and control is passed to the calling environment

  • 16. 
    Which table should you query to determine when your procedure was last compiled?
    • A. 

      USER_PROCEDURES

    • B. 

      USER_PROCS

    • C. 

      USER_OBJECTS

    • D. 

      USER_PLSQL_UNITS

  • 17. 
    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 '08:00' AND '18:00') THEN RAISE_APPLICATION_ERROR (-20500, 'You may insert into the EMPLOYEES table only during business hours.'); END IF; END; / What type of trigger is it?
    • A. 

      DML trigger

    • B. 

      INSTEAD OF trigger

    • C. 

      Application trigger

    • D. 

      System event trigger

    • E. 

      This is an invalid trigger

  • 18. 
    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?
    • A. 

      The value of DISCOUNT_RATE always remains 0.00 in a session.

    • B. 

      The value of DISCOUNT_RATE is set to 0.10 each time the package is invoked in a session.

    • C. 

      The value of DISCOUNT_RATE is set to 1.00 each time the procedure DISPLAY_PRICE is invoked. 1z0-147

    • D. 

      The value of DISCOUNT_RATE is set to 0.10 when the package is invoked for the first time in a session.

  • 19. 
    Examine this code: CREATE OR REPLACE TRIGGER update_emp AFTER UPDATE ON emp BEGIN INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE); END; You issue an UPDATE command in the EMP table that results in changing 10 rows. How many rows are inserted into the AUDIT_TABLE?
    • A. 

      1

    • B. 

      10

    • C. 

      None

    • D. 

      A value equal to the number of rows in the EMP table

  • 20. 
    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?
    • A. 

      Creating the table has no effect and procedure PROCESS_CUSTOMER still accesses data from public synonym CUSTOMER table.

    • B. 

      If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER is invalidated and gives compilation errors. 1z0-147

    • C. 

      If the structure of your CUSTOMER table is entirely different from the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER successfully recompiles and accesses your CUSTOMER table.

    • D. 

      . If the structure of your CUSTOMER table is the same as the public synonym CUSTOMER table then the procedure PROCESS_CUSTOMER successfully recompiles when invoked and accesses your CUSTOMER table.

  • 21. 
    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.

  • 22. 
    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?
    • A. 

      SHOW FUNCTION_ERROR

    • B. 

      SHOW USER_ERRORS

    • C. 

      SHOW ERRORS

    • D. 

      . SHOW ALL_ERRORS

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

      A specific error or any errors occurs

    • B. 

      A database is shut down or started up.

    • C. 

      A specific user or any user logs on or off.

    • D. 

      A user executes a CREATE or an ALTER table statement

    • E. 

      A user executes a SELECT statement with an ORDER BY clause. /A user executes a JOIN statement that uses four or more tables.

  • 24. 
    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 APD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?
    • A. 

      EXECUTE UPD_BAT_STAT(V_ID);

    • B. 

      UPD_BAT_STAT(V_ID

    • C. 

      RUN UPD_BAT_STAT(V_ID);

    • D. 

      START UPD_BAT_STAT(V_ID);

  • 25. 
    Which statement about triggers is true?
    • A. 

      You use an application trigger to fire when a DELETE statement occurs

    • B. 

      You use a database trigger to fire when an INSERT statement occurs

    • C. 

      You use a system event trigger to fire when an UPDATE statement occurs.

    • D. 

      You use INSTEAD OF trigger to fire when a SELECT statement occurs

Back to Top Back to top
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.