Will This Be On The Turing Test?: October 2013

Wednesday, October 23, 2013

My Oracle 12c Cheat Sheet (for Windows)

I. installation

To install Oracle 12c on Windows, follow the excellent, step by step instructions given at this blog post by Tomaz Lesinski:
http://dbaora.com/install-oracle-12c-12-1-0-1-on-windows/
If you follow Tomasz Lesinski's sage advice, then all should go well. You might need to manually intervene during the installation process to "allow access" through your Windows Firewall (or you could turn the firewall off during the install). And if you encounter any "file not found" errors during the install, this probably means that the zip files got corrupted during the download, so just delete them and repeat the download.

II. using the sample schemas

To locate the sample schemas (hr, sh, oe, etc) which are now hidden away in "pluggable" databases, follow the simple explanations and directions given at the "slowdatum" blog here:
http://slowdatum.wordpress.com/2013/07/25/oracle-12c-finding-example-schema-hrshoe-etc/

Here are the essential six steps:

1. From the command line, enter SQLPLUS the usual way with:

sqlplus / as sysdba

2. In SQLPLUS connect to the "pluggable" database like this:

SQL> connect sys/pass@localhost:1521/pdborcl as sysdba

3. Now you must start up the pluggable database (by executing the command "startup").

4. Unlock the sample schemas (you only have to do this the first time you use them), for example:
SQL> alter user hr identified by hr account unlock;

5. Now you can connect to the hr account:

SQL> connect hr/hr@localhost:1521/pdborcl

6. Before you can connect to "hr" (etc) in SQLDeveloper, you must first unlock the accounts in SQLPLUS (as described in #4 above). Then to create the connection the slightly tricky part is that for accounts in pluggable databases you don't use the SID, rather you use the "Service Name", which by default should be "pdborcl".

III. to plug or not to plug?

What if you don't want to mess with pluggable databases? Here are a couple of things that you might find helpful:
1. In "container" databases (that is, non-pluggable ones) usernames must start with "C##". This does not apply to users in pluggable databases. Basically, the New Order as of 12c is that "container" databases (as opposed to "pluggable" databases) have "common" users, as opposed to "local" users which one has in the "pluggable" databases. Anyway, "common" usernames must start with "C##".
2. Previously (in 11g, for example) when you created a new user, and you wanted that user to have sufficient privileges to do stuff, you could do something like this (for a user called, for example, c##student):

GRANT CONNECT, RESOURCE TO c##student;
GRANT SELECT_CATALOG_ROLE TO c##student;
GRANT CREATE VIEW TO c##student;
BUT now (at least this is what I have found) you must also do the following:
GRANT UNLIMITED TABLESPACE TO c##student.

For more on container databases, common users, etc, check out some of these links:

Tuesday, October 22, 2013

A closer look at a simple PL/SQL procedure

Please refer to page 569 of Steven Feuerstein's classic "Oracle PL/SQL Programming", the fifth edition. This blog post will focus on the procedure "apply_discount". Before going any further, note the following three important things:
1. To actually create the procedure, the "PROCEDURE" keyword must, obviously, be preceded by the "CREATE" keyword.
2. Two typos exist on line 11: (i) change ":=" to just "=", and (ii) remove the semicolon at the end of the line.
3. The table name "order" is invalid (it raises a "ORA-00903: invalid table name" error), because "order" is a reserved word, so change it to "order_table".

Now let's get down to business.

1. First, create the tables:

CREATE TABLE ORDER_TABLE
( ITEM_ID NUMBER,
ORDER_ID NUMBER,
ITEM_AMOUNT NUMBER);

CREATE TABLE ITEM
( COMPANY_ID NUMBER,
ORDER_ID NUMBER,
SALESPERSON_ID NUMBER);

CREATE TABLE COMPANY
( COMPANY_ID NUMBER,
COMPANY_NAME VARCHAR2(30));

---------------------------------------------

2. Second, populate the tables with some data:

insert into order_table
values (1, 1, 3);
insert into order_table
values (2, 1, 4);
insert into order_table
values (3, 2, 5);

insert into item
values (1, 1, 10.0);
insert into item
values (2, 1, 20.0);
insert into item
values (1, 2, 10.0);
insert into item
values (3, 2, 30.0);
insert into item
values (2, 3, 20.0);

insert into company
values (1, 'bozo_inc');
insert into company
values (2, 'bus_inc');

---------------------------------------------

3. Third, create the procedure

CREATE PROCEDURE apply_discount
(company_id_in IN company.company_id%TYPE, discount_in IN number)
IS
min_discount CONSTANT NUMBER := 0.05;
max_discount CONSTANT NUMBER := .25;
invalid_discount EXCEPTION;
BEGIN
IF discount_in BETWEEN min_discount AND max_discount
THEN
UPDATE item
SET item_amount=item_amount*(1-discount_in)
WHERE EXISTS ( SELECT 'x' FROM order_table
WHERE order_table.order_id=item.order_id
AND order_table.company_id=company_id_in);
IF SQL%ROWCOUNT = 0 THEN RAISE NO_DATA_FOUND; END IF;
ELSE
RAISE invalid_discount;
END IF;
EXCEPTION
WHEN invalid_discount
THEN
DBMS_OUTPUT.PUT_LINE('The specified discount ('||discount_in||') is invalid.');
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('No orders in the system for this company: '||
TO_CHAR(company_id_in))
END apply_discount;

For your cutting and pasting convenience, here it is without the line numbers:

CREATE PROCEDURE apply_discount
(company_id_in IN company.company_id%TYPE, discount_in IN number)
IS
min_discount CONSTANT NUMBER := 0.05;
max_discount CONSTANT NUMBER := .25;
invalid_discount EXCEPTION;
BEGIN
IF discount_in BETWEEN min_discount AND max_discount
THEN
UPDATE item
SET item_amount=item_amount*(1-discount_in)
WHERE EXISTS ( SELECT 'x' FROM order_table
WHERE order_table.order_id=item.order_id
AND order_table.company_id=company_id_in);
IF SQL%ROWCOUNT = 0 THEN RAISE NO_DATA_FOUND; END IF;
ELSE
RAISE invalid_discount;
END IF;
EXCEPTION
WHEN invalid_discount
THEN
DBMS_OUTPUT.PUT_LINE('The specified discount ('||discount_in||') is invalid.');

WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE('No orders in the system for this company: '||TO_CHAR(company_id_in))
END apply_discount;

---------------------------------------------

4. Fourth, check to make sure things are as they should be:

select i.item_id, i.order_id, c.company_name, i.item_amount
from item i join order_table o on (i.order_id = o.order_id)
join company c on (o.company_id = c.company_id)
order by 1;

ITEM_ID ORDER_ID COMPANY_NAME ITEM_AMOUNT
---------- ---------- ------------------------------ -----------
1 1 bozo_inc 10
1 2 bozo_inc 10
2 3 bus_inc 20
2 1 bozo_inc 20
3 2 bozo_inc 30

Notes:
a. According to the data loaded into the tables in Step 2 above, there are two companies, three orders, and five line items spread across those three orders. All of the data is summarized in the select statement directly above.
b. For this data, the "item" table has one line for each line item in each order. Therefore, the item_id field is not unique. If you wanted to (and you should want to!) have a primary key on this table it would be a composite key made up of "item_id" and "order_id". There should ~~probably~~ also be a "qty" field. And a better name for the table would be "line_item".

---------------------------------------------

5. Fifth run the procedure (after making a backup):

create table item_bup as select * from item;
Table created.
execute apply_discount(2, 0.15);
PL/SQL procedure successfully completed.

---------------------------------------------

6. Sixth, compare with results from step Four above:

ITEM_ID ORDER_ID COMPANY_NAME ITEM_AMOUNT
---------- ---------- ------------------------------ -----------
1 1 bozo_inc 10
1 2 bozo_inc 10
2 3 bus_inc 17
2 1 bozo_inc 20
3 2 bozo_inc 30

Notes: The discount was correctly applied to only items ordered by company #2, "bus_inc".

---------------------------------------------

7. Seventh, restore original table and rerun on other company:

drop table item;
create table item as select * from item_bup;
execute apply_discount(1, 0.15);

ITEM_ID ORDER_ID COMPANY_NAME ITEM_AMOUNT
---------- ---------- ------------------------------ -----------
1 1 bozo_inc 8.5
1 2 bozo_inc 8.5
2 3 bus_inc 20
2 1 bozo_inc 17
3 2 bozo_inc 25.5

This time, the discount was applied to items ordered by company #1, but the price for items ordered by company #2 were left unchanged.

---------------------------------------------

8. What's up with that "WHERE EXISTS" statement?

The where statement on line 13 (refer to the procedure in Step 3 above) is unnecessarily convoluted, or is it? The following select statement would achieve the same results, and is more straightforward:

WHERE order_id in
(select order_id
from order_table
where company_id = company_id_in)

But "EXISTS" is famously more efficient than "IN":
http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance/2065374#2065374

Also, "IN" can cause serious problems related to "three-value logic" (ie, NULLs).

Also check out this interesting discussion of IN versus JOIN versus EXISTS:
http://www.gregreda.com/2013/06/03/join-vs-exists-vs-in/

Wednesday, October 16, 2013

SQL Fundamentals Final Project

Since I won't be at the final class, I am posting this for my "team-mates" Victoria and Ed (right now it's incomplete, but I hope to have it finished by the time class starts at 6:30.....):
-------------------------------------------------------------------------------------------

#7.
create sequence emp_seq start with 207;

#8.
alter table emp
add constraint pk_emp_empid
primary key (employee_id);

#9.
alter table dept
add constraint pk_dept_depid
primary key (department_id);

#10.
alter table emp
add constraint fk_emp_depid
foreign key (department_id)
references dept (department_id);

#11.
insert into emp
(employee_id, last_name, email, hire_date, job_id, department_id)
values
(emp_seq.nextval, 'Steinmetz', 'curt@nowhere.com', sysdate, 'IT_PROG', 60);

#13:
select first_name||' '||last_name "Full Name", department_name
from emp join dept
using (department_id)
order by department_name;

#14.
select first_name||' '||last_name, salary, employee_id
from emp
where salary >= (select max(salary) from emp);

#15
select last_name, job_id, department_id, department_name, city
from emp join dept
using (department_id)
join location
using (location_id)
where upper(city) like 'SEATTLE';

(NOTE: REPLACE 'SEATTLE' WITH 'TOKYO')

#16.
select last_name,
to_char(hire_date, 'MM/DD/YYYY') "Hire Date",
to_char(salary, '$999,999.99') "Salary"
from emp

#17., #18, #19
create table emp5
as select * from emp
where 1 = 2;
create table emp8
as select * from emp
where 1 = 2;
create table empall
as select * from emp
where 1 = 2;

#20.
insert all
when department_id = 50 then into emp5 values
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
when department_id = 80 then into emp8 values
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
when last_name is not null then into empall values
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
from emp

where department_id =
(select department_id from emp
where upper(last_name) = 'MATOS');