0

databases Online Quiz - 233

Description: databases Online Quiz - 233
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0
  1. a) CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER

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

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

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

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


Correct Option: A,C
  1. a) g_comm has a value of 15 at 9:06am for Smith.

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

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

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

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


Correct Option: B
  1. a) A stored procedure is typically written in SQL.

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

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

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

  5. e) 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) VARCHAR2

  2. b) BOOLEAN

  3. c) OUT

  4. d) IN


Correct Option: C
Explanation:

To solve this question, the user needs to be familiar with the different types of arguments that can be passed between a procedure and the calling environment.

A. VARCHAR2 is a data type used to store character strings in Oracle.

B. BOOLEAN is a data type used to store logical values (TRUE or FALSE) in Oracle.

C. OUT is a parameter mode that is used to pass values from a procedure to the calling environment.

D. IN is a parameter mode that is used to pass values from the calling environment to a procedure.

Therefore, the correct answer is:

The Answer is: C

  1. a) SHOW FUNCTION_ERROR

  2. b) SHOW USER_ERRORS

  3. c) SHOW ERRORS

  4. d) SHOW ALL_ERRORS


Correct Option: C
Explanation:

To solve this question, the user needs to know how to check for the errors in an SQL function.

The correct answer is:

C. SHOW ERRORS

Explanation:

When a function is created with compilation errors, the SHOW ERRORS command can be issued to see the actual error message. This command displays the compilation errors of the last CREATE or REPLACE FUNCTION or PACKAGE statement that was executed in the current session. Using this command can help identify and fix any syntax errors or other issues with the function code.

Option A is incorrect because there is no SHOW FUNCTION_ERROR command in SQL *Plus.

Option B is incorrect because SHOW USER_ERRORS is not a valid command in SQL *Plus.

Option D is incorrect because SHOW ALL_ERRORS is not a valid command in SQL *Plus.

  1. a) You need to execute the command CALCTAX(1000); .

  2. b) You need to execute the command EXECUTE FUNCTION calc tax; .

  3. c) You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000); .

  4. d) You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX;

  5. e) You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);


Correct Option: E
  1. a) Local variables.

  2. b) Arguments.

  3. c) Boolean variables.

  4. d) Substitution variables.


Correct Option: B
Explanation:

When creating stored procedures and functions, the option that allows you to transfer values to and from the calling environment is:

B. Arguments.

Arguments, also known as parameters, are variables that are defined within a stored procedure or function and can be used to receive values from the calling environment. These values can be passed to the procedure or function when it is executed, and the procedure or function can also return values back to the calling environment using these arguments.

Option A, local variables, are variables defined within the scope of the procedure or function and are not used for transferring values to and from the calling environment.

Option C, boolean variables, are variables that can hold a true or false value and are not specifically designed for transferring values to and from the calling environment.

Option D, substitution variables, are variables used in SQL*Plus and Oracle SQL Developer tools and are not used for passing values to and from the calling environment in stored procedures or functions.

So, the correct answer is B. Arguments.

  1. a) When the procedure contains no SQL statements.

  2. b) When the procedure contains no PL/SQL commands.

  3. c) When the procedure needs to be used by many client applications accessing several remote databases.

  4. d) When the procedure needs to be used by many users accessing the same schema objects on a local database.


Correct Option: D
  1. a) PROCEDURE add_product (p_order_date DATE);

  2. b) PROCEDURE add_product (p_name VARCHER2, P_ORDERED DATE);

  3. c) PROCEDURE add_product (p_prodname VARCHER2, P_PRISE NUMBER);

  4. d) PROCEDURE add_product (p_prize NUMBER, P_DESCRIPTION VARCHER2);


Correct Option: D
  1. a) When declaring arguments length is not allowed.

  2. b) When declaring arguments each argument must have a mode specified.

  3. c) When declaring arguments each argument must have a length specified.

  4. d) When declaring a VARCHAR2 argument it must be specified.


Correct Option: A
  1. a) In the package body.

  2. b) In the data base triggers.

  3. c) In the package specification.

  4. d) In the procedures declare section using the exact name in each.


Correct Option: A
  1. a) The COMMIT and ROLLBACK commands are allowed in the packaged function.

  2. b) You can not use packaged functions in a query statement.

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

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

  5. 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 Option: C

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)

  1. a) GRANT SELECT ON ADD_PLAYER TO PUBLIC;

  2. b) GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;

  3. c) GRANT INSERT ON PLAYER TO PUBLIC;

  4. d) GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;

  5. e) REVOKE INSERT ON PLAYER FROM PUBLIC;


Correct Option: B,E
  1. a) When declaring arguments length is not allowed.

  2. b) When declaring arguments each argument must have a mode specified.

  3. c) When declaring arguments each argument must have a length specified.

  4. d) When declaring a VARCHAR2 argument it must be specified.


Correct Option: A
  1. a) Trap it with a Handler

  2. b) Propagate it to the Calling Environment

  3. c) a & then b

  4. d) b & then a


Correct Option: C
- Hide questions