Tag: databases

Questions Related to databases

  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

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_home := 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; You run this SELECT statement: SELECT first_name, last_name gen_email_name(first_name, last_name, 108) EMAIL FROM employees; What occurs?

  1. Employee 108 has his email name updated based on the return result of the function.

  2. The statement fails because functions called from SQL expressions cannot perform DML.

  3. The statement fails because the functions does not contain code to end the transaction.

  4. The SQL statement executes successfully, because UPDATE and DELETE statements are ignoring in stored functions called from SQL expressions.

  5. The SQL statement executes successfully and control is passed to the calling environment.


Correct Option: B
  1. You need to execute the command CALCTAX(1000); .

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

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

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

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


Correct Option: E
Explanation:

To run the given function from the SQL*Plus prompt, the user needs to know the correct syntax for invoking a function in Oracle SQL. The function CALCTAX takes in a single parameter, sal, and returns the result of sal * 0.05.

Option A is incorrect because it is missing the keyword SELECT to return the result of the function.

Option B is incorrect because the keyword FUNCTION is not used in the EXECUTE statement, and the argument sal is not provided.

Option C is incorrect because SQL*Plus environment variables are not required to execute a function, and it is missing the keyword SELECT to return the result of the function.

Option D is incorrect because it is missing the argument sal.

Option E is the correct answer because it first declares an SQL*Plus environment variable X, executes the function CALCTAX with the argument 1000, and assigns the result to the variable X. The keyword SELECT is not required because the result is assigned to a variable.

Therefore, the answer is: E. You need to create a SQL*Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);

  1. Use a drop procedure statement to drop a standalone procedure.

  2. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package specification.

  3. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package body.

  4. For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.


Correct Option: A