Oracle Tools and Bindings with languages
Mariusz Piorkowski, Dr. Andrea Valassi, Sebastien Ponce, Zbigniew Baranowski, Jose Carlos Luna Duran, Rostislav Titov
CERN IT Department CH-1211 Geneva 23 Switzerland
www.cern.ch/it
Agenda
Oracle DB administrative tools Me Java Rostislav OCI Andrea OCCI Sebastien Perl Jose Carlos Luna Python Zbigniew
www.cern.ch/it
CERN IT Department CH-1211 Geneva 23 Switzerland
Oracle Tools and Bindings with languages - 2
Oracle DB administrative tools Agenda How to install and configure it? How they work? How efficiently we can use them?
Tips & hints,
Examples
www.cern.ch/it
CERN IT Department CH-1211 Geneva 23 Switzerland
DB administrative tools - 3
DB administrative tools
What tools: PL/SQL Developer,
SQL Developer, Golden 6.0, SQLPLUS rlwrap,
www.cern.ch/it
CERN IT Department CH-1211 Geneva 23 Switzerland
DB administrative tools - 4
PL/SQL Developer
What is PL/SQL Developer?
PL/SQL Developer is an
Integrated Development Environment that is specifically targeted at the development.
PL/SQL Developer has its focus
on PL/SQL development. But dont underestimate the possibilities of the SQL Command Window.
DB administrative tools - 5
PL/SQL Developer - installation
System requirements
PL/SQL Developer will run on: Windows all release so far, Mac OS is not supported - but you can run on Virtual Machine like CrossOver more installation info: http://dgielis.blogspot.fr/2010/09/plsql-developer-on-osx.html Linux not support as well - but on a Linux box you can use Wine more info: http://johanlouwers.blogspot.fr/2008/09/plsql-developer-onlinux.html The supported Oracle Server versions are 7.x, 8.x, 8i, 9i, 10g and 11g on any platform. To connect to an Oracle database, PL/SQL Developer requires a 32-bit SQL*Net, Net 8, Net 9, Net 10 or Net 11 version, in other hand it require Oracle Instant Client (32-bit)
DB administrative tools - 6
PL/SQL Developer - installation
Easy installation? steps (for Windows)
1) Download PL/SQL Developer (9.0.6) from DFS: G:\Applications\AllroundAutomations 2) Run the setup, 3) Download Oracle Instant Client 32-bit version from Oracle site,
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
4) Unpack it under C:\ProgramFiles\InstantClient, 5) Create directories C:\ProgramFiles\InstantClient\network\admin, 6) Create or past inside above directory tnsnames.ora file, 7) Launch PL/SQL Developer,
DB administrative tools - 7
PL/SQL Developer first start up
If you started PL/SQL Developer without connecting to DFS, go to: Tools > Preferences > Oracle / Connection
you can select the Oracle Home with the correct TNSNAMES.ORA file.
tnsnames.ora file directory OCI.dll library location
DB administrative tools - 8
PL/SQL Developer first start up PL/SQL Developer - setup at CERN
We dont need to create additional network/admin directories inside InstantClient directory. Instead of that we can specify location of tnsnames.ora file on DFS: G:\Applications\Oracle\ADMIN\tnsnames.ora
tnsnames.ora file will be always up to date
DB administrative tools - 9
PL/SQL Developer first start up
Main window
New Window
New Connection
Execute (F8)
DB administrative tools - 10
PL/SQL Developer
SQL Window - Develop, run, test, tune etc
DB administrative tools - 11
PL/SQL Developer
SQL Window Single query window
DB administrative tools - 12
PL/SQL Developer
SQL Window Run multiple SQL statements
DB administrative tools - 13
PL/SQL Developer
SQL Window Create a graph based on the result
DB administrative tools - 14
PL/SQL Developer
SQL Window Export query results
DB administrative tools - 15
PL/SQL Developer
SQL Window Previous or Next SQL query
DB administrative tools - 16
PL/SQL Developer
Command Window
DB administrative tools - 17
PL/SQL Developer
Command Window SQL *Plus like environment
DB administrative tools - 18
PL/SQL Developer
Command Window Built in script editor
DB administrative tools - 19
PL/SQL Developer
Program Window
- Multi-level undo & redo,
- Bookmarks
- Block indent & unindent - Powerful find & replace with
reqular expressions
- A macro recorder and library - Column editing
- Split editing
- And more
DB administrative tools - 20
PL/SQL Developer
Program Window
DB administrative tools - 21
PL/SQL Developer
Test/Debug Window
DB administrative tools - 22
PL/SQL Developer Test Window
- Debug your program
- Show values of variables
- Set breakpoints - Unconditionally - Use Condition - Use Message
- Dont Break
- Use Pass Count
DB administrative tools - 23
PL/SQL Developer
Table editor create table directly by predefine interface
DB administrative tools - 24
PL/SQL Developer
Table editor Define table Define columns Define constraints Define indexes Etc
DB administrative tools - 25
PL/SQL Developer
Table editor get the SQL script
Save SQL to file
Copy SQL to clipboard
Open in command window
DB administrative tools - 26
PL/SQL Developer
Diagram Window
DB administrative tools - 27
PL/SQL Developer
Diagram Window Create diagrams by dragging objects Setting automatic foreign key relations Show and hide specific items
DB administrative tools - 28
PL/SQL Developer Tips & Tricks
Tips & Tricks - Drag a table, view or procedure to a SQL or Program Window, - Right click on any database-object anywhere, - Change the hotkeys to fit your needs, - Place frequently used connect strings in the log-in pop-list,
DB administrative tools - 29
PL/SQL Developer Other features
Export User Objects Compare User Objects in two schemas Event Monitor (monitor messages sent to pipes) Sessions overview Export/Import tables
SQL Inserts Oracle export format PL/SQL Developer dump format
More info:
http://www.allroundautomations.com/plsqldev.html
DB administrative tools - 30
SQL Developer
What is Oracle SQL Developer? - Oracle SQL Developer is a free graphical tool for database
development. Using SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. Free download from Oracle website:
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
System requirements
- SQL Developer can connect to any Oracle Database version 9.2.0.1 and later and runs on Windows, Linux, and Mac OSX.
DB administrative tools - 31
SQL Developer
Advantages
No need to install anything, simple unzip downloaded
package and software is ready to use,
Portable copy unzipped files on flash drive and you can use it on any PC, You can connect in parallel to several different databases,
NOT NECESSERY, SPECIALLY WHEN YOU MISSED TEST DATABASE WITH PRODUCTION
DB administrative tools - 32
SQL Developer
First connection
New DB connection
DB administrative tools - 33
SQL Developer
Main window
DB administrative tools - 34
SQL Developer
Run a query
DB administrative tools - 35
SQL Developer
Generate explain plan (F10) really handy to optimize SQL queries
DB administrative tools - 36
SQL Developer
SQL Tuning Advisor (Ctrl+F12) written recommendations how improve SQL statement
DB administrative tools - 37
SQL Developer
Query Builder
DB administrative tools - 38
SQL Developer
Edit objects
DB administrative tools - 39
SQL Developer
Export results interesting option Publish to APEX
DB administrative tools - 40
SQL Developer
Publish to APEX
DB administrative tools - 41
SQL Developer
Extensions
DB administrative tools - 42
SQL Developer
Extensions e.g. Insider (live monitoring)
DB administrative tools - 43
SQL Developer
Much more features like:
- Reports, - DBA tools, - you can create your own database, - Data modeler, - Browser, - Migration wizard - Enables the migration of third party database on to Oracle - etc
More info:
http://docs.oracle.com/cd/E12151_01/index.htm
DB administrative tools - 44
Benthic - Golden 6.x
Golden 6.x
- Is much more simpler tools that two previous one but still quite handy for SQL statements.
System requirements
- Golden is available only for Windows, however the same like for PL/SQL Developer we can use workaround to install it on MacOS and Linux, - Oracle Instant Client 32-bit is necessary.
DB administrative tools - 45
Benthic - Golden 6.x
Installation steps
- Copy the Instant Client dll's to C:\Oracle - Put C:\Oracle at the beginning of PATH system variable, - Copy tnsnames.ora to C:\Oracle - Add the system variable TNS_ADMIN with a value of C:\Oracle - Set the OCI DLL field of Golden's Login Options window to "C:\Oracle\oci.dll" (you will have to restart Golden after setting this value).
Benthic - Golden 6.x
Login
DB administrative tools - 47
Benthic - Golden 6.x
Main window simple view but sometimes is exactly what we need
DB administrative tools - 48
Benthic - Golden 6.x
Simple query building
DB administrative tools - 49
Benthic - Golden 6.x
DBMS Output window (F10) - for PL/SQL scripts
DB administrative tools - 50
Benthic - Golden 6.x
More info:
http://www.benthicsoftware.com/
DB administrative tools - 51
Cherry on the cake
rlwrap - is a 'readline wrapper', a small utility that uses the GNU readline library to allow the editing of keyboard input for any command.
DB administrative tools - 52
rlwrap
Finally something only on Linux - rlwrap compiles and runs on most Unix(-like) systems, including cygwin Installation
gunzip rlwrap*.gz tar -xvf rlwrap*.tar cd rlwrap* ./configure make make check make install
DB administrative tools - 53
rlwrap
Usage together with SQL*Plus or RMAN
Questions?
