1 / 55

Oracle Tools and Bindings with languages

Oracle Tools and Bindings with languages. Mariusz Piorkowski, Dr. Andrea Valassi, Sebastien Ponce, Zbigniew Baranowski, Jose Carlos Luna Duran, Rostislav Titov. Agenda. Oracle DB administrative tools – Me Java – Rostislav OCI – Andrea OCCI – Sebastien Perl – Jose Carlos Luna

brooksjason
Download Presentation

Oracle Tools and Bindings with languages

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Tools and Bindings with languages Mariusz Piorkowski, Dr. Andrea Valassi, Sebastien Ponce, Zbigniew Baranowski, Jose Carlos Luna Duran, Rostislav Titov

  2. Agenda • Oracle DB administrative tools – Me • Java – Rostislav • OCI – Andrea • OCCI – Sebastien • Perl – Jose Carlos Luna • Python – Zbigniew Oracle Tools and Bindings with languages - 2

  3. Oracle DB administrative tools Agenda • How to install and configure it? • How they work? • How efficiently we can use them? • Tips & hints, • Examples DB administrative tools - 3

  4. DB administrative tools What tools: • PL/SQL Developer, • SQL Developer, • Golden 6.0, • SQLPLUS – rlwrap, • … • … • … • … DB administrative tools - 4

  5. 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

  6. 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-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) DB administrative tools - 6

  7. 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

  8. 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

  9. 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

  10. PL/SQL Developer – first start up • Main window New Window New Connection Execute (F8) DB administrative tools - 10

  11. PL/SQL Developer • SQL Window- Develop, run, test, tune etc… DB administrative tools - 11

  12. PL/SQL Developer • SQL Window – Single query window DB administrative tools - 12

  13. PL/SQL Developer • SQL Window – Run multiple SQL statements DB administrative tools - 13

  14. PL/SQL Developer • SQL Window – Create a graph based on the result DB administrative tools - 14

  15. PL/SQL Developer • SQL Window – Export query results DB administrative tools - 15

  16. PL/SQL Developer • SQL Window – Previous or Next SQL query DB administrative tools - 16

  17. PL/SQL Developer • Command Window DB administrative tools - 17

  18. PL/SQL Developer • Command Window – SQL *Plus like environment DB administrative tools - 18

  19. PL/SQL Developer • Command Window – Built in script editor DB administrative tools - 19

  20. 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

  21. PL/SQL Developer • Program Window DB administrative tools - 21

  22. PL/SQL Developer • Test/Debug Window DB administrative tools - 22

  23. 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

  24. PL/SQL Developer • Table editor – create table directly by predefine interface DB administrative tools - 24

  25. PL/SQL Developer • Table editor • Define table • Define columns • Define constraints • Define indexes • Etc… DB administrative tools - 25

  26. 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

  27. PL/SQL Developer • Diagram Window DB administrative tools - 27

  28. PL/SQL Developer • Diagram Window • Create diagrams by dragging objects • Setting automatic foreign key relations • Show and hide specific items DB administrative tools - 28

  29. 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

  30. 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

  31. 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

  32. 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

  33. SQL Developer • First connection New DB connection DB administrative tools - 33

  34. SQL Developer • Main window DB administrative tools - 34

  35. SQL Developer • Run a query DB administrative tools - 35

  36. SQL Developer • Generate explain plan (F10) – really handy to optimize SQL queries DB administrative tools - 36

  37. SQL Developer • SQL Tuning Advisor (Ctrl+F12) – written recommendations how improve SQL statement DB administrative tools - 37

  38. SQL Developer • Query Builder DB administrative tools - 38

  39. SQL Developer • Edit objects DB administrative tools - 39

  40. SQL Developer • Export results – interesting option Publish to APEX DB administrative tools - 40

  41. SQL Developer • Publish to APEX DB administrative tools - 41

  42. SQL Developer • Extensions DB administrative tools - 42

  43. SQL Developer • Extensions – e.g. Insider (live monitoring) DB administrative tools - 43

  44. 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

  45. 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

  46. 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).

  47. Benthic - Golden 6.x • Login DB administrative tools - 47

  48. Benthic - Golden 6.x • Main window – simple view but sometimes is exactly what we need DB administrative tools - 48

  49. Benthic - Golden 6.x • Simple query building DB administrative tools - 49

  50. Benthic - Golden 6.x • DBMS Output window (F10) - for PL/SQL scripts DB administrative tools - 50

More Related