• databases Online Quiz - 172
Back
0

databases Online Quiz - 172

Description: databases Online Quiz - 172
Number of Questions: 20
Created by:
Tags: databases
Start the Quiz
Attempted 0/20 Correct 0 Score 0
‹
›

Which type of argument passes a value from a procedure to the calling environment?

technology databases

  1. VARCHAR2

  2. BOOLEAN

  3. OUT

  4. IN

Show answer
Correct Option: C

AI Explanation

To answer this question, we need to understand the different types of arguments in programming.

Option A) VARCHAR2 - VARCHAR2 is a data type used to store character strings of variable length. However, it is not directly related to passing values from a procedure to the calling environment.

Option B) BOOLEAN - BOOLEAN is a data type that represents logical values (true or false). Similar to VARCHAR2, it is not specifically used for passing values from a procedure to the calling environment.

Option C) OUT - This option is the correct answer. The OUT parameter is used to pass values from a procedure to the calling environment. It allows the procedure to modify the value of the parameter and return it to the caller.

Option D) IN - The IN parameter is used to pass values from the calling environment to a procedure. It allows the procedure to use the value but does not modify it or return it to the caller.

The correct answer is C) OUT because it is the type of argument that passes a value from a procedure to the calling environment.

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID,LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must invoke the APD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?

technology databases

  1. EXECUTE UPD_BAT_STAT(V_ID);

  2. UPD_BAT_STAT(V_ID);

  3. RUN UPD_BAT_STAT(V_ID);

  4. START UPD_BAT_STAT(V_ID);

Show answer
Correct Option: B

AI Explanation

To successfully invoke the UPD_BAT_STAT procedure within the ADD_PLAYER procedure, the correct statement to add is:

B. UPD_BAT_STAT(V_ID);

Explanation: In PL/SQL, to invoke a procedure, you simply write the procedure name followed by the parameter values enclosed in parentheses. Therefore, the correct syntax to invoke the UPD_BAT_STAT procedure with the parameter V_ID is UPD_BAT_STAT(V_ID).

Option A (EXECUTE UPD_BAT_STAT(V_ID)) is incorrect because the EXECUTE keyword is not required when invoking a procedure.

Option C (RUN UPD_BAT_STAT(V_ID)) is incorrect because the RUN keyword is not used to invoke a procedure in PL/SQL.

Option D (START UPD_BAT_STAT(V_ID)) is incorrect because the START keyword is not used to invoke a procedure in PL/SQL.

Therefore, the correct answer is B. UPD_BAT_STAT(V_ID).

When creating a function in SQL *Plus, you receive this message: .Warning: Function created with compilation errors.. Which command can you issue to see the actual error message?

technology databases

  1. SHOW FUNCTION_ERROR

  2. SHOW USER_ERRORS

  3. SHOW ERRORS

  4. SHOW ALL_ERRORS

Show answer
Correct Option: C

AI Explanation

To see the actual error message when creating a function in SQL*Plus, you can issue the command:

C. SHOW ERRORS

This command will display the compilation errors, if any, associated with the function creation. It provides detailed information about the errors encountered during the compilation process, such as the line number and the specific error message.

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?

technology databases

  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.

Show answer
Correct Option: B

AI Explanation

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

Option A) Employee 108 has his email name updated based on the return result of the function. This option is incorrect because the UPDATE statement inside the function updates the email name in the "employees" table, but the SELECT statement does not update any data. It only retrieves the "first_name", "last_name", and the result of the "gen_email_name" function for employee 108.

Option B) The statement fails because functions called from SQL expressions cannot perform DML. This option is correct. Functions called from SQL expressions cannot perform Data Manipulation Language (DML) operations like UPDATE, INSERT, or DELETE. In the given code, the function "gen_email_name" contains an UPDATE statement, so when it is called in the SELECT statement, it will fail.

Option C) The statement fails because the function does not contain code to end the transaction. This option is incorrect because the failure of the statement is not related to transaction control. The issue is that the function contains an UPDATE statement, which is not allowed in a function called from a SQL expression.

Option D) The SQL statement executes successfully because UPDATE and DELETE statements are ignored in stored functions called from SQL expressions. This option is incorrect. UPDATE and DELETE statements are not ignored in stored functions called from SQL expressions. They are not allowed in such functions.

Option E) The SQL statement executes successfully and control is passed to the calling environment. This option is incorrect. The SQL statement will fail due to the presence of the UPDATE statement in the function.

The correct answer is B. The statement fails because functions called from SQL expressions cannot perform DML operations like UPDATE.

Given a function CALCTAX : CREATE OR REPLACE FUNCTION calc tax (sal NUMBER) RETURN NUMBER IS BEGIN RETURN (sal * 0.05); END; If you want to run the above function from the SQL *Plus prompt, which statement is true?
technology databases

  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);

Show answer
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);

Which statement is valid when removing procedures?

technology databases

  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.

Show answer
Correct Option: A

Examine this code: CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT 'placeholder', p_location VARCHAR2 DEFAULT 'Boston') IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept; / Which three are valid calls to the add_dep procedure ? (Choose three)

technology databases

  1. add_dept;

  2. add_dept( .Accounting .);

  3. add_dept(, .New York .);

  4. add_dept(p_location=> .New York .);

Show answer
Correct Option: A,B,D

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; BEGIN SELECT HITS / AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which statement will successfully invoke this function in SQL *Plus?

technology databases

  1. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

  2. EXECUTE CALC_PLAYER_AVG (31);

  3. CALC_PLAYER (.RUTH.);

  4. CALC_PLAYER_AVG(31);

  5. START CALC_PLAYER_AVG(31)

Show answer
Correct Option: A

AI Explanation

To determine which statement will successfully invoke the given function in SQL *Plus, let's go through each option:

Option A) SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT; This option is correct. It successfully invokes the function CALC_PLAYER_AVG by passing the PLAYER_ID column from the PLAYER_BAT_STAT table as the argument. The function will calculate the average for each player.

Option B) EXECUTE CALC_PLAYER_AVG (31); This option is incorrect. The EXECUTE statement is used to execute a stored procedure or anonymous block, not a function. In this case, you should use the SELECT statement to invoke the function.

Option C) CALC_PLAYER (.RUTH.); This option is incorrect. The function name is CALC_PLAYER_AVG, not CALC_PLAYER. Additionally, the argument should be provided as a value, not a string literal. The correct format is CALC_PLAYER_AVG(31).

Option D) CALC_PLAYER_AVG(31); This option is incorrect. Although the function name and argument are correct, you should use the SELECT statement to invoke the function.

Option E) START CALC_PLAYER_AVG(31) This option is incorrect. The START command is used to execute a script file in SQL *Plus, not to invoke a function.

The correct answer is Option A) SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT. This option successfully invokes the CALC_PLAYER_AVG function by passing the PLAYER_ID column from the PLAYER_BAT_STAT table as the argument.

When creating store procedures and functions which construct allows you to transfer values to and from the calling environment?

technology databases

  1. Local variables.

  2. Arguments.

  3. Boolean variables.

  4. Substitution variables.

Show answer
Correct Option: B

AI Explanation

To answer this question, you need to understand the concepts of store procedures and functions.

When creating store procedures and functions, the construct that allows you to transfer values to and from the calling environment is known as "arguments".

Option A) Local variables - Local variables are variables that are declared and used within the scope of the procedure or function itself. They do not allow you to transfer values to and from the calling environment.

Option B) Arguments - Arguments are variables that are passed to a procedure or function when it is called. They allow you to transfer values to and from the calling environment. This is the correct option.

Option C) Boolean variables - Boolean variables are variables that can have a value of either true or false. They do not specifically allow you to transfer values to and from the calling environment.

Option D) Substitution variables - Substitution variables are used in SQL*Plus, a command-line interface for Oracle Database. They are used to substitute values at runtime, but they do not specifically allow you to transfer values to and from the calling environment in store procedures and functions.

Therefore, the correct answer is B) Arguments.

Which type of argument passes a value from a calling environment?

technology databases

  1. VARCHAR2.

  2. BOOLEAN.

  3. OUT.

  4. IN.

Show answer
Correct Option: D

Under which situation do you create a server side procedure?

technology databases

  1. When the procedure contains no SQL statements.

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

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

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

Show answer
Correct Option: D

AI Explanation

To answer this question, you need to understand the concept of server-side procedures and their purposes.

Option A) When the procedure contains no SQL statements - This option is incorrect because server-side procedures are typically used to execute SQL statements and perform database operations.

Option B) When the procedure contains no PL/SQL commands - This option is incorrect because server-side procedures can contain PL/SQL commands. PL/SQL is a procedural language used in Oracle databases for writing stored procedures, functions, and triggers.

Option C) When the procedure needs to be used by many client applications accessing several remote databases - This option is incorrect because in this situation, you would typically create a distributed database system or implement a data access layer to handle the interaction between the client applications and remote databases. Server-side procedures may be involved, but this is not the primary reason for creating them.

Option D) When the procedure needs to be used by many users accessing the same schema objects on a local database - This option is correct because server-side procedures are often created to provide a centralized and controlled way for multiple users to access and manipulate the same schema objects on a local database. By creating a server-side procedure, you can ensure consistent and secure access to the shared data.

Therefore, the correct answer is D) When the procedure needs to be used by many users accessing the same schema objects on a local database. This option is correct because server-side procedures are commonly used in this situation to manage and control access to shared data.

Examine this package body:CREATE OR REPLACE PACKAGE BODY forward_packISV_sum NUMBER;- 44 -PROCEDURE calc_ord(. . . );PROCEDURE generate_summary(. . . )ISBEGINCalc_ord(. . . );. . .END calc_ord;END forward_pack;/ Which construct has a forward declaration?

technology databases

  1. V_SUM

  2. CALC_ORD.

  3. FORWARD_PACK

  4. GENERATE_SUMMARY.

Show answer
Correct Option: B

Examine this procedure:CREATE OR REPLACE PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHER2(30))ISBEGININSERT INTO PLAYER(ID, LAST_NAME)VALUES(V_ID, V_LAST_NAME);COMMIT;END; Why does this command fail when executed?

technology databases

  1. When declaring arguments length is not allowed.

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

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

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

Show answer
Correct Option: A

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)

technology databases

  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;

Show answer
Correct Option: B,E

The PROCEDURE_ADD_PRODUCT is defined within a package specifications as follows: PROCEDURE_ADD_PRODUCT (P_PRODNO NUMBER,P_PRODNAME VARCHER2); Which procedure declaration can’t be added to package specifications?

technology databases

  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);

Show answer
Correct Option: D

Command to see the current user ?

technology databases

  1. show users;

  2. display user;

  3. show name;

  4. show user;

Show answer
Correct Option: D

Command to change SQL prompt name ?
technology databases

  1. SQL> set sqlprompt “Qwest > “

  2. SQL> set prompt “Qwest > “

  3. SQL> sqlprompt set “Qwest > “

  4. SQL> prompt “Qwest > “

Show answer
Correct Option: A

Command to switch to UNIX prompt ?
technology databases

  1. bye

  2. end

  3. host

  4. None of above

Show answer
Correct Option: C

Which one of the following is not an Explicit Cursor attributes

technology databases

  1. cursor_name%Found

  2. cursor_name%NOTFOUND

  3. cursor_name%OPEN

  4. cursor_name%ROWCOUNT

Show answer
Correct Option: C

Which one of the following is not an Implicit Cursor attributes

technology databases

  1. SQL%Found

  2. SQL%ISOPEN

  3. SQL%NOTFOUND

  4. SQL%ROWCOUNT

Show answer
Correct Option: B
- Hide questions
ADVERTISEMENT

Find more quizzes from top tags

general knowledge
3615 Quizzes
119858 Questions
 technology
307 Quizzes
36705 Questions
 sports
963 Quizzes
19148 Questions
 history
1187 Quizzes
13475 Questions
 physics
598 Quizzes
13441 Questions
 biology
1160 Quizzes
12174 Questions
 science & technology
551 Quizzes
11015 Questions
 chemistry
490 Quizzes
9892 Questions
 maths
518 Quizzes
9324 Questions
 softskills
0 Quizzes
7131 Questions