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
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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);
- 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.
- 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;
- 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
- 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.
- 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
- 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')
- 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.
- 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
- 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
- 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
- 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
- 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.
- 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;
- 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.
- 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
- 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.
Related Topics
Featured Quizzes
Popular Topics
Back to top