Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Oracle Basics: Querying an Oracle Database  : Page 2

Oracle's SQL*Plus is no SQL Server Query Analyzer, but with a little practice you'll get the hang of it.


advertisement
Your Third Hurdle: Typing, Running, and Editing a Query
Now try entering and executing a simple query. First enter the simple example below:

select * from scott.dept

As you enter each row, SQL*Plus assigns a line number. In order to run the query after you have finished typing it, you have two choices. Before hitting the Enter key on the last line, you can type a semicolon (or after hitting Enter, you can type the "/" key).



Below is the query and its result:

SQL> select 2 * 3 from scott.dept 4 / DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL>

Now suppose you wanted to modify the query to add a join to the "emp" table. The editing capabilities built into SQL*Plus are all based on keystroke commands. For example, "L" lists the code. However, SQL*Plus also supports the "edit" command. The edit command shells out to the editor of your choice. By default the editor is Microsoft Notepad.

Type the word edit followed by the SQL> prompt and a Notepad window with the previous SQL command you typed should appear. Use Cut and Paste and any other common commands to modify the code.

In my case, I've changed the above code to this:

select dname,ename from scott.dept d, scott.emp e where d.deptno = e.deptno

To get back to SQL*Plus, simply save and exit Notepad. The code will then appear in the SQL*Plus window. Hit "/" to execute it. You can choose which editor will be invoked by choosing Define Editor in the Edit menu.

SQL*Plus can also load and execute code from a file in one step. Simply type "@" followed by the name of the file—as in @c:\example.sql. Experienced Oracle developers or DBAs will likely have numerous scripts that they use this way.

Your Fourth Hurdle: Browsing Objects in the Schema
As I've pointed out, SQL*Plus doesn't come with an object browser. Here are some commands that will at least show you the basics about the tables in Oracle.

The command "DESCRIBE ['desc' for short] table name" returns a list of the columns with their data types for the given table:

SQL> desc scott.emp Name Null? Type -------------------- -------- ---------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL>

Oracle also contains a slew of views to access Oracle's metadata. There are three basic types of views:

1. USER_  Views prefaced with "user_" show only the information about objects in the schema of the user. So if I log in as "scott," then the following code supplies me with the list below:

SQL> select table_name 2 from user_tables; TABLE_NAME ------------------------------ BONUS DEPT EMP PLAN_TABLE SALGRADE SQL>

2. ALL_  Views prefaced with "all_" show information about all objects to which a user has access. The code below therefore returns more than just the four tables listed in the previous example:

SQL> select owner,table_name 2 from all_tables; OWNER TABLE_NAME ------------------ ------------------------------ SYS DUAL SYS SYSTEM_PRIVILEGE_MAP SYS TABLE_PRIVILEGE_MAP SYS STMT_AUDIT_OPTION_MAP SYS AUDIT_ACTIONS SYS PSTUBTBL SYSTEM DEF$_TEMP$LOB SYS AURORA$IIOP$SYSTEM$PROPERTIES MDSYS OGIS_SPATIAL_REFERENCE_SYSTEMS MDSYS MD$DICTVER MDSYS CS_SRS SYSTEM HELP SCOTT DEPT SCOTT EMP SCOTT BONUS SCOTT SALGRADE SCOTT PLAN_TABLE

3. DBA_  If you have the necessary permissions, you can access views prefaced by "dba_." These show information about all objects in the system. The code below lists every table in the Oracle database:

SQL> select owner, table_name 2 from dba_tables

As you've seen, accessing a database using SQL*Plus and Oracle is different and harder than with the familiar Query Analyzer in SQL Server. It does take time to become proficient in all the details and nuances of a new environment. I hope I've given you enough to get you started and make your first time that much easier.


Joseph Lax is the principal of DB Directions, a company specializing in all aspects of database architecture. He has spent the last 12 years providing expertise on performance optimization, test design, and backup and recovery strategies for banks, telecommunication firms, and manufacturing companies.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date