0

databases Online Quiz - 173

Description: databases Online Quiz - 173
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0

Implicit cursors are declared for

  1. Some DML,PL/SQL statements

  2. All DML,PL/SQL statements

  3. Only PL/SQL statements

  4. None of the Above


Correct Option: B

Which is the procedure used to issue User-Defined error messages from stored Sub-Programs?

  1. Raise Application procedure

  2. Raise Application Error procedure

  3. Application Error procedure

  4. None of the Above


Correct Option: B

How do you handle an Exception?

  1. Trap it with a Handler

  2. Propagate it to the Calling Environment

  3. a & then b

  4. b & then a


Correct Option: C

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

  1. A comma has been left after the STATS_EXIST_EXCEPTION exception.

  2. The STATS_EXIST_EXCEPTION has not been declared as a number.

  3. The STATS_EXIST_EXCEPTION has not been declared as an exception.

  4. None


Correct Option: C

AI Explanation

To answer this question, let's go through each option and understand why it is correct or incorrect:

Option A) A comma has been left after the STATS_EXIST_EXCEPTION exception.

This option is incorrect because there is no comma after the STATS_EXIST_EXCEPTION exception in the given procedure. Therefore, this is not the reason preventing the successful creation of the procedure.

Option B) The STATS_EXIST_EXCEPTION has not been declared as a number.

This option is incorrect because the STATS_EXIST_EXCEPTION is not required to be declared as a number. It is used as an exception in the procedure.

Option C) The STATS_EXIST_EXCEPTION has not been declared as an exception.

This option is correct because the STATS_EXIST_EXCEPTION has not been declared as an exception in the given procedure. In order to use an exception, it needs to be declared beforehand using the EXCEPTION keyword followed by the exception name.

Option D) None

This option is incorrect because, as explained above, the STATS_EXIST_EXCEPTION has not been declared as an exception in the given procedure, which prevents the successful creation of the procedure.

The correct answer is C. The STATS_EXIST_EXCEPTION has not been declared as an exception. This option is correct because the exception needs to be declared before it can be used in the procedure.

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

  1. To duplicate the functionality of other triggers.

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

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

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


Correct Option: C,D

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

  1. Only local or packaged sub programs can be overloaded.

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

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

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

  5. Overloading allows different subprograms with the same name but different in either number or type or order of parameter.


Correct Option: A,E

AI Explanation

To answer this question, we need to understand the concept of overloading in packages.

Option A) Only local or packaged subprograms can be overloaded. This option is true. Overloading in packages allows us to define multiple subprograms with the same name, but they must be either local or packaged subprograms.

Option B) Overloading allows different functions with the same name that differ only in their return types. This option is false. Overloading in packages does not allow different functions with the same name that differ only in their return types. Overloading is based on the parameters, not the return types.

Option C) Overloading allows different subprograms with the same number, type, and order of the parameter. This option is false. Overloading does not consider the order of the parameters. It only considers the number and types of the parameters.

Option D) Overloading allows different subprograms with the same name and the same number or type of the parameters. This option is false. Overloading allows different subprograms with the same name, but they must have different number or type of parameters.

Option E) Overloading allows different subprograms with the same name but different in either number or type or order of the parameter. This option is true. Overloading in packages allows us to define multiple subprograms with the same name, but they must have different number or type or order of parameters.

Therefore, the correct statements about the overloading feature of packages are A) Only local or packaged subprograms can be overloaded, and E) Overloading allows different subprograms with the same name but different in either number or type or order of the parameter.

What does “REPLACE” indicate in creating procedures?

  1. An existing will be dropped and it will be replaced by the new version.

  2. An existing will be dropped and it will not be replaced by the new version.

  3. No change will occur

  4. There is no such option available in creating procedures.


Correct Option: A

Which two statements about packages are true? (Choose two)

  1. Packages can be nested.

  2. You can pass parameters to packages.

  3. A package is loaded into memory each time it is invoked.

  4. The contents of packages can be shared by many applications.

  5. You can achieve information hiding by making package constructs private.


Correct Option: D,E

Which of the following statements is false with respect to packages?

  1. The package itself cannot be called, parameterized, or nested.

  2. The format of a package is similar to that of a subprogram.

  3. The contents can be shared by many applications once written.

  4. None of the above.


Correct Option: D

Private constructs in the package body are hidden and inaccessible.This is an advantage of a package

  1. True

  2. False


Correct Option: A
  1. CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER

  2. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER) RETURN NUMBER

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

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

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


Correct Option: A,C
  1. A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters.

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

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

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


Correct Option: C

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)

  1. EXECUTE UPD_BAT_STAT;

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

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

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

  5. RUN UPD_BAT_STAT;


Correct Option: A,B

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_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; 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?

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

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

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

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

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

  6. g_comm has a value of 10 at 9:03am for both Jones and Smith.


Correct Option: B

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

  1. Querying with the SELECT * notification.

  2. Declaring variables with the %TYPE attribute.

  3. Specifying schema names when referencing objects.

  4. Declaring records by using the %ROWTYPE attribute.

  5. Specifying package.procedure notation while executing procedures.


Correct Option: A,B,D
  1. A stored procedure is typically written in SQL.

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

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

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

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


Correct Option: B,C
  1. A package.

  2. A stored function.

  3. A stored procedure.

  4. Another database trigger.


Correct Option: C
- Hide questions