PL/SQL Exam

1. Which Oracle supply package allows you to run jobs at use defined times?

DBMS_JOB

DBMS_RUN

DBMS_PIPE

DBMS_SQL

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.

Explanation

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.

2. Which Oracle supply package allows you to run jobs at use defined times?

DBMS_JOB

DBMS_RUN

DBMS_PIPE

DBMS_SQL

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.

Explanation

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.

3. Which type of package construct must be declared both within the package specification and package body?

All package variables.

Boolean variables.

Private procedures and functions.

Public procedures and functions.

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.

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.

4. 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?

The errors are stored in the EMP_PROC.ERR file.

The errors are displayed to the screen when the script file is run.

The errors are stored in the procedure_errors data dictionary view.

YOU need to issue the SHOWERRORS command in the SQL Plusenvironment to see the errors.

YOU need to issue the display errors command in the SQL Plus environmentto see the errors.

not-available-via-ai

Explanation

not-available-via-ai

5. 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?

ERROR at line 1:

Row is: Row is: Row is:

Row is: 1 Row is: 2 Row is: 3

Row is: 4 Row is: 5 Row is: 6

not-available-via-ai

Explanation

not-available-via-ai

6. 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?

EMP_ID LOG_DATE NEW_SALARY ACTION---------- -------- ---------- ----------201 24-SEP-02 13000 NEW SAL202 24-SEP-02 600 NEW SAL

EMP_ID LOG_DATE NEW_SALARY ACTION---------- -------- ---------- ----------201 24-SEP-02 14000 NEW SAL202 24-SEP-02 7000 NEW SAL

EMP_ID LOG_DATE NEW_SALARY ACTION---------- -------- ---------- ----------201 24-SEP-02 NEW SAL202 24-SEP-02 NEW SAL

No rows are inserted

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 SAL

Explanation

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 SAL

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

ROW_UPDATE('SALARY')

UPDATING('SALARY')

CHANGING('SALARY')

COLUMN_UPDATE('SALARY')

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.

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.

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

It produces the output Procedure B calling C

It produces the output Procedure C calling B

It produces a compilation error because procedure C requires a forward declaration

It produces a compilation error because procedure B requires a forward declaration

It produces a compilation error because identified g_value is not declared in procedure A

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.

Explanation

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.

9. Which data dictionary views gives you the names and the source code of all the procedures that you have created?

USER_SOURCE

USER_OBJECTS

USER_PROCEDURES

USER_SUBPROGRAMS

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.

Explanation

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.

10. 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?

DROP Package G_Comm;

DROP Package Specification G_Comm;

DROP Package Body G_Comm;

You cannot accomplish this;

not-available-via-ai

Explanation

not-available-via-ai

11. In order for you to create run a package MAINTAIN_DATA which privilege do you need?

EXECUTE privilege on the MAINTAIN_DATA package.

INVOKE privilege on the MAINTAIN_DATA package.

EXECUTE privilege on the program units in the MAINTAIN_DATApackage.

Object privilege on all of the objects that the MAINTAIN_DATA package is accessing.

Execute privilege on the program units inside the MAINTAIN_DATApackage and execute privilege on the MAINTAIN_DATA package.

not-available-via-ai

Explanation

not-available-via-ai

12. When creating a function, in which section will you typically find the RETURN keyword?

HEADER only

DECLARATIVE

EXECUTABLE and HEADER

DECLARATIVE,EXECUTABLE and EXCEPTION HANDLING

not-available-via-ai

Explanation

not-available-via-ai

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

Index

Cluster /Dimensions /Database links

Package

Function

Synonyms

not-available-via-ai

Explanation

not-available-via-ai

Submit

14. 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?

Query the USER_SOURCE data dictionary view.

Query the USER_PROCEDURES data dictionary view.

Query the USER_DEPENDENCIES data dictionary views.

Set the SQL Plus environment variable trade code=true and run theACCOUNT_TRANSACTION procedure.

Set the SQL Plus environment variable DEPENDENCIES=TRUE and run the Account_Transaction procedure.

not-available-via-ai

Explanation

not-available-via-ai

15. 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?

When the NEW_PRODUCT_VIEW is dropped.

When rows of the product table are updated through SQI Plus.

When the internal logic of the QUERY_PRODUCT procedure is modified.

When a new column that can contain null values is added to the product table.

When a new procedure s created that updates rows in the product table directly.

not-available-via-ai

Explanation

not-available-via-ai

16. Which compiler directive to check the purity level of functions?

PRAGMA SECURITY_LEVEL

PRAGMA SEARIALLY_REUSABLE.

PRAGMA RESTRICT_REFERRENCES.

PRAGMA RESTRICT_PURITY_LEVEL

PRAGMA RESTRICT_FUNCTION_REFERRENCE

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.

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.

17. Why do we use INSTEAD OF trigger?

To perform clean up action when ending a user session

To insert data into view that doesn't accept insert normally

To insert into an audit table when data is updated in sensitive column

To modify data in which DML statement has been issued against an inherently non-updatable view

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.

Explanation

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.

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

Use SQL command delete in the procedure to delete the rows.

Use DBMS_SQL packaged routines in the procedure to delete the rows.

Use DBMS_DML packaged routines in the procedure to delete the rows.

Use DBMSDELETE packaged routines in the procedure to delete the rows.

You cannot have a delete statement without providing a table name beforecompile time.

not-available-via-ai

Explanation

not-available-via-ai

19. The number of cascading triggers is limited by which data base initialization parameter?

CASCADE_TRIGGER_CNT

OPEN_CURSORS

OPEN_TRIGGERS

OPEN_DB_TRIGGERS

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.

Explanation

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.

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

PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNDS, WNPS);

PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNPS);

PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, RNPS, WNPS);

PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG,ALLOW_GROUP_BY);

not-available-via-ai

Explanation

not-available-via-ai

21. You need to recompile several program units you have recently modified through a PL/SQL program. Which statement is true?

You cannot recompile program units using a PL/SQL program.

You can use the DBMS_DDL. REOMPILE package procedure to recompile the program units.

You can use the DBMS_ALTER. COMPILE packaged procedure torecompile the program units.

You can use the DBMS_DDL.ALTER_COMPILE packaged procedure to recompile the program units.

You can use the DBMS_SQL.ALTER_COMPILE packaged procedure to recompile the program units.

not-available-via-ai

Explanation

not-available-via-ai

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

The package specification is dropped.

The package specification is invalidated.

The package is invalidate.

The package body is invalidated.

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.

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.

23. You need to drop a table from within a stored procedure. How do you implement this?

You cannot drop a table from a stored procedure.

Use the DROP command in the procedure to drop the table.

Use the DBMS_DDL packaged routines in the procedure to drop the table.

Use the DBMS_SQL packaged routines in the procedure to drop the table.

Use the DBMS_DROP packaged routines in the procedure to drop the table.

not-available-via-ai

Explanation

not-available-via-ai

24. 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)

Oracle Server records only the signature for each PL/SQL program unit.

Signature mode is the default mode of remote dependency

Oracle server records both the timestamp and the signature for each PL/SQL program unit.

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.

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.

Submit

25. 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)

GRANT SELECT ON ADD_PLAYER TO PUBLIC;

GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;

GRANT INSERT ON PLAYER TO PUBLIC;

GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;

REVOKE INSERT ON PLAYER FROM PUBLIC;

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.

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.

Submit

26. 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)

The syntax of the UPDATE statement is incorrect.

You cannot update a table using a stored procedure.

The format parameter p_bonus_value is declared but is not used anywhere. /The formal parameter p_cutoff cannot have a DEFAULT clause

The declaration of the format parameter p_margin cannot have a DEFAULT clause

The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause

not-available-via-ai

Explanation

not-available-via-ai

Submit

27. When using a packaged function in a query, what is true?

The COMMIT and ROLLBACK commands are allowed in the packaged function.

You can not use packaged functions in a query statement

The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.

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.

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.

28. Which two statements about the overloading feature of packages are true? (Choose two)

Only local or packaged subprograms can be overloaded.

Overloading allows different functions with the same name that differ only in their return types.

Overloading allows different subprograms with the same number, type and order of parameters.

Overloading allows different subprograms with the same name and same number or type of parameters

Overloading allows different subprograms with the same name, but different in either number, type or order of parameters

not-available-via-ai

Explanation

not-available-via-ai

Submit

29. Why do stored procedures and functions improve performance? (Chose two)

They reduce network round trips.

They postpone PL/SQL parsing until run time.

They allow the application to perform high speed processing locally.

They reduce the number of calls to the database and decrease network trafficby bundling commands.

They reduce the number of calls to the database and decrease network trafficby using the local PL/SQL engine.

not-available-via-ai

Explanation

not-available-via-ai

Submit

30. If there is any changes applied to the package specification or body of a stored sub-program which statement is true about it?

Package Specification only requires recompilation

Package body only requires recompilation

Both package & body requires recompilation

Both package & body does not require recompilation.

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.

Explanation

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.

31. 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 compile time error occurs.

A runtime error occurs. The effect of trigger body and the triggering statement are rolled back.

The trigger fires successfully. The update on the EMP table occurs, and data is inserted into theAUDIT_TABLE table.

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.

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.

32. Which of the following statements about LOB are true?

LOB is a database object

LOB represents a data type that is used to store large, unstructured data.

LOB can be stored inside or outside a database

Internal LOB is a category of LOB

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.

Explanation

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.

Submit

33. Which three are true regarding error propagation? (Choose three)

An exception cannot propagate across remote procedure calls.

An exception raised inside a declaration immediately propagates to the current block

The use of the RAISE; statement in an exception handler reprises the current exception

An exception raised inside an exception handler immediately propagates to the enclosing block

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.

Explanation

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.

Submit

34. 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)

If the signature of procedure PROCESS_EMP is modified and successfully recompiles, the EMP table is invalidated

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.

Explanation

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.

Submit

35. Which statement is true?

Stored functions can be called from the SELECT and WHERE clauses only.

Stored functions do not permit calculations that involve database links in a distributed environment.

Stored functions cannot manipulate new types of data, such as longitude and latitude

Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application.

not-available-via-ai

Explanation

not-available-via-ai

Submit

36. Which three statements are true regarding database triggers? (Choose three)

A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database

A database trigger needs to be executed explicitly whenever a particular event takes place.

A database trigger executes implicitly whenever a particular event takes place

not-available-via-ai

Explanation

not-available-via-ai

Submit