0

databases Online Quiz - 143

Description: databases Online Quiz - 143
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0
  1. 32

  2. 16

  3. Can't nest triggers inside others.

  4. 2


Correct Option: B

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

  1. Begin Dbms_output.put_line(add_three_numbers (3,4,5)); End; /

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

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

  4. select add_three_numbers (3, b=>4,c=>5) from dual


Correct Option: D

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

  1. All are true

  2. 1, 2 and 4 are true

  3. 1 and 2 alone true

  4. 2 and 3 alone are ture


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

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

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

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


Correct Option: A
  1. Close cursor statement is not included

  2. No exception block present

  3. exit when c%notfound; this should have been given as exit when c%not_found;

  4. I emp%row_type; it should be given as %ROWTYPE


Correct Option: D
  1. There is no error in the statement

  2. Exception handler is missing

  3. CHAR is not a valid datatype

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


Correct Option: D

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?

  1. It will print the value of divide operation

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

  3. WHEN ZERO_DIVIDE will be executed

  4. When others exception will be executed as pe_ratio is not initialized.


Correct Option: C

Which attribute is not correct for an explicit cursor?

  1. %ROWCOUNT

  2. %ISOPEN

  3. %NOTFOUND

  4. %COUNT


Correct Option: D

What is the smallest PL/SQL Block?

  1. Begin Dbms_output.put_line('Heello World'); exception when others then Dbms_output.put_line('error'); end; /

  2. Begin null; End;

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

  4. begin Dbms_output.put_line('Heello World'); end; /


Correct Option: B

Syntax for case structure?

  1. Case [ TRUE | selector_variable ]] When [criteria1 | expression1 ] then criteria1_statements; When [criteria1 | expression1 ] then Criteria2_statements; Else block statement; end;

  2. Case value case [criteria1 | expression1 ] then criteria1_statements; case [criteria1 | expression1 ] then Criteria2_statements; Else block statement; end case;

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

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


Correct Option: C

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

  1. The exception B will be handled in inner block

  2. The exception B will be handled in outer block exception handler

  3. The exception will not be handled as there is no handler defined for B in inner block

  4. The exception section in inner block is executed and also Outer block exception section as we have defined there too


Correct Option: B

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

  1. There is no error in the statement

  2. Exception handler is missing

  3. DBMS_OUTPUT.PUT_LINE package must be declared

  4. semicolon is missing in the line "Dbms_output.put_line(‘Hello world’)"


Correct Option: D

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

  1. No errors in the block

  2. ORA-04091: table PLSQL.employees is mutating. Trigger/function may not see it. ORA-06512: at PL/SQL "DBMS_CALL_SQL"

  3. There is no exception handler in the function

  4. No rows updated exception will occur


Correct Option: B

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

  1. If the function exists, it will be dropped and replaced with the new version created by the statement.

  2. If the function does not exist, it will create a new one.

  3. No action will be taken

  4. There is no such option available.


Correct Option: A
  1. Some DML,PL/SQL statements

  2. All DML,PL/SQL statements

  3. Only PL/SQL statements

  4. None of the Above


Correct Option: B

For processing rows in a specific sequence,

  1. Use ORDER BY clause

  2. Use ROW clause

  3. Use ROW BY clause

  4. None of the Above


Correct Option: A
- Hide questions