Oracle 11g PL/SQL Interview Questions For Software Testers



Software testing - Questions and Answers - Oracle 11g PL/SQL Interview Questions
  1. Question: what are the compound symbols?
    Answer: := assignment operator
    .. range operator
    || concatenation operator
    -- single-line comment indicator
    all about 16 symbols
  2. Question: What is NUMBER datatype? Answer: You use the NUMBER datatype to store fixed-point or floating-point numbers of virtually any size.
  3. Question: What is varchar2 data type

    Answer: varchar2 data type can be between 1 and 4000 bytes for the VARCHAR2 column. For example, assume you declare a column VARCHAR2 with a maximum size of 50 characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece stores only the 10 characters (10 bytes), not 50.

  4. What is tnsnames.ora
    Answer: tnsnames.ora is a SQL*Net configuration file that normally resides in the ORACLE_HOME/network/admin directory.---- keeps names to all databases to connect.
  5. What is Oracle SQL Developer?
    Answer: Oracle SQL Developer is a free graphical tool for database development. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own. SQL Developer enhances productivity and simplifies your database development tasks. can be used for Unit Testing
    SQL Developer can connect to any Oracle Database version 10g and later and runs on Windows, Linux and Mac OSX.
    Tips on SQL Developer:
    View -> dbms output. click green crest and activate your account (student. click to execute code.
    dbms_output ---- can be used for debugging
    The PL/SQL dbms_output package has a put_line procedure to allow you to write data to flat file or to direct your PL/SQL output to a screen.
    set serveroutput on
    Begin
    dbms_output.put_line('Hello Tester');
    End;
    /
  6. describe the following code:
    b := null;
    If b Then -- not true, go to elsif
    c := 'T';
    Elsif Not b Then -- not true, go to else
    c := 'F';
    Else
    c := 'N';
    End If;
  7. FIX all errors below:
    Declare
    x number;
    c char;
    n number;
    Begin
    If n = 0
    c := 'T';
    End If;
    If n = 0 Then
    x := x + 1;
    c := 'T'
    End If;
    If n = 0 Then
    c := 'T';
    EndIf;
    If n = 0 Then
    c := 'T';
    End If
    If n = 0 Then
    c := 'T';
    Elseif n = 1 Then
    c := 'F';
    End If;
    If n = 0 Then
    c := 'T';
    Else If n = 1 Then
    End If;
    End;

  8. Question: What is a cursor in PL/SQL?

    Oracle creates a memory area, known as context area, for processing an SQLstatement, which contains all information needed for processing the statement, forexample, number of rows processed, etc. A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor.

  9. What are the differencies between implicit and explisit coursor?
  10. What are the differences between strong and weak Cursor variables?
  11. what are Bulk Binds?
  12. Question: How to create login for tester with only view privileges?
    Answer:
    1.Login as DBA
    2. SQL> CREATE USER TESTER
    IDENTIFIED BY PASSWORD1;
    3. SQL> GRANT CREATE SESSION TO TESTER;
    4. SQL> GRANT SELECT ANY TABLE TO TESTER;
    NOW YOU AN QUERY OR VIEW TRIGGERS
    5. SQL> DESC ALL_TRIGGERS;
    Default Oracle login SCOTT/tiger
  13. QUESTION: HOW TO EXECUTE FUNCTION AND SEE RESULT
    Answer:
    VAR MY_RETURN_RESULT VARCHAR2
    EXEC :MY_RETURN_RESULT := MY_FUNCTION(MY_PARAMETER);
    PRINT MY_RETURN_RESULT
  14. What is SGA?
    Answer: System Global Area
  15. QUESTION: - HOW TO SAVE YOUR QUERY AND RESULT IN THE TEXT FILE(working in SQLplus and SQL Developer):
    Answer:
    1. SPOOL C:\TEST_FILE.TXT
    2. ---QUERY
    3. SPOOL OFF
  16. What is RAISE statement;
    Answer:

    RAISE statement - raise the last exception. In Oracle PL/SQL, the RAISE statement is used to explicitly raise an exception within a PL/SQL block. It immediately stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler. It can be used to raise both system defined and user defined exceptions.


    BEGIN
    IF THEN
    RAISE WRITE_ERROR_TO_LOG_PROCEDURE;show errors
    END IF;

    EXCEPTION
    WHEN WRITE_WRITE_TO_LOG_PROCEDURE THEN
    ROLLBACK;
    END;
  17. What are the useful Oracle tools for testing?
    Answer: Go to DBMS_monitor to turn on SQL trace
  18. How to enable debugging SQL Developer version 3
    Answer:
    For debugging in SQL Developer version 3
    1. set priveledge for using debugger
    2. Run PL/SQL window opens
    3. Verify that debugger menu avalible
    4. Grant debug connect session, debug any procedure to tester
    5. see message Grant succeded
  19. Please explain the meaning of the following task:
    create public synonym for testing_staff
  20. Question: What is the output of the following command SQL> ED
    Answer: SQL> ED command starts notepad
  21. what are ROWID and ROWNUM in oracle 11?
  22. Question: Why long type does not allows indexing in ORACLE 11 and what data type that allows indexing we can use?
    Answer: For example LOB data type
  23. Please explain the following comments:
    No commit inside trigger! must be separate procedure.
  24. In ORACLE we can use quoted "x+y" and unquoted identifiers. Please provide explanation, differences and examples.
  25. Question: Is password in ORACLE 11 case sensitive or not?
    Answer: In ORACLE 11 a password can be set as not case sensitive
  26. The continue statement is new for ORACLE 11. Please describe it and give example of a continue statement.
  27. Question:What do you know about DBMS_LOB package?
    Answer: DBMS_LOB for work with big objects up to 4GB; Tell about Datatypes and Rules and Limits.
  28. Question: What different types of ORACLE/PLSQL joins do you know?
    Answer:
    Oracle SQL JOINS are used to retrieve data from multiple tables. An Oracle/ PLSQL
    JOIN is used whenever two or more tables are joined in a SQL statement.
    There are four different types of Oracle/ PLSQL joins:
     • Oracle INNER JOIN (or sometimes called SQL simple join)
     • Oracle LEFT OUTER JOIN (or sometimes called SQL LEFT JOIN)
     • Oracle RIGHT OUTER JOIN (or sometimes called SQL RIGHT JOIN)
     • Oracle FULL OUTER JOIN (or sometimes called SQL FULL JOIN)
  29. Question: Please tell us about some useful PL/SQL tips and tricks for testers that you know?
    1. set serveroutput on
    2. Set sql> show errors ---- for showing compiled errors for Procedure from error table
    3. Create and have a list of all user-defined exeptions
    4. SQL> DESC DICT - gives dictionary
    5. SQL> SELECT COUNT (*) FROM DICT; ----GIVES NUMBER OF DICTIONARY TABLES IF YOU CONNECT AS DBA.
    6.SQLCODE function returns the numeric code of the exception being handled.
    (Outside an exception handler, SQLCODE returns 0.)
    7. DBMS_OUTPUT.PUT_LINE('This is for testing and debugging');
    allows you to write information to a buffer throughout the execution of a trigger/procedure.
    especially useful for displaying PL/SQL debugging information.
  30. Question: Write a query to display which tables your user account has select access to:
    select table_name, tablespace_name
    from all_tables;
  31. Write a query to view table privileges that have been granted to your current user account:
    select grantee, table_name, privilege
    from user_tab_privs
    where grantee = sys_context('USERENV','CURRENT_USER')
    order by table_name, privilege;
  32. Question: When a user session is terminated, which processes are responsible for cleaning up and releasing locks? (Choose all that apply.)
    A. DBWn
    B. LGWR
    C. MMON
    D. PMON
    Answer : D. PMON is responsible for cleaning up failed user processes. It reclaims all the resources held by the user and releases all locks on tables and rows held by the user. No other process is involved in the session cleanup.
  33. Question: we have a table
    Address (
    street CHAR(20) NOT NULL,
    apartment CHAR(8),
    city CHAR(30) NOT NULL,
    );
    write a SQL statement that returns no apartment addresses only.
    Answer: SELECT * FROM Address WHERE apartment IS NULL;
  34. Question: What is alert log file?
    Answer: A file where Oracle Database writes information about the database start-
    ups, shutdown, check points, redo log switches, errors, and warning information.
    deadlock A special kind of lock conflict that prevents two or more transactions from
    completing because each transaction has a lock on a resource needed by the other transaction



On this page I put some PL/SQL interview questions. These PL/SQL interview questions are very simple and mainly were used for interviewing software testers who is involved in database PL/SQL testing or grey box testing.The interview questions found above are listed in order of complexity. However all new interview questions (regardless of there difficulty) will be added to the bottom of the list. You can find more SQL interview questions searching the WEB.

END Basic PL/SQL interview questions.
Find more on sql fundamentals

Interview Questions QA Main Page
© January 2012 Alex Samurin geocities.com/xtremetesting/ © eXtremeSoftwareTesting.com