Skip to content

Commit cbbc2ad

Browse files
anthony-tuiningaanthony-tuininga
committed
Add Oracle Open World 2017 Hands-on Lab (tutorial).
1 parent 76f01f0 commit cbbc2ad

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

51 files changed

+3642
-0
lines changed

‎samples/tutorial/Python-and-Oracle-Database-12c-Scripting-for-the-Future.html‎

Lines changed: 2205 additions & 0 deletions
Large diffs are not rendered by default.

‎samples/tutorial/aq.py‎

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
#------------------------------------------------------------------------------
2+
# aq.py (Section 10.1)
3+
#------------------------------------------------------------------------------
4+
5+
#------------------------------------------------------------------------------
6+
# Copyright 2017, Oracle and/or its affiliates. All rights reserved.
7+
#------------------------------------------------------------------------------
8+
9+
import cx_Oracle
10+
import decimal
11+
12+
con = cx_Oracle.connect("pythonhol", "welcome", "localhost/orclpdb")
13+
cur = con.cursor()
14+
15+
BOOK_TYPE_NAME = "UDT_BOOK"
16+
QUEUE_NAME = "BOOKS"
17+
QUEUE_TABLE_NAME = "BOOK_QUEUE_TABLE"
18+
19+
# Cleanup
20+
cur.execute(
21+
"""begin
22+
dbms_aqadm.stop_queue('""" + QUEUE_NAME + """');
23+
dbms_aqadm.drop_queue('""" + QUEUE_NAME + """');
24+
dbms_aqadm.drop_queue_table('""" + QUEUE_TABLE_NAME + """');
25+
execute immediate 'drop type """ + BOOK_TYPE_NAME + """';
26+
exception when others then
27+
if sqlcode <> -24010 then
28+
raise;
29+
end if;
30+
end;""")
31+
32+
# Create type
33+
print("Creating books type UDT_BOOK...")
34+
cur.execute("""
35+
create type %s as object (
36+
title varchar2(100),
37+
authors varchar2(100),
38+
price number(5,2)
39+
);""" % BOOK_TYPE_NAME)
40+
41+
# Create queue table and queue and start the queue
42+
print("Creating queue table...")
43+
cur.callproc("dbms_aqadm.create_queue_table",
44+
(QUEUE_TABLE_NAME, BOOK_TYPE_NAME))
45+
cur.callproc("dbms_aqadm.create_queue", (QUEUE_NAME, QUEUE_TABLE_NAME))
46+
cur.callproc("dbms_aqadm.start_queue", (QUEUE_NAME,))
47+
48+
# Enqueue a few messages
49+
booksType = con.gettype(BOOK_TYPE_NAME)
50+
book1 = booksType.newobject()
51+
book1.TITLE = "The Fellowship of the Ring"
52+
book1.AUTHORS = "Tolkien, J.R.R."
53+
book1.PRICE = decimal.Decimal("10.99")
54+
book2 = booksType.newobject()
55+
book2.TITLE = "Harry Potter and the Philosopher's Stone"
56+
book2.AUTHORS = "Rowling, J.K."
57+
book2.PRICE = decimal.Decimal("7.99")
58+
options = con.enqoptions()
59+
messageProperties = con.msgproperties()
60+
for book in (book1, book2):
61+
print("Enqueuing book", book.TITLE)
62+
con.enq(QUEUE_NAME, options, messageProperties, book)
63+
con.commit()
64+
65+
# Dequeue the messages
66+
options = con.deqoptions()
67+
options.navigation = cx_Oracle.DEQ_FIRST_MSG
68+
options.wait = cx_Oracle.DEQ_NO_WAIT
69+
while con.deq(QUEUE_NAME, options, messageProperties, book):
70+
print("Dequeued book", book.TITLE)
71+
con.commit()

‎samples/tutorial/bind_insert.py‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
#------------------------------------------------------------------------------
2+
# bind_insert.py (Section 4.2 and 4.3)
3+
#------------------------------------------------------------------------------
4+
5+
#------------------------------------------------------------------------------
6+
# Copyright 2017, Oracle and/or its affiliates. All rights reserved.
7+
#------------------------------------------------------------------------------
8+
9+
import cx_Oracle
10+
11+
con = cx_Oracle.connect("pythonhol", "welcome", "localhost/orclpdb")
12+
cur = con.cursor()
13+
14+
rows = [ (1, "First" ), (2, "Second" ),
15+
(3, "Third" ), (4, "Fourth" ),
16+
(5, "Fifth" ), (6, "Sixth" ),
17+
(7, "Seventh" ) ]
18+
19+
cur.executemany("insert into mytab(id, data) values (:1, :2)", rows)
20+
21+
# Now query the results back
22+
23+
cur2 = con.cursor()
24+
cur2.execute('select * from mytab')
25+
res = cur2.fetchall()
26+
print(res)

‎samples/tutorial/bind_insert.sql‎

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
-------------------------------------------------------------------------------
2+
-- bind_insert.sql (Section 4.2)
3+
-------------------------------------------------------------------------------
4+
5+
/*-----------------------------------------------------------------------------
6+
* Copyright 2017, Oracle and/or its affiliates. All rights reserved.
7+
*---------------------------------------------------------------------------*/
8+
9+
set echo on
10+
11+
connect pythonhol/welcome@localhost/orclpdb
12+
13+
drop table mytab;
14+
15+
create table mytab (id number, data varchar2(20), constraint my_pk primary key (id));
16+
17+
exit

‎samples/tutorial/bind_query.py‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
#------------------------------------------------------------------------------
2+
# bind_query.py (Section 4.1)
3+
#------------------------------------------------------------------------------
4+
5+
#------------------------------------------------------------------------------
6+
# Copyright 2017, Oracle and/or its affiliates. All rights reserved.
7+
#------------------------------------------------------------------------------
8+
9+
import cx_Oracle
10+
11+
con = cx_Oracle.connect("pythonhol", "welcome", "localhost/orclpdb")
12+
cur = con.cursor()
13+
14+
cur.prepare("select * from dept where deptno = :id order by deptno")
15+
16+
cur.execute(None, id = 20)
17+
res = cur.fetchall()
18+
print(res)
19+
20+
cur.execute(None, id = 10)
21+
res = cur.fetchall()
22+
print(res)

‎samples/tutorial/bind_sdo.py‎

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,47 @@
1+
#------------------------------------------------------------------------------
2+
# bind_sdo.py (Section 4.4)
3+
#------------------------------------------------------------------------------
4+
5+
#------------------------------------------------------------------------------
6+
# Copyright 2017, Oracle and/or its affiliates. All rights reserved.
7+
#------------------------------------------------------------------------------
8+
9+
import cx_Oracle
10+
con = cx_Oracle.connect("pythonhol", "welcome", "localhost/orclpdb")
11+
12+
cur = con.cursor()
13+
14+
# Create table
15+
cur.execute("""begin
16+
execute immediate 'drop table testgeometry';
17+
exception when others then
18+
if sqlcode <> -942 then
19+
raise;
20+
end if;
21+
end;""")
22+
cur.execute("""create table testgeometry (
23+
id number(9) not null,
24+
geometry MDSYS.SDO_GEOMETRY not null)""")
25+
26+
# Create and populate Oracle objects
27+
typeObj = con.gettype("MDSYS.SDO_GEOMETRY")
28+
elementInfoTypeObj = con.gettype("MDSYS.SDO_ELEM_INFO_ARRAY")
29+
ordinateTypeObj = con.gettype("MDSYS.SDO_ORDINATE_ARRAY")
30+
obj = typeObj.newobject()
31+
obj.SDO_GTYPE = 2003
32+
obj.SDO_ELEM_INFO = elementInfoTypeObj.newobject()
33+
obj.SDO_ELEM_INFO.extend([1, 1003, 3])
34+
obj.SDO_ORDINATES = ordinateTypeObj.newobject()
35+
obj.SDO_ORDINATES.extend([1, 1, 5, 7])
36+
print("Created object", obj)
37+
38+
# Add a new row
39+
print("Adding row to table...")
40+
cur.execute("insert into testgeometry values (1, :obj)", obj = obj)
41+
print("Row added!")
42+
43+
# Query the row
44+
print("Querying row just inserted...")
45+
cur.execute("select id, geometry from testgeometry");
46+
for row in cur:
47+
print(row)

‎samples/tutorial/clob.py‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
#------------------------------------------------------------------------------
2+
# clob.py (Section 7.1)
3+
#------------------------------------------------------------------------------
4+
5+
#------------------------------------------------------------------------------
6+
# Copyright 2017, Oracle and/or its affiliates. All rights reserved.
7+
#------------------------------------------------------------------------------
8+
9+
import cx_Oracle
10+
11+
con = cx_Oracle.connect("pythonhol", "welcome", "localhost/orclpdb")
12+
cur = con.cursor()
13+
14+
print("Inserting data...")
15+
cur.execute("truncate table testclobs")
16+
longString = ""
17+
for i in range(5):
18+
char = chr(ord('A') + i)
19+
longString += char * 250
20+
cur.execute("insert into testclobs values (:1, :2)",
21+
(i + 1, "String data " + longString + ' End of string'))
22+
con.commit()
23+
24+
print("Querying data...")
25+
cur.prepare("select * from testclobs where id = :id")
26+
cur.execute(None, {'id': 1})
27+
(id, clob) = cur.fetchone()
28+
print("CLOB length:", clob.size())
29+
clobdata = clob.read()
30+
print("CLOB data:", clobdata)

‎samples/tutorial/clob_string.py‎

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,35 @@
1+
#------------------------------------------------------------------------------
2+
# clob_string.py (Section 7.2)
3+
#------------------------------------------------------------------------------
4+
5+
#------------------------------------------------------------------------------
6+
# Copyright 2017, Oracle and/or its affiliates. All rights reserved.
7+
#------------------------------------------------------------------------------
8+
9+
import cx_Oracle
10+
11+
con = cx_Oracle.connect("pythonhol", "welcome", "localhost/orclpdb")
12+
cur = con.cursor()
13+
14+
print("Inserting data...")
15+
cur.execute("truncate table testclobs")
16+
longString = ""
17+
for i in range(5):
18+
char = chr(ord('A') + i)
19+
longString += char * 250
20+
cur.execute("insert into testclobs values (:1, :2)",
21+
(i + 1, "String data " + longString + ' End of string'))
22+
con.commit()
23+
24+
def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
25+
if defaultType == cx_Oracle.CLOB:
26+
return cursor.var(cx_Oracle.LONG_STRING, arraysize = cursor.arraysize)
27+
28+
con.outputtypehandler = OutputTypeHandler
29+
30+
print("Querying data...")
31+
cur.prepare("select * from testclobs where id = :id")
32+
cur.execute(None, {'id': 1})
33+
(id, clobdata) = cur.fetchone()
34+
print("CLOB length:", len(clobdata))
35+
print("CLOB data:", clobdata)

‎samples/tutorial/connect.py‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
#------------------------------------------------------------------------------
2+
# connect.py (Section 1.1 and 1.2)
3+
#------------------------------------------------------------------------------
4+
5+
#------------------------------------------------------------------------------
6+
# Copyright 2017, Oracle and/or its affiliates. All rights reserved.
7+
#------------------------------------------------------------------------------
8+
9+
import cx_Oracle
10+
con = cx_Oracle.connect("pythonhol", "welcome", "localhost/orclpdb")
11+
print("Database version:", con.version)

‎samples/tutorial/connect_drcp.py‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
#------------------------------------------------------------------------------
2+
# connect_drcp.py (Section 2.3 and 2.5)
3+
#------------------------------------------------------------------------------
4+
5+
#------------------------------------------------------------------------------
6+
# Copyright 2017, Oracle and/or its affiliates. All rights reserved.
7+
#------------------------------------------------------------------------------
8+
9+
import cx_Oracle
10+
con = cx_Oracle.connect("pythonhol", "welcome", "localhost/orclpdb:pooled",
11+
cclass="PYTHONHOL", purity=cx_Oracle.ATTR_PURITY_SELF)
12+
print("Database version:", con.version)

0 commit comments

Comments
 (0)