\documentclass[10pt,a4paper]{article}

% Packages
\usepackage{fancyhdr}           % For header and footer
\usepackage{multicol}           % Allows multicols in tables
\usepackage{tabularx}           % Intelligent column widths
\usepackage{tabulary}           % Used in header and footer
\usepackage{hhline}             % Border under tables
\usepackage{graphicx}           % For images
\usepackage{xcolor}             % For hex colours
%\usepackage[utf8x]{inputenc}    % For unicode character support
\usepackage[T1]{fontenc}        % Without this we get weird character replacements
\usepackage{colortbl}           % For coloured tables
\usepackage{setspace}           % For line height
\usepackage{lastpage}           % Needed for total page number
\usepackage{seqsplit}           % Splits long words.
%\usepackage{opensans}          % Can't make this work so far. Shame. Would be lovely.
\usepackage[normalem]{ulem}     % For underlining links
% Most of the following are not required for the majority
% of cheat sheets but are needed for some symbol support.
\usepackage{amsmath}            % Symbols
\usepackage{MnSymbol}           % Symbols
\usepackage{wasysym}            % Symbols
%\usepackage[english,german,french,spanish,italian]{babel}              % Languages

% Document Info
\author{Jacek Gebal (jgebal)}
\pdfinfo{
  /Title (ruby-plsql-cheat-sheet.pdf)
  /Creator (Cheatography)
  /Author (Jacek Gebal (jgebal))
  /Subject (ruby-plsql Cheat Sheet)
}

% Lengths and widths
\addtolength{\textwidth}{6cm}
\addtolength{\textheight}{-1cm}
\addtolength{\hoffset}{-3cm}
\addtolength{\voffset}{-2cm}
\setlength{\tabcolsep}{0.2cm} % Space between columns
\setlength{\headsep}{-12pt} % Reduce space between header and content
\setlength{\headheight}{85pt} % If less, LaTeX automatically increases it
\renewcommand{\footrulewidth}{0pt} % Remove footer line
\renewcommand{\headrulewidth}{0pt} % Remove header line
\renewcommand{\seqinsert}{\ifmmode\allowbreak\else\-\fi} % Hyphens in seqsplit
% This two commands together give roughly
% the right line height in the tables
\renewcommand{\arraystretch}{1.3}
\onehalfspacing

% Commands
\newcommand{\SetRowColor}[1]{\noalign{\gdef\RowColorName{#1}}\rowcolor{\RowColorName}} % Shortcut for row colour
\newcommand{\mymulticolumn}[3]{\multicolumn{#1}{>{\columncolor{\RowColorName}}#2}{#3}} % For coloured multi-cols
\newcolumntype{x}[1]{>{\raggedright}p{#1}} % New column types for ragged-right paragraph columns
\newcommand{\tn}{\tabularnewline} % Required as custom column type in use

% Font and Colours
\definecolor{HeadBackground}{HTML}{333333}
\definecolor{FootBackground}{HTML}{666666}
\definecolor{TextColor}{HTML}{333333}
\definecolor{DarkBackground}{HTML}{4A051F}
\definecolor{LightBackground}{HTML}{F9F7F8}
\renewcommand{\familydefault}{\sfdefault}
\color{TextColor}

% Header and Footer
\pagestyle{fancy}
\fancyhead{} % Set header to blank
\fancyfoot{} % Set footer to blank
\fancyhead[L]{
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{C}
    \SetRowColor{DarkBackground}
    \vspace{-7pt}
    {\parbox{\dimexpr\textwidth-2\fboxsep\relax}{\noindent
        \hspace*{-6pt}\includegraphics[width=5.8cm]{/web/www.cheatography.com/public/images/cheatography_logo.pdf}}
    }
\end{tabulary}
\columnbreak
\begin{tabulary}{11cm}{L}
    \vspace{-2pt}\large{\bf{\textcolor{DarkBackground}{\textrm{ruby-plsql Cheat Sheet}}}} \\
    \normalsize{by \textcolor{DarkBackground}{Jacek Gebal (jgebal)} via \textcolor{DarkBackground}{\uline{cheatography.com/22528/cs/4621/}}}
\end{tabulary}
\end{multicols}}

\fancyfoot[L]{ \footnotesize
\noindent
\begin{multicols}{3}
\begin{tabulary}{5.8cm}{LL}
  \SetRowColor{FootBackground}
  \mymulticolumn{2}{p{5.377cm}}{\bf\textcolor{white}{Cheatographer}}  \\
  \vspace{-2pt}Jacek Gebal (jgebal) \\
  \uline{cheatography.com/jgebal} \\
        \uline{\seqsplit{www}.oraclethoughts.com}
  \end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Cheat Sheet}}  \\
   \vspace{-2pt}Published 19th July, 2015.\\
   Updated 10th May, 2016.\\
   Page {\thepage} of \pageref{LastPage}.
\end{tabulary}
\vfill
\columnbreak
\begin{tabulary}{5.8cm}{L}
  \SetRowColor{FootBackground}
  \mymulticolumn{1}{p{5.377cm}}{\bf\textcolor{white}{Sponsor}}  \\
  \SetRowColor{white}
  \vspace{-5pt}
  %\includegraphics[width=48px,height=48px]{dave.jpeg}
  Measure your website readability!\\
  www.readability-score.com
\end{tabulary}
\end{multicols}}




\begin{document}
\raggedright
\raggedcolumns

% Set font size to small. Switch to any value
% from this page to resize cheat sheet text:
% www.emerson.emory.edu/services/latex/latex_169.html
\footnotesize % Small font.

\begin{multicols*}{2}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Session connect}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql(:default).connect! \{:username =\textgreater{} 'hr', :password =\textgreater{} 'hr', :database =\textgreater{} 'xe'\}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# opens a default connection to database} \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql.connect! \{:username =\textgreater{} 'hr', :password =\textgreater{} 'hr', :database =\textgreater{} 'xe'\}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# opens a default connection to database} \tn 
% Row Count 6 (+ 3)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql(:another).connect! \{:username =\textgreater{} 'hr', :password =\textgreater{} 'hr', :database =\textgreater{} 'xe'\}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# opens a second connection (referenced by Symbol :another)} \tn 
% Row Count 10 (+ 4)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql(:another).logoff} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# disconnects connection (referenced by symbol :another)} \tn 
% Row Count 13 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Transaction}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql(:another).connection.autocommit} = false} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\#disables auto commit in :another connection} \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql.autocommit?} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# returns the current status of autocommit} \tn 
% Row Count 4 (+ 2)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.commit} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\#commits a transaction in :default connection} \tn 
% Row Count 6 (+ 2)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql(:another).rollback} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\#rollbacks changes for :another connection} \tn 
% Row Count 8 (+ 2)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.savepoint "save\_this\_point"} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\#sets a transaction savepoint in :default connection} \tn 
% Row Count 11 (+ 3)
% Row 5
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql.rollback\_to "save\_this\_point"} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\#rollbacks to specified savepoint in :default connection} \tn 
% Row Count 14 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Insert into table}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.employees.insert \{:employee\_id =\textgreater{} 1, :name =\textgreater{} 'James bond', :hire\_date =\textgreater{} Time.local(0007,07,07)\}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# inserts one row into the employees table using key-value pairs (Ruby Hash object)} \tn 
% Row Count 5 (+ 5)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql.employees.insert \{:employee\_id =\textgreater{} 2, :name =\textgreater{} 'Tony Stark'\}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# inserts one row into the employees table, with partial column list} \tn 
% Row Count 9 (+ 4)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.employees.insert {[} \{:employee\_id =\textgreater{} 3, :name =\textgreater{} 'Darth Vader'\}, \{:employee\_id =\textgreater{} 4, :name =\textgreater{} 'Luke Skywalker'\}{]}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# inserts multiple rows into the employees table using Array of Hashes} \tn 
% Row Count 14 (+ 5)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees.insert\_values} *{[} {[} 5, 'Batman', Time.local(1990,01,01){]}, {[}6,'Spiderman',Time.local(1999,02,02){]} {]}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# inserts multiple rows, specifying Array of Array of values} \tn 
% Row Count 19 (+ 5)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees.insert\_values} {[}:employee\_id, :name{]}, *{[} {[} 7, 'Superman'{]}, {[}8, 'Hulk'{]} {]}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# inserts multiple rows, specifying columns first and subset of values} \tn 
% Row Count 23 (+ 4)
% Row 5
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees.insert\_values} {[} 9, 'Thor', Time.local(1990,09,09){]}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# inserts one row, specifying only Array of values} \tn 
% Row Count 27 (+ 4)
% Row 6
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees.insert\_values} {[}:employee\_id, :name{]}, {[} 10, 'Sandman' {]}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# inserts one row, specifying subset of columns (Array) and Array of values} \tn 
% Row Count 31 (+ 4)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Select statements}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.select(:first, "SELECT * FROM employees")} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\{:employee\_id =\textgreater{} 1, :name =\textgreater{} 'James bond', :hire\_date =\textgreater{} '0007-07-07'\} \{\{nl\}\}\# returns first row of a query as a Ruby Hash} \tn 
% Row Count 4 (+ 4)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.select\_one("SELECT} count(*) FROM employees")} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}10 \{\{nl\}\}\# returns a scalar value from a first row from single column query} \tn 
% Row Count 7 (+ 3)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.select\_one("SELECT} employee\_id FROM employees WHERE 1=2")} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}nil \{\{nl\}\}\# returns nil Object (NULL) when no data found} \tn 
% Row Count 11 (+ 4)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql.select(:all, "SELECT * FROM employees ORDER BY employee\_id")} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}{[} \{:employee\_id =\textgreater{} 1, :name =\textgreater{} 'James bond', :hire\_date =\textgreater{} '0007-07-07'\}, \{...\}, ... {]} \{\{nl\}\}\# returns all rows from a query as an Array of Hashes} \tn 
% Row Count 17 (+ 6)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Delete from table/view}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.employees.delete :employee\_id =\textgreater{} 10 \{\{nl\}\}plsql.employees.delete "employee\_id = 10"} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\#delete record in table with WHERE condition} \tn 
% Row Count 3 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Table/View meta-data}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.execute "CREATE OR REPLACE VIEW employees\_v AS SELECT * FROM employees"} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\#creates a VIEW} \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql.employees\_v.class} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}PLSQL::View \{\{nl\}\}\# The employees\_v Object is of PLSQL::View class} \tn 
% Row Count 6 (+ 3)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.employees.class} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}PLSQL::Table \{\{nl\}\}\# The employees Object is of PLSQL::Table class} \tn 
% Row Count 9 (+ 3)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees\_synonym.class}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}PLSQL::Table \{\{nl\}\}\# The emplyees\_synonym Object is also of PLSQL::Table class} \tn 
% Row Count 12 (+ 3)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees.column\_names} \{\{nl\}\}plsql.employees\_v.column\_names} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}{[} employee\_id, name, hire\_date {]} \{\{nl\}\}\# returns all column names in table} \tn 
% Row Count 16 (+ 4)
% Row 5
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql.employees.columns \{\{nl\}\}plsql.employees\_v.columns} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\{ :employee\_id =\textgreater{} \{\{\{nl\}\} ~~~~:position=\textgreater{}1, :data\_type=\textgreater{}"NUMBER", :data\_length=\textgreater{}22, :data\_precision=\textgreater{}15, :data\_scale=\textgreater{}0, :char\_used=\textgreater{}nil,\{\{nl\}\} ~~~~:type\_owner=\textgreater{}nil, :type\_name=\textgreater{}nil, :sql\_type\_name=\textgreater{}nil, :nullable =\textgreater{} false, :data\_default =\textgreater{} nil\}\{\{nl\}\} ~~, ...\}\{\{nl\}\} \# returns column meta-data} \tn 
% Row Count 26 (+ 10)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Record and Object Types}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\#Given a FUNCTION get\_full\_name( p\_employee employees\%ROWTYPE ) RETURN VARCHAR2\{\{nl\}\}\{\{nl\}\}  plsql.get\_full\_name( \{:p\_employee =\textgreater{} \{:employee\_id =\textgreater{} 2, :first\_name =\textgreater{} 'Tony', :last\_name =\textgreater{} 'Stark', :hire\_date =\textgreater{} nil\} \} )\{\{nl\}\} plsql.get\_full\_name( \{:employee\_id =\textgreater{} 2, :first\_name =\textgreater{} 'Tony', :last\_name =\textgreater{} 'Stark', :hire\_date =\textgreater{} nil\} )\{\{nl\}\} plsql.get\_full\_name( \{'EMPLOYEE\_ID' =\textgreater{} 2, 'first\_name' =\textgreater{} 'Tony', 'last\_NaMe' =\textgreater{} 'Stark', 'hire\_date' =\textgreater{} nil\} )} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}'Tony Stark'} \tn 
% Row Count 10 (+ 10)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\# Accepts a record as a parameter (by name or by position) and executes the function returning String (VARCHAR2) \newline \# Record fields can be defined as a Symbol (:employee\_id) or as a String ('employee\_id') \newline \# Works the same way with package level record types and Oracle object types}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Varrays and Nested Tables}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\#Given a TYPE table\_of\_int IS TABLE OF INTEGER;\{\{nl\}\} \#Given FUNCTION sum\_items(p\_items TABLE\_OF\_INT) RETURN INTEGER\{\{nl\}\}\{\{nl\}\}  plsql.sum\_items( {[}1,2,3,4,5{]} )\{\{nl\}\} plsql.sum\_items( :p\_items =\textgreater{} {[}1,2,3,4,5{]} )} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}15} \tn 
% Row Count 6 (+ 6)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\# Nested tables are passed in and returned as Ruby Array Object type \newline \# Works the same way for VARRAYS}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Associative arrays (plsql tables, index-by tables)}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\#Given a package MY\_PACKAGE\{\{nl\}\} \# contains TYPE index\_table\_of\_int IS TABLE OF INTEGER INDEX BY BINARY\_INTEGER;\{\{nl\}\} \# contains FUNCTION sum\_items(p\_items INDEX\_TABLE\_OF\_INT) RETURN INTEGER;\{\{nl\}\}\{\{nl\}\}  \seqsplit{plsql.my\_package.sum\_items(} \{ -1 =\textgreater{} 1, 5 =\textgreater{} 2, 3 =\textgreater{} 3, 4 =\textgreater{} 4\} )} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}10} \tn 
% Row Count 7 (+ 7)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\# Associative arrays are passed in and returned as a Ruby Hash containing list of key value pairs \newline \# Where key is the element position in Array and value is the value at the position}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Resources}}  \tn
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\{\{popup="https://github.com/rsim/ruby-plsql"\}\}https://github.com/rsim/ruby-plsql\{\{/popup\}\} ruby-plsql project page \newline % Row Count 3 (+ 3)
\{\{popup="https://github.com/rsim/ruby-plsql-spec"\}\}https://github.com/rsim/ruby-plsql-spec\{\{/popup\}\} ruby-plsql-spec project page \newline % Row Count 6 (+ 3)
\{\{popup="http://blog.rayapps.com/tags/ruby-plsql/"\}\}http://blog.rayapps.com/tags/ruby-plsql/\{\{/popup\}\} Raimonds Simanovskis blog \newline % Row Count 9 (+ 3)
\{\{popup="http://www.oraclethoughts.com/tag/ruby-plsql/"\}\}http://www.oraclethoughts.com/tag/ruby-plsql/\{\{/popup\}\}My blog% Row Count 12 (+ 3)
} \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Connection parameters}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.connection.prefetch\_rows} = 100} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# sets number of rows to be fetched at once} \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.connection.database\_version}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# returns version of database as an Array =\textgreater{} {[}11, 2, 0, 2{]}} \tn 
% Row Count 5 (+ 3)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.dbms\_output\_stream} = STDOUT} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# redirects dbms\_output to standard output (console)} \tn 
% Row Count 8 (+ 3)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.dbms\_output\_buffer\_size} = 100\_000} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# sets dbms\_output buffer size to 100,000} \tn 
% Row Count 10 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Execute SQL statement or PLSQL block}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.execute "CREATE SYNONYM employees\_synonym FOR employees"} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# executes any given string as a SQL or PLSQL statement} \tn 
% Row Count 4 (+ 4)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql.execute \textless{}\textless{}-SQL\{\{nl\}\} ~~CREATE TABLE test\_employees (\{\{nl\}\} ~~~~employee\_id   NUMBER(15),\{\{nl\}\} ~~~~name          VARCHAR2(50),\{\{nl\}\} ~~~~hire\_date     DATE\{\{nl\}\} 	 ~~)\{\{nl\}\} SQL} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\#executes multi-line string statements too} \tn 
% Row Count 11 (+ 7)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Select from a table/view}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees.select(:first}, "ORDER BY employee\_id") \{\{nl\}\}plsql.employees.first("ORDER BY employee\_id")} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\{:employee\_id =\textgreater{} 1, :name =\textgreater{} 'James bond', :hire\_date =\textgreater{} '0007-07-07'\} \{\{nl\}\}\# returns first row from a table} \tn 
% Row Count 6 (+ 6)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees.select(:first}, "WHERE employee\_id = :a", 2) \{\{nl\}\}plsql.employees.first("WHERE employee\_id = :a", 2) \{\{nl\}\}plsql.employees.first(:employee\_id =\textgreater{} 2)} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\{:employee\_id =\textgreater{} 2, :name =\textgreater{} 'Tony Stark', :hire\_date =\textgreater{} nil\} \{\{nl\}\}\# returns first row from a table with WHERE condition} \tn 
% Row Count 13 (+ 7)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees.select(:all}, "ORDER BY employee\_id") \{\{nl\}\}plsql.employees.all("ORDER BY employee\_id") \{\{nl\}\}plsql.employees.all(:order\_by =\textgreater{} :employee\_id)} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}{[} \{:employee\_id =\textgreater{} 1, :name =\textgreater{} 'James bond', :hire\_date =\textgreater{} '0007-07-07'\}, \{...\}, ... {]} \{\{nl\}\}\# returns all rows from a table sorted using ORDER BY} \tn 
% Row Count 21 (+ 8)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees.all(:employee\_id} =\textgreater{} 2, :order\_by =\textgreater{} :employee\_id)} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}{[} \{:employee\_id =\textgreater{} 2, :name =\textgreater{} 'Tony Stark', :hire\_date =\textgreater{} nil\} {]} \{\{nl\}\}\# returns all rows from a table with WHERE condition} \tn 
% Row Count 26 (+ 5)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.employees.all "WHERE employee\_id = 2 AND hire\_date IS NULL"  \{\{nl\}\}plsql.employees.all( \{:employee\_id =\textgreater{} 2, :hire\_date =\textgreater{} nil\} )} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}{[} \{:employee\_id =\textgreater{} 2, :name =\textgreater{} 'Tony Stark', :hire\_date =\textgreater{} nil\} {]} \{\{nl\}\}\# returns all rows from a table with WHERE condition on NULL value} \tn 
% Row Count 32 (+ 6)
\end{tabularx}
\par\addvspace{1.3em}

\vfill
\columnbreak
\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Select from a table/view (cont)}}  \tn
% Row 5
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees.all(:hire\_date} =\textgreater{} :is\_not\_null)} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}{[} \{:employee\_id =\textgreater{} 1, :name =\textgreater{} 'James bond', :hire\_date =\textgreater{} '0007-07-07'\}, \{...\}, ... {]} \{\{nl\}\}\# returns all rows from a table with WHERE condition on NOT NULL value} \tn 
% Row Count 5 (+ 5)
% Row 6
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees.select(:count)} \{\{nl\}\}plsql.employees.count} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}10 \{\{nl\}\}\# returns count of rows in the table} \tn 
% Row Count 8 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Update table/view}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.employees.update :name =\textgreater{} 'Test'} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# updates field name in all records} \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql.employees.update :name =\textgreater{} 'Superman II', :where =\textgreater{} \{:employee\_id =\textgreater{} 7\} \{\{nl\}\}plsql.employees.update :name =\textgreater{} 'Superman II', :where =\textgreater{} "employee\_id = 7"} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# updates field in table with a where condition} \tn 
% Row Count 7 (+ 5)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.employees.update :name =\textgreater{} 'Superman II', :hire\_date =\textgreater{} Time.local(2000,01,01), :where =\textgreater{} "employee\_id = 7"} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# updates two fields in table with a where condition} \tn 
% Row Count 12 (+ 5)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Sequence}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.execute "CREATE SEQUENCE employees\_seq"} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\#executes a statement to create a sequence} \tn 
% Row Count 2 (+ 2)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees\_seq.nextval}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}1 \{\{nl\}\}\# returns NEXTVAL for sequence} \tn 
% Row Count 4 (+ 2)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.employees\_seq.currval}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}1 \{\{nl\}\}\# returns CURRVAL for sequence} \tn 
% Row Count 6 (+ 2)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Package}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.test\_package.class} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}PLSQL::Package\{\{nl\}\} \# A plsql package is Object of PLSQL::Package class} \tn 
% Row Count 3 (+ 3)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.test\_package.test\_variable} = 1} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\# Assigns a value to package public variable} \tn 
% Row Count 5 (+ 2)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\seqsplit{plsql.test\_package.test\_variable}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}1\{\{nl\}\} \# Reads a value to package public variable} \tn 
% Row Count 8 (+ 3)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Procedure / Function}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\# given a FUNCTION uppercase( p\_string VARCHAR2 ) RETURN VARCHAR2\{\{nl\}\}\{\{nl\}\}  plsql.uppercase( 'xxx' )\{\{nl\}\} plsql.uppercase( :p\_string =\textgreater{} 'xxx' )} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}'XXX'\{\{nl\}\} \# executes the function binding parameters by position or name and returns scalar Object as a value} \tn 
% Row Count 6 (+ 6)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{\# given a FUNCTION copy\_function( p\_from VARCHAR2, p\_to OUT VARCHAR2, p\_to\_double OUT VARCHAR2 ) RETURN NUMBER\{\{nl\}\}\{\{nl\}\}  plsql.copy\_function( 'abc', nil, nil)\{\{nl\}\} plsql.copy\_function( :p\_from =\textgreater{} 'abc', :p\_to =\textgreater{} nil, :p\_to\_double =\textgreater{} nil)\{\{nl\}\} plsql.copy\_function( 'abc' )} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}{[} 3, \{ :p\_to =\textgreater{} "abc", :p\_to\_double =\textgreater{} "abcabc" \} {]}\{\{nl\}\} \# executes the function and returns 2 element Array\{\{nl\}\} \# with first element being function result and second element being a Hash of OUT parameters} \tn 
% Row Count 17 (+ 11)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\#Given a PROCEDURE copy\_proc( p\_from VARCHAR2, p\_to OUT VARCHAR2, p\_to\_double OUT VARCHAR2 )\{\{nl\}\}\{\{nl\}\}  plsql.copy\_proc( 'abc', nil, nil)\{\{nl\}\} plsql.copy\_proc( :p\_from =\textgreater{} 'abc', :p\_to =\textgreater{} nil, :p\_to\_double =\textgreater{} nil)\{\{nl\}\} plsql.copy\_proc( 'abc' )} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\{ :p\_to =\textgreater{} 'abc', :p\_to\_double =\textgreater{} 'abcabc' \}\{\{nl\}\} \# executes the procedure and returns a Hash of OUT parameters as a :name =\textgreater{} 'value' pairs} \tn 
% Row Count 25 (+ 8)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}

\begin{tabularx}{8.4cm}{X}
\SetRowColor{DarkBackground}
\mymulticolumn{1}{x{8.4cm}}{\bf\textcolor{white}{Cursors}}  \tn
% Row 0
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\#Given a FUNCTION get\_empolyees RETURN SYS\_REFCURSOR\{\{nl\}\}\{\{nl\}\}  plsql.get\_employees do |result|\{\{nl\}\} ~~result.fields\{\{nl\}\} end} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}{[} :employee\_id, :name, :hire\_date {]}\{\{nl\}\} \# returns the list of columns of a cursor as an Array} \tn 
% Row Count 6 (+ 6)
% Row 1
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql.get\_employees do |result|\{\{nl\}\} ~~result.fetch\_hash\_all\{\{nl\}\} end\{\{nl\}\} plsql.get\_employees\{ |cursor| cursor.fetch\_hash\_all \}\{\{nl\}\} plsql.get\_employees\{ |any\_name| \seqsplit{any\_name.fetch\_hash\_all} \}\{\{nl\}\}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}{[} \{:employee\_id =\textgreater{} 1, :name =\textgreater{} 'James bond', :hire\_date =\textgreater{} '0007-07-07'\}, \{...\}, ... {]}\{\{nl\}\} \# fetches all rows from a cursor and returns them as an Array of Hashes} \tn 
% Row Count 15 (+ 9)
% Row 2
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.get\_employees\{ |result| result.fetch\_hash \}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}\{:employee\_id =\textgreater{} 1, :name =\textgreater{} 'James bond', :hire\_date =\textgreater{} '0007-07-07'\}\{\{nl\}\} \# fetches one row from a cursor and returns it as a Hash} \tn 
% Row Count 19 (+ 4)
% Row 3
\SetRowColor{white}
\mymulticolumn{1}{x{8.4cm}}{plsql.get\_employees\{ |result| result.fetch \}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}{[}1, 'James bond', '0007-07-07'{]}\{\{nl\}\} \# fetches one row from a cursor and returns it as a Array of values} \tn 
% Row Count 23 (+ 4)
% Row 4
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{plsql.get\_employees\{ |result| result.fetch\_all \}} \tn 
\mymulticolumn{1}{x{8.4cm}}{\hspace*{6 px}\rule{2px}{6px}\hspace*{6 px}{[}{[}1, 'James bond', '0007-07-07'{]}, {[}...{]}, ... {]}\{\{nl\}\} \# fetches all rows from a cursor and returns them as an Array of Arrays of values} \tn 
% Row Count 27 (+ 4)
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\SetRowColor{LightBackground}
\mymulticolumn{1}{x{8.4cm}}{\# cursor needs to be accessed inside a block ( do .. end / \{ .. \} ) \newline \# as cursors are automatically closed after the function call ends}  \tn 
\hhline{>{\arrayrulecolor{DarkBackground}}-}
\end{tabularx}
\par\addvspace{1.3em}


% That's all folks
\end{multicols*}

\end{document}