PL/SQL(1z0-147) Dumps

149 Questions | Total Attempts: 2756

SettingsSettingsSettings
Please wait...
Pl SQL Quizzes & Trivia

Questions and Answers
  • 1. 
    Examine this procedure: CREATE OR REPLACE PROCEDURE  DELETE_PLAYER(V_IDIN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID =  V_ID EXCEPTION WHEN STATS_EXI TS_EXCEPTI ON THEN DBMS_OUTPUT.  PUT_LINE(Cannotdeletethisplayer, childrecordsexistin PLAYER_BAT_STAT table);END;  What prevents this procedure from being created successfully? 
    • A. 

      A comma has been left after the STATS_EXIST_EXCEPTION exception.

    • B. 

      The STATS_EXIST_EXCEPTION has not been declared as a number.

    • C. 

      The STATS_EXIST_EXCEPTION has not been declared as an exception.

    • D. 

      Only predefined exceptions are allowed in the EXCEPTION section.

  • 2. 
    Under which two circumstances do you design database triggers? (Choose two)
    • A. 

      To duplicate the functionality of other triggers.

    • B. 

      To replicate built-in constraints in the Oracle server such as primary key and foreign key.

    • C. 

      To guarantee that when a specific operation is performed, related actions are performed.

    • D. 

      For centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement.

  • 3. 
    Local procedure A calls remote procedure B. Procedure B was compiled at 8 A.M. Procedure A was modified and recompiled at 9 A.M. Remote procedure B was later modified and recompiled at 11 A.M. The dependency mode is set to TI MESTAMP. What happens when procedure A is invoked at 1 P.M?
    • A. 

      There is no affect on procedure A and it runs successfully.

    • B. 

      Procedure B is invalidated and recompiles when invoked.

    • C. 

      Procedure A is invalidated and recompiles for the first time it is invoked.

    • D. 

      Procedure A is invalidated and recompiles for the second time it is invoked.

  • 4. 
    What is a condition predicate in a DML trigger?
    • A. 

      A conditional predicate allows you to specify a WHEN-LOGGING-ON condition in the trigger body.

    • B. 

      A conditional predicate means you use the NEW and OLD qualifiers in the trigger body as a condition.

    • C. 

      A conditional predicate allows you to combine several DBM triggering events into one in the trigger body.

    • D. 

      A conditional predicate allows you to specify a SHUTDOWN or STARTUP condition in the trigger body.

  • 5. 
    This statement fails when executed: CREATE OR REPLACE TRI GGER CALC_TEAM_AVG AFTER I NSERT ON PLAYER BEGIN INSERT INTO PLAYER_BATSTAT ( PLAYER_I D, SEASON_YEAR, AT_BATS, HI TS) VALUES ( : NEW. I D, 1 997, 0, 0) ; END; To which type must you convert the trigger to correct the error?
    • A. 

      Row

    • B. 

      Statement

    • C. 

      ORACLE FORM trigger

    • D. 

      Before

  • 6. 
    An internal LOB is _____.
    • A. 

      A table

    • B. 

      A column that is a primary key

    • C. 

      Stored in the database.

    • D. 

      A file stored outside of the database, with an internal pointer to it from a database column.

  • 7. 
    You need to disable all triggers on the EMPLOYEES table. Which command accomplishes this?
    • A. 

      None of these commands; you cannot disable multiple triggers on a table in one command.

    • B. 

      ALTER TRIGGERS ON TABLE employees DISABLE;

    • C. 

      ALTER employees DISABLE ALL TRIGGERS;

    • D. 

      ALTER TABLE employees DISABLE ALL TRIGGERS;

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

      The trigger fires successfully.

    • B. 

      The trigger fails because it needs to be a row level AFTER UPDATE trigger.

    • C. 

      The trigger fails because a SELECT statement on the table being updated is not allowed.

    • D. 

      The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger.

  • 9. 
    You need to implement a virtual private database (vpd). In order to have the vpd functionality, a trigger is required to fire when every user initiates a session in the database. What type of trigger needs to be created?
    • A. 

      DML trigger

    • B. 

      System event trigger

    • C. 

      INSTEAD OF trigger

    • D. 

      Application trigger

  • 10. 
    Which two program declarations are correct for a stored program unit? (Choose two)
    • A. 

      CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER

    • B. 

      CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER) RETURN NUMBER

    • C. 

      CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER)

    • D. 

      CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER(10,2)

    • E. 

      CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER(10, 2))

  • 11. 
    The creation of which four database objects will cause a DDL trigger to fire? (Choose four)
    • A. 

      Index

    • B. 

      Cluster

    • C. 

      Package

    • D. 

      Function

    • E. 

      Synonyms

    • F. 

      Dimensions

    • G. 

      Database links

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

      The locations, departments, and employees tables are empty.

    • B. 

      The departments table has one row. The locations and the employees tables are empty.

    • C. 

      The location table has one row. The departments and the employees tables are empty.

    • D. 

      The locations table and the departments table both have one row. The employees table is empty.

  • 13. 
    What is true about stored procedures?
    • A. 

      A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters.

    • B. 

      A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification.

    • C. 

      A stored procedure must have at least one executable statement in the procedure body.

    • D. 

      A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters.

  • 14. 
    Examine the trigger: CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON Emp_tab FOR EACH ROW DELCARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM Emp_tab; DMBS_OUTPUT.PUT_LINE( . There are now . || a || . employees, .); END; This trigger results in an error after this SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499; How do you correct the error?
    • A. 

      Change the trigger type to a BEFORE DELETE.

    • B. 

      Take out the COUNT function because it is not allowed in a trigger.

    • C. 

      Remove the DBMS_OUTPUT statement because it is not allowed in a trigger.

    • D. 

      Change the trigger to a statement-level trigger by removing FOR EACH ROW.

  • 15. 
    The OLD and NEW qualifiers can be used in which type of trigger?
    • A. 

      Row level DML trigger

    • B. 

      Row level system trigger

    • C. 

      Statement level DML trigger

    • D. 

      Row level application trigger

    • E. 

      Statement level system trigger

    • F. 

      Statement level application trigger

  • 16. 
    Which view displays indirect dependencies, indenting each dependency?
    • A. 

      DEPTREE

    • B. 

      IDEPTREE

    • C. 

      INDENT_TREE

    • D. 

      I_DEPT_TREE

  • 17. 
    Examine this code: CREATE OR REPLACE PROCEDURE audit_action (p_who VARCHAR2) AS BEGIN INSERT INTO audit(schema_user) VALUES(p_who); END audit_action; / CREATE OR REPLACE TRIGGER watch_it AFTER LOGON ON DATABASE CALL audit_action(ora_login_user) / What does this trigger do?
    • A. 

      The trigger records an audit trail when a user makes changes to the database.

    • B. 

      The trigger marks the user as logged on to the database before an audit statement is issued.

    • C. 

      The trigger invoked the procedure audit_action each time a user logs on to his/her schema and adds the username to the audit table.

    • D. 

      The trigger invokes the procedure audit_action each time a user logs on to the database and adds the username to the audit table.

  • 18. 
    Examine this procedure: CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID IN NUMBER DEFAULT 10, V_AB IN NUMBER DEFAULT 4) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB WHERE PLAYER_ID = V_ID; COMMIT; END; Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)
    • A. 

      EXECUTE UPD_BAT_STAT;

    • B. 

      EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);

    • C. 

      EXECUTE UPD_BAT_STAT(31, 'FOUR', 'TWO');

    • D. 

      UPD_BAT_STAT(V_AB=>10, V_ID=>31);

    • E. 

      RUN UPD_BAT_STAT;

  • 19. 
    Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2 IS v_email_name VARCHAR2(19); 6 BEGIN v_email_name := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) || [email protected] .; UPDATE employees SET email = v_email_name WHERE employee_id = p_id; RETURN v_email_name; END; Which statement removes the function?
    • A. 

      DROP gen_email_name;

    • B. 

      REMOVE gen_email_name;

    • C. 

      DELETE gen_email_name;

    • D. 

      DROP FUNCTION gen_email_name;

  • 20. 
    Examine this code: CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(p_comm IN NUMBER); END comm_package; / User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which statement is true?
    • A. 

      G_comm has a value of 15 at 9:06am for Smith.

    • B. 

      G_comm has a value of 15 at 9:06am for Jones.

    • C. 

      G_comm has a value of 20 at 9:06am for both Jones and Smith.

    • D. 

      G_comm has a value of 15 at 9:03 am for both Jones and Smith.

    • E. 

      G_comm has a value of 10 at 9:06am for both Jones and Smith.

    • F. 

      G_comm has a value of 10 at 9:03am for both Jones and Smith

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

      V_PLAYER_AVG := .333;

    • B. 

      BB_PACK.UPD_PLAYER_STAT.V_PLAYER_AVG := .333;

    • C. 

      BB_PACK.V_PLAYER_AVG := .333;

    • D. 

      This variable cannot be assigned a value from outside of the package.

  • 22. 
    What can you do with the DBMS_LOB package?
    • A. 

      Use the DBMS_LOB.WRITE procedure to write data to a BFILE.

    • B. 

      Use the DBMS_LOB.BFILENAME function to locate an external BFILE.

    • C. 

      Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE.

    • D. 

      Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed.

  • 23. 
    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; ELSIF 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; / What is the name of the private procedure in this package?
    • A. 

      CALC_TAX

    • B. 

      INSERT_EMP

    • C. 

      UPDATE_SAL

    • D. 

      DELETE_EMP

    • E. 

      UPDATE_EMP

    • F. 

      MANAGE_EMPS

  • 24. 
    Which two dopes the INSTEAD OF clause in a trigger identify? (Choose two)
    • A. 

      The view associated with the trigger.

    • B. 

      The table associated with the trigger.

    • C. 

      The event associated with the trigger.

    • D. 

      The package associated with the trigger.

    • E. 

      The statement level or for each row association to the trigger.

  • 25. 
    Which three are valid ways to minimize dependency failure? (Choose three)
    • A. 

      Querying with the SELECT * notification.

    • B. 

      Declaring variables with the %TYPE attribute.

    • C. 

      Specifying schema names when referencing objects.

    • D. 

      Declaring records by using the %ROWTYPE attribute.

    • E. 

      Specifying package.procedure notation while executing procedures.

Back to Top Back to top