Take This Oracle PL/SQL(1z0-147) Dumps Test

Reviewed by Editorial Team
The ProProfs editorial team is comprised of experienced subject matter experts. They've collectively created over 10,000 quizzes and lessons, serving over 100 million users. Our team includes in-house content moderators and subject matter experts, as well as a global network of rigorously trained contributors. All adhere to our comprehensive editorial guidelines, ensuring the delivery of high-quality content.
Learn about Our Editorial Process
| By Ranaguha
R
Ranaguha
Community Contributor
Quizzes Created: 3 | Total Attempts: 7,048
| Attempts: 3,776
SettingsSettings
Please wait...
  • 1/145 Questions

    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 comma has been left after the STATS_EXIST_EXCEPTION exception.
    • The STATS_EXIST_EXCEPTION has not been declared as a number.
    • The STATS_EXIST_EXCEPTION has not been declared as an exception.
    • Only predefined exceptions are allowed in the EXCEPTION section.
Please wait...
About This Quiz

Have you practiced all the PL/SQL queries in Oracle? Take this Oracle PL/SQL(1z0-147) Dumps test to see how well you have practiced the queries and commands. This quiz will help you check your knowledge of queries as well as other facts related to the programming language. With this quiz, you can test your understanding as well as enhance your knowledge See morewith instant feedback. Go for it, and all the best! Try to get a perfect score, as it will boost your confidence.

Take This Oracle PL/SQL(1z0-147) Dumps Test - Quiz

Quiz Preview

  • 2. 

    Under which two circumstances do you design database triggers? (Choose two)

    • To duplicate the functionality of other triggers.

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

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

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

    Correct Answer(s)
    A. To guarantee that when a specific operation is performed, related actions are performed.
    A. For centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement.
    Explanation
    Database triggers are designed under two circumstances:
    1) To guarantee that when a specific operation is performed, related actions are performed. This means that when a certain event occurs in the database, the trigger will automatically execute a set of actions or procedures to ensure data consistency or enforce business rules.
    2) For centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement. This means that the trigger is designed to respond to a specific statement or event, regardless of who or what initiated it, ensuring consistent behavior across the database.

    Rate this question:

  • 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?

    • There is no effect on procedure A, and it runs successfully.

    • Procedure B is invalidated and recompiles when invoked.

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

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

    Correct Answer
    A. Procedure A is invalidated and recompiles for the second time it is invoked.
    Explanation
    When the dependency mode is set to TIMESTAMP, it means that the procedure's timestamp is checked to determine if it needs to be recompiled. In this scenario, procedure A was modified and recompiled at 9 A.M, and procedure B was modified and recompiled at 11 A.M. When procedure A is invoked at 1 P.M, its timestamp will be compared to the timestamp of procedure B, and since procedure B was recompiled after procedure A, procedure A will be invalidated and recompiled for the second time it is invoked.

    Rate this question:

  • 4. 

    What is a condition predicate in a DML trigger?

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

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

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

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

    Correct Answer
    A. A conditional predicate allows you to combine several DBM triggering events into one 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?

    • Row

    • Statement

    • ORACLE FORM trigger

    • Before

    Correct Answer
    A. Row
    Explanation
    The given statement is a row-level trigger because it is triggered after each row is inserted into the PLAYER table. To correct the error, the trigger must be converted to a statement-level trigger. This means that the trigger will be executed once for each statement, regardless of the number of rows affected.

    Rate this question:

  • 6. 

    An internal LOB is _____.

    • A table

    • A column that is a primary key

    • Stored in the database.

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

    Correct Answer
    A. Stored in the database.
    Explanation
    An internal LOB refers to a large object data type, such as text, image, or binary data, that is stored in the database. It is not a table or a column that is a primary key. Additionally, it is not a file stored outside of the database with an internal pointer to it from a database column. Therefore, the correct answer is that an internal LOB is stored in the database.

    Rate this question:

  • 7. 

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

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

    • ALTER TRIGGERS ON TABLE employees DISABLE;

    • ALTER employees DISABLE ALL TRIGGERS;

    • ALTER TABLE employees DISABLE ALL TRIGGERS;

    Correct Answer
    A. ALTER TABLE employees DISABLE ALL TRIGGERS;
    Explanation
    The correct answer is "ALTER TABLE employees DISABLE ALL TRIGGERS;" as this command specifically targets the EMPLOYEES table and disables all triggers associated with it. The other options provided are incorrect as they either do not include the "TABLE" keyword or do not specify disabling all triggers.

    Rate this question:

  • 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?

    • The trigger fires successfully.

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

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

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

    Correct Answer
    A. The trigger fails because a SELECT statement on the table being updated is not allowed.
    Explanation
    The trigger fails because a SELECT statement on the table being updated is not allowed. In Oracle, when a trigger is fired, it cannot contain a SELECT statement on the table that is being updated. This is to prevent any potential conflicts or inconsistencies that may arise from querying the same table that is currently being modified. Therefore, in this scenario, the trigger fails because it violates this restriction.

    Rate this question:

  • 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?

    • DML trigger

    • System event trigger

    • INSTEAD OF trigger

    • Application trigger

    Correct Answer
    A. System event trigger
    Explanation
    A system event trigger needs to be created in order to have the virtual private database (vpd) functionality. This type of trigger fires when every user initiates a session in the database, allowing for the implementation of the vpd.

    Rate this question:

  • 10. 

    Which two program declarations are correct for a stored program unit? (Choose two)

    • CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER

    • CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER) RETURN NUMBER

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

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

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

    Correct Answer(s)
    A. CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER
    A. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER)
    Explanation
    The two correct program declarations for a stored program unit are:

    1) CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER
    This is a correct declaration for a stored function named tax_amt that takes in a parameter p_id of type NUMBER and returns a value of type NUMBER.

    2) CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER)
    This is a correct declaration for a stored procedure named tax_amt that takes in two parameters: p_id of type NUMBER and p_amount of type NUMBER. The p_amount parameter is an OUT parameter, meaning it can be used to return a value to the calling program.

    Rate this question:

  • 11. 

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

    • Index

    • Cluster

    • Package

    • Function

    • Synonyms

    • Dimensions

    • Database links

    Correct Answer(s)
    A. Index
    A. Package
    A. Function
    A. Synonyms
    Explanation
    When an Index, Package, Function, or Synonym is created, it will cause a DDL (Data Definition Language) trigger to fire. DDL triggers are used to perform actions in response to changes in the database schema, such as the creation or modification of objects. In this case, the creation of these four objects will trigger the DDL trigger to execute any associated actions or code.

    Rate this question:

  • 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?

    • The locations, departments, and employees tables are empty.

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

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

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

    Correct Answer
    A. The locations, departments, and employees tables are empty.
    Explanation
    The result of the EXECUTE command is that the locations, departments, and employees tables are empty. This is because the code does not include any INSERT statements to populate the tables with data. Therefore, when the insert_location procedure is invoked, it does not insert any rows into the tables.

    Rate this question:

  • 13. 

    What is true about stored procedures?

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

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

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

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

    Correct Answer
    A. A stored procedure must have at least one executable statement in the procedure body.
    Explanation
    A stored procedure must have at least one executable statement in the procedure body. This means that a stored procedure cannot be empty and must contain at least one statement that can be executed when the procedure is called.

    Rate this question:

  • 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?

    • Change the trigger type to a BEFORE DELETE.

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

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

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

    Correct Answer
    A. Change the trigger to a statement-level trigger by removing FOR EACH ROW.
    Explanation
    The error in the trigger is caused by the presence of the "FOR EACH ROW" clause. This clause is used for row-level triggers, but in this case, the trigger should be a statement-level trigger. By removing the "FOR EACH ROW" clause, the trigger will be corrected and will no longer result in an error.

    Rate this question:

  • 15. 

    The OLD and NEW qualifiers can be used in which type of trigger?

    • Row level DML trigger

    • Row level system trigger

    • Statement level DML trigger

    • Row level application trigger

    • Statement level system trigger

    • Statement level application trigger

    Correct Answer
    A. Row level DML trigger
    Explanation
    The OLD and NEW qualifiers can be used in row level DML triggers. These qualifiers allow access to the old and new values of the affected rows in the trigger body. This is useful when performing actions based on changes made to specific columns in a table. In row level system triggers, statement level DML triggers, row level application triggers, statement level system triggers, and statement level application triggers, the OLD and NEW qualifiers are not available.

    Rate this question:

  • 16. 

    Which view displays indirect dependencies, indenting each dependency?

    • DEPTREE

    • IDEPTREE

    • INDENT_TREE

    • I_DEPT_TREE

    Correct Answer
    A. IDEPTREE
    Explanation
    IDEPTREE is the correct answer because it is the view that displays indirect dependencies, indenting each dependency. This view provides a clear and organized representation of the dependencies by indenting each level, making it easier to understand the relationship between different elements.

    Rate this question:

  • 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?

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

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

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

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

    Correct Answer
    A. The trigger invokes the procedure audit_action each time a user logs on to the database and adds the username to the audit table.
    Explanation
    The given trigger, "watch_it", is an "AFTER LOGON" trigger, which means it will be executed after a user logs on to the database. This trigger calls the "audit_action" procedure and passes the "ora_login_user" as the parameter, which represents the username of the user who logged in. The "audit_action" procedure then inserts the username into the "audit" table. Therefore, the trigger invokes the procedure each time a user logs on to the database and adds the username to the audit table.

    Rate this question:

  • 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)

    • EXECUTE UPD_BAT_STAT;

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

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

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

    • RUN UPD_BAT_STAT;

    Correct Answer(s)
    A. EXECUTE UPD_BAT_STAT;
    A. EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);
    Explanation
    The correct answer is "EXECUTE UPD_BAT_STAT;" and "EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);". These two statements will successfully invoke the procedure in SQL *Plus. The first statement invokes the procedure with the default values for V_ID and V_AB. The second statement invokes the procedure with specific values for V_ID and V_AB.

    Rate this question:

  • 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?

    • DROP gen_email_name;

    • REMOVE gen_email_name;

    • DELETE gen_email_name;

    • DROP FUNCTION gen_email_name;

    Correct Answer
    A. DROP FUNCTION gen_email_name;
    Explanation
    The DROP FUNCTION gen_email_name; statement removes the function gen_email_name from the database. This statement is used to delete a stored function from the database.

    Rate this question:

  • 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?

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

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

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

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

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

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

    Correct Answer
    A. G_comm has a value of 15 at 9:06am for Jones.
    Explanation
    At 9:01am, Jones executes the code "EXECUTE comm_package.g_comm := 15", which sets the value of g_comm to 15. Therefore, at 9:06am, g_comm will still have a value of 15 for Jones.

    Rate this question:

  • 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?

    • V_PLAYER_AVG := .333;

    • BB_PACK.UPD_PLAYER_STAT.V_PLAYER_AVG := .333;

    • BB_PACK.V_PLAYER_AVG := .333;

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

    Correct Answer
    A. This variable cannot be assigned a value from outside of the package.
    Explanation
    The given package BB_PACK does not have a public variable named V_PLAYER_AVG. It only has a private variable V_PLAYER_AVG declared in the package body. Private variables cannot be accessed or assigned a value from outside of the package. Therefore, the correct answer is that the variable cannot be assigned a value from outside of the package.

    Rate this question:

  • 22. 

    What can you do with the DBMS_LOB package?

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

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

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

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

    Correct Answer
    A. Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed.
    Explanation
    The DBMS_LOB package in a database management system (DBMS) allows users to manipulate large objects (LOBs) such as BFILEs. One of the functionalities provided by this package is the ability to close a file that is being accessed. This is done using the DBMS_LOB.FILECLOSE procedure. By closing the file, the user ensures that no further operations can be performed on it, preventing any potential issues or conflicts.

    Rate this question:

  • 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?

    • CALC_TAX

    • INSERT_EMP

    • UPDATE_SAL

    • DELETE_EMP

    • UPDATE_EMP

    • MANAGE_EMPS

    Correct Answer
    A. UPDATE_SAL
    Explanation
    The name of the private procedure in this package is UPDATE_SAL.

    Rate this question:

  • 24. 

    Which two dopes the INSTEAD OF clause in a trigger identify? (Choose two)

    • The view associated with the trigger.

    • The table associated with the trigger.

    • The event associated with the trigger.

    • The package associated with the trigger.

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

    Correct Answer(s)
    A. The view associated with the trigger.
    A. The event associated with the trigger.
    Explanation
    The INSTEAD OF clause in a trigger identifies the view associated with the trigger and the event associated with the trigger.

    Rate this question:

  • 25. 

    Which three are valid ways to minimize dependency failure? (Choose three)

    • Querying with the SELECT * notification.

    • Declaring variables with the %TYPE attribute.

    • Specifying schema names when referencing objects.

    • Declaring records by using the %ROWTYPE attribute.

    • Specifying package.procedure notation while executing procedures.

    Correct Answer(s)
    A. Querying with the SELECT * notification.
    A. Declaring variables with the %TYPE attribute.
    A. Declaring records by using the %ROWTYPE attribute.
    Explanation
    Specifying schema names when referencing objects helps to minimize dependency failure by ensuring that the correct object is referenced, even if there are multiple objects with the same name in different schemas. Querying with the SELECT * notification minimizes dependency failure by retrieving all columns from a table, ensuring that any changes to the table's structure are automatically reflected in the query. Declaring variables with the %TYPE attribute minimizes dependency failure by automatically adjusting the variable's data type if the referenced object's data type is changed. Declaring records by using the %ROWTYPE attribute minimizes dependency failure by automatically adjusting the record's structure if the referenced table's structure is changed.

    Rate this question:

  • 26. 

    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)

    • EXECUTE add_dept(p_loc=>2500)

    • EXECUTE add_dept('Education', 2500)

    • EXECUTE add_dept('2500', p_loc =>2500)

    • EXECUTE add_dept(p_name=>'Education', 2500)

    • EXECUTE add_dept(p_loc=>2500, p_name=>'Education')

    Correct Answer(s)
    A. EXECUTE add_dept(p_loc=>2500)
    A. EXECUTE add_dept('Education', 2500)
    A. EXECUTE add_dept('2500', p_loc =>2500)
    A. EXECUTE add_dept(p_loc=>2500, p_name=>'Education')
    Explanation
    The given code is creating a procedure called "add_dept" with two parameters, "p_name" and "p_loc". The "p_name" parameter has a default value of ".unknown" and the "p_loc" parameter has a default value of 1700. The procedure inserts a new row into the "departments" table using the values passed as parameters.

    The four valid invocations of the "add_dept" procedure are:

    1. EXECUTE add_dept(p_loc=>2500): This invocation passes a value of 2500 for the "p_loc" parameter, using the named parameter syntax.

    2. EXECUTE add_dept('Education', 2500): This invocation passes the values 'Education' and 2500 for the "p_name" and "p_loc" parameters, respectively.

    3. EXECUTE add_dept('2500', p_loc =>2500): This invocation passes the values '2500' and 2500 for the "p_name" and "p_loc" parameters, respectively.

    4. EXECUTE add_dept(p_loc=>2500, p_name=>'Education'): This invocation passes a value of 2500 for the "p_loc" parameter and 'Education' for the "p_name" parameter, using the named parameter syntax.

    Rate this question:

  • 27. 

    Which two describe a stored procedure? (Choose two)

    • A stored procedure is typically written in SQL.

    • A stored procedure is a named PL/SQL block that can accept parameters.

    • A stored procedure is a type of PL/SQL subprogram that performs an action.

    • A stored procedure has three parts: the specification, the body, and the exception handler part.

    • The executable section of a stored procedure contains statements that assigns values, control execution, and return values to the calling environment.

    Correct Answer(s)
    A. A stored procedure is a named PL/SQL block that can accept parameters.
    A. A stored procedure is a type of PL/SQL subprogram that performs an action.
    Explanation
    A stored procedure is a named PL/SQL block that can accept parameters, meaning it can be given a specific name and can take in input values. It is also a type of PL/SQL subprogram that performs an action, indicating that it is designed to execute a specific task or set of tasks.

    Rate this question:

  • 28. 

    To be callable from a SQL expression, a user-defined function must do what?

    • Be stored only in the database.

    • Have both IN and OUT parameters.

    • Use the positional notation for parameters.

    • Return a BOOLEAN or VARCHAR2 data type.

    Correct Answer
    A. Be stored only in the database.
    Explanation
    A user-defined function must be stored only in the database in order to be callable from a SQL expression. This means that the function must be created and saved within the database system, rather than being stored externally or in a different location. Storing the function in the database allows it to be accessed and executed within the SQL environment, making it available for use in SQL expressions.

    Rate this question:

  • 29. 

    Examine the procedure: CREATE OR REPLACE PROCEDURE INSERT TEAM (V_ID in NUMBER,V_CITY in VARCHER2 DEFAULT ‘AUSTIN’V_NAME in VARCHER2) IS BEGIN INSERT INTO TEAM (id, city,name) VALUES (v_id,v_city,v_name); COMMIT; 9 END; Which two statements will successfully invoke this procedure in SQL Plus? (Choose two)

    • EXECUTE INSERT_TEAM;

    • EXECUTE INSERT_TEAM (3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN');

    • EXECUTE INSERT_TEAM (3, 'AUSTIN', 'LONGHORNS');

    • EXECUTE INSERT_TEAM (V_ID := V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN');

    • EXECUTE INSERT_TEAM (3, 'LONGHORNS');

    Correct Answer(s)
    A. EXECUTE INSERT_TEAM (3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN');
    A. EXECUTE INSERT_TEAM (3, 'AUSTIN', 'LONGHORNS');
    Explanation
    The first statement, "EXECUTE INSERT_TEAM (3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN');" will successfully invoke the procedure because it provides the correct values for the parameters V_ID, V_NAME, and V_CITY.

    The second statement, "EXECUTE INSERT_TEAM (3, 'AUSTIN', 'LONGHORNS');" will also successfully invoke the procedure because it provides the correct values for the parameters V_ID, V_CITY, and V_NAME.

    Rate this question:

  • 30. 

    How can you migrate from a LONG to a LOB data type for a column?

    • Use the DBMS_MANAGE_LOB.MIGRATE procedure.

    • Use the UTL_MANAGE_LOB.MIGRATE procedure.

    • Use the DBMS_LOB.MIGRATE procedure.

    • Use the ALTER TABLE command.

    • You cannot migrate from a LONG to a LOB date type for a column.

    Correct Answer
    A. Use the ALTER TABLE command.
    Explanation
    To migrate from a LONG to a LOB data type for a column, you can use the ALTER TABLE command. This command allows you to modify the structure of a table, including changing the data type of a column. By using the ALTER TABLE command, you can alter the column's data type from LONG to LOB, which is a more modern and flexible data type for handling large amounts of data. This migration process ensures that the column can now store and manage LOB data efficiently.

    Rate this question:

  • 31. 

    You need to remove the database trigger BUSINESS_HOUR . Which command do you use to remove the trigger in the SQL *Plus environment?

    • DROP TRIGGER business_hour;

    • DELETE TRIGGER business_hour;

    • REMOVE TRIGGER business_hour;

    • ALTER TRIGGER business_hour REMOVE;

    • DELETE FROM USER_TRIGGERS WHERE TRIGGER_NAME = .BUSINESS_HOUR;

    Correct Answer
    A. DROP TRIGGER business_hour;
    Explanation
    The correct command to remove a trigger in the SQL *Plus environment is "DROP TRIGGER business_hour;". This command removes the trigger named "business_hour" from the database.

    Rate this question:

  • 32. 

    A CALL statement inside the trigger body enables you to call ______.

    • A package.

    • A stored function.

    • A stored procedure.

    • Another database trigger.

    Correct Answer
    A. A stored procedure.
    Explanation
    A CALL statement inside the trigger body enables you to call a stored procedure. This allows you to execute a set of predefined instructions or actions within the trigger, providing more flexibility and functionality in the trigger's logic. By calling a stored procedure, you can encapsulate complex operations or business logic in a separate module, improving code reusability and maintainability.

    Rate this question:

  • 33. 

    You are about to change the arguments of the CALC_TEAM_AVG function. Which dictionary view can you query to determine the names of the procedures and functions that invoke the CALC_TEAM_AVG function?

    • USER_PROC_DEPENDS

    • USER_DEPENDENCIES

    • USER_REFERENCES

    • USER_SOURCE

    Correct Answer
    A. USER_DEPENDENCIES
    Explanation
    You can query the USER_DEPENDENCIES dictionary view to determine the names of the procedures and functions that invoke the CALC_TEAM_AVG function. The USER_DEPENDENCIES view provides information about dependencies between database objects, including procedures and functions. By querying this view, you can identify which objects are dependent on the CALC_TEAM_AVG function and therefore may need to be updated if the arguments of the function are changed.

    Rate this question:

  • 34. 

    You create a DML trigger. For the timing information, which is valid with a DML trigger?

    • DURING

    • INSTEAD

    • ON SHUTDOWN

    • BEFORE

    • ON STATEMENT EXECUTION

    Correct Answer
    A. BEFORE
    Explanation
    A DML trigger can be set to execute either before or after a DML operation is performed. In this case, the correct answer is "BEFORE," indicating that the trigger will be executed before the DML operation takes place. This allows the trigger to modify or validate the data before it is inserted, updated, or deleted in the database table.

    Rate this question:

  • 35. 

    Which type of argument passes a value from a procedure to the calling environment?

    • VARCHAR2

    • BOOLEAN

    • OUT

    • IN

    Correct Answer
    A. OUT
    Explanation
    The type of argument that passes a value from a procedure to the calling environment is the "OUT" argument. When a procedure is called with an OUT argument, it allows the procedure to modify the value of the argument and pass it back to the calling environment. This allows for two-way communication between the procedure and the calling environment.

    Rate this question:

  • 36. 

    You want to create a PL/SQL block of code that calculates discounts on customer orders. This code will be invoked from several places, but only within the program unit ORDERTOTAL. What is the most appropriate location to store the code that calculates the discounts?

    • A stored procedure on the server.

    • A block of code in a PL/SQL library.

    • A standalone procedure on the client machine.

    • A block of code in the body of the program unit ORDERTOTAL.

    • A local subprogram defined within the program unit ORDERTOTAL.

    Correct Answer
    A. A local subprogram defined within the program unit ORDERTOTAL.
    Explanation
    A local subprogram defined within the program unit ORDERTOTAL is the most appropriate location to store the code that calculates the discounts. This ensures that the code is easily accessible and can be invoked from within the program unit ORDERTOTAL. Storing the code as a local subprogram also promotes modularity and encapsulation, as it keeps the code closely related to the program unit it is being used in.

    Rate this question:

  • 37. 

    Which statement about triggers is true?

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

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

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

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

    Correct Answer
    A. You use a database trigger to fire when an INSERT statement occurs.
  • 38. 

    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?

    • EXECUTE UPD_BAT_STAT(V_ID);

    • UPD_BAT_STAT(V_ID);

    • RUN UPD_BAT_STAT(V_ID);

    • START UPD_BAT_STAT(V_ID);

    Correct Answer
    A. UPD_BAT_STAT(V_ID);
    Explanation
    The correct statement to successfully invoke the UPD_BAT_STAT procedure in the given procedure is "UPD_BAT_STAT(V_ID);". This statement directly calls the UPD_BAT_STAT procedure with the parameter V_ID.

    Rate this question:

  • 39. 

    Which four triggering events can cause a trigger to fire? (Choose four)

    • A specific error or any errors occurs.

    • A database is shut down or started up.

    • A specific user or any user logs on or off.

    • A user executes a CREATE or an ALTER table statement

    • A user executes a SELECT statement with an ORDER BY clause.

    • A user executes a JOIN statement that uses four or more tables.

    Correct Answer(s)
    A. A specific error or any errors occurs.
    A. A database is shut down or started up.
    A. A specific user or any user logs on or off.
    A. A user executes a CREATE or an ALTER table statement
    Explanation
    A trigger can be fired when a specific error or any errors occur, when a database is shut down or started up, when a specific user or any user logs on or off, and when a user executes a CREATE or an ALTER table statement. These triggering events can activate a trigger and perform certain actions or operations based on the defined logic within the trigger.

    Rate this question:

  • 40. 

    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?

    • SHOW FUNCTION_ERROR

    • SHOW USER_ERRORS

    • SHOW ERRORS

    • SHOW ALL_ERRORS

    Correct Answer
    A. SHOW ERRORS
    Explanation
    To see the actual error message when creating a function in SQL *Plus, you can issue the command "SHOW ERRORS". This command will display the compilation errors that occurred during the creation of the function, allowing you to identify and address any issues in your code.

    Rate this question:

  • 41. 

    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 11 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?

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

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

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

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

    Correct Answer
    A. 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.
    Explanation
    If the structure of the newly created CUSTOMER table is the same as the public synonym CUSTOMER table, the procedure PROCESS_CUSTOMER will successfully recompile and access the data from the newly created table. This is because the procedure is designed to process customer information from the public synonym CUSTOMER table, and if the structure of the new table matches that of the public synonym, the procedure can still access the data without any compilation errors.

    Rate this question:

  • 42. 

    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?

    • V_MAX_TEAM_SALARY := 7500000;

    • BB_PACK.ADD_PLAYER.V_MAX_TEAM_SALARY := 75000000;

    • BB_PACK.V_MAX_TEAM_SALARY := 75000000;

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

    Correct Answer
    A. BB_PACK.V_MAX_TEAM_SALARY := 75000000;
    Explanation
    The correct answer is "BB_PACK.V_MAX_TEAM_SALARY := 75000000;". This statement successfully assigns the value of $75,000,000 to the V_MAX_TEAM_SALARY variable within the BB_PACK package.

    Rate this question:

  • 43. 

    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 ?

    • 1

    • 10

    • None

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

    Correct Answer
    A. 1
    Explanation
    The code provided is a trigger that is executed after an update on the emp table. It inserts a row into the audit_table with the current user and date. Since the trigger is executed after each update, regardless of the number of rows updated, only one row will be inserted into the audit_table.

    Rate this question:

  • 44. 

    Examine this package CREATE OR REPLACE PACKAGE discounts IS G_ID NUMBER:=7839; DISCOUNT_RATE NUMBER 0. 00; PROCEDURE DISPLAY_PRICE (V_PRICE NUMBER); END DISCOUNTS; / CREATE OR REPLACE PACKAGE BODY discounts IS PROCEDURE DISPLAY_PRICE (V_PRICE_NUMBER) 12 IS BEGIN DBMS_OUTPUT.PUT_LINE(‘DISCOUNTED||2_4 (V_PRICE*NVL(DISCOUNT_RATE, 1))) END DISPLAY_PRICE; BEGIN DISCOUNT_RATE;=0. 10; END DISCOUNTS; / Which statement is true?

    • The value of DISCOUNT_RATE always remain 0. 00 in a session.

    • The value of DISCOUNT_RATE is set to 0. 10 each time the package are invoked in a session.

    • The value of DISCOUNT_RATE is set to 1 each time the procedure DISPLAY_PRICE is invoked.

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

    Correct Answer
    A. The value of DISCOUNT_RATE is set to 0. 10 when the package is invoked for first time in a session.
    Explanation
    The correct answer is that the value of DISCOUNT_RATE is set to 0.10 when the package is invoked for the first time in a session. This can be inferred from the package body where the BEGIN statement sets the value of DISCOUNT_RATE to 0.10.

    Rate this question:

  • 45. 

    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?

    • DML trigger

    • INSTEAD OF trigger

    • Application trigger

    • System event trigger

    • This is an invalid trigger.

    Correct Answer
    A. This is an invalid trigger.
  • 46. 

    Which table should you query to determine when your procedure was last compiled?

    • USER_PROCEDURES

    • USER_PROCS

    • USER_OBJECTS

    • USER_PLSQL_UNITS

    Correct Answer
    A. USER_OBJECTS
    Explanation
    To determine when a procedure was last compiled, you should query the USER_OBJECTS table. This table contains information about all objects owned by the current user, including procedures. The LAST_DDL_TIME column in the USER_OBJECTS table indicates the timestamp of the last compile or modification of the object. Therefore, by querying this table and filtering for the specific procedure, you can retrieve the last compilation time of the procedure.

    Rate this question:

  • 47. 

    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); BEGIN v_email_home := 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; You run this SELECT statement: SELECT first_name, last_name gen_email_name(first_name, last_name, 108) EMAIL FROM employees; What occurs?

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

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

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

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

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

    Correct Answer
    A. The statement fails because functions called from SQL expressions cannot perform DML.
  • 48. 

    What part of a database trigger determines the number of times the trigger body executes?

    • Trigger type

    • Trigger body

    • Trigger event

    • Trigger timing

    Correct Answer
    A. Trigger type
    Explanation
    The trigger type determines the number of times the trigger body executes. Different trigger types have different behaviors when it comes to execution. For example, a trigger type of "before insert" will execute the trigger body once for each row being inserted, while a trigger type of "after update" will execute the trigger body once for each row being updated. Therefore, the trigger type is what determines the frequency of execution for the trigger body.

    Rate this question:

  • 49. 

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

    • The rows are selected and ordered.

    • The validity of the SQL statement is established.

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

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

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

    Correct Answer
    A. The SQL statement is run and the number of rows processed is returned.
    Explanation
    During the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations, the SQL statement is executed and the number of rows processed is returned. This means that the actual operation of inserting, updating, or deleting data from the database is performed, and the result of this operation is the number of rows that were affected or processed.

    Rate this question:

Quiz Review Timeline (Updated): Aug 28, 2023 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Aug 28, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Jan 21, 2013
    Quiz Created by
    Ranaguha

Related Topics

Featured Quizzes

Which Sonic Character Are You? Which Sonic Character Are You?
Harry Potter House Quiz: Which Hogwarts House Do You Belong To? Harry Potter House Quiz: Which Hogwarts House Do You Belong To?
What Ethnicity Do I Look Like? What Ethnicity Do I Look Like?
Basketball Quiz Questions And Answers Basketball Quiz Questions And Answers
Best Friend Quiz: Are You Really Best Friends? Best Friend Quiz: Are You Really Best Friends?
What Is My Talent? What Is My Talent?
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.