Behavioral Oracle Database Interview Questions & Answers:

1. How To Connect ASP Pages to Oracle Servers?

If you are running Windows IIS Web server and serving ASP Web pages, you can get data from Oracle servers into your ASP pages through ODBC drivers. To do this, you need to install the correct Oracle ODBC driver and define a DSN on the IIS Web server.

Then you can use ADODB objects to connect to the Oracle server over the ODBC driver in your ASP pages. The tutorial example below gives you a good example:

<pre><%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=ggl_DSN;UID=ggl;PWD=retneclgg"
Set oRS = oConn.Execute("SELECT * FROM dev_faq")
Response.write("<p>Data from Oracle server via ODBC:")
Response.write("</pre>")
Do While NOT oRS.EOF
Response.Write(oRS("ID") & vbcrlf)
oRS.MoveNext
Loop
Response.write("</pre>")
oRS.close
oConn.close
%></pre>

2. How To Connect MS Access to Oracle Servers?

Once you got a DSN defined in the ODBC manager that connects to an Oracle server, you can connect a normal MS Access document to the Oracle server, and link an Access table to Oracle table. The tutorial below gives you a good example:

► Start MS Access with a new database file.
► Go to File menu.
► Select Get External Data.
► Select Import.... The Import dialog box shows up.
► Select Files of type: ODBC Database(). The Select Data Source dialog box shows up.
► Click the Machine Data Source tab. You should see the DSN name "ggl_DSN" you defined earlier.
► Select "ggl_DSN".
► Enter User Name: ggl.
► Enter Password: retneclgg.

You should see the Oracle ODBC Driver Connect dialog box as shown in the picture below:
Import Oracle tables to MS Access

Click the OK button to continue. You should see a list of tables available for you to import from the Oracle server as shown in the picture below:
Importing Oracle tables via ODBC

3. How To Define a Data Source Name (DSN) in ODBC Manager?

DSN (Data Source Name) is an ODBC connection identifier for Windows applications. Here is how you can define a DSN on your Windows system:

► Go to Control Panel.
► Go to Administrative Tools.
► Run Data Sources (ODBC).
► Go to System DSN tab.
► Click the Add button.
► Select the "Oracle in XE" driver.
► Enter Data Source Name: ggl_DSN.
► Enter Description: globalguideline DSN Oracle Setting.
► Enter TNS Service Name: XE.
► Click the Test Connection button.
► Enter User Name: ggl.
► Enter Password: retneclgg.
► Click the OK button.

You should see a "Connection successful" as shown in the following picture:
DSN Setting for Oracle ODBC Driver

4. How To Check the Oracle TNS Settings?

If you have installed an Oracle server or an Oracle client tool on your local system, the TNS is automatically installed with a simple configuration file, tnsnames.ora, to define Oracle connect identifiers.

For example, if you have Oracle XE server installed, you will have the tnsnames.ora located at oraclexeapporacleproduct10.2.0serverNETWORKADMIN. It should contain a connect identifier called XE defined as:

XE =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = localhost)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

5. How To Create Tables for ODBC Connection Testing?

If you want to follow the tutorial exercises in the sections below, you need to create a user account and a table for ODBC connection testing as shown here:

SQL> CONNECT system/retneclgg
Connected.

SQL> CREATE USER ggl IDENTIFIED BY retneclgg ACCOUNT UNLOCK;
User created.

SQL> GRANT CREATE SESSION TO ggl;
Grant succeeded.

SQL> GRANT CREATE TABLE TO ggl;
Grant succeeded.

SQL> ALTER USER ggl DEFAULT TABLESPACE USERS;
User altered.

SQL> ALTER USER dev QUOTA 4M ON USERS;
User altered.

SQL> connect ggl/retneclgg;
Connected.

SQL> CREATE TABLE dev_faq (id NUMBER);
SQL> INSERT INTO dev_faq VALUES (3);
SQL> INSERT INTO dev_faq VALUES (5);
SQL> INSERT INTO dev_faq VALUES (7);

6. How Can Windows Applications Connect to Oracle Servers?

A Windows application can connect to an Oracle server directly, if it knows how to use the Oracle TNS technology.

A Windows application can connect to an Oracle server indirectly through Windows ODBC manager, be caused offers ODBC drivers to support the ODBC API. The diagram below shows how MS Access can connect to an Oracle server through the ODBC driver:
Oracle ODBC Connection

7. How To Find Out What Oracle ODBC Drivers Are Installed?

To find out what Oracle ODBC drivers are installed on your Windows system, you can use the ODBC manager to look at them:

► Go to Control Panel.
► Go to Administrative Tools.
► Run Data Sources (ODBC).
► Go to System DSN tab.
► Click the Add button.

You should a list of all ODBC drivers installed on your system. Oracle ODBC drivers should be on the list. For example, your installed ODBC driver list could look like the one the picture bellow with two Oracle ODBC drivers, "Oracle in OraClient10g_home1" and "Oracle in XE":
Oracle ODBC Drivers

8. How To Install Oracle ODBC Drivers?

Oracle offers different ODBC drivers for different versions of Oracle servers. When you install an Oracle server or a client tools on your Windows system, the appropriate ODBC driver will be installed for you automatically.

If you want to install a specific ODBC driver, you need to go to http://www.oracle.com/technology/software/tech/windows/odbc/index.html. Download the right ODBC driver and follow the instructions to install it.

9. What Is Oracle Open Database Communication (ODBC)?

ODBC, Open Database Communication, a standard API (application program interface) developed by Microsoft for Windows applications to communicate with database management systems.

Oracle offers ODBC drivers to allow Windows applications to connect Oracle server through ODBC.

10. How To Invoke the Original Export Import Utilities?

If you really want to run the original export import utilities, you can still go to "bin" directory of the Oracle server path and run the "exp" or "imp" command. The tutorial exercise below tells you how to run the export and import utilities in help modes:

>cd oraclexeapporacleproduct10.2.0serverBIN

>exp help=y
You can let Export prompt you for parameters by entering the
EXP command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP
command followed by various arguments. To specify parameters,
you use:

Format: EXP KEYWORD=value or
KEYWORD=(value1,value2,...,valueN)

Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned
table
......

>imp help=y
......