Take This Oracle PL/SQL Test

Approved & Edited by ProProfs Editorial Team
The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.
Learn about Our Editorial Process
| By Sudha_test
S
Sudha_test
Community Contributor
Quizzes Created: 12 | Total Attempts: 36,992
Questions: 40 | Attempts: 4,628

SettingsSettingsSettings
Take This Oracle PL/SQL Test - Quiz

Are you familiar with PL/SQL in Oracle? Take this Oracle PL/SQL test and test your knowledge. If you learned Oracle, this quiz would be an easy one for you. We have got a set of questions for your practice and knowledge-testing on PL/SQL in Oracle. Just go for it, and find out your results. Even if you are not sure about any of your answers, we will help you with the correct answer. All the best! Don't forget to share the quiz with other Oracle users.


Questions and Answers
  • 1. 

    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?

    • A.

      SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

    • B.

      EXECUTE CALC_PLAYER_AVG(31);

    • C.

      CALC_PLAYER('RUTH');

    • D.

      CALC_PLAYER_AVG(31);

    • E.

      START CALC_PLAYER_AVG(31)

    Correct Answer
    A. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;
    Explanation
    The correct statement to successfully invoke this function in SQL *Plus is "SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;". This statement calls the function CALC_PLAYER_AVG and passes the PLAYER_ID column from the table PLAYER_BAT_STAT as the argument. It will return the average calculated by the function for each player in the table.

    Rate this question:

  • 2. 

    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)

    • A.

      Add_dept;

    • B.

      Add_dept('Accounting');

    • C.

      Add_dept(, 'New York');

    • D.

      Add_dept(p_location=>'New York);

    Correct Answer(s)
    A. Add_dept;
    B. Add_dept('Accounting');
    D. Add_dept(p_location=>'New York);
    Explanation
    The first valid call to the add_dept procedure is add_dept; which uses the default values for both parameters. The second valid call is add_dept('Accounting'); which specifies a value for the p_dept_name parameter and uses the default value for the p_location parameter. The third valid call is add_dept(p_location=>'New York); which specifies a value for the p_location parameter using named notation.

    Rate this question:

  • 3. 

    Examine this code: CREATE OR REPLACE PACKAGE bonus IS g_max_bonus NUMBER := .99; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER; FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY bonus IS v_salary employees.salary%TYPE; v_bonus employees.commission_pct%TYPE; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employee_id = p_emp_id; RETURN v_bonus * v_salary; END calc_bonus FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employees RETURN v_bonus * v_salary + v_salary; END cacl_salary; END bonus; /   Which statement is true?

    • A.

      You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table.

    • B.

      You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table

    • C.

      You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the EMPLOYEES table.

    • D.

      You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table

    Correct Answer
    B. You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table
    Explanation
    The BONUS.CALC_SALARY packaged function can be called from a SELECT command against the EMPLOYEES table. This is because the function retrieves the salary and commission_pct values from the employees table and calculates the salary by multiplying the bonus and salary values. Therefore, it can be used in a SELECT statement to calculate and display the salary for a specific employee.

    Rate this question:

  • 4. 

    You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE. What type of trigger do you create?

    • A.

      FOR EACH ROW trigger on the EMP table.

    • B.

      Statement-level trigger on the EMP table.

    • C.

      FOR EACH ROW trigger on the AUDIT_TABLE table

    • D.

      Statement-level trigger on the AUDIT_TABLE table

    • E.

      FOR EACH ROW statement-level trigger on the EMPtable.

    Correct Answer
    A. FOR EACH ROW trigger on the EMP table.
    Explanation
    The correct answer is "FOR EACH ROW trigger on the EMP table" because the question specifically mentions that the trigger should monitor every row that is changed in the EMP table and place that information into the AUDIT_TABLE. A FOR EACH ROW trigger is used to perform an action for each row affected by the triggering event, which in this case is a row being changed in the EMP table.

    Rate this question:

  • 5. 

    which of the following passes a value from the procedure to the calling environment?

    • A.

      In

    • B.

      Out

    • C.

      Return

    • D.

      None of the above

    Correct Answer
    B. Out
    Explanation
    The keyword "out" is used in programming languages to pass a value from a procedure to the calling environment. When a parameter is defined as "out" in a procedure, it means that the procedure can modify the value of that parameter and the modified value will be reflected in the calling environment. This allows the procedure to pass back a value to the calling code. Therefore, "out" is the correct answer as it specifically deals with passing a value from the procedure to the calling environment.

    Rate this question:

  • 6. 

    create or replace trigger emp_trig after update on emp begin insert into audit values(user,sysdate); end; an update statement on emp affects 100 rows. How many rows will be inserted into audit table after this trigger fires?

    • A.

      100

    • B.

      1

    • C.

      0

    • D.

      Same no of rows as in emp table

    Correct Answer
    B. 1
    Explanation
    The trigger is set to fire after an update on the "emp" table. Therefore, for each update statement on the "emp" table, only one row will be inserted into the "audit" table.

    Rate this question:

  • 7. 

    Which determines the no of times a trigger is fired?

    • A.

      Trigger timing

    • B.

      Trigger event

    • C.

      Trigger type

    • D.

      Trigger body

    Correct Answer
    C. Trigger type
    Explanation
    The correct answer is "trigger type." The trigger type determines the number of times a trigger is fired. Different trigger types have different firing behaviors, such as firing once per record or firing multiple times per record. The trigger type is an important factor in controlling the execution and timing of triggers in a system.

    Rate this question:

  • 8. 

    While attempting to create a package specification in SQL*Plus, you receive this error: Warning: Package created with compilation errors. Which command should you execute to see a more detailed error message?

    • A.

      Show compilation errors

    • B.

      Display errors

    • C.

      Show errors

    • D.

      Show procedure errors

    Correct Answer
    C. Show errors
    Explanation
    To see a more detailed error message when creating a package specification in SQL*Plus, you should execute the "show errors" command. This command will display the compilation errors that occurred during the creation of the package specification, allowing you to identify and address the specific issues causing the error.

    Rate this question:

  • 9. 

    How will u delete a trigger emp_trig permanently?

    • A.

      Drop trigger emp_trig;

    • B.

      Delete trigger emp_trig;

    • C.

      Delete from user_triggers where trigger_name=’emp_trig’;

    • D.

      Alter trigger emp_trig disable

    Correct Answer
    A. Drop trigger emp_trig;
    Explanation
    The correct answer is "drop trigger emp_trig;". This is because the DROP TRIGGER statement is used to permanently delete a trigger from the database. In this case, it is specifically deleting the trigger named "emp_trig".

    Rate this question:

  • 10. 

    In a pl/sql loop,you need to find whether a sql statement has  fetched rows successfully.Which cursor attibute will you use?

    • A.

      Sql%isopen

    • B.

      Sql%rowcount

    • C.

      Sql%found

    • D.

      You cannot use cursor attributes for pl/sql loops

    Correct Answer(s)
    B. Sql%rowcount
    C. Sql%found
    Explanation
    In a PL/SQL loop, you can use the cursor attribute sql%rowcount to determine whether a SQL statement has fetched rows successfully. The sql%rowcount attribute returns the number of rows affected by the most recent SQL statement. If it returns a value greater than zero, it means that rows have been fetched successfully. Another cursor attribute sql%found can also be used to check if any rows have been fetched. However, the other options sql%isopen and "you cannot use cursor attributes for PL/SQL loops" are incorrect explanations.

    Rate this question:

  • 11. 

    What are the  common oracle predefined exceptions that occur while executing select statements?

    • A.

      No_data_found

    • B.

      Too_many_rows

    • C.

      Zero_divide

    • D.

      Invalid_cursor

    Correct Answer(s)
    A. No_data_found
    B. Too_many_rows
    Explanation
    The common Oracle predefined exceptions that occur while executing select statements are "no_data_found" and "too_many_rows". The "no_data_found" exception is raised when a select statement does not return any rows. The "too_many_rows" exception is raised when a select statement returns more than one row.

    Rate this question:

  • 12. 

    Begin       ------      exception        when no_data_found           ----------        when value_error            ---------        when others            -----------      end;      if you encounter an error while dividing a number by zero, which exception handler will be executed?

    • A.

      Value_error

    • B.

      No_data_found

    • C.

      Others

    • D.

      None of the above

    Correct Answer
    C. Others
    Explanation
    The "others" exception handler will be executed if an error occurs while dividing a number by zero. This handler is used to catch any exceptions that are not specifically handled by the other exception handlers in the code. Since there is no specific handler for the division by zero error (value_error) or any other specific error (no_data_found), the "others" handler will be executed in this case.

    Rate this question:

  • 13. 

    What type of cursor will u use when you use the cursor many times in the PL/SQL block with some different datas to be fetched depending upon differrent values

    • A.

      Cursors with parameters

    • B.

      Use explicit cursor so many time in the PL/SQL block

    • C.

      Cursors with attributes

    • D.

      Any of the above

    Correct Answer
    A. Cursors with parameters
    Explanation
    When you need to use the cursor multiple times in a PL/SQL block with different data to be fetched depending on different values, you can use cursors with parameters. This allows you to pass different values to the cursor each time it is used, ensuring that the correct data is fetched based on the specified parameters. By using cursors with parameters, you can dynamically control the data retrieval process in your PL/SQL block.

    Rate this question:

  • 14. 

    whT does the %rowcount return when no rows are returned by a select statement?

    • A.

      0

    • B.

      Undefined

    • C.

      NULL

    • D.

      None of the above

    Correct Answer
    A. 0
    Explanation
    When no rows are returned by a select statement, the %rowcount returns 0. This means that the select statement did not find any matching rows in the database.

    Rate this question:

  • 15. 

    What will be the value of a uninitialised but declared variable when it is first used in the executable section

    • A.

      0

    • B.

      NULL

    • C.

      Show error

    • D.

      None of the above

    Correct Answer
    B. NULL
    Explanation
    When an uninitialized but declared variable is first used in the executable section, its value will be NULL. This means that the variable does not have a specific value assigned to it yet. NULL is a special value in programming that represents the absence of a value or a null pointer. Therefore, when a variable is used without being assigned a value, it is considered NULL.

    Rate this question:

  • 16. 

    which clause has to be used in the explicit cursor to lock the rows first?

    • A.

      Where current of

    • B.

      FOR UPDATE OF

    • C.

      Any cursor Attribute

    • D.

      Any Cursor Variable

    Correct Answer
    B. FOR UPDATE OF
    Explanation
    The FOR UPDATE OF clause has to be used in the explicit cursor to lock the rows first. This clause allows the cursor to lock the selected rows in a table, preventing other transactions from modifying them until the transaction is complete. It specifies the columns that will be updated in the locked rows. By using this clause, the cursor ensures that the rows it fetches are locked for exclusive use by the current transaction.

    Rate this question:

  • 17. 

    .which clause has to be used to reference the current row from an explicit cursor

    • A.

      FOR UPDATE OF

    • B.

      Where current of

    • C.

      Any cursor Attribute

    • D.

      Any Cursor Variable

    Correct Answer
    B. Where current of
    Explanation
    The "where current of" clause is used to reference the current row from an explicit cursor. This clause is used in conjunction with the UPDATE statement to specify that the update should be performed on the current row pointed to by the cursor. It is a way to uniquely identify and update the current row without having to specify any additional conditions or criteria.

    Rate this question:

  • 18. 

    . BEGIN FOR I in 1..10 LOOP If I=2 or I=4 Null; ELSE INSERT into easn(record) values (I); End if; Commit; END LOOP; ROLLBACK: END: How many values will have been inserted into easn table?

    • A.

      0

    • B.

      4

    • C.

      8

    • D.

      10

    Correct Answer
    C. 8
    Explanation
    The loop iterates from 1 to 10. If the value of I is 2 or 4, the loop will skip the insertion statement and move to the next iteration. For all other values of I, the INSERT statement will be executed, inserting the value of I into the easn table. Since the values 2 and 4 are skipped, there will be 8 values (1, 3, 5, 6, 7, 8, 9, 10) inserted into the easn table.

    Rate this question:

  • 19. 

    DECLARE             V_NAME CONSTANT NUMBER(9);         What is the error with the above statment ?

    • A.

      No error

    • B.

      Should initialize with value.

    • C.

      Should not mention width for data type.

    • D.

      Syntax error that constant should be after data type

    Correct Answer
    B. Should initialize with value.
    Explanation
    The correct answer is "Should initialize with value." In PL/SQL, when declaring a constant, it is mandatory to initialize it with a value. Therefore, the declaration of V_NAME constant without assigning a value to it is incorrect.

    Rate this question:

  • 20. 

    Declare           V_lower := 4;           V_upper := 4;        Begin             for i in V_lower .. V_upper loop                     ---------            End loop; end;    How many times does the loop execute?

    • A.

      0

    • B.

      1

    • C.

      4

    • D.

      5

    Correct Answer
    B. 1
    Explanation
    The loop executes only once because the lower and upper bounds of the loop (V_lower and V_upper) are both set to 4. Since the lower and upper bounds are the same, the loop will only iterate once.

    Rate this question:

  • 21. 

    FOR I IN 1..6        LOOP INSERT INTO val_table(vcol)  VALUES(I); COMMIT; END LOOP; ROLLBACK; How many rows will be inserted after the execution of above PL/SQL?

    • A.

      0

    • B.

      6

    • C.

      3

    • D.

      10

    Correct Answer
    B. 6
    Explanation
    The given PL/SQL code uses a loop to insert values into the "val_table" table. The loop iterates from 1 to 6, so it will insert 6 rows into the table. After the loop, a COMMIT statement is executed, which saves the changes made by the INSERT statements. However, immediately after the COMMIT, a ROLLBACK statement is executed, which undoes all the changes made since the last COMMIT. Therefore, even though 6 rows were inserted, they will be rolled back and no rows will be permanently inserted into the table.

    Rate this question:

  • 22. 

    IF in_val > 2000 THEN out_val = in_val + 1;       ELSIF in_val > 3000 THEN          out_val = in_val + 2;       ELSIF in_val > 4000 THEN out_val = in_val + 3; ELSE out_val = in_val + 4; END IF;     What is the out_val value, if the in_val is 2500?

    • A.

      2501

    • B.

      2502

    • C.

      2504

    • D.

      NULL

    Correct Answer
    A. 2501
    Explanation
    The value of out_val will be 2501 because the first condition (in_val > 2000) is true for the given input of 2500. Therefore, the code will execute the corresponding statement (out_val = in_val + 1) and assign the value of 2501 to out_val.

    Rate this question:

  • 23. 

    . What is the value of v_price if the following block is executed with v_value=250 declare v_value number(6,2); v_price number(6,2); begin if v_value>150 then v_price=2500; elsif v_value>200 then v_price=3500; elsif v_value>300 then v_price=4500; else v_price=null; end if;

    • A.

      Null

    • B.

      4500

    • C.

      3500

    • D.

      2500

    Correct Answer
    D. 2500
    Explanation
    The value of v_price would be 2500 if the block is executed with v_value=250. This is because the condition v_value>150 is true, so the first branch of the if-else statement is executed and v_price is assigned the value of 2500.

    Rate this question:

  • 24. 

    In which situation you use make use of explicit cursor?

    • A.

      When the select returns zero rows

    • B.

      When the select returns one row

    • C.

      When the select returns more than one row

    • D.

      When DML operations are performed

    Correct Answer
    C. When the select returns more than one row
    Explanation
    When the select statement returns more than one row, it is necessary to use an explicit cursor. An explicit cursor allows the programmer to retrieve and manipulate multiple rows returned by a select statement. By using an explicit cursor, the programmer can fetch each row one by one and perform necessary operations on them. This is especially useful when dealing with complex queries that return multiple rows of data.

    Rate this question:

  • 25. 

    You want to create a cursor that can be used several times in a block. Selecting a different active set each time that it is opened. Which type of cursor do you create?

    • A.

      A cursor FOR loop.

    • B.

      A multiple selection cursor.

    • C.

      A cursor for each active set.

    • D.

      A cursor that uses parameters

    Correct Answer
    D. A cursor that uses parameters
    Explanation
    A cursor that uses parameters allows for the creation of a cursor that can be used multiple times in a block, with the ability to select a different active set each time it is opened. By using parameters, the cursor can be dynamically modified to retrieve different data sets based on the specified parameters, providing flexibility and reusability in the code.

    Rate this question:

  • 26. 

    Which statement is true when writing a cursor FORloop?

    • A.

      You must explicitly fetch the rows within a cursor FOR loop.

    • B.

      You must explicitly open the cursor prior to the cursor FOR loop

    • C.

      You must explicitly close the cursor prior to the end of program

    • D.

      You do not explicitly open, fetch or close a cursor within a cursor FOR loop

    • E.

      You must explicitly declare the record variable that holds the row returned fromthe cursor.

    Correct Answer
    D. You do not explicitly open, fetch or close a cursor within a cursor FOR loop
    Explanation
    In a cursor FOR loop, you do not need to explicitly open, fetch, or close a cursor. The loop automatically opens the cursor, fetches the rows, and closes the cursor when all the rows have been processed. Therefore, the correct answer is that you do not explicitly open, fetch, or close a cursor within a cursor FOR loop.

    Rate this question:

  • 27. 

    Which statement about implicit cursors is true?

    • A.

      Implicit cursors are declared implicitly only for DML statements.

    • B.

      Implicit cursors are declared implicitly for all the DML and SELECTstatements.

    • C.

      Programmers need to close all the implicit cursors before the end of the PL/SQLprogram.

    • D.

      Programmers can declare implicit cursors by using the cursor type in thedeclaration section.

    Correct Answer
    B. Implicit cursors are declared implicitly for all the DML and SELECTstatements.
    Explanation
    Implicit cursors are automatically declared for all DML (Data Manipulation Language) and SELECT statements in PL/SQL. Programmers do not need to explicitly declare them using the cursor type in the declaration section. Additionally, programmers do not need to close implicit cursors before the end of the PL/SQL program.

    Rate this question:

  • 28. 

    You need to create a PL/SQL program to insert records into employee table. Which block of code successfully uses the insert command?

    • A.

      DECLAREv_hiredate DATE:=SYSDATE:BEGININSERT INTO emp(empnp, ename, hiredate, deptno)VALUES(empno_sequence.nextval, ‘and name’,v_hiredate and deptno)

    • B.

      DECLAREv-hiredate DATE:=SYSDATE:BEGININSERT INTO emp(empnp,ename,hiredate,deptno)

    • C.

      DECLAREv-hiredate DATE:=SYSDATE:BEGININSERT INTO emp(empnp,ename,hiredate)VALUES(empno_sequence.nextval, name, v_hiredate)END:

    • D.

      DECLAREv-hiredate DATE:=SYSDATE:BEGININSERT INTO emp(empnp,ename,heridate,deptno)VALUES(empno_sequence.nextval, ‘and name’,v_hitedate and deptno)Job=ClerkEND:

    Correct Answer
    C. DECLAREv-hiredate DATE:=SYSDATE:BEGININSERT INTO emp(empnp,ename,hiredate)VALUES(empno_sequence.nextval, name, v_hiredate)END:
    Explanation
    The block of code successfully uses the insert command because it declares a variable v_hiredate of type DATE and initializes it with the current date using the SYSDATE function. It then uses the INSERT INTO statement to insert a record into the emp table. It specifies the columns empnp, ename, and hiredate in the target table and provides values for these columns using the empno_sequence.nextval (to generate a unique empno), the variable name, and the variable v_hiredate. The code is enclosed within a BEGIN-END block, indicating the start and end of the PL/SQL program.

    Rate this question:

  • 29. 

    Declare X NUMBER; V_SAL NUMBER; V_found VARCHAR2(10) := ‘TRUE’ Begin X:=1; V_SAL :=1000; Declare V_found VARCHAR2(10); Y NUMBER; Begin IF (V_sal>500) THEN V_found := ‘YES’; END IF; DBMS_OUTPUT.PUT_LINE(‘value f V_found is’ || V_found); DBMS_OUTPUT.PUT_LINE (‘value f V_found is’ || V_found); Y:20; END; DBMS_OUTPUT.PUT_LINE (‘value f V_found is’ || V_found); DBMS_OUTPUT.PUT_LINE (‘value f Y is’ || TO_CHAR (Y); END; Why does this code produce an error when executed?

    • A.

      The value f V_found cannot be YES.

    • B.

      Variable V_found is declared at more than one location.

    • C.

      Variable Y is declared in the inner block and referenced in the outer block.

    • D.

      Variable V_sal is declared in the outer block and referenced in the inner block.

    Correct Answer
    C. Variable Y is declared in the inner block and referenced in the outer block.
    Explanation
    The code produces an error because variable Y is declared in the inner block and referenced in the outer block. In PL/SQL, variables declared in a block are only accessible within that block and any nested blocks within it. So, when the code tries to reference the variable Y in the outer block, it results in an error because Y is not declared in the outer block.

    Rate this question:

  • 30. 

    Given the executable section of a PL/SQL block FOR employee_record IN Salary_Cursor Loop employee_id_table (employee_id):=employee_record.last_name; END Loop;  Close Salary_Cursor; END; Why does this section cause an error?

    • A.

      The cursor needs to be opened.

    • B.

      Terminating conditions are missing.

    • C.

      No FETCH statements were issued

    • D.

      The cursor does not need to be explicitly closed.

    Correct Answer
    D. The cursor does not need to be explicitly closed.
    Explanation
    The section causes an error because the cursor does not need to be explicitly closed. In PL/SQL, cursors are automatically closed when the block ends. Therefore, attempting to close the cursor explicitly will result in an error.

    Rate this question:

  • 31. 

    In which section of a PL/SQL block is a user defined exception raised?

    • A.

      Heading.

    • B.

      Executable.

    • C.

      Declarative.

    • D.

      Exception handling.

    Correct Answer
    B. Executable.
    Explanation
    In a PL/SQL block, a user-defined exception is raised in the executable section. This section contains the actual code and logic of the program where the exception can be raised. The executable section is where the program performs its tasks and executes statements, and it is within this section that the user-defined exception can be triggered based on certain conditions or events.

    Rate this question:

  • 32. 

    When handler can handel only system defined exceptions

    • A.

      True

    • B.

      False

    Correct Answer
    B. False
    Explanation
    The given answer is False. This means that the statement "When handler can handle only system defined exceptions" is not true. In programming, a handler can handle both system-defined exceptions and user-defined exceptions. A handler is a block of code that is executed when an exception occurs, and it can be designed to handle any type of exception, regardless of whether it is a system-defined exception or a custom exception created by the programmer. Therefore, the statement is incorrect.

    Rate this question:

  • 33. 

    For loop counter variable can be referenced in inside as assignment target

    • A.

      True

    • B.

      False

    Correct Answer
    B. False
    Explanation
    The statement is false because in a for loop, the counter variable is only accessible within the loop itself and cannot be referenced outside of it. Once the loop is completed, the counter variable is no longer accessible or usable.

    Rate this question:

  • 34. 

    Trigger timing refers how many times trigger should fire

    • A.

      True

    • B.

      False

    Correct Answer
    B. False
    Explanation
    The statement is incorrect. Trigger timing does not refer to how many times a trigger should fire. Instead, trigger timing refers to when a trigger should fire, such as before, after, or instead of a specific event or action.

    Rate this question:

  • 35. 

    Explict cursor has to fectch at least one row

    • A.

      True

    • B.

      False

    Correct Answer
    B. False
    Explanation
    An explicit cursor does not have to fetch at least one row. It can be used to fetch multiple rows or no rows at all, depending on the conditions specified in the cursor query. The explicit cursor allows the programmer to have more control over the fetching process and can be used to fetch specific rows based on certain criteria. Therefore, the statement "explicit cursor has to fetch at least one row" is false.

    Rate this question:

  • 36. 

    Which SQL  function can not be used in PL/SQL?

    • A.

      Ceil

    • B.

      Substr

    • C.

      Decode

    • D.

      All of the above

    Correct Answer
    C. Decode
    Explanation
    The decode function is a SQL function that cannot be used in PL/SQL. PL/SQL is a procedural language used for writing Oracle database triggers, stored procedures, and functions. It has its own set of functions and syntax, and the decode function is not part of it. Therefore, the correct answer is decode.

    Rate this question:

  • 37. 

    Create or replace procedure p1(a number default 12) as begin dbms_output.put_line('hi'); end; How does you execute the above procedure.

    • A.

      Exec p1

    • B.

      Exec :x := p1(10)

    • C.

      Both ways

    • D.

      None of the above

    Correct Answer
    A. Exec p1
    Explanation
    To execute the above procedure, you can use the "exec p1" command. This command will execute the procedure and display the output "hi" using the dbms_output.put_line statement inside the procedure. The other option ":x := p1(10)" is not valid because it is attempting to assign a value to a variable, which is not necessary in this case. Therefore, the correct way to execute the procedure is by using the "exec p1" command.

    Rate this question:

  • 38. 

    How many min number of times a simple loop will execute?

    • A.

      0

    • B.

      1

    • C.

      Undefined

    • D.

      None of the above

    Correct Answer
    B. 1
    Explanation
    A simple loop will always execute at least once because the loop condition is checked at the end of each iteration. If the condition is not met initially, the loop will still execute once before terminating. Therefore, the minimum number of times a simple loop will execute is 1.

    Rate this question:

  • 39. 

    Begin declare    a number:=56;    b number:=0;    c number; begin   c:=a/b; dbms_output.put_line('hi'); exception when zero_divide then dbms_output.put_line('error'); raise; end; exception when others then dbms_output.put_line('error2'); end; what is the output

    • A.

      It throws an exception

    • B.

      Errorerror2

    • C.

      Syntax error

    • D.

      None of the above

    Correct Answer
    B. Errorerror2
    Explanation
    The code snippet declares three variables, "a" with a value of 56, "b" with a value of 0, and "c" without an initial value. It then attempts to divide "a" by "b", which is a division by zero error. This error is caught in the first exception block, where it prints "error" and raises the exception again. After that, the code falls into the second exception block and prints "error2". Therefore, the output of this code would be "errorerror2".

    Rate this question:

  • 40. 

    Declare   r1 Exception; begin   raise r1; exception  when r1 then  raise_application_error(-22221,'error1') ; end; What is the output?

    • A.

      It raises application error and program terminates abnormally

    • B.

      It throws syntax error

    • C.

      Both options are correct

    • D.

      None of the above

    Correct Answer
    B. It throws syntax error
    Explanation
    The given code snippet raises a syntax error. In the "raise" statement, the variable "r1" is being raised, but it has not been declared or assigned a value. Therefore, the code is not valid and will throw a syntax error.

    Rate this question:

Quiz Review Timeline +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

  • Current Version
  • Aug 22, 2023
    Quiz Edited by
    ProProfs Editorial Team
  • Dec 23, 2010
    Quiz Created by
    Sudha_test
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.