johnhonan’s gists

johnhonan / del_rows_w_backup.sql

Created December 19, 2017 19:57

EXECUTE IMMEDIATE to first create a timestamped backup of a table and then delete rows

	-- This shows how to create a procedure
	-- which uses EXECUTE IMMEDIATE to first create a timestamped backup of a table
	-- before deleting all the rows from that table

	CREATE TABLE hr.my_test_table2 AS SELECT * FROM hr.EMPLOYEES;

	CREATE OR REPLACE PROCEDURE hr.del_rows
	(p_table_name IN VARCHAR2, p_rows_deld OUT NUMBER)
	IS
	BEGIN

johnhonan / case_agg.sql

Created December 7, 2017 19:49

CASE statement and sub-query aggregate

	SELECT salary_group, count(*) FROM
	(SELECT
	employee_id,
	last_name,
	CASE
	WHEN salary <= 3000 THEN 'poor'
	WHEN salary >3000 AND salary <= 11000 THEN 'okay'
	WHEN salary > 11000 THEN 'rich'
	ELSE 'Theres a problem'
	END AS salary_group

johnhonan / subq_join.sql

Created December 5, 2017 21:07

Joining subqueries

	SELECT DISTINCT
	job_history.employee_id,
	employees.first_name,
	x.max_start,
	y.min_start
	FROM hr.job_history
	JOIN hr.employees
	ON job_history.employee_id = employees.employee_id
	JOIN
	(SELECT employee_id, max(start_date) as max_start

johnhonan / subq_join.sql

Created December 5, 2017 21:06

	SELECT DISTINCT
	job_history.employee_id,
	employees.first_name,
	x.max_start,
	y.min_start
	FROM hr.job_history
	JOIN hr.employees
	ON job_history.employee_id = employees.employee_id
	JOIN
	(SELECT employee_id, max(start_date) as max_start

johnhonan / find_dupes.sql

Last active December 5, 2017 20:54

Dupe example create tables

	CREATE TABLE "HR"."PRODUCTS"
	( "PRODUCT_ID" NUMBER,
	"PRODUCT_DESC" VARCHAR2(30 BYTE)
	);

	Insert into HR.PRODUCTS (PRODUCT_ID,PRODUCT_DESC) values (10,'Oranges');
	Insert into HR.PRODUCTS (PRODUCT_ID,PRODUCT_DESC) values (11,'Apples');
	Insert into HR.PRODUCTS (PRODUCT_ID,PRODUCT_DESC) values (15,'Pineapples');
	Insert into HR.PRODUCTS (PRODUCT_ID,PRODUCT_DESC) values (34,'bananas');
	Insert into HR.PRODUCTS (PRODUCT_ID,PRODUCT_DESC) values (10,'Coconuts');

johnhonan / autonomous_audit.sql

Created November 30, 2017 19:33

Oracle example of autonomous transaction for logging audit messages

	-- 1) Create a table in the hr database called 'log'
	-- with two fields; log_msg (text) and time_of_msg (datetime)

	-- 2) Modify the procedure below to insert a record into
	-- the log table;
	CREATE OR REPLACE PROCEDURE hr.log_details (msg IN VARCHAR2)
	IS
	PRAGMA AUTONOMOUS_TRANSACTION; -- run a commit here regardless of any other transaction state
	BEGIN
	INSERT INTO hr.log(msg_id,log_msg,time_of_msg)

johnhonan / calc_list.py

Created November 29, 2017 21:14

calculator which can take lists and uses map-reduce functions

	class Calculator(object): # define a calculator object
	def add(self, x): # define addition function
	# use comprehebnsion to check that all
	# elements in a list are numeric
	number_types = (int, long, float, complex) # accepted number types
	if all(isinstance(item, number_types) for item in x):
	return reduce(lambda a,b: a+b, x)
	else:
	raise ValueError # if one or both values are not accepted number types return a Value Error

johnhonan / calculator_test.py

Created November 29, 2017 20:26

Calculator test functions

	import unittest
	from calculator import Calculator

	# test the calculator functionality
	class TestCalculator(unittest.TestCase):
	def setUp(self):
	self.calc = Calculator()

	# this tests the add functionality
	# 2 + 2 = 4

johnhonan / calculator.py

Created November 29, 2017 20:26

python calculator

	class Calculator(object): # define a calculator object
	def add(self, x, y): # define addition function
	number_types = (int, long, float, complex) # accepted number types

	if isinstance(x, number_types) and isinstance(y, number_types): # if both values are accepted number types
	return x + y # return the sum of the values
	else:
	raise ValueError # if one or both values are not accepted number types return a Value Error

	def subtract(self, x, y):

johnhonan / create_a_package.sql

Created November 28, 2017 20:53

	-- This is a package specification
	CREATE OR REPLACE PACKAGE hr.job_pack
	AUTHID CURRENT_USER
	IS -- Declare package components.
	-- Author: jhonan@gmail.com
	-- Date: 28-Nov-2017
	-- Procedures to add/update and delete
	-- into the jobs table
	PROCEDURE ADD_JOB (v_job_id VARCHAR2,v_job_title VARCHAR2);
	PROCEDURE UPD_JOB (v_job_id VARCHAR2,v_job_title VARCHAR2);

John Honan johnhonan