Viewing Objects and Data with the DESCRIBE and SELECT Statements in Oracle 12c

By Chris Ruel, Michael Wessler

Perhaps the most common statements you will run in your Oracle 12c database as a Database Administrator (DBA) are the DESCRIBE and SELECT commands. After all, a big part of your job will be researching what is in the database and analyzing the current conditions.

Say you want to get some information about the jobs in your company.

  1. Open a terminal to your OS as the Oracle software owner.

  2. Set your environment with oraenv.

  3. Type <sqlplus> and press Enter.

  4. Type <hr> and press Enter.

  5. Type <your password> and press Enter.

  6. Type <DESCRIBE jobs> and press Enter.

    The following output appears:

    SQL> DESCRIBE jobs
     Name             Null?  Type
     ----------------------------- -------- --------------------
     JOB_ID            NOT NULL VARCHAR2(10)
     JOB_TITLE           NOT NULL VARCHAR2(35)
     MIN_SALARY               NUMBER(6)
     MAX_SALARY               NUMBER(6)
  7. To see the job_id and job_title, type

    <SELECT job_id, job_title FROM jobs;>

    and press Enter.

    You should see the following output:

    SQL> select job_id, job_title from jobs;
    JOB_ID   JOB_TITLE
    ---------- -----------------------------------
    AD_PRES  President
    AD_VP   Administration Vice President
    AD_ASST  Administration Assistant
    FI_MGR   Finance Manager
    FI_ACCOUNT Accountant
    AC_MGR   Accounting Manager
    AC_ACCOUNT Public Accountant
    SA_MAN   Sales Manager
    SA_REP   Sales Representative
    PU_MAN   Purchasing Manager
    PU_CLERK  Purchasing Clerk
    ST_MAN   Stock Manager
    ST_CLERK  Stock Clerk
    SH_CLERK  Shipping Clerk
    IT_PROG  Programmer
    MK_MAN   Marketing Manager
    MK_REP   Marketing Representative
    HR_REP   Human Resources Representative
    PR_REP   Public Relations Representative
    19 rows selected.

The DESCRIBE command allows you to see the table structure. This includes the column names, the data types, and whether the columns are allowed to be empty (null). This information can be very important when constructing various SQL statements. For example, if you were inserting a row, you would need to supply values for job_id and job_title because they are NOT NULL.

The SELECT statement is very simple. Notice that it was typed all on one line. SQL doesn’t really care how you break up statements line by line, as long as you don’t break words in half.

Break up SQL statements by clause. For longer, more complex statements, you may use many line breaks. These breaks can help make statements easier to read.

Here are two SELECT statements and their output:

SQL> select *
 2 from jobs
 3 where job_title = 'President';
JOB_ID   JOB_TITLE              MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES  President                20080   40000
SQL> select *
 2 from jobs
 3 where job_title like 'P%';
JOB_ID   JOB_TITLE              MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES  President                20080   40000
AC_ACCOUNT Public Accountant             4200    9000
PU_MAN   Purchasing Manager            8000   15000
PU_CLERK  Purchasing Clerk             2500    5500
IT_PROG  Programmer                4000   10000
PR_REP   Public Relations Representative      4500   10500
6 rows selected.

Note that instead of using a list of columns, an asterisk (*) was used. That tells the SELECT clause is to return all the columns, as opposed to what was shown earlier where just two columns were selected.

Notice the use of the WHERE clause. The WHERE clause restricts what data is returned. In this example, the WHERE clause is used in two ways:

  • As an equality (=): You search for exactly what you want to find.

  • As a fuzzy search (LIKE): You can use wild cards to complete search terms. Oracle uses the percent sign as a wild card symbol.

The use of the % symbol specifies that you want to select all rows that begin with capital P and then have anything after them. Often, on operating systems, you see an asterisk used as a wild card. That’s not the case inside an SQL statement, though; instead, use a percent sign (%).

Adding to our SELECT statement, you see

SQL> select lower(job_id), upper(job_title) title, max_salary
 2 from jobs
 3 where job_title like 'P%'
 4 and max_salary < 14000
 5 order by max_salary ASC;
LOWER(JOB_ TITLE                MAX_SALARY
---------- ----------------------------------- ----------
pu_clerk  PURCHASING CLERK             5500
ac_account PUBLIC ACCOUNTANT             9000
it_prog  PROGRAMMER                10000
pr_rep   PUBLIC RELATIONS REPRESENTATIVE     10500

Some functions were added to the columns in the SELECT clause. Functions take and input to produce an output: in this case, job_id and the job_title. The character functions UPPER and LOWER were used. Can you guess what they do? In this case, it’s pretty obvious. Oracle has dozens of functions for you to use to act on your data in all kinds of ways.

In this case, it is demonstrated how it is not necessarily important how your data is stored; you can display it however you want. Notice the names of the columns for job_id and job_title in the output. job_id seems to be a mix of our function and the column_name.

That’s because Oracle automatically uses whatever you type in the SELECT clause for your column heading. On the second column, job_title, use an “alias” to make the output is a little prettier.

An alias comes after the column construct but before the comma. In this example, title is the alias. The alias will always default to uppercase unless you put double quotes (“ ”) around it. You also need to use double quotes if your alias is more than one word. For example

SQL> select upper(job_title) "Job Title"
 2 from jobs
 3 where job_title like 'P%';
Job Title
-----------------------------------
PRESIDENT
PUBLIC ACCOUNTANT
PURCHASING MANAGER
PURCHASING CLERK
PROGRAMMER
PUBLIC RELATIONS REPRESENTATIVE

The use of the AND statement is a construct of the WHERE clause. The AND statement allows you to use multiple conditions to restrict our data.

Last, the ORDER BY clause sorts the output on the column specified, either numerically or alphabetically, depending on the data type. By default, it sorts in ascending order. The ASC (ascending) key word was added for clarification. You could have used DESC instead to order the results in descending numeric order of max_salary.