Oracle 11g PL/SQL Interview Questions For Software Testers

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
    
    Oracle Database 12c
35. Question: What blocks storing column does not have nor need a setting for PCTFREE. (Select one: CLOB, LONG, IOT)
    Answer: CLOB
36. Question: Name two that requered data in the UNDO tablespace
    Answer: Both queries and DML need data in the UNDO tablespace.
37. Question: What database models support Oracle Database 12c?
    Answer: Oracle Database 12c provides support for the relational and object elational database models.
38. Question: Is Oracle SQL is a procedural query language?
    Answer: NO
39. Question: The acronym DDL stands for which type of Oracle SQL command? -
    Answer: The acronym DDL stands for - Data definition Language.
40. Question: Is for Oracle Database the alternative to quoting strings with single quotes is double quotes.
    Answer: NO
41. Question: What single-row function would be most appropriate to use to convert a DATE column to the format "YYYY/MM/DD HH:MI:SS" for a report?
    Answer: TO_CHAR
42. Question: You want to roll up totals by department, but only see the totals for two of the departments. To do this, you'll have to use the - and the - clauses.
    Answer: GROUP BY; HAVING
43. Question: Your query using set operators has multiple subqueries and you want to change the order of evaluation.
    What is the best method for changing the evaluation order?
    Answer: Put parentheses around pairs or queries that you want to run before any other operators.
44. Question: You're using SQL Developer and running some INSERT and UPDATE statements. What is the easiest way to perform a COMMIT on your work?
    Answer: Click on COMMIT icon at the top of SQL Window.
45. Question: Is a datafile in a (select one:SMALLFILE / BIGFILE) tablespace is limited to about 64 GB if you have a 16K block size.
    Answer: SMALLFILE
46. Question: What is the logical database structure is the most granular in an Oracle Database.
    Answer: data block
47. Question: What is the difference between a view starting with V$ and a view starting with GV$?
    Answer: The GV$ view contains an INST_ID column that identifies which database instance in the cluster this row is referencing.
48. A user created a table successfully, but the first time they tried to insert a row, it gave them an error message. What could be the problem?
    Answer: The table was created with SEGMENT CREATION DEFERRED
49. A user's session has failed due to network issues before a COMMIT was issued. What Oracle background process performs the cleanup operation?
    Answer: PMON

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.