Java on Oracle interview questions

1. What is JServer and what is it used for? Oracle JServer Option is a Java Virtual Machine (Java VM) which runs within the Oracle database server’s address space. Oracle also provides a JServer Accelerator to compile Java code natively. This speeds up the execution of Java code by eliminating interpreter overhead.
2. How does one install the Oracle JServer Option?Follow these steps to activate the Oracle JServer/ JVM option:
   1. Make sure your database is started with large java_pool_size (>20M) and shared_pool_size (>50M) INIT.ORA parameter values.
   2. Run the $ORACLE_HOME/javavm/install/initjvm.sql script from SYS AS SYSDBA to install the Oracle JServer Option on a database.
   3. Grant JAVAUSERPRIV to users that wants to use Java:
      SQL> GRANT JAVAUSERPRIV TO SCOTT;
   4. The rmjvm.sql script can be used to deinstall the JServer option from your database.
   5. Follow the steps in the Oracle Migrations Guide to upgrade or downgrade the JServer option from one release to
      another.
3. ce code into the database? Use the “CREATE OR REPLACE JAVA SOURCE” command or “loadjava” utility. Loaded code can be viewed by selecting from the USER_SOURCE view.
4. Why does one need to publish Java in the database? Publishing Java classes on the database makes it visible on a SQL and PL/SQL level. It is important to publish your code before calling it from SQL statements or PL/SQL code.
5. What is JDBC and what is it used for? JDBC is a set of classes and interfaces written in Java to allow other Java programs to send SQL statements to a relational database management system. Oracle provides three categories of JDBC drivers: (a) JDBC Thin Driver (No local Net8 installation required/ handy for applets), (b) JDBC OCI for writing stand-alone Java applications, (c) JDBC KPRB driver (default connection) for Java Stored Procedures and Database JSP’s.
6. How does one connect with the JDBC Thin Driver?
   The the JDBC thin driver provides the only way to access Oracle from the Web (applets). It is smaller and faster than the OCI drivers, and doesn’t require a pre-installed version of the JDBC drivers.

   import java.sql.*;
   class dbAccess {
    public static void main (String args []) throws SQLException
    {
          DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
   
          Connection conn = DriverManager.getConnection
               (”jdbc:oracle:thin:@hostname:1526:orcl”, “scott”, “tiger”);
                               // @machineName:port:SID,   userid,  password
   
          Statement stmt = conn.createStatement();
          ResultSet rset = stmt.executeQuery(”select BANNER from SYS.V_$VERSION”);
          while (rset.next())
                System.out.println (rset.getString(1));   // Print col 1
          stmt.close();
    }
   }
   

7. How does one connect with the JDBC OCI Driver? One must have Net8 (SQL*Net) installed and working before attempting to use one of the OCI drivers.
   

   import java.sql.*;
   class dbAccess {
    public static void main (String args []) throws SQLException
    {
          try {
                Class.forName (”oracle.jdbc.driver.OracleDriver”);
          } catch (ClassNotFoundException e) {
                e.printStackTrace();
          }
   
          Connection conn = DriverManager.getConnection
               (”jdbc:oracle:oci8:@hostname_orcl”, “scott”, “tiger”);
                       // or oci7 @TNSNames_Entry,    userid,  password
   
          Statement stmt = conn.createStatement();
          ResultSet rset = stmt.executeQuery(”select BANNER from SYS.V_$VERSION”);
          while (rset.next())
                System.out.println (rset.getString(1));   // Print col 1
          stmt.close();
    }
   }
   

8. How does one connect with the JDBC KPRB Driver? One can obtain a handle to the default or current connection (KPRB driver) by calling the OracleDriver.defaultConenction() method. Please note that you do not need to specify a database URL, username or password as you are already connected to a database session. Remember not to close the default connection. Closing the default connection might throw an exception in future releases of Oracle.
   

   import java.sql.*;
   class dbAccess {
    public static void main (String args []) throws SQLException
    {
          Connection conn = (new oracle.jdbc.driver.OracleDriver()).defaultConnection();
   
          Statement stmt = conn.createStatement();
          ResultSet rset = stmt.executeQuery(”select BANNER from SYS.V_$VERSION”);
          while (rset.next())
                System.out.println (rset.getString(1));   // Print col 1
          stmt.close();
    }
   }
   

9. What is SQLJ and what is it used for? SQLJ is an ANSI standard way of coding SQL access in Java. It provides a Java precompiler that translates SQLJ call to JDBC calls. The idea is similar to that of other Oracle Precompilers.
10. How does one deploy SQLJ programs? Use the sqlj compiler to compile your *.sqlj files to *.java and *.ser files. The *.ser files contain vendor specific database code. Thereafter one invokes the javac compiler to compile the .java files to *.class files. The *.class and *.ser files needs to be deployed.
11. What is JDeveloper and what is it used for? JDeveloper is the Oracle IDE (Integrated Development Environment) for developing SQLJ and JDBC programs, applets, stored procedures, EJB’s, JSP’s etc.
12. What is InfoBus DAC and what is it used for? InfoBus DAC (Data Aware Controls) is a standard Java extension used in JDeveloper to create data aware forms. It replaced the JBCL interface that were used in JDeveloper V1 and V2.
13. What is a JSP and what is it used for? Java Server Pages (JSP) is a platform independent presentation layer technology that comes with SUN’s J2EE platform. JSPs are normal HTML pages with Java code pieces embedded in them. JSP pages are saved to *.jsp files. A JSP compiler is used in the background to generate a Servlet from the JSP page.
14. What is the difference between ASP and JSP? Active Server Pages (ASP) is a Microsoft standard, which is easier to develop than Java Server Pages (JSP). However ASP is a proprietary technology and is less flexible than JSP. For more information about ASP, see the Oracle ASP FAQ.
15. How does one invoke a JSP? A JSP gets invoked when you call a *.jsp file from your Web Server like you would call a normal *.html file. Obviously your web server need to support JSP pages and must be configured properly to handle them.
16. How does a JSP gets executed? The first time you call a JSP, a servlet (*.java) will be created and compiled to a .class file. The class file is then executed on the server. Output produced by the servlet is returned to the web browser. Output will typically be HTML or XML code.
17. What is a Java Stored Procedure/ Trigger? A Java Stored Procedure is a procedure coded in Java (as opposed to PL/SQL) and stored in the Oracle database. Java Stored procedures are executed by the database JVM in database memory space. Java Stored Procedures can be developed in JDBC or SQLJ. Interfacing between PL/SQL and Java are extremely easy. Please note that Java Stored procedures are by default executed with invokers rights. PL/SQL procedures are by default executed with defines rights.

Oracle interview questions

1. What’s the command to see the current user name? Sql> show user;
2. What’s the command to change the SQL prompt name?
   

   SQL> set sqlprompt “database-1 > ”
   database-1 >
   database-1 >

3. How do you switch to DOS prompt from SQL prompt? SQL> host
4. How do I eliminate duplicate rows in an Oracle database?
   

   SQL> delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name);

   or

   SQL> delete duplicate_values_field_name dv from table_name ta where rowid < (select min(rowid) from table_name tb where ta.dv=tb.dv);

5. How do I display row number with records? Use the row-num pseudocolumn with query, like
   

   SQL> select rownum, ename from emp;

6. How do you display the records within a given range?
   

   select rownum, empno, ename from emp where rowid in
   (select rowid from emp where rownum < =&rangeend
   minus
   select rowid from emp where rownum<&rangebegin);

7. The NVL function only allows the same data type. But here’s the task: if the commission field is null, then the text “Not Applicable” should be displayed, instead of blank space. How do you write the query?
   

   SQL> select nvl(to_char(comm.),’Not Applicable’) from emp;

8. Explain explicit cursor attributes. There are four cursor attributes used in Oracle: cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN
9. Explain implicit cursor attributes. Same as explicit cursor but prefixed by the word SQL: SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN
10. How do you view version information in Oracle?
    

    SQL> select banner from $version;

Oracle interview questions

1. What is an oracle instance?
2. What is a view?
3. What is referential integrity?
4. Name the data dictionary that stores user-defined constraints?
5. What is a collection of privileges?
6. What is a snapshot?
7. What is a synonym?
8. What is a cursor?
9. What is a sequence?
10. What is a trigger?
11. What is an exception?
12. What is a partition of table?
13. What are pseudo-columns in SQL? Provide examples.
14. What are the Data Control statements?
15. What is a schema?
16. What is a type?
17. What is a data model?
18. What is a relation?
19. Advantages of redo log files?
20. What is an Archiver?
21. What is a database buffer cache?
22. What are the background processes in Oracle?
23. %type and %rowtype are attributes for…?
24. What are the steps in a two-phase commit?
25. What is a union, intersect, minus?
26. What is a join, explain the types of joins?
27. What is a co-related sub-query?
28. ODBC stands for…?
29. Data-type used to work with integers is?
30. Describe data models?
31. Describe the Normalization principles?
32. What are the types of Normalization?
33. What is de-normalization?

Interview questions for Oracle database administrator

1. Differentiate between TRUNCATE and DELETE
2. What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?
3. Can you use a commit statement within a database trigger?
4. What is an UTL_FILE.What are different procedures and functions associated with it?
5. Difference between database triggers and form triggers?
6. What is OCI. What are its uses?
7. What are ORACLE PRECOMPILERS?
8. What is syntax for dropping a procedure and a function? Are these operations possible?
9. Can a function take OUT parameters. If not why?
10. Can the default values be assigned to actual parameters?
11. What is difference between a formal and an actual parameter?
12. What are different modes of parameters used in functions and procedures?
13. Difference between procedure and function.
14. Can cursor variables be stored in PL/SQL tables.If yes how. If not why?
15. How do you pass cursor variables in PL/SQL?
16. How do you open and close a cursor variable.Why it is required?
17. What should be the return type for a cursor variable.Can we use a scalar data type as return type?
18. What is use of a cursor variable? How it is defined?
19. What WHERE CURRENT OF clause does in a cursor?
20. Difference between NO DATA FOUND and %NOTFOUND
21. What is a cursor for loop?
22. What are cursor attributes?
23. Difference between an implicit & an explicit cursor.
24. What is a cursor?
25. What is the purpose of a cluster?
26. How do you find the numbert of rows in a Table ?
27. Display the number value in Words?
28. What is a pseudo column. Give some examples?
29. How you will avoid your query from using indexes?
30. What is a OUTER JOIN?
31. Which is more faster - IN or EXISTS?
32. When do you use WHERE clause and when do you use HAVING clause?
33. There is a % sign in one field of a column. What will be the query to find it?
34. What is difference between SUBSTR and INSTR?
35. Which datatype is used for storing graphics and images?
36. What is difference between SQL and SQL*PLUS?
37. What is difference between UNIQUE and PRIMARY KEY constraints?
38. What is difference between Rename and Alias?
39. What are various joins used while writing SUBQUERIES?

Typical Oracle questions

1. Tell us about yourself, your background.
2. What are the three major characteristics that you bring to this company?
3. What version of Oracle were you running?
4. How many databases did the organization have and what sizes?
5. What motivates you to do a good job?
6. What two or three things are most important to you at work?
7. What qualities do you think are essential to be successful in this kind of work?
8. What courses did you attend? What job certifications do you hold?
9. What subjects/courses did you excel in? Why?
10. What subjects/courses gave you trouble? Why?
11. How does your previous work experience prepare you for this position?
12. How do you define ’success’?
13. What has been your most significant accomplishment to date?
14. Describe a challenge you encountered and how you dealt with it.
15. Describe a failure and how you dealt with it.
16. Describe the ‘ideal’ job… the ‘ideal’ supervisor.
17. What leadership roles have you held?
18. What prejudices do you hold?
19. What do you like to do in your spare time?
20. What are your career goals (a) 3 years from now; (b) 10 years from now?
21. How does this position match your career goals?
22. What have you done in the past year to improve yourself?
23. In what areas do you feel you need further education and training to be successful?
24. What do you know about our company?
25. Why do you want to work for this company. Why should we hire you?
26. Where do you see yourself fitting in to this organization initially? How about in 5 years?
27. Why are you looking for a new job?
28. How do you feel about re-locating?
29. Are you willing to travel?
30. What are your salary requirements?
31. When would you be available to start if you were selected?
32. Did you use online or off-line backups?
33. If you have to advise a backup strategy for a new application, how would you approach it and what questions will you ask?
34. If a customer calls you about a hanging database session, what will you do to resolve it?
35. Compare Oracle to any other database that you know. Why would you prefer to work on one and not on the other?