Question 1

Maximum nesting levels in triggers.

technology databases

32
16
Can't nest triggers inside others.
2

Answer

Correct Option: B

Question 2

The maximum size for expressions, variables, and arguments passed to stored procedures.

technology databases

255
256
16483
1024

Answer

Correct Option: C

Question 3

create or replace function add_three_numbers (a number := 0, b number := 0, c number := 0) return number is Begin return a+b+c; End; / Which is not a valid procedure call in Oracle 10g

technology databases

Begin Dbms_output.put_line(add_three_numbers (3,4,5)); End; /
Begin Dbms_output.put_line(add_three_numbers (a => 3, b=>4,c=>5)); End; /
Begin Dbms_output.put_line (add_three_numbers (3, b=>4,c=>5)); End; /
select add_three_numbers (3, b=>4,c=>5) from dual

Answer

Correct Option: D

Question 4

To be callable from SQL expressions, a user-defined function must: 1. Be a stored function 2. Accept only IN parameters 3. Accept only valid SQL data types, not PL/SQL specific types, as parameters 4. Return data types that are valid SQL data types, not PL/SQL specific types

technology databases

All are true
1, 2 and 4 are true
1 and 2 alone true
2 and 3 alone are ture

Answer

Correct Option: A

Question 5

In For Loop syntax to go in reverse order

technology databases

begin For 1 in reverse 1..3 loop dbms_output.put_line(‘I is’ ||i); End loop; End; /
begin For 1 in 1..3 reverse loop dbms_output.put_line(‘I is’ ||i); End loop; End; /
begin For 1 in 3..1 loop dbms_output.put_line(‘I is’ ||i); End loop; End; /
begin For 1 in 3..1 reverse loop dbms_output.put_line(‘I is’ ||i); End loop; End; /

Answer

Correct Option: A

Question 6

Declare Cursor c is select * from emp; I emp%row_type; Begin open c; fetch c into I; loop Dbms_output_line(‘Emp name:’ || i.ename); fetch c into I; exit when c%notfound; End loop; End; What is the error in above PL/SQL Block

technology databases

Close cursor statement is not included
No exception block present
exit when c%notfound; this should have been given as exit when c%not_found;
I emp%row_type; it should be given as %ROWTYPE

Answer

Correct Option: D

Question 7

Declare A char := ‘AB’; Begin A = 1; End; What is the error in the above block?

technology databases

There is no error in the statement
Exception handler is missing
CHAR is not a valid datatype
Run time error The error displayed as Declare * Error at line 1: ORA-06502: PL/SQL: numeric or value error: character string too small. Will come

Answer

Correct Option: D

Question 8

SET SERVEROUTPUT ON; DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN -- Calculation might cause division-by-zero error. pe_ratio := stock_price / net_earnings; dbms_output.put_line('Price/earnings ratio = ' || pe_ratio); EXCEPTION -- exception handlers begin -- Only one of the WHEN blocks is executed. WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error dbms_output.put_line('Company must have had zero earnings.'); pe_ratio := null; WHEN OTHERS THEN -- handles all other errors dbms_output.put_line('Some other kind of error occurred.'); pe_ratio := null; END; -- exception handlers and block end here What will be outcome of this program block?

technology databases

It will print the value of divide operation
When others exception will be executed as we can't concatenate number with a string in dbms_output statement directlt. We have to use to_char to convert it as varchar2
WHEN ZERO_DIVIDE will be executed
When others exception will be executed as pe_ratio is not initialized.

Answer

Correct Option: C

Question 9

Which attribute is not correct for an explicit cursor?

technology databases

%ROWCOUNT
%ISOPEN
%NOTFOUND
%COUNT

Answer

Correct Option: D

Question 10

What is the smallest PL/SQL Block?

technology databases

Begin Dbms_output.put_line('Heello World'); exception when others then Dbms_output.put_line('error'); end; /
Begin null; End;
Declare a varchar2(100) := 'Hello World'; Begin Dbms_output.put_line(a); exception when others then Dbms_output.put_line('error'); end; /
begin Dbms_output.put_line('Heello World'); end; /

Answer

Correct Option: B

Question 11

What is true about the simple loop statement? 1. Loop block has entry criteria 2. Loop block checks for exit criteria 3. We need to put an entry criteria manually 4. We need to put an exit criteria manually. 5. Loop without any exit criteria will be indefinite loop.

technology databases

1, 2 and 4 are true
all statements are valid
Only 4 an d 5 are true
1, 4 and 5 are true

Answer

Correct Option: C

Question 12

Syntax for case structure?

technology databases

Case [ TRUE | selector_variable ]] When [criteria1 | expression1 ] then criteria1_statements; When [criteria1 | expression1 ] then Criteria2_statements; Else block statement; end;
Case value case [criteria1 | expression1 ] then criteria1_statements; case [criteria1 | expression1 ] then Criteria2_statements; Else block statement; end case;
Case [ TRUE | selector_variable ]] When [criteria1 | expression1 ] then criteria1_statements; When [criteria1 | expression1 ] then Criteria2_statements; Else block statement; end case;
Case [ TRUE | selector_variable ]] Loop When [criteria1 | expression1 ] then criteria1_statements; When [criteria1 | expression1 ] then Criteria2_statements; Else block statement; end Loop;

Answer

Correct Option: C

Question 13

BEGIN . . . BEGIN IF X=1 THEN RAISE A: ELSEIF X=2 THEN RAISE B; ELSE RAISE C; EXCEPTION WHEN A THEN . . . END; EXCEPTION WHEN B THEN . . . END; Whar will happen to exception B

technology databases

The exception B will be handled in inner block
The exception B will be handled in outer block exception handler
The exception will not be handled as there is no handler defined for B in inner block
The exception section in inner block is executed and also Outer block exception section as we have defined there too

Answer

Correct Option: B

Question 14

Begin Dbms_output.put_line(‘Hello world’) End; What is the error in the above block?

technology databases

There is no error in the statement
Exception handler is missing
DBMS_OUTPUT.PUT_LINE package must be declared
semicolon is missing in the line "Dbms_output.put_line(‘Hello world’)"

Answer

Correct Option: D

Question 15

DECLARE sal_limit NUMBER ( 4 ) := 0 ; my_ename emp.ename%TYPE ; my_sal emp.sal%TYPE ; CURSOR my_cursor IS SELECT ename , sal FROM emp WHERE sal > sal_limit ; BEGIN sal_limit := 1200 ; OPEN my_cursor; fetch my_cursor INTO my_ename , my_sal ; LOOP FETCH my_cursor INTO my_ename , my_sal ; EXIT WHEN my_cursor%NOTFOUND ; -- nothing returned INSERT INTO new_table VALUES ( my_ename , my_sal ) ; END LOOP ; COMMIT ; END ;

technology databases

For cursor fetching we have to use Cursor For loops
There is no close statement for closing the cursor statement
No errors in the block
No exception handler found

Answer

Correct Option: B

Question 16

CREATE OR REPLACE FUNCTION dml_call_sql (p_sal NUMBER) RETURN NUMBER IS BEGIN INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary) VALUES(1, 'employee 1', '[email protected]', SYSDATE, 'SA_MAN', 1000); RETURN (p_sal + 100); END; / Function created. UPDATE employees SET salary = dml_call_sql(2000) WHERE employee_id = 170; What is the error in the block

technology databases

No errors in the block
ORA-04091: table PLSQL.employees is mutating. Trigger/function may not see it. ORA-06512: at PL/SQL "DBMS_CALL_SQL"
There is no exception handler in the function
No rows updated exception will occur

Answer

Correct Option: B

Question 17

What does the REPLACE option indicate while used in creating functions?

technology databases

If the function exists, it will be dropped and replaced with the new version created by the statement.
If the function does not exist, it will create a new one.
No action will be taken
There is no such option available.

Answer

Correct Option: A

Question 18

How many type of cursors are available?

technology databases

1
2
3
4

Answer

Correct Option: B

Question 19

Implicit cursors are declared for

technology databases

Some DML,PL/SQL statements
All DML,PL/SQL statements
Only PL/SQL statements
None of the Above

Answer

Correct Option: B

Question 20

For processing rows in a specific sequence,

technology databases

Use ORDER BY clause
Use ROW clause
Use ROW BY clause
None of the Above

Answer

Correct Option: A

Description: databases Online Quiz - 143
Number of Questions: 20
Created by: Aliensbrain Bot
Tags: databases

databases Online Quiz - 143

Find more quizzes from top tags