PL/SQL Exam
(108).jpg "PL/SQL Exam - Quiz")
Try your best. . :)
- 1.
The number of cascading triggers is limited by which data base initialization parameter?
- A.
CASCADE_TRIGGER_CNT
- B.
OPEN_CURSORS
- C.
OPEN_TRIGGERS
- D.
OPEN_DB_TRIGGERS
Correct Answer
B. OPEN_CURSORSExplanation
The correct answer is OPEN_CURSORS. The number of cascading triggers is limited by the OPEN_CURSORS database initialization parameter. This parameter determines the maximum number of open cursors (a cursor is a handle or pointer to a specific memory location that holds the result of a query) that a session can have at any given time. When a trigger fires and cascades to another trigger, it requires an open cursor. If the number of open cursors exceeds the value set for OPEN_CURSORS, it can lead to resource contention and performance issues.Rate this question:
-
- 2.
Which type of package construct must be declared both within the package specification and package body?
- A.
All package variables.
- B.
Boolean variables.
- C.
Private procedures and functions.
- D.
Public procedures and functions.
Correct Answer
D. Public procedures and functions.Explanation
Public procedures and functions must be declared both within the package specification and package body in order to make them accessible and usable by other parts of the program. This ensures that the procedures and functions can be called and executed from outside the package.Rate this question:
-
- 3.
Why do stored procedures and functions improve performance? (Chose two)
- A.
They reduce network round trips.
- B.
They postpone PL/SQL parsing until run time.
- C.
They allow the application to perform high speed processing locally.
- D.
They reduce the number of calls to the database and decrease network traffic by bundling commands.
- E.
They reduce the number of calls to the database and decrease network traffic by using the local PL/SQL engine.
Correct Answer(s)
B. They postpone PL/SQL parsing until run time.
D. They reduce the number of calls to the database and decrease network traffic by bundling commands. -
- 4.
The QUERY_PRODUCT procedure directly references the product table. There is a NEW_PRODUCT_VIEW view created based on the NOT NULL columns of the table. The ADD_PRODUCT procedure updates the table indirectly by the way of NEW_PRODUCT_VIEW view. Under which circumstances does the procedure ADD_PRODUCT get invalidated but automatically get complied when invoked?
- A.
When the NEW_PRODUCT_VIEW is dropped.
- B.
When rows of the product table are updated through SQI Plus.
- C.
When the internal logic of the QUERY_PRODUCT procedure is modified.
- D.
When a new column that can contain null values is added to the product table.
- E.
When a new procedure s created that updates rows in the product table directly.
Correct Answer
D. When a new column that can contain null values is added to the product table. -
- 5.
You need to recompile several program units you have recently modified through a PL/SQL program. Which statement is true?
- A.
You cannot recompile program units using a PL/SQL program.
- B.
You can use the DBMS_DDL. REOMPILE package procedure to recompile the program units.
- C.
You can use the DBMS_ALTER. COMPILE packaged procedure to recompile the program units.
- D.
You can use the DBMS_DDL.ALTER_COMPILE packaged procedure to recompile the program units.
- E.
You can use the DBMS_SQL.ALTER_COMPILE packaged procedure to recompile the program units.
Correct Answer
D. You can use the DBMS_DDL.ALTER_COMPILE packaged procedure to recompile the program units. -
- 6.
In order for you to create run a package MAINTAIN_DATA which privilege do you need?
- A.
EXECUTE privilege on the MAINTAIN_DATA package.
- B.
INVOKE privilege on the MAINTAIN_DATA package.
- C.
EXECUTE privilege on the program units in the MAINTAIN_DATA package.
- D.
Object privilege on all of the objects that the MAINTAIN_DATA package is accessing.
- E.
Execute privilege on the program units inside the MAINTAIN_DATA package and execute privilege on the MAINTAIN_DATA package.
Correct Answer
A. EXECUTE privilege on the MAINTAIN_DATA package. -
- 7.
You have created a script file EMP_PROC.SQL that holds the text to create a procedure PROCESS_EMP. You have compiled the procedure for SQL Plus environment by running the script file EMP_PROC.SQL. What happens if there are syntax errors in the procedure PROCESS_EMP?
- A.
The errors are stored in the EMP_PROC.ERR file.
- B.
The errors are displayed to the screen when the script file is run.
- C.
The errors are stored in the procedure_errors data dictionary view.
- D.
YOU need to issue the SHOWERRORS command in the SQL Plus environment to see the errors.
- E.
YOU need to issue the display errors command in the SQL Plus environment to see the errors.
Correct Answer
D. YOU need to issue the SHOWERRORS command in the SQL Plus environment to see the errors. -
- 8.
You need to create a stored procedure, which deletes rows from a table. The name of the table from which the rows are to be deleted is unknown until run time. Which method do you implement while creating such a procedure?
- A.
Use SQL command delete in the procedure to delete the rows.
- B.
Use DBMS_SQL packaged routines in the procedure to delete the rows.
- C.
Use DBMS_DML packaged routines in the procedure to delete the rows.
- D.
Use DBMSDELETE packaged routines in the procedure to delete the rows.
- E.
You cannot have a delete statement without providing a table name before compile time.
Correct Answer
B. Use DBMS_SQL packaged routines in the procedure to delete the rows. -
- 9.
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; SELECTS HITS/AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE PLAYER_ID_V_ID; RETURN(V_AVG); END; This function must be moved to a package. Which additional statement must be added to the function to allow you to continue using the function in the group by the clause of a select statement?
- A.
PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNDS, WNPS);
- B.
PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNPS);
- C.
PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, RNPS, WNPS);
- D.
PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, ALLOW_GROUP_BY);
Correct Answer
A. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNDS, WNPS); -
- 10.
The programmer view developed a procedure ACCOUNT_TRANSACTION left organization. You were assigned a task to modify this procedure. YOU want to find all the program units invoking the ACCOUNT_TRANSACTION procedure. How can you find this information?
- A.
Query the USER_SOURCE data dictionary view.
- B.
Query the USER_PROCEDURES data dictionary view.
- C.
Query the USER_DEPENDENCIES data dictionary views.
- D.
Set the SQL Plus environment variable trade code=true and run the ACCOUNT_TRANSACTION procedure.
- E.
Set the SQL Plus environment variable DEPENDENCIES=TRUE and run the Account_Transaction procedure.
Correct Answer
C. Query the USER_DEPENDENCIES data dictionary views. -
- 11.
All users currently have the INSERT privileges on the PLAYER table. You want only your users to insert into this table using the ADD_PLAYER procedure. Which two actions must you take? (Choose two)
- A.
GRANT SELECT ON ADD_PLAYER TO PUBLIC;
- B.
GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;
- C.
GRANT INSERT ON PLAYER TO PUBLIC;
- D.
GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;
- E.
REVOKE INSERT ON PLAYER FROM PUBLIC;
Correct Answer(s)
B. GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;
E. REVOKE INSERT ON PLAYER FROM PUBLIC;Explanation
To ensure that only users can insert into the PLAYER table using the ADD_PLAYER procedure, two actions must be taken. First, the privilege to execute the ADD_PLAYER procedure should be granted to the PUBLIC group using the statement "GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;". This allows all users to execute the procedure. Second, the privilege to insert directly into the PLAYER table should be revoked from the PUBLIC group using the statement "REVOKE INSERT ON PLAYER FROM PUBLIC;". This prevents users from bypassing the ADD_PLAYER procedure and directly inserting into the table.Rate this question:
-
- 12.
Which Oracle supply package allows you to run jobs at use defined times?
- A.
DBMS_JOB
- B.
DBMS_RUN
- C.
DBMS_PIPE
- D.
DBMS_SQL
Correct Answer
A. DBMS_JOBExplanation
DBMS_JOB is the correct answer because it is an Oracle supply package that allows users to schedule and run jobs at specific times. This package provides procedures to create, submit, and manage jobs in the database. Users can set the desired timing for the jobs to be executed, making it a suitable choice for running jobs at user-defined times.Rate this question:
-
- 13.
You need to drop a table from within a stored procedure. How do you implement this?
- A.
You cannot drop a table from a stored procedure.
- B.
Use the DROP command in the procedure to drop the table.
- C.
Use the DBMS_DDL packaged routines in the procedure to drop the table.
- D.
Use the DBMS_SQL packaged routines in the procedure to drop the table.
- E.
Use the DBMS_DROP packaged routines in the procedure to drop the table.
Correct Answer
D. Use the DBMS_SQL packaged routines in the procedure to drop the table. -
- 14.
Which data dictionary views gives you the names and the source code of all the procedures that you have created?
- A.
USER_SOURCE
- B.
USER_OBJECTS
- C.
USER_PROCEDURES
- D.
USER_SUBPROGRAMS
Correct Answer
A. USER_SOURCEExplanation
The correct answer is USER_SOURCE because this data dictionary view provides the names and source code of all the procedures that the user has created. It includes information about the procedure's name, line number, and the actual source code. This view is specific to the user and only displays the procedures that they have created, making it the appropriate choice for this question.Rate this question:
-
- 15.
Examine this trigger. CREATE OR REPLACE TRIGGER UPD_TEAM_SALARY AFTER INSERT OR UPDATE OR DELETE ON PLAYER FOR EACH ROW BEGIN UPDATE TEAM SET TOT_SALARY=TOT_SALARY+:NEW SALARY. WHERE ID=:NEW:TEAM_ID; You will be adding additional coat later but for now you want the current block to fire when updated the salary column. Which solution should you use to verify that the user is performing an update on the salary column?
- A.
ROW_UPDATE('SALARY')
- B.
UPDATING('SALARY')
- C.
CHANGING('SALARY')
- D.
COLUMN_UPDATE('SALARY')
Correct Answer
B. UPDATING('SALARY')Explanation
The correct solution to verify that the user is performing an update on the salary column is to use UPDATING('SALARY'). This is because the UPDATING function is used in triggers to check if a specific column is being updated. In this case, the trigger is designed to fire after an insert, update, or delete operation on the PLAYER table, and the UPDATING('SALARY') function will return true if the salary column is being updated.Rate this question:
-
- 16.
Examine this package: CREATE OR REPLACE PACKAGE BB_PACK V_MAX_TEAM_SALARY NUMBER(12,2); PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER); DB_PACK;/ CREATE OR REPLACE PACKAGE BODY BB_PACK IS V_WHERE_AVG NUMBER(4,3); PROCEDURE UPD_PLAYER_STAT (V_ID IN NUMBER, V_AVG 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; An outside procedure VALIDATE_PLAYER_STAT is executed from this package. What will happen when this procedure changes?
- A.
The package specification is dropped.
- B.
The package specification is invalidated.
- C.
The package is invalidate.
- D.
The package body is invalidated.
Correct Answer
D. The package body is invalidated.Explanation
When the outside procedure VALIDATE_PLAYER_STAT is executed from this package, it will invalidate the package body. This means that any dependent objects that rely on the package body will become invalid and need to be recompiled. However, the package specification will remain intact and unaffected.Rate this question:
-
- 17.
Which Oracle supply package allows you to run jobs at use defined times?
- A.
DBMS_JOB
- B.
DBMS_RUN
- C.
DBMS_PIPE
- D.
DBMS_SQL
Correct Answer
A. DBMS_JOBExplanation
DBMS_JOB is an Oracle supply package that allows users to schedule and run jobs at user-defined times. This package provides a way to automate tasks and execute them at specified intervals or on specific dates and times. It is commonly used for running batch jobs, data loading, and other repetitive tasks in Oracle databases.Rate this question:
-
- 18.
CREATE OR REPLACE PACKAGE COMPILE_THIS IS G_VALUE VARCHAR2(100); PROCEDURE A; PROCEDURE B; END COMPILE_THIS; / CREATE OR REPLACE PACKAGE BODY COMPILE_THIS IS PROCEDURE A IS BEGIN G_VALUE := (‘HELLO WORLD’); END A; PROCEDURE B IS BEGIN C; DBMS_OUTPUT. PUT_LINE (‘PROCEDURE B CALLING C’); END B; PROCEDURE C IS BEGIN B; DBMS_OUTPUT. PUT_LINE (‘PROCEDURE C CALLING B’); END; END COMILE_THIS; / Procedure C is a local construct to the package. What happens when this package is compiled?
- A.
It produces the output Procedure B calling C
- B.
It produces the output Procedure C calling B
- C.
It produces a compilation error because procedure C requires a forward declaration
- D.
It produces a compilation error because procedure B requires a forward declaration
- E.
It produces a compilation error because identified g_value is not declared in procedure A
Correct Answer
C. It produces a compilation error because procedure C requires a forward declarationExplanation
When the package is compiled, it will produce a compilation error because procedure C requires a forward declaration. This means that procedure B needs to be declared before procedure C in order for it to be called correctly. Since procedure B is called within procedure C, it needs to be declared first to avoid any compilation errors.Rate this question:
-
- 19.
Which compiler directive to check the purity level of functions?
- A.
PRAGMA SECURITY_LEVEL
- B.
PRAGMA SEARIALLY_REUSABLE.
- C.
PRAGMA RESTRICT_REFERRENCES.
- D.
PRAGMA RESTRICT_PURITY_LEVEL
- E.
PRAGMA RESTRICT_FUNCTION_REFERRENCE
Correct Answer
C. PRAGMA RESTRICT_REFERRENCES.Explanation
The correct answer is PRAGMA RESTRICT_REFERRENCES. This compiler directive is used to check the purity level of functions. It ensures that the functions are not modifying the state of the program or accessing any global variables, making them pure functions. This directive helps in enforcing functional programming principles and improving code maintainability and testability.Rate this question:
-
- 20.
Which of the following statements about LOB are true?
- A.
LOB is a database object
- B.
LOB represents a data type that is used to store large, unstructured data.
- C.
LOB can be stored inside or outside a database
- D.
Internal LOB is a category of LOB
Correct Answer(s)
B. LOB represents a data type that is used to store large, unstructured data.
D. Internal LOB is a category of LOBExplanation
The given answer is correct. LOB stands for Large Object and it represents a data type that is used to store large, unstructured data such as text, images, videos, etc. It can be stored inside or outside a database. Internal LOB is a category of LOB that refers to LOBs that are stored within the database itself, while external LOB refers to LOBs that are stored outside the database.Rate this question:
-
- 21.
You work as an application developer for Dolliver Inc. The company uses an oracle database. You own subprograms that reference to other subprograms on remote locations. Oracle server uses the signature mode of remote dependency in order to manage remote dependencies among the subprograms. Which of the following statements about the signature mode of dependency are true? (Choose two)
- A.
Oracle Server records only the signature for each PL/SQL program unit.
- B.
Using the signature mode prevents the unnecessary recompilation of dependent local procedures, as it allows remote procedures to be recompiled without affecting the dependent local procedures.
- C.
Signature mode is the default mode of remote dependency
- D.
Oracle server records both the timestamp and the signature for each PL/SQL program unit.
Correct Answer(s)
B. Using the signature mode prevents the unnecessary recompilation of dependent local procedures, as it allows remote procedures to be recompiled without affecting the dependent local procedures.
D. Oracle server records both the timestamp and the signature for each PL/SQL program unit.Explanation
The signature mode of dependency in Oracle Server records both the timestamp and the signature for each PL/SQL program unit. This allows for the tracking of changes to the program unit and ensures that dependent local procedures do not need to be recompiled unnecessarily when remote procedures are recompiled. By only recording the signature, rather than the entire program unit, the server can efficiently manage remote dependencies without impacting the performance of the application. Therefore, both statements about the signature mode of dependency are true.Rate this question:
-
- 22.
You work as an application developer for federal Inc. the company uses an oracle database. The database contains a package named G_Comm. You want to remove the package specification from the database while retaining the package body. Which of the following statements will you use to accomplish this?
- A.
DROP Package G_Comm;
- B.
DROP Package Specification G_Comm;
- C.
DROP Package Body G_Comm;
- D.
You cannot accomplish this;
Correct Answer
D. You cannot accomplish this; -
- 23.
If there is any changes applied to the package specification or body of a stored sub-program which statement is true about it?
- A.
Package Specification only requires recompilation
- B.
Package body only requires recompilation
- C.
Both package & body requires recompilation
- D.
Both package & body does not require recompilation.
Correct Answer
A. Package Specification only requires recompilationExplanation
If there are any changes applied to the package specification or body of a stored sub-program, only the package specification requires recompilation. The package body does not need to be recompiled.Rate this question:
-
- 24.
Why do we use INSTEAD OF trigger?
- A.
To perform clean up action when ending a user session
- B.
To insert data into view that doesn't accept insert normally
- C.
To insert into an audit table when data is updated in sensitive column
- D.
To modify data in which DML statement has been issued against an inherently non-updatable view
Correct Answer
D. To modify data in which DML statement has been issued against an inherently non-updatable viewExplanation
An INSTEAD OF trigger is used to modify data in a situation where a DML statement has been issued against an inherently non-updatable view. In this scenario, the view itself cannot be directly modified, but by using an INSTEAD OF trigger, we can intercept the DML statement and modify the underlying data that the view represents. This allows us to make changes to the data indirectly through the trigger, providing a workaround for updating the view.Rate this question:
-
- 25.
When using a packaged function in a query, what is true?
- A.
The COMMIT and ROLLBACK commands are allowed in the packaged function.
- B.
You can not use packaged functions in a query statement
- C.
The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.
- D.
The packaged function can execute and INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery
- E.
The packaged function can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used.
Correct Answer
C. The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.Explanation
When using a packaged function in a query, the function cannot execute any INSERT, UPDATE, or DELETE statements against the table that is being queried. This is because the packaged function is designed to perform specific calculations or operations, rather than modifying the data in the table. The function can only be used to retrieve or manipulate the data within the function itself, and not make any changes to the underlying table.Rate this question:
-
- 26.
CREATE OR REPLACE PACKAGE pack_cur IS CURSOR c1 IS SELECT prodid FROM poduct ORDER BY prodid DESC; PROCEDURE proc1; PROCEDURE proc2; END pack_cur; / CREATE OR REPLACE PACKAGE BODY pack_cur IS v_prodid NUMBER; PROCEDURE proc1 IS BEGIN OPEN C1 LOOP FETCH C1 INTO v_prodid; DBMS_OUTPUT.PUT_LINE ( 'Row is: '| | c1%ROWCOUNT); EXIT WHEN c1%ROWCONT >= 3; END LOOP; END procl; PROCEDURE proc2 IS BEGIN LOOP FETCH C1 INTO v_prodid; DBMS_OUTPUT.PUT_LINE ( 'Row is: '| | c1%ROWCOUNT); EXIT WHEN c1%ROWCONT >= 6; END LOOP; CLOSE C1; END proc2; END pack_cur; / The product table has more than 1000 rows. The SQL *Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure PROC1 from SQL *Plus with the command: EXECUTE pack_cur.proc1 What is the output in your session?
- A.
ERROR at line 1:
- B.
Row is: Row is: Row is:
- C.
Row is: 1 Row is: 2 Row is: 3
- D.
Row is: 4 Row is: 5 Row is: 6
Correct Answer
C. Row is: 1 Row is: 2 Row is: 3 -
- 27.
Procedure PROCESS_EMP references the table EMP. Procedure UPDATE_EMP updates rows if table EMP through procedure PROCESS_EMP. There is a remote procedure QUERY_EMP that queries the EMP table through the local procedure PROCESS_EMP. The dependency mode is set to TIMESTAMP in this session. Which two statements are true? (Choose two)
- A.
If the signature of procedure PROCESS_EMP is modified and successfully recompiles, the EMP table is invalidated
- B.
If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time
- C.
If the signature of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time
- D.
If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the first time.
- E.
If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the second time
Correct Answer(s)
B. If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time
C. If the signature of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first timeExplanation
If the internal logic of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time. Similarly, if the signature of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time. This is because UPDATE_EMP depends on the PROCEDURE_EMP and any changes to the procedure will invalidate the dependent objects.Rate this question:
-
- 28.
You have the following table: CREATE TABLE Emp_log ( Emp_id NUMBER Log_date DATE, New_salary NUMBER, Action VARCHAR (20)); You have the following data in the EMPLOYEES table: EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID ----------- ------------------- ------------ ------------- 100 King 24000 90 101 Kochhar 17000 90 102 De Haan 17000 90 103 Hunold 9000 60 104 Ernst 6000 60 105 Austin 4800 60 106 Pataballa 4800 60 107 Lorentz 4200 60 108 Greenberg 12000 100 201 Hartstein 13000 20 202 Fay 6000 20 You create this trigger: CREATE OR REPLACE TRIGGER Log_salary_increase 36 AFTER UPDATE ON employees FOR EACH ROW WHEN (new.Salary > 1000) BEGIN INSERT INTO Emp_log (Emp_id, Log_date, New_Salary, Action) VALUES (: new.Employee_id, SYSDATE, :new.salary, 'NEW SAL' ); END / Then, you enter the following SQL statement: UPDATE Employee SET Salary = Salary + 1000.0 Where Department_id = 20M What are the result in the EMP_LOG table?
- A.
EMP_ID LOG_DATE NEW_SALARY ACTION ---------- -------- ---------- ---------- 201 24-SEP-02 13000 NEW SAL 202 24-SEP-02 600 NEW SAL
- B.
EMP_ID LOG_DATE NEW_SALARY ACTION ---------- -------- ---------- ---------- 201 24-SEP-02 14000 NEW SAL 202 24-SEP-02 7000 NEW SAL
- C.
EMP_ID LOG_DATE NEW_SALARY ACTION ---------- -------- ---------- ---------- 201 24-SEP-02 NEW SAL 202 24-SEP-02 NEW SAL
- D.
No rows are inserted
Correct Answer
B. EMP_ID LOG_DATE NEW_SALARY ACTION ---------- -------- ---------- ---------- 201 24-SEP-02 14000 NEW SAL 202 24-SEP-02 7000 NEW SALExplanation
The trigger "Log_salary_increase" is created to insert a new row into the "Emp_log" table whenever there is an update on the "employees" table and the new salary is greater than 1000.0. In the given SQL statement, the salary of employees with department_id = 20 is increased by 1000.0. Since the new salary of both employees with employee_id 201 and 202 becomes greater than 1000.0, the trigger is activated and inserts new rows into the "Emp_log" table with the respective employee_id, log_date, new_salary, and action as 'NEW SAL'. Therefore, the result in the EMP_LOG table will be:
EMP_ID LOG_DATE NEW_SALARY ACTION
-------------------------------------
201 24-SEP-02 14000 NEW SAL
202 24-SEP-02 7000 NEW SALRate this question:
-
- 29.
You have an AFTER UPDATE row-level on the table EMP. The trigger queries the EMP table and inserts the updating user's information into the AUDIT_TABLE. What happens when the user updates rows on the EMP table?
- A.
A compile time error occurs.
- B.
A runtime error occurs. The effect of trigger body and the triggering statement are rolled back.
- C.
A runtime error occurs. The effect of trigger body is rolled back, but the update on the EMP table takes place
- D.
The trigger fires successfully. The update on the EMP table occurs, and data is inserted into theAUDIT_TABLE table.
- E.
A runtime error occurs. The update on the EMP table does not take place, but the insert into the AUDIT_TABLE occurs.
Correct Answer
B. A runtime error occurs. The effect of trigger body and the triggering statement are rolled back.Explanation
When the user updates rows on the EMP table, a runtime error occurs. This means that an error is encountered during the execution of the trigger. As a result, both the effect of the trigger body and the triggering statement (the update on the EMP table) are rolled back. This means that any changes made by the trigger and the update statement are undone, ensuring data consistency. Additionally, the trigger successfully inserts data into the AUDIT_TABLE table.Rate this question:
-
- 30.
Which three are true regarding error propagation? (Choose three)
- A.
An exception cannot propagate across remote procedure calls.
- B.
An exception raised inside a declaration immediately propagates to the current block
- C.
The use of the RAISE; statement in an exception handler reprises the current exception
- D.
An exception raised inside an exception handler immediately propagates to the enclosing block
Correct Answer(s)
A. An exception cannot propagate across remote procedure calls.
B. An exception raised inside a declaration immediately propagates to the current block
C. The use of the RAISE; statement in an exception handler reprises the current exceptionExplanation
An exception cannot propagate across remote procedure calls because exceptions are not automatically transmitted across network boundaries. An exception raised inside a declaration immediately propagates to the current block because it is not caught or handled within the declaration. The use of the RAISE; statement in an exception handler reprises the current exception, meaning it raises the same exception again.Rate this question:
-
- 31.
When creating a function, in which section will you typically find the RETURN keyword?
- A.
HEADER only
- B.
DECLARATIVE
- C.
EXECUTABLE and HEADER
- D.
DECLARATIVE,EXECUTABLE and EXCEPTION HANDLING
Correct Answer
C. EXECUTABLE and HEADER -
- 32.
Which two statements about the overloading feature of packages are true? (Choose two)
- A.
Only local or packaged subprograms can be overloaded.
- B.
Overloading allows different functions with the same name that differ only in their return types.
- C.
Overloading allows different subprograms with the same number, type and order of parameters.
- D.
Overloading allows different subprograms with the same name and same number or type of parameters
- E.
Overloading allows different subprograms with the same name, but different in either number, type or order of parameters
Correct Answer(s)
A. Only local or packaged subprograms can be overloaded.
E. Overloading allows different subprograms with the same name, but different in either number, type or order of parameters -
- 33.
Examine this code: CREATE OR REPLACE PROCEDURE set_bonus (p_cutoff IN VARCHAR2 DEFAULT 'WEEKLY'p_employee_id IN employees_employee_id%TYPE p_salary IN employees_salary%TYPE, p_bonus_percent IN OUT NUMBER DEFAULT 1.5, p_margin OUT NUMBER DEFAULT 2, p_bonus_value OUT NUMBER) IS BEGIN UPDATE emp_bonus SET bonus_amount =(p_salary * p_bonus_percent)/p_margin WHERE employee_id = p_employee_id; END set_bonus; / You execute the CREATE PROCEDURE statement above and notice that it fails. What are two reasons why it fails? (Choose two)
- A.
The syntax of the UPDATE statement is incorrect.
- B.
You cannot update a table using a stored procedure.
- C.
The format parameter p_bonus_value is declared but is not used anywhere. /The formal parameter p_cutoff cannot have a DEFAULT clause
- D.
The declaration of the format parameter p_margin cannot have a DEFAULT clause
- E.
The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause
Correct Answer(s)
D. The declaration of the format parameter p_margin cannot have a DEFAULT clause
E. The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause -
- 34.
Which three statements are true regarding database triggers? (Choose three)
- A.
A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database
- B.
A database trigger needs to be executed explicitly whenever a particular event takes place.
- C.
A database trigger executes implicitly whenever a particular event takes place
- D.
A database trigger fires whenever a data event (such as DML) or system event (such as logon, shutdown) occurs on a schema or database.
- E.
With a schema, triggers fire for each event for all users; with a database, triggers fire for each event for that specific user
Correct Answer(s)
A. A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database
D. A database trigger fires whenever a data event (such as DML) or system event (such as logon, shutdown) occurs on a schema or database.
E. With a schema, triggers fire for each event for all users; with a database, triggers fire for each event for that specific user -
- 35.
The creation of which four database objects will cause a DDL trigger to fire? (Choose four)
- A.
Index
- B.
Cluster /Dimensions /Database links
- C.
Package
- D.
Function
- E.
Synonyms
Correct Answer(s)
A. Index
C. Package
D. Function
E. Synonyms -
- 36.
Which statement is true?
- A.
Stored functions can be called from the SELECT and WHERE clauses only.
- B.
Stored functions do not permit calculations that involve database links in a distributed environment.
- C.
Stored functions cannot manipulate new types of data, such as longitude and latitude
- D.
Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application.
Correct Answer(s)
A. Stored functions can be called from the SELECT and WHERE clauses only.
D. Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application. -
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 22, 2023Quiz Edited by
ProProfs Editorial Team -
Feb 16, 2009Quiz Created by
Tikamp
Back to top