SA2 DBS - Sheet1 Flashcards
In anonymous block, DECLARE states “this is the start of a block”. In Procedure, ____________ states “this is the start of a subprogram”.
CREATE PROCEDURE
Which keyword is omitted in the declaration section of the procedure? _____
DECLARE
Subprograms are named PL/SQL blocks that are compiled and stored in the _______.
DATABASE
The IN parameters can only be read within the procedure and cannot be modified.
True
The positional parameter passing uses the associate operator.
False
The symbol»_space; is used for association operator?
False
The code below is a correct example of passing parameters by combination notation.
add_dept (p_loc=>1400, ‘EDUCATION’ );
False
The Positional parameter passing is the common method of passing parameters.
True
The type of parameter that is declared in procedure heading is called ________.
FORMAL
Which can be used to assign a default value to IN parameters?
All the options
The ____ parameters can be literal values, variables, or expressions that are sent to the parameter list of a called subprogram.
ACTUAL
Which parameter mode can be assigned a default value?
IN
OUT
IN OUT
IN
How many value is returned by a function?_____
1
A stored function must return exactly ____ value.
one
The functions used in SQL statements cannot use ____ or IN OUT modes.
IN
What is missing in the code below?
create or replace function sample
return number
is
begin
return 5+5;
end;
Group of answer choices
Formal parameter
Nothing is missing
Expression evaluation
Variable
Nothing is missing
Which of the statement below is valid? Assuming that get_sal procedure exists with one OUT parameter.
Group of answer choices
x:=100; DBMS_OUTPUT.PUT_LINE(get_sal(x));
All the options
x:= 200; DBMS_OUTPUT.PUT_LINE(get_sal(100+x));
PUT_LINE(get_sal(100));
x:=100; DBMS_OUTPUT.PUT_LINE(get_sal(x));
The purpose of the OR REPLACE clause is to ______ an existing procedure.
OVERWRITE
The _____ are named PL/SQL blocks that are compiled and stored in the database.
SUBPROGRAMS
The DECLARE keyword is not used in a subprogram.
Group of answer choices
True
False
True
In Procedure, all parameter modes must be specified.
Group of answer choices
True
False
False
The formal parameters can be literal values, variables, or expressions that are sent to the parameter list of a called subprogram.
Group of answer choices
True
False
The Named parameter passing is the common method of passing parameters.
Group of answer choices
True
False
False
The DEFAULT keyword is used to assign a default value for formal parameters.
Group of answer choices
True
False
What is the symbol used for association operator?
Group of answer choices
=
> >
=>
> =
=>
Assuming add_dept procedure has two parameters: p_name and p_id, what is the error in the given procedure call?
add_dept (‘ADVERTISING’, p_loc => 1400);
Group of answer choices
No error
The value of the positional parameter
The named parameter
Positional parameter must come before named parameter
The named parameter
If Combination notation is used, the _______ parameters must come first.
Group of answer choices
POSITIONAL
NAMED
POSITIONAL
What keyword is missing in the given function construct?
CREATE [OR REPLACE] function_name
[(parameter1 [mode1] datatype1, …)]
RETURN datatype IS|AS
[local_variable_declarations; …]
BEGIN
actions;
RETURN expression;
END [function_name];
FUNCTION
Functions return only a single value, and the value is returned through a ______ statement.
RETURN
A function must have a _______ clause in the header and at least one RETURN statement in the executable section.
Group of answer choices
PARAMETER
DECLARATION
RETURN
EXIT
RETURN
What follows the RETURN keyword at the function header?
Group of answer choices
IS
PARAMETER
VARIABLE
DATA TYPE
DATA TYPE
The _______ is added to CREATE PROCEDURE clause to overwrite an existing procedure.
OR REPLACE
Subprograms become the building blocks of packages and triggers.
Group of answer choices
True
False
True
If Combination notation is used, the positional parameters must come first before the named parameters.
Group of answer choices
True
False
True
To invoke a procedure from another procedure, use a direct call inside an executable section of the block.
Group of answer choices
True
False
t
Given the procedure below, what is the correct way of invoking the procedure?
CREATE OR REPLACE PROCEDURE show_emps
(p_emp_id IN NUMBER, p_department_id IN number,
p_hiredate IN DATE) IS
begin
. . . . . .
End;
Group of answer choices
show_emps(101, p_hiredate => ’01-dec-2007’, p_department_id = 10)
Show_emps(101, 10, ’01-dec-2006’);
show_emps(p_department_id => 10, p_hiredate => ’01-dec-1007’, p_emp_id => 101)
All the options
show_emps(p_department_id => 10, p_hiredate => ’01-dec-1007’, p_emp_id => 101)
The operator => is called _______.
Group of answer choices
Associate operator
Association operator
Referencing operator
Reference operator
Association operator
Using an _______ parameter mode, you can pass a value into a procedure that can be updated within the procedure.
Group of answer choices
OUT
IN
All the options
IN OUT
IN OUT
Which type of parameter passing uses an association operator?
Group of answer choices
Positional
IN OUT
Combination
Named
Named
The functions used in SQL statements cannot use OUT or _____ modes.
IN OUT
Which of the statement below is valid? Assuming that get_sal function exists with no parameter.
Group of answer choices
v_salary := get_sal() + 50;
get_sal + 50;
v_salary := get_sal + 50;
get_sal() + 50;
v_salary := get_sal() + 50;
What is missing in the code below?
create or replace function sample
is num number(2);
begin
num := 5+5;
return num;
end;
Group of answer choices
Formal parameter
Not in the options
Size of the returning value
No return value
No return value
The functions used in SQL statements cannot use OUT or _____ modes.
Group of answer choices
IN
IN OUT
IN OUT
In the given Procedure header, the ________clause is optional.
CREATE OR REPLACE PROCEDURE name [parameters] IS|AS
OR REPLACE
The block structure of the subprograms is similar to the structure of anonymous blocks.
Group of answer choices
True
False
True
The IN mode parameters may or may not be specified.
Group of answer choices
True
False
True
The actual is a special form of a variable, whose input values are initialized by the calling environment when the subprogram is called, and whose output values are returned to the calling environment when the subprogram returns control to the caller.
Group of answer choices
True
False
False
What is missing in the given code below?
CREATE or replace procedure raise_sal
empid my_employees.employee_id%TYPE := 1234, emp_raise number IS
BEGIN
update employees
set salary = salary + (salary * (emp_raise/100) where employee_id = emp;
END;
Group of answer choices
Procedure name after END keyword
Not in the options
Nothing is missing
Incomplete declaration of emp_raise parameter
IN
Procedure name after END keyword
What is the error of the given code?
BEGIN
add_dept(name =>’new dept’, ‘new location’);
END;
Group of answer choices
Positional parameter must come before named parameter
Invoking the procedure
Actual parameter values
Do not enclose parameter values with single quote
Positional parameter must come before named parameter
How many private variables are there in the given code?
CREATE or replace procedure raise_sal
(empid my_employees.employee_id%TYPE := 1234, emp_raise number) IS
BEGIN
update employees
set salary = salary + (salary * (emp_raise/100) where employee_id = emp;
END;
Group of answer choices
2
3
1
0
2
What is missing in the code below?
create or replace function sample
return number
is num number(2);
begin
num := 5+5;
end;
Group of answer choices
Size of the returning value
No return value
Formal parameter
Incomplete expression
No return value
The block structure of the subprograms is similar to the structure of _____ blocks.
ANONYMOUS
The executable section of the procedure body requires a minimum of _____ statement.
ONE
In the given Procedure header, the underlined clause/keyword is optional.
CREATE OR REPLACE PROCEDURE name [parameters] IS|AS
Group of answer choices
True
False
False
A procedure is a PL/SQL block containing local variables, a BEGIN, and an END. The procedure_name after the END keyword is mandatory.
Group of answer choices
True
False
False
Three ways of passing parameters from the calling environment:
Group of answer choices
POSITIONAL, NAMED, COMBINATION
POSITION, NAME, COMBINATION
POSITIONAL, NAMED, COMBINATIONAL
d. POSITION, NAMED, COMBINATION
POSITIONAL, NAMED, COMBINATION
A ______ is a named PL/SQL block that returns exactly one value.
FUNCTION
The functions used in SQL statements cannot use _____ or IN OUT modes.
IN
Which of the statement below is valid? Assuming that get_sal function exists with parameter.
Group of answer choices
get_sal + 50;
get_sal() + 50;
v_salary := get_sal() + 50;
Not in the options
get_sal() + 50;
You cannot invoke a procedure from inside an SQL statement.
Group of answer choices
True
False
True
A procedure can be invoked in another procedure.
Group of answer choices
True
False
True
How many parameters are there in the given code?
CREATE or replace procedure raise_sal
(empid my_employees.employee_id%TYPE := 1234, emp_raise number) IS
BEGIN
update employees
set salary = salary + (salary * (emp_raise/100) where employee_id = emp;
END;
Group of answer choices
1
2
3
0
2
In stored function, the parameter mode should only be ______ .
IN
In Procedure, the default parameter mode is IN.
Group of answer choices
True
False
True
Procedures may have an exception section.
Group of answer choices
True
False
True
The parameter modes are specified in:
Group of answer choices
ACTUAL PARAMETER
FORMAL PARAMETER
FORMAL PARAMETER
In stored function, the RETURN clause is used instead of ____ mode.
OUT
A function must have a RETURN clause in the _____ and at least one RETURN statement in the executable section.
HEADER
In Procedure, the default parameter mode is _______.
IN
Parameters in subprograms are optional.
Group of answer choices
True
False
t
Another way of initializing a default value for parameter is by using assignment operator.
Group of answer choices
True
False
True
The OUT parameter mode supplies an input value, which can be returned (output) as a modified value.
Group of answer choices
True
False
False
The _____ are containers that enable you to group together related PL/SQL subprograms, variables, cursors, and exceptions.
PACKAGES
What is missing in the given construct in order to remove an entire package?
DROP ________ PACKAGE_NAME;
PACKAGES
The ____ option drops and re-creates the package specification.
OR REPLACE
The OR REPLACE option deletes an existing package bod
The OR REPLACE option overwrite an existing package body.
t
What is missing in the given package body construct?
CREATE [OR REPLACE] PACKAGE package_name
IS|AS
private type and variable declarations subprogram bodies
[BEGIN initialization statements]
END [package_name];
BODY
The package specification should contain the subprogram ________ and associated parameters terminated by a semicolon.
NAME
The package body contains the executable code of the _________ which were declared in the package specification. It may also contain its own variable declarations.
SUBPROGRAMS
Variables declared in the package body are considered private.
t
You need to recompile the package specification whenever the body of the subprograms that are in the package body changes.
Package construct (variable, procedure, function, and so on) that can be seen and executed from _____ the package are considered public.
OUTSIDE
The OR REPLACE options _______ and re-creates the package specification.
DROPS
Using the package name after the END keyword is mandatory.
One of the reason for using packages is encapsulation wherein related programs and variables are grouped together.
t
The implementation (i.e., the detailed code) of a procedure or function that is declared in a ________ is done in the package body.
PACKAGE SPECIFICATION
Package body contains only subprograms that are declared in the package specification.
The OR REPLACE option overwrite an existing package body.
t
