=========================
SQL*Plus and alternatives
=========================

:Author:   Catherine Devlin
:Location: Ohio Oracle User Group
:Date:     Apr. 3, 2008

slides at catherinedevlin.blogspot.com

.. class:: handout

  try --theme=medium-white
  
GUI Golden Age
--------------

* TOAD
* sqldeveloper
* PL/SQL Developer
* TORA
* squirrel

Options
-------

.. class:: small

========= ====== === ==== ======= ====== ======
Tool      Lang   Win Wow  Install Matur  Extend
========= ====== === ==== ======= ====== ======
SQL*Plus         Yes low  easy    high   no
--------- ------ --- ---- ------- ------ ------
gqlplus   C      No  med  easy    high   low
--------- ------ --- ---- ------- ------ ------
Senora    perl   Yes high hard    med-hi med
--------- ------ --- ---- ------- ------ ------
YASQL     perl   ?   high hard    med    low
--------- ------ --- ---- ------- ------ ------
sqlpython python Yes high med     low    high
========= ====== === ==== ======= ====== ======

.. class:: handout

  YASQL maturity problems - 
    
	\i flaw on numbers
	
	\I - dumps you from the environment
	
	hr@xe@xe
	
	show indexes not working
	
	desc not working - sometimes - 

SQL*Plus customizations
-----------------------

Make your script library handy

  Windows: "Start In"
  
  batchfile with cd
  
Set EDITOR environment variable
	
Oops
----

"I thought I was in development."
	
Worthwhile prompt
-----------------

set sqlprompt "_user'@'_connect_identifier> "

$ORACLE_HOME/sqlplus/admin/glogin.sql

.. class:: handout

  These are predefined substitution variables.

  With 10g, login scripts run at each connect.

HTML
----

set markup html on

Book
----

  .. image:: sqlplusbook.jpeg
     :height: 800

gqlplus
-------

Command history

Name Completion

.. class:: handout

  YASQL help alleges completion, too

gqlplus drawback
----------------

Useless prompt

gqlplus installation
--------------------

Linux: Binary executable

  (copy to where you want)

or ./configure, make
     
Common features
---------------

========= ========= ==== ==== ===========
Tool      history   tidy bind anon PL/SQL
========= ========= ==== ==== ===========
SQL*Plus  No        No   Yes  Yes
--------- --------- ---- ---- -----------
gqlplus   up-key    No   Yes  Yes
--------- --------- ---- ---- -----------
Senora    'hi'      Yes  Yes  Yes
--------- --------- ---- ---- -----------
YASQL     up-key    Yes  No   No      
--------- --------- ---- ---- -----------
sqlpython up / hi   Yes  Yes  No      
========= ========= ==== ==== ===========

.. class:: handout

  Show tidiness with select * from departments
  
Senora
------

Shell ENvironment
for ORAcle

Senora: Unixy goodness
----------------------

What if each table were a file?

* ls
* cat
* grep
* head

.. class:: handout

  -h for help

  ls table/*

  ls emp*

  ls -l emp*

  grep Geitz employees

  cat employees

  head -5 employees

Data dictionary exploration
---------------------------

* refs
* deps
* find
* pull

.. class:: handout

  go for employees

  deps -U

  pull add_job_history

Senora tuning
-------------

* xqueries
* space
* kept
* jobs
* locks

.. class:: handout

  register Tuning

Senora quirks
-------------

Must register plugins

error message: "what ?"

.. class:: handout

  "sp init" ? 
  
  are you on the right track, or not?

Extending Senora
----------------

Open DataDictionary.pm

copy and modify function "find"

Restart Senora

.. class:: handout

  "find" in DataDictionary.pm

  make sure to also get $COMMANDS->{find}
  
  select username, terminal, logon_date
  from v$session
  where username is not null;
  
Senora installation
-------------------

cpan> install DBI

cpan> install DBD::Oracle

Download & unzip tarball

.. class:: handout

  Precompiled binary is available for Windows
  
  There is probably a more sophisticated method
  
YASQL: output flexibility
-------------------------

* ;*n*
* \\g
* \\s
* \\i

YASQL
-----

* >
* >>
* \|
* <

.. class:: handout

  pipe to more

  < from groups.csv (truncate first)

YASQL info
----------

* show tables

* \\dt

* \\di

* \\dc

YASQL installation
------------------

cpan> install Term::ReadLine::Perl Term::ReadKey Text::CSV_XS

Download tarball

./configure, make, make install

.. class:: handout

  Installing under cygwin:
  
	yasql installation - cygwin
	must unzip within cygwin, not windows
	(install diffutils)
	install DBI
	set ORACLE_HOME
	force install DBD::Oracle
	make
	make install

sqlpython output
----------------

* \\h

* \\x

* \\t or tselect

.. class:: handouts

  dump to html
  
sqlpython
---------

* comments
* compare
* autobind
* longops

failover
--------

..class:: handout

  demonstrate with grep

  not on windows

extending sqlpython
-------------------

edit 
usr/lib/python2.5/site-packages/sqlpython/mysqlpy.py

add function "do_who"

installing sqlpython
--------------------

set ORACLE_HOME

for XE, google 'cx_Oracle XE'

install python-dev, python-setuptools

easy_install -UZ sqlpython

Review
------

========= ====== === ==== ======= ====== ======
Tool      Lang   Win Wow  Install Matur  Extend
========= ====== === ==== ======= ====== ======
SQL*Plus         Yes low  easy    high   no
--------- ------ --- ---- ------- ------ ------
gqlplus   C      No  med  easy    high   low
--------- ------ --- ---- ------- ------ ------
Senora    perl   Yes high hard    med-hi med
--------- ------ --- ---- ------- ------ ------
YASQL     perl   ?   high hard    med    low
--------- ------ --- ---- ------- ------ ------
sqlpython python Yes high med     low    high
========= ====== === ==== ======= ====== ======

Resources
---------

* http://gqlplus.sourceforge.net/
* http://senora.sourceforge.net/
* http://yasql.sourceforge.net/
* http://pypi.python.org/pypi/sqlpython
* http://catherinedevlin.blogspot.com

On this presentation
--------------------

This presentation is an `S5 <http://meyerweb.com/eric/tools/s5/>`_ document generated from a `ReStructured Text <http://docutils.sourceforge.net/rst.html>`_ file (`here <sqlplusalternatives.rst>`_) by `rst2s5 <http://docutils.sourceforge.net/docs/user/slide-shows.html>`_.