Oracle Tools and Bindings with languages

CERN IT Department CH-1211 Geneva 23 Switzerland

www.cern.ch/it

Mariusz Piorkowski, Dr. Andrea Valassi, Sebastien Ponce, Zbigniew Baranowski, Jose

Carlos Luna Duran, Rostislav Titov

Oracle Tools and Bindings

with languages

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

CERN IT Department CH-1211 Geneva 23 Switzerland

www.cern.ch/it

Oracle DB administrative tools

Agenda

•

How to install and configure it?

•

How they work?

•

How efficiently we can use them?

– Tips & hints,

•

Examples

CERN IT Department CH-1211 Geneva 23 Switzerland www.cern.ch/it

DB administrative tools

What tools:

•

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

don’t underestimate the possibilities of the SQL Command Window.

PL/SQL Developer - installation

DB administrative tools - 6

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-on-linux.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)

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,

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.

DB administrative tools - 8

tnsnames.ora file directory

OCI.dll library location

PL/SQL Developer – first start up

PL/SQL Developer - setup at CERN

We don’t 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

PL/SQL Developer – first start up

PL/SQL Developer

• SQL Window - Develop, run, test, tune etc…

PL/SQL Developer

• SQL Window – Single query window

PL/SQL Developer

• SQL Window – Run multiple SQL statements

PL/SQL Developer

• SQL Window – Create a graph based on the result

PL/SQL Developer

• SQL Window – Export query results

PL/SQL Developer

• SQL Window – Previous or Next SQL query

PL/SQL Developer

• Command Window

PL/SQL Developer

• Command Window – SQL *Plus like environment

PL/SQL Developer

• Command Window – Built in script editor

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…

PL/SQL Developer

• Program Window

PL/SQL Developer

• Test/Debug Window

PL/SQL Developer

•

Test Window

- Debug your program

- Show values of variables - Set breakpoints

- Unconditionally - Use Condition - Use Message - Don’t Break

- Use Pass Count

PL/SQL Developer

• Table editor – create table directly by predefine interface

PL/SQL Developer

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

PL/SQL Developer

• Diagram Window

- Create diagrams by dragging objects - Setting automatic foreign key relations - Show and hide specific items

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,

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

SQL Developer

DB administrative tools - 31

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

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 …

SQL Developer

New DB connection

SQL Developer

•

Main window

SQL Developer

•

Run a query

SQL Developer

• Generate explain plan (F10) – really handy to optimize SQL queries

SQL Developer

• SQL Tuning Advisor (Ctrl+F12) – written recommendations how

improve SQL statement

SQL Developer

•

Query Builder

SQL Developer

•

Edit objects

SQL Developer

• Export results – interesting option Publish to APEX

SQL Developer

•

Publish to APEX

SQL Developer

DB administrative tools - 42

SQL Developer

•

Extensions

– e.g. Insider (live monitoring)

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

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.

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

Benthic - Golden 6.x

• Main window – simple view but sometimes is exactly what

we need

Benthic - Golden 6.x

Benthic - Golden 6.x

• DBMS Output window (F10) - for PL/SQL scripts

Benthic - Golden 6.x

•

More info:

http://www.benthicsoftware.com/

Cherry on the cake

DB administrative tools - 52

•

rlwrap

is a 'readline

wrapper', a small utility

that uses the GNU

readline library to allow

the editing of keyboard

input for any command.

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