Oracle Cheat Sheet

Feature comparison of utPLSQL v.2.3.0 vs. ruby-plsql v.0.5.3

Oracle General

Creating an object type

-- an abstract object
CREATE OR REPLACE TYPE RECTANGLE FORCE AS
    OBJECT (
        LENGTH NUMBER,
        WIDTH NUMBER,
        NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE DISPLAY,
        -- define a map or order function for comparison
        NOT INSTANTIABLE NOT FINAL MAP MEMBER FUNCTION AREA
                RETURN NUMBER
    ) NOT INSTANTIABLE NOT FINAL;
/
CREATE OR REPLACE TYPE SQUARE UNDER RECTANGLE (
    CONSTRUCTOR FUNCTION SQUARE (LENGTH NUMBER, WIDTH NUMBER)
         RETURN SELF AS RESULT,
    OVERRIDING FINAL MEMBER PROCEDURE DISPLAY,
    OVERRIDING FINAL MAP MEMBER FUNCTION AREA RETURN NUMBER,
    STATIC FUNCTION TEST RETURN VARCHAR2
) FINAL;
/
CREATE OR REPLACE TYPE BODY SQUARE AS
    CONSTRUCTOR FUNCTION SQUARE (LENGTH NUMBER, WIDTH NUMBER)
         RETURN SELF AS RESULT AS
    BEGIN
        SELF.LENGTH := LENGTH;
        SELF.WIDTH := WIDTH;
        RETURN;
    END;
    OVERRIDING FINAL MEMBER PROCEDURE DISPLAY AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Square: '
                             || SELF.LENGTH);
    END;
    OVERRIDING FINAL MAP MEMBER FUNCTION AREA
         RETURN NUMBER AS
    BEGIN
        RETURN LENGTH * LENGTH;
    END;
    STATIC FUNCTION TEST RETURN VARCHAR2 AS
    BEGIN
        -- SELF and instance attributes are not available
        -- in static functions
        RETURN 'test';
    END;
END;
/

SELF refers to the current instance of an object. It is an implicit parameter for methods - IN for functions and IN OUT for procedures. It may be omitted when used with MEMBER methods. Objects support only Oracle types - no user defined types are supported in attributes and methods.

Creating a package

CREATE OR REPLACE PACKAGE TEST_PACKAGE AS
  FUNCTION TEST_FUNCTION RETURN VARCHAR2;
  PROCEDURE TEST_PROCEDURE;
END TEST_PACKAGE;
/
CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE AS
    FUNCTION TEST_FUNCTION RETURN VARCHAR2 AS
        V_TEST  VARCHAR2(10);
        V_TEST2 VARCHAR2(10) NOT NULL := 'test2';
    BEGIN
        V_TEST := 'test';
        RETURN V_TEST;
    END TEST_FUNCTION;

    PROCEDURE TEST_PROCEDURE IS
    BEGIN
        NULL;
    END TEST_PROCEDURE;
END TEST_PACKAGE;
/

A package consists of a specification and a body.

DELETE

DELETE	DELETE FROM table_name
	WHERE condition ;

UPDATE

UPDATE	UPDATE table_name
	SET field_name = new_value
	WHHERE condition ;

SELECT

SELECT	SELECT field1, field2..... FROM table_name
	WHERE condition ;

INSERT

INSERT	INSERT INTO table_name (field1, filed2)
	VALUES(field1_value, field2_value);

CREATE TABLE

CREATE TABLE table_name (	CREATE TABLE student (
field_1 data_type,	id NUMBER,
field_2 data_type);	name VARCHAR2(50));

Functions

Date Functions

"add_months (d, n)"	Date d plus n months
"months_between (d, e)"	Months between dates d and e (d-e)
last_day (d)	Last day of the month containing date d
"next_day (d, weekday)"
"new_time (d, z1, z2)"
"round (d[, fmt])"
"trunc (d[, fmt])"
"extract (c from d)"

BASIC COMMANDS

SELECT

INSERT

UPDATE

DELETE

SAN (STORAGE AREA NETWORK)

Definición	Caracteristicas
Una red de área de almacenamiento (SAN) es una red de alta velocidad que proporciona el acceso al almacenamiento a nivel de bloque. El objetivo de las SAN es mejorar la disponibilidad y el rendimiento de las aplicaciones al separar el tráfico de almacenamiento del resto de la LAN.	proporciona un conjunto de recursos de almacenamiento que se pueden administrar y asignar de manera centralizada según sea necesario

nuevo

Blocco anonimo

begin

    null;

end;

Anonymous Block

BEGIN

listener.ora example

# listener.ora Network Configuration File: D:\oracle\product\12.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\oracle\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\oracle\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname.unmc.edu)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

if set host=hostname.unmc.edu will solve the no tns listner issue from toad

No listener error: ora-015241

listner is up and seems fine.
$oracle_home/network/admin/sqlnet.ora
host=localhost	host=127.0.0.1
USE_SID_AS_SERVICE=on	for tnsnames.ora with sid hard coded

First Scope of 12c Database

https://localhost:5500/em
-- with Em Express Login issues:switch to CDB$ROOT, run exec dbms_xdb_config.setglobalportenabled(TRUE);
--check https port
--common user need em_express_basic or em_empress_all role

set oracle_sid=cdb
sqlplus sys as sysdba
alter pluggable database pdb1 open;

select name, cdb,con_id from v$database;
select instance_name, status,con_id from v$instance;
select dbms_xdb_config.getHttpsPort() from dual;
select con_id, name, open_mode from v$pdbs;

--swtich to different pdb
ALTER SESSION SET CONTAINER=pdb1;
show con_name
show parameter dispatchers

--switch back to cdb
Alter session set container=CDB$ROOT;

Create Oracle12c DB

using dbca, configure the cdb,pdb, listener and em ( default port 5500)

Installation

For testing, using default options, configure oracle_home,oracle_base and inventory structure

Misc Other Multitenant Management Commands

Cloning from NonCDB to CDB
NonCDB must support multitenant and use dblink on NONCDB to connect
DBLink user must have CREATE SESSION and CREATE PLUGGABLE DATABASE privileges

CREATE PLUGGABLE DATABASE <new_pdb> FROM NON$CDB@<dblink>

       FILE_NAME_CONVERT=('</source datafile path/>,'</target datafile path/>');

@ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

ALTER PLUGGABLE DATABASE <target pdb> OPEN;

Moving a PDB

CREATE PLUGGABLE DATABASE <new pdb> FROM <old pdb>@<dblink> RELOCATE;

ALTER PLUGGABLE DATABASE <new pdb> OPEN;

Removing a PDB

ALTER PLUGGABLE DATABASE <name> CLOSE;

DROP PLUGGABLE DATABASE <name> INCLUDING DATAFILES;

Exporting/Unplugging a pdb to a single compressed file

ALTER PLUGGABLE DATABASE <pdb_name> UNPLUG INTO '</path/><filename>.pdb';

Importing/Plugging in a pdb from a single compressed file

CREATE PLUGGABLE DATABASE <new pdb name> USING '</path/><filename>.pdb';

Note that compressed pdb files for export and import are suffixed by .pdb and are a zip fle format.

PROXY Database Functionality

A special type of PDB is a Proxy PDB. A Proxy PDB essentially is a PDB that is linked to another PDB so that if a PDB is being migrated to another environment and there is a desire to not modify all source code to new location references first, they can still use the old references on a Proxy and the actions will take place on the New DB.

To setup, first setup a dblink to the pluggable target

CREATE PLUGGABLE DATABASE <proxy pdb name> AS PROXY FROM <target pdb>@<dblink>;

NOTE: dblink may be dropped after proxy db is created

In a proxy DB the alter Database and Alter Pluggable Database commands apply to the proxy db. All other DDL applies to the target db.

Moving PDB's (Unplugging/Plugging in PDB)

Exporting/Unplugging An Existing PDB
To unplug a database, use the following commands. It is recommended that the path used match the datafile storage location.

ALTER PLUGGABLE DATABASE <pdb_name> CLOSE;

ALTER PLUGGABLE DATABASE <pdb_name> UNPLUG INTO '</path/><name>.xml';

DROP PLUGGABLE DATABASE <pdb_name> KEEP DATAFILES;

Importing/Plugging in PDB into a CDB
Before importing/plugging in a PDB into a CDB a small procedure should be run to Validate the integrity and compatibility of the PDB.

SET SERVEROUTPUT ON

DECLARE

     l_result     BOOLEAN;

BEGIN

     l_result := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

                          PDB_DESCR_FILE => '</path/><name>.xml',

                          PDB_NAME       => '<name>');

     IF l_result THEN

          DBMS_OUTPUT.PUT_LINE('Compatible, OK to Proceed');

     ELSE

          DBMS_OUTPUT.PUT_LINE('Incompatible, See PDB_PLUG_IN_VIOLATIONS for details');

     END IF;

END;

If the pdb is validated, then use the following commands to import/plug it in. Reference the xml file path specified during export, and the datafile path...

CREATE PLUGGABLE DATABASE <new_pdb_name> USING '</path/><name>.xml'

       FILE_NAME_CONVERT=('</source path/>','</dest path/>');

ALTER PLUGGABLE DATABASE <new_pdb_name> OPEN;

Backup and Recovery

Backup
RMAN connection to root container...
Normal Backup will capture full instance
For just Root Container

     BACKUP DATABASE ROOT

For Pluggable Databases

     BACKUP PLUGGABLE DATABASE <pdb1,pdb2,pdb3>

RMAN connection to pluggable database will only work on that pdb

Restore
Connect to root container. Normal restore is full instance. For pdb...

RUN {ALTER PLUGGABLE DATABASE <pdb> CLOSE;

     SET UNTIL TIME "<timeset value>";

     RESTORE PLUGGABLE DATABASE <pdb>;

     RECOVER PLUGGABLE DATABASE <pdb>;

     ALTER PLUGGABLE DATABASE <pdb>OPEN; }

Managing a Multitenant Database

Startup and Shutdown
Startup and Shutdown of a multitenant database function the same as on a regular database, however, if connected to pluggable database, only the pluggable database shuts down. If connected to the root container database then the entire instance shuts down. Pluggable databases also have their own commands that can be run from the root container or other pluggable db.

ALTER PLUGGABLE DATABASE <name>OPEN READ WRITE{RESTRICTED}{FORCE};

ALTER PLUGGABLE DATABASE <name> OPEN READ ONLY {RESTRICTED}{FORCE};

ALTER PLUGGABLE DATABASE <name> OPEN UPGRADE {RESTRICTED};

ALTER PLUGGABLE DATABASE <name> CLOSE {IMMEDIATE};

To retain state as startup state of container...

ALTER PLUGGABLE DATABASE <name> SAVE STATE;

Roles and Users
Common Users and Roles must be created in the root container and prefixed by the characters

c##

Local Users and Roles must be created in pdb

Granting Roles and Privileges

GRANT <privelege/role> TO <user> CONTAINER=<PDB name or ALL>;

If local only, grant from pdb and omit container argument.

Cloning/Creating a PDB

First, set your source and target datafile paths...

ALTER SESSION SET PDB_FILE_NAME_CONVERT='</seed path/>','</target path/>';

Then run the create command from the target root container...

CREATE PLUGGABLE DATABASE <New PDB Name>

                          ADMIN USER <Username>

                                IDENTIFIED BY <Password>

                          FROM <Source PDB[@dblink]>

Finally, Open the newly created database...

ALTER PLUGGABLE DATABASE <target pdb> OPEN;

NOTE: Creating a PDB is just cloning from the seed db.
[@dblink] is optional and used when creating PDB from existing PDB on another instance.
If using dblink, the link user should be an administrative user on the source PDB

Daily Use Commands (from SQL command line)

Connect to Contaner or PDB
`CONN <user>/<pwd>@//<host>:<listener port>/<service> {as sysdba};`
`CONN <user>/<pwd>@//<tns_entry> {as sysdba};`
Display Current Container or PDB
`SHOW CON_NAME;`	`SELECT SYS_CONTEXT('USERENV','CON_NAME')`
`SHOW CON_ID` ;	`FROM DUAL;`
List Containers and PDBs on Instance
`SELECT PDB_NAME, Status`	`SELECT Name, Open_Mode`	`SELECT Name,PDB`
`FROM DBA_PDBS`	`FROM V$PDBS`	`FROM V$SERVICES`
`ORDER BY PDB_Name;`	`ORDER BY Name;`	`ORDER BY Name;`
Change Container or PDB
`ALTER SESSION SET container=<name>;`	`ALTER SESSION SET container=cdb$root;`

Essential Terminology

Multitenant	The Oracle Architecture that consists of a CDB, and one or more PDBs
Container Database (CDB)	A traditional database instance, but has the ability to support PDBs
Pluggable Database (PDB)	A collection of Tablespaces which supports it's own independant Role and User security, and can be easily moved between CDBs
Root Database	The instance administrative layer that sets above PDBs. Users and Roles here must be preceded by c##
Seed Database	A PDB that remains offline to be used as a template for creating new blank PDBs

Expectation syntax

Base expectation

ut.expect( actual_value ).to_( matcher );

Negated expectation

ut.expect( actual_value ).not_to( matcher );

Shortcuts syntax

ut.expect( actual_value ).to_matcher;

ut.expect( actual_value ).not_to_matcher;

Catching exceptions

procedure my_code_raises_zero_divisor is
  l_my_number number;
begin
  l_my_number := 1/0; --should raise
  ut.fail('Expected exception but nothing was raised');
exception
  when others then
    ut.expect( sqlcode ).to_equal( -1476 );
end;

Executing tests

Run all unit tests in my current schema

exec ut.run();

Run all unit tests in current schema after it was changed to HR

alter session set current_schema='HR';

exec ut.run();

Run all unit tests in specific schema

exec ut.run('HR');

Run all unit tests in specific package of current schema

exec ut.run('test_betwnstr');

Run all unit tests in specific schema.package

exec ut.run('hr.test_betwnstr');

Run one specific test only

exec ut.run('hr.test_betwnstr.big_end_position');

Run several items

exec ut.run(ut_varchar2_list(
    'hr.test_award_bonus',
    'hr.test_betwnstr.big_end_position'));

Run test using suitepath

  exec ut.run(':com.my_org.my_project');

Run the tests as a select statement

select * from table(ut.run());

Non-equality matchers

be_like

ut.expect( 'Lorem_impsum' ).to_(
  be_like( a_mask => '%rem\_%', a_escape_char => '\' ) );

 ut.expect( 'Lorem_impsum' ).to_( be_like( '%rem%sum' ) );

a_mask, a_escape_char -> see Oracle like operator

match

ut.expect( a_actual => '123-456-ABcd' ).to_(
  match( a_pattern => '\d{3}-\d{3}-[a-z]', a_modifiers => 'i' ) );

  ut.expect( 'some value' ).to_( match( '^some.*' ) );

a_pattern, a_modifiers -> see regexp_like function

be_between

ut.expect( 3 ).to_( be_between( 1, 3 ) );

be_greater_or_equal

ut.expect( sysdate ).to_( be_greater_or_equal( sysdate - 1 ) );

be_greater_than

ut.expect( 2 ).to_( be_greater_than( 1 ) );

be_less_or_equal

ut.expect( 3 ).to_( be_less_or_equal( 3 ) );

be_less_than

exec ut.expect( 3 ).to_( be_less_than( 2 ) );

Equality matcher

equal

ut.expect( 'a dog' ).to_(
  equal( 'a dog' , a_nulls_are_equal => false ) );

a_nulls_are_equal is true by default

equal with cursors

open l_expected for select * from dual;

open l_actual for select * from dual where 1 = 0;

ut.expect( l_expected ).to_(
  equal(l_actual, a_exclude=>'column_a,column_b') );

equal on objects

ut.expect( anydata.convertObject(l_expected) ).to_(
  equal( anydata.convertObject(l_actual) ) );

equal on collections

ut.expect( anydata.convertCollection(l_expected) ).to_(
  equal( anydata.convertCollection(l_actual) ) );

Expectation structure

Base expectation block

ut.expect( actual_value ).to_( matcher );

Negated expectation block

ut.expect( actual_value ).not_to( matcher );

Shortcuts to matchers

ut.expect( actual_value ).[not_]to_matcher;

Common annotations

--%disabled	Suite / test will not execute
--%rollback(auto)	Automatic savepoints/rollbacks - default
--%rollback(manual)	No savepoint/rollback

Unary matchers

be_empty

open l_cursor for select * from dual where 1 = 0;

ut.expect( l_cursor ).to_( be_empty() );

be_false

  ut.expect( ( 1 = 0 ) ).to_( be_false() );

be_not_null

  ut.expect( to_clob('ABC') ).to_( be_not_null() );

be_null

  ut.expect( 1 ).to_( be_null() );

be_true

  ut.expect( ( 1 = 1 ) ).to_( be_true() );

Procedure annotations

--%test	The procedure is a test
--%test(description)	A test with description
--%beforetest(procedure_name)	Runs the procedure before annotated test
--%aftertest(procedure_name)	Runs the procedure after annotated test
--%beforeall	The procedure to run before first test in suite
--%afterall	The procedure to run after last test in suite
--%beforeeach	The procedure to run before each test
--%aftereach	The procedure to after each test

Package annotations

--%suite	Package is a test suite
--%suite(description)	A suite with description
--%suitepath(org.my_org.my_project)	Similar to Java package. Groups suites in namespaces

Annotations are sinlgle-line comments starting with a % sign.
Needed in package specification only (documentation)

 Regions

Function	Mac	PC
Rendering	Option+1	Alt+1
Dynamic Actions	Option+2	Alt+2
Processing	Option+3	Alt+3
Page Shared Components	Option+4	Alt+4
Grid Layout	Option+5	Alt+5
Property Editor	Option+6	Alt+6
Gallery Regions	Option+7	Alt+7
Gallery Items	Option+8	Alt+8
Gallery Buttons	Option+9	Alt+9

 Miscellaneous

Help	Option+F1	Alt+F1
Messages	Ctrl+F1	Ctrl+F1
Page Search	Ctrl+Option+F	Ctrl+Alt+F
Keyboard Shortcuts	Option+Shift+F	Alt+Shift+F1

 Region Actions

Display From Here	Ctrl+Option+D	Ctrl+Alt+D
Display From Page	Ctrl+Option+T	Ctrl+Alt+T
Restore/Expand	Option+F11	Alt+F11
Toggle Empty Region Positions	Ctrl+Option+E	Ctrl+Alt+E

 Actions

Function	Mac	PC
Save	Ctrl+Option+S	Ctrl+Alt+S
Save and Run Page	Ctrl+Option+R	Ctrl+Alt+R
Undo	Ctrl+Z	Ctrl+Z
Redo	Ctrl+Y	Ctrl+Y

API

Feature	utPLSQL	ruby-plsql
Complexity	Complex and inconsistent	Consistent and dense
Test coverage for API	NO Tested manually by users and contributors	YES API is tested by unit tests
Learning curve	High Mailnly due to inconsistency and workarounds and tricks used to overcome nature and limitations of PL/SQL	Low/medium Need to learn RSpec and Ruby basics and how to use Array and Hash Objects
IDE support	No IDE support for API itself. Running tests, reporting from IDE is not supported	Highly supported with JetBrains Rubymine (or IntelliJ) Ruby, RSpec, Cucumber, Gherkin syntax highlighting and code completion Test execution, exporting test results Support for GIT/SVN/Mercurial, PLSQL, SQL, Jira, Stash and more (many of free plugins available)
Completeness	Medium	High
Integrates with CI (Jenkins)	not directly Doable through external calls with Java and Maven Integration suffers from API reporting limitations	YES CI-JUnit Reporter plugins available
Documentation	Incomplete online documentation	Everything described by examples Concept of self documenting tests

Reporting

Feature	utPLSQL	ruby-plsql
Build in report types	3 build-in types: - screen output to client console - file output (needs to write to DB server) - html file output (needs to write to DB server) Outputs incomplete, console output noisy.	4 build-in types - dotted - very dense, useful for developers - documentation - QA text reporting oriented - HTML - like documentation but in publishable form - JSON - for machine processing
Extensibility / third party	Can be extended - do it yourself	Available open-source libraries for other output formatting (like CI JUnit formatters)
Build in code coverage generation	NO	YES
Supplies timing for tests	NO	YES
Supplies count of tests executed	not directly	YES
Full stack trace for exceptions	NO	YES
Self-documenting tests / tests expressivness	NO procedure_name_30_char_limit no place for test description description is placed inside single assertion "somewhere inside test code"	YES tests and example blocks have a "full text descriptive names"

Test execution

Feature	utPLSQL	ruby-plsql
Needs compilation prior to execution	YES	NO
Test invocation	- connect to DB - call API to execute package or suite	execute "rspec" from command prompt in project root
Default tests execution scope	NONE You need to explicitly state either a suite or a package to be tested'	All test for project By default, calling "rspec" command from project root will exeute all tests for project
Parallel test executiuon	Doable - do it yourself. By splitting test into separate suites and running them from CI in parallel jobs.	YES-automatic with open-source libraries
Transaction management	Manual	Automatic Conforms with RSpec standard for keeping the object(s) unchanged outside of test scope
Test setup/cleanup	One mandatory setup and cleanup per test package. (Bolierplate code when not used) Useless when different setup needed for each test.	Optional multiple setups cleanups can be defined on each level of example group. Two triggering modes can be mixed for setup/cleanup: - Before all tests in example group - Before each example group Setups /cleanups available for entire suite (before suite/after suite) Setups /cleanups can be invoked with filtering by tags too

Test structure

Feature	utPLSQL	ruby-plsql
Physical test location	Tests located in database schemes / packages or procedures	Yests organized in project into folders/spec files
Physical test organization	Strict - database oriented Schema/package/procedure	Flexible - project oriented Within a test file, tests organized into nestable example groups
Logical test organization	Limited to package level grouping Packages can be organized into suites Each suite can contain many packages Each package can be placed in many suites	Flexible Each test(example) can be labelled with tags Each example group can be labelled with tags Each tag can be assigned to many tests/example groups
Test execution granularity	All tests in a single test package or All tests in a single suite of test packages	Single test or All tests in a specified example group or All tests in a mask-specified directory/file or All test with a specific tag(s) or All tests except specific tag(s) and more
Identifying and naming tests	Each assertion has a mandatory text description Assertion is a test	Each example group can have a descriptive free text name Each example can have a descriptive free text name Each example can contain many assertions composing the test
Re-usable tests/shared examples	NO Separate tests are needed for two functions do the same thing but on different objects(datatypes)	YES Standard of shared examples for testing of identical behavior on different objects(datatypes)
Test suites definition	Defined in database tables	Defined as tags in test definition files or by test file location in directory structure
Suites management	Calls to API prior to test execution, persisted in DB per user	Tags defined beside the test definition in test files
Reporting configuration	Calls to API prior to test execution, persisted in DB per user	Parameter when executin tests
Customizations within test/suite/project	NO One common library per database	YES Own assertions/configurations can be added to tests or project

Assertions

Feature	utPLSQL	ruby-plsql
Assertion types	Two One for equality based matchers One to check if expression evaluates to TRUE	Multiple assertions(matchers) <,>,=,!=,inclusion,regexp,datatype(class),...
Assertion definition	Defined per datatype	Defined per operator
Assertions are used the same way	NO Different usage depending on compared type	YES All assertions follow common pattern
Assertion on User Defined Type data	NO	YES
Assertion on Collection Type data	YES (cumbersome and undocumented usage)	YES
Assertion on PL/SQL records data	YES (cumbersome and undocumented usage)	YES
Assertion on Cursor data	YES (complex usage)	YES
Assertion on complex structured data	NO	YES
Assertion on TIMESTAMP/CLOB/BLOB/RAW	NO	YES
Assertion on success (no exception)	NO	YES
Can tests table/view structure	NO	YES

Generic

Feature	utPLSQL	ruby-plsql
Installation	per DB instance	per client (developer/CI server)
Cross sessions testing	NO	YES
Cross database testing	NO	YES
Can be used for privileges testing	NO	YES
Can be used for VPD/RLS testing	NO	YES
Exception handling	Poor	Full stack trace
Test - tested code isolation	Low	High
Runs with invalid DB dependencies	NO (disappearing tests)	YES
Runs without tested DB objects / code	NO (disappearing tests)	YES
Migration across databases	Needs installation of framework and tests	Trivial. Change of connect string
Test language maturity	Low	Industry standard
Performance	Excellent (100% in database)	Sufficient Suffers from network overhead Suffers from Ruby startup (2-5 secs)
Cucumber support	NO	YES
Suitable for integration testing	NO	YES
Community Activity	Low	Low for ruby-plsql High for RSpec

1
Oracle General

Oracle Cheat Sheet

Table of contents