Oracle Database Developer

Oracle server version information is stored in a table called: PRODUCT_COMPONENT_VERSION. You can use a simple SELECT statement to view the version information like this:

>.insqlplus

Enter user-name: SYSTEM/globalguideline AS SYSDBA

Connected to an idle instance

SQL> COL PRODUCT FORMAT A35

SQL> COL VERSION FORMAT A15

SQL> COL STATUS FORMAT A15

SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT VERSION STATUS

----------------------------------- ----------- ----------

NLSRTL 10.2.0.1.0 Production

Oracle Database 10g Express Edition 10.2.0.1.0 Product

PL/SQL 10.2.0.1.0 Production

TNS for 32-bit Windows: 10.2.0.1.0 Production

f you Oracle server or client installed on your windows system, you can start the command-line SQL*Plus in two ways:

1. Click Start > All Programs > Oracle ... > Start SQL Command Line. The SQL*Plus command window will show up with a message like this:

SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>

2. Click Start > Run..., enter "cmd" and click OK. A Windows command window will show up. You can then use Windows commands to start the command-line SQL*Plus as shown in the tutorial exercise below:

>cd c:oraclexeapporacleproduct10.2.0server

>.insqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

Copyright (c) 1982, 2005, Oracle. All rights reserved.

A "connect identifier" is an identification string of a single set of connection information to a specific target database instance on a specific Oracle server.

Connect identifiers are defined and stored in a file called tnsnames.ora located in $ORACLE_HOME/network/admin/ directory. Here is one example of a "connect identifier" definition:

ggl_XE =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)

(HOST = www.rendc.org)

(PORT = 1521)

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = XE)

)

)

The above "connect identifier" defines "TNS_XE" with the following connection information:

* The network hostname: www.rendc.org.

* The network port number: 1521.

* The name of the target database instance: XE.

In order to connect a SQL*Plus session to an Oracle server, you need to:

1. Obtain the connection information from the Oracle server DBA.

2. Define a new "connect identifier" called "ggl_XE" in your tnsnames.org file with the given connection information.

3. Run the CONNECT command in SQL*Plus as shown in the tutorial exercise below:

>cd c:oraclexeapporacleproduct10.2.0server

>.insqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue ...

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> CONNECT ggl/retneclgg@ggl_XE;

Connected.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE

---------

05-MAR-06

Of course, you will get an error, if you use a wrong connect identifier. Here is an example of how SQL*Plus react to a wrong connect identifier:

SQL> CONNECT ggl/retneclgg@WRONG;

ERROR:

ORA-12154: TNS:could not resolve the connect identifier

specified

Warning: You are no longer connected to ORACLE.

What you need to do in this case:

* Check the CONNECT command to make sure that the connect identifier is entered correctly.

* Check the tnsnames.ora file to make sure that the connect identifier is defined correctly.

* Check the tnsnames.ora file to make sure that there is no multiple definitions of the same connect identifier.

* Check your files system to see if you have multiple copies of tnsnames.ora in different Oracle home directories, because you installed multiple versions of Oracle. If you do have multiple copies, make sure your SQL*Plus session is picking up the correct copy of tnsnames.ora.

There are 4 types of commands you can run at the SQL*Plus command line prompt:

1. SQL commands - Standard SQL statements to be executed on target database on the Oracle server. For example: "SELECT * FROM ggl_faq;" is a SQL command.

2. PL/SQL commands - PL/SQL statements to be executed by the Oracle server. For example: "EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to www.rendc.org')" runs a PL/SQL command.

SQL*Plus commands - Commands to be executed by the local SQL*Plus program itself. For example: "SET NULL 'NULL'" is a SQL*Plus command.

OS commands - Commands to be executed by the local operating system. For example: "HOST dir" runs an operating system command on the local machine.

SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list:

* SET AUTOCOMMIT OFF - Turns off the auto-commit feature.

* SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output.

* SET HEADING OFF - Stops displaying the header line of the query output.

* SET LINESIZE 256 - Sets the number of characters per line when displaying the query output.

* SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output.

* SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output.

* SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output.

* SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output.

* SET TIMING ON - Asks SQL*Plus to display the command execution timing data.

* SET WRAP OFF - Turns off the wrapping feature when displaying query output.

Normally, when you run a SELECT statement in SQL*Plus, the output will be displayed on your screen. If you want the output to be saved to local file, you can use the "SPOOL fileName" command to specify a local file and start the spooling feature. When you are done with your SELECT statement, you need to close the spool file with the "SPOOL OFF" command. The following tutorial exercise gives you a good example:

SQL> connect HR/retneclgg

SQL> SET HEADING OFF

SQL> SET FEEDBACK OFF

SQL> SET LINESIZE 1000

SQL> SPOOL empemployees.lst

SQL> SELECT * FROM EMPLOYEES;

......

SQL> SPOOL OFF

You should get all records in employees.lst with fixed length fields.

Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views:

* An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.

* A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.

* A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.