Oracle Tools and Bindings with languages Mariusz Piorkowski,

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

Agenda CERN IT Department CH-1211 Geneva 23 Switzerland Oracle DB administrative tools – Me Java – Rostislav OCI – Andrea OCCI – Sebastien Perl – Jose Carlos Luna Python – Zbigniew 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 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, 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 don’t 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-developeron-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) 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 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 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 - Don’t 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?