The Oracle Database and Developer Webinar Series With the support from the Oracle ACE Program, I am pleased to offer a series of webinars for Oracle User Groups throughout the world. The webinars come from more than two decades of active experience working as a database administrator and developer across most hardware platforms and versions of Oracle. They are designed to supplement knowledge and cover topics not typically found at seminars and training courses. Knowing how difficult it is to travel and attend conferences, or even attend a seminar of interest without it conflicting with other seminars running at the same time, can be a challenge. In today's busy world, it can be very difficult to get the time from work or justify the cost to attend conferences. The goal of these webinars is to help educate all those working with Oracle databases, multimedia and computing in general, and enable them to gain insight into new ideas into how to use and work with this technology. There is no cost involved for the Oracle User group. These webinars are designed to go for 45 minutes (unless specified otherwise). Where possible additional materials are made available. One month's notice is required and the start time of the webinar needs to be agreed ahead of time to ensure it is reasonable based on the time zone. The webinars are presented over the internet using software provided by the user group or institution (for example, goto meeting). The webinars can also be taped and shown ahead of time. They can be stored with the user group on their archive site. Questions and answers can be included in the webinar timeframe, or addressed after the webinar. Presentations can be adjusted to include user group branding and information. The base time zone is East Coast Australia and all seminars are in English. If practical, the webinar can be presented as a traditional presentation as a user group topic. The webinars are broken down into the following categories: A. University Lectures B. Oracle Database Tutorials C. Oracle PL/SQL Basics D. Oracle 12c Topics E. Oracle PL/SQL Tutorials F. Museum Lectures G Unstructured Data in Database H. Digital Imaging and Multimedia I. High School Series

The webinars have been updated to include Oracle 12c content. There is no restriction which webinars a user group can choose to use. They can present them in any sequence or order based on what they believe is best for their members or attendees. Marcelle Kratochvil

April 2014

The Oracle Database and Developer Webinar Series.

[email protected]

Page 1 of 33

Biography Marcelle is an internationally recognised expert in multimedia in an Oracle database. With the release of her book "Managing Unstructured Data in the Oracle Database", she detailed methodologies for loading, analysing, querying and selling all types of unstructured data. The book describes from first principles the base concepts of unstructured data providing a solid foundation for all technologies that are built on top of it. Over the last fourteen years Marcelle has been active in the core development of Piction (http://www.piction.com) and has been involved in the installation and management of this product in countries around the world. This product has won a number of technology awards. In addition to the development of Piction, Marcelle has also been involved in the running of Oracle DBA and Development training courses. She is an Oracle ACE Director and has extensive experience in Oracle Database Administration, including the performance of large databases, network management and application development. She has worked in government, private organisations, been a contractor, consultant, troubleshooter, trainer, manager and has worked on a large range of sites around the world. She has been a regular presenter at OpenWorld and first presented at OpenWorld in 1993 and has been presenting every year continuously since 2008. She has presented webinars to a number of user groups over the last five years and has also provided training webinars for Oracle. She has designed training courses and been heavily involved in training on database, developer, network, security and advanced feature topics. She has been an Oracle Beta tester since Oracle 8i and has done a number of internal technical presentations to Oracle developers in the US. She was Oracle PL/SQL developer of the year in 2004, has won awards for best paper and has even written a database column under a pseudonym. Based in Canberra Australia, Marcelle travels frequently overseas to present and do training courses. With over 15 years experience in the multimedia field and over 25 years working on Oracle databases as both a DBA and Developer, Marcelle also manages the Unstructured Data SIG: https://sites.google.com/site/ommuds/ and maintains a blog with papers and other useful information at: http://eternal-donut.blogspot.com.au/ Her book is available on Amazon or for review at: http://www.packtpub.com/managing-multimedia-and-unstructured-data-in-oracledatabase/book The Oracle ACE program can be referenced at: http://www.oracle.com/technetwork/community/oracle-ace/index.html

The Oracle Database and Developer Webinar Series.

Page 2 of 33

Table of Contents A. University Lectures..........................................................................................................................5 1. An introduction to fuzzy logic in the database............................................................................ 5 2. A review of real world databases................................................................................................. 5 3. Understanding the basics of scalability....................................................................................... 6 4. A discussion on the different roles in an IT organisation.............................................................6 5. Looking at the basics of screen design and database integration................................................6 B. Oracle Database Tutorials................................................................................................................ 8 1. Breaking the new 12c features.....................................................................................................8 2. Transparent scalability................................................................................................................. 8 3.Tuning for hard core DBAs ......................................................................................................... 8 4. A review of hardware/storage architectures and virtualizations.................................................. 9 5. Rosetta stone for Windows and Unix DBAs............................................................................... 9 6. Backups......................................................................................................................................10 7. Networking: Internal firewalls, TNS, Listeners, embedded gateway, apache and IIS..............10 8. Configuring the ODBC Gateway...............................................................................................10 9. Oracle XE...................................................................................................................................11 10. Security – SSO and LDAP for HTTP...................................................................................... 11 C. Oracle PL/SQL Basics................................................................................................................... 12 1. Introduction to PL/SQL for Java, C and Object Oriented Programmers................................... 12 2. Packages, procedures, functions and types................................................................................ 12 3. Efficient PL/SQL coding practices............................................................................................ 12 4. Dynamic SQL............................................................................................................................ 13 5. FOR Loop.................................................................................................................................. 13 6. Recursion and Scope..................................................................................................................13 7. Hierarchical Statements............................................................................................................. 13 8. Prevention techniques (infinite loops)....................................................................................... 14 9. Error Handling........................................................................................................................... 14 10. Conditional Compilation (Preprocessor Control).................................................................... 14 D. Oracle 12c Topics.......................................................................................................................... 15 1. Database Scalability and Oracle 12c......................................................................................... 15 2. Breaking the 12c Database........................................................................................................ 15 3. Managing Oracle 12c on Windows............................................................................................15 4. Using the Oracle 12c new PL/SQL features.............................................................................. 15 5. Building your own web services using PL/SQL in Oracle 12c. A how to guide.......................16 E. Oracle PL/SQL Tutorials................................................................................................................17 1. 12c SQL Features – which ones work....................................................................................... 17 2. DBA Basics for developers........................................................................................................17 3. Oracle XE for developers (series)..............................................................................................17 4. HTTP Web development............................................................................................................18 5. How build a webservice and parse it in XML .......................................................................... 18 6. Creating object types and methods............................................................................................ 18 7. XML, Semantics, Text, Spatial and Data mining...................................................................... 19 8. Using internet PL/SQL supplied packages................................................................................ 19 9. SMTP and mail servers.............................................................................................................. 19 10. Using the sys.anydata data type............................................................................................... 20 F. Museum Lectures........................................................................................................................... 21 1. Circa data type........................................................................................................................... 21 2. Data forensics I – Finding the data............................................................................................ 21

The Oracle Database and Developer Webinar Series.

Page 3 of 33

3. Data forensics II – Accession Numbers..................................................................................... 21 4. Data forensics III – Cleansing the data...................................................................................... 22 5. Data forensics IV– Mining the data........................................................................................... 22 6. Multilingual issues..................................................................................................................... 22 7. Diacritics and character sets...................................................................................................... 23 8. Working with Social Networks.................................................................................................. 23 9. Data protection and security...................................................................................................... 23 10. Digital image processing......................................................................................................... 24 G. Unstructured Data in the Database................................................................................................ 25 1. Oracle Multimedia Primer - understanding the basics ..............................................................25 2. Oracle Multimedia Warehouses - An introduction .................................................................. 25 3. Integrating the Oracle database with a Social Network.............................................................26 4. Storing and Tuning Unstructured data and multimedia in Oracle.............................................26 5. Using PHP, Perl, Visual Basic, Ruby and Python to use Multimedia inside Oracle.................26 6. Connecting Oracle to other databases using ODBC gateway - tips and techniques..................27 7. Unstructured Data - database or filesystem? The debate continues..........................................27 8. Tips and techniques for searching against a Multimedia Warehouse........................................27 9. How to configure and tune a multi-terabyte database containing unstructured data ................28 10. Understanding how indexing on unstructured data works.......................................................28 H. Digital Imaging and Multimedia................................................................................................... 29 1. How to build and run efficient searches against multimedia using metadata............................29 2. How to design an efficient Multimedia Image Warehouse........................................................29 3. How to load multimedia into the Oracle database - a primer.................................................... 29 4. The case for storing multimedia in the database........................................................................29 5. How to do object querying of multimedia objects stored in the database.................................30 6. How your business can sell digital images store in the Oracle database................................... 30 7. How a business can leverage their digital assets....................................................................... 30 8. How a DBA can overcome their fear of working with multimedia...........................................30 9. How to secure your digital assets that are stored in the Oracle database..................................31 10. Understanding the basics of what a digital image is................................................................31 I. High School Series.......................................................................................................................... 32 1. A career in computing................................................................................................................ 32 2. Gaming.......................................................................................................................................32 3. An introduction to computer hardware...................................................................................... 32 4. An introduction to computer software....................................................................................... 32 5. Excite the imagination............................................................................................................... 33

The Oracle Database and Developer Webinar Series.

Page 4 of 33

A. University Lectures These seminars are designed for Universities with a lecture style format. They cover abstract and non traditional database concepts. They are also available for User Groups that wish to have presentations that go beyond the standard topics and cover new and exciting fields.

1. An introduction to fuzzy logic in the database All data in the real world is imprecise to a degree, yet we try and constrain it to fixed mathematical models and wonder why so much time is needed to clean it and understand it. Fuzzy logic concepts attempt to define and encapsulate all data within degrees. Though no databases yet exist that can correctly handle the data, object/relational databases can be adapted to work with it. This seminar covers the basics of fuzzy logic and how by changing the way you think and deal with data you can better handle and work with it. . Detailing how all real world data is fuzzy and describing the myth of data accuracy . Understanding the basics of set theory . Simulating degrees using SQL . Covering statistical analysis . Looking at other languages like Lisp and Prolog . Dealing with data accuracy and the data integrity models (eventual consistency) . How to deal with degrees (binomials and polynomials)

2. A review of real world databases When production ready relational databases first appeared in the 1980s they were greeted with scepticism due to their poor performance. The use of them gained in popularity because they solved a key issue regarding adhoc data access which was the Achilles heel of existing databases. Switch to 2013 and the use of non relational databases has grown in popularity due to the need to deal with large volumes of data, but are these databases running into the same flexibility issues? This seminar covers all the different types of databases that have appeared in the market since the 1980s and whether the industry has learnt from the mistakes of the past and is correctly dealing with the current databases found in the marketplace. . Discussing relational and non relational database models . History of databases and how its repeating itself to achieve scalability . Database types covered include: Relational; NoSQL and BigData; Spatial; XML; Semantic; Warehouse; OLTP; Object . Covering the mentality that newer must be better with computing technology

The Oracle Database and Developer Webinar Series.

Page 5 of 33

3. Understanding the basics of scalability Any experienced database administrator will know what scalability is and that most developers have no basic concept of it. They constantly battle the business needs, developer needs and user requirements to ensure systems built perform well. What most people do not realise is that in recent years the concept of scalability has changed and now encompasses new dimensions that must be addressed. This seminar covers the new concept of transparent scalability and reviews the break points found in application systems and how they have to be dealt with to ensure application scalability. . . . . .

Introducing the idea of transparent scalability Discussing project/team scalability Look at hardware architectures and how they scale Discussing scalability breakpoints and the developer PC/Gaming mentality Reviewing database scalability

4. A discussion on the different roles in an IT organisation This seminar covers the different positions within an IT organisation and how they interact together. The text book view for how an IT organisation works doesn't always match what is found in the real world and how by using real life experience from working in a large number of government and private organisations around the world, one can begin to analyse and understand how to successfully work with and handle a real world business. . Covering a real world perspective on the different positions in a variety of IT organisations and what works and does not work with them. . Reviewing cultural barriers, communication and team dynamics . Also covered are the issues found today of outsourcing, tele-commuting, software development and business management . Dealing with the different personalities and social issues of today in an IT organisation

5. Looking at the basics of screen design and database integration Gaming design is very different to application screen design, yet most education is focused on how to build a better gaming interface. For businesses that use the internet, application screen design is crucial to the running of their business and enabling the general public and staff to do their business quickly, efficiently and accurately. This seminar covers the real world issues of screen design for businesses using the internet and how the computer, human interaction is often neglected and how businesses like Microsoft and Apple struggle at times to build user friendly interfaces that cater to all the types of users. . . . .

Designing for scalability Understanding network issues and screen design The tiers of users Three tier architectures and how it impacts screen design

The Oracle Database and Developer Webinar Series.

Page 6 of 33

. Using web-services . HTTP stateless transactions and database integration . Looking at the basic issues of computer human integration (CHI)

The Oracle Database and Developer Webinar Series.

Page 7 of 33

B. Oracle Database Tutorials This series is designed for intermediate to advanced Oracle database administrators. Each webinar takes a traditional and approaches it from a different direction given useful insights and ideas for the database administrator tired from reading and learning traditional material.

1. Breaking the new 12c features It's easy to talk about the new 12c features that come bundled with the Oracle database. But do they actually work? This seminar will go through the main features and try to break them and then show workarounds for those that do break. It does not cover containers, but does cover all the other DBA 12c related features. Some of which DBAs might not have heard of or covered. Target Audience: Any Oracle database administrator or developer.

2. Transparent scalability What really is scalability? A goal since the database first appeared was to come up with some metric for determining whether the database could handle large numbers of users and process large volumes of data. From TPC-C benchmarks to quoting quoting core numbers and memory size, the competition has ensured that the waters have become murky and confusing. Determining whether the database and the underlying application can in fact scale is a difficult task. Also, a key point which is missed is the focus is on scaling upwards. As Apple has shown with the iPhone, being able to scale downwards to small devices is also of great importance. This seminar covers a new concept called transparent scalability which is more in tune with how the real world works. Discussed will be points on how to determine effort in scaling, tuning breakpoints and understanding the effort from the outset and why not all the databases are the same. Target Audience: Any Oracle database administrator, developer or manager.

3.Tuning for hard core DBAs Tools like Enterprise Manager or Toad are great for helping administrators for managing multiple databases. The challenge is can they manage, tune, diagnose and solve performance issues without these tools? The issue is that these tools are meant to supplement a database administrator, not replace them with less experienced ones. A hard core DBA is one who can tune and manage a database from a command line

The Oracle Database and Developer Webinar Series.

Page 8 of 33

prompt. This seminar will go through some key features available to the database administrator on all Unix platforms and on Windows. How they can use these tools to diagnose problems and then from a SQL*Plus command line hone in on the problem and resolve the issue. From tuning I/O, memory and disk to the now very important network tuning. Being able to query against key data dictionary tables is a very useful skill for the database administrator. Target Audience: For the advanced database administrator. This is a fast paced seminar and will cover a large amount of information in a very short time. Where possible examples will be used, but time constraints limit this seminar to being a mind dump of useful techniques.

4. A review of hardware/storage architectures and virtualizations The Oracle database runs on hardware. Yet virtualizations can now sneak in between creating a transparent layer making the whole environment more complex to tune and manage. Even though the are new chipsets and hardware architectures most are still based on concepts well known twenty years ago. From NUMA to clusters to MPPs and now Oracle Exadata hardware. Yet what do they all mean, how well do they truly work with Oracle and which ones are redundant, doomed to fail or likely to succeed? This seminar will cover hardware architectures with an Oracle database perspective in mind. Covering scalability and potential holes and bottlenecks. The goal is to detail all the architectures available and provide a roadmap for ones to come. Target Audience: Experienced administrators and developers

5. Rosetta stone for Windows and Unix DBAs Which platform is better? Windows 2012, Linux or Solaris? Such question inspire a vociferous response equal to the debates of PC versus Mac and Star Trek versus Star Wars debates. Of course any serious and experienced administrator knows VMS was the best operating system. But that is besides the point. With age one realises loyalty to an operating system is a futile exercise and ones energies can be better directed to more important tasks like exposing holes in the performance aspects of developer queries. Given that Windows and Unix are the dominant environments for Oracle, the question has to be asked, what are the core differences between the two and how does a Unix administrator even begin to understand the intricacies of Windows and vice versa? This seminar will provide that translation and show various methods available to make the move easily between both environments. Covered are setup and configuration methods common across both platforms as well as the basics in shell commands. Target Audience: Experienced database administrators

The Oracle Database and Developer Webinar Series.

Page 9 of 33

6. Backups Backups are the bread and butter of the database administrator. The use of rman as a backup tool is well known and understood. The rules are changing, the environment is changing. With block level recovery on file systems and sites now deploying in virtualizations with snapshot capability, these tools are being surreptitiously used as backups by inexperienced administrators. So the question has to be asked. Are these tools safe to use, better to use and do they make rman redundant? Are modern Oracle database administrator living in a self imposed bubble using Oracle tools for backups when there is a possibility that these ones are better, easier and more reliable? Or are these administrator doing the right thing by sticking to Oracle backups? This seminar will tackle these questions and provide answers that need to be addressed. Target Audience: Intermediate to experienced database administrators

7. Networking: Internal firewalls, TNS, Listeners, embedded gateway, apache and IIS With the move for a lot of organisation to web enable their database for private or private access, the role of the database administrator has grown into one in which they now need to have a good network understanding. There is also a perception that all web based environments are three-tier. Where in reality, in a lot of cases the most efficient methods are to take out the middle / application tier and move it back into the database. This option covers some of the common web servers and how to use them to integrate with an Oracle database. Target Audience: Experienced database administrators and developers

8. Configuring the ODBC Gateway The Oracle database comes bundled with an ODBC gateway that enabled read/write access to any datasource. This includes MySQL, SQLServer, Postgres and a huge range of ODBC compliant source. You can even access spreadsheets and flat files using ODBC. Most administrators struggle to understand the use and implications of having such a valuable connection in the database. It's also fair to say most have no idea how to configure it and then tune and manage it. This seminar will cover from start to finish how to configure the gateway and then all the little issues one needs to be aware when using it. Target Audience: For any database administrator and developer.

The Oracle Database and Developer Webinar Series.

Page 10 of 33

9. Oracle XE The Oracle 11.2.0.2 comes in an XE version. This is a light edition of Oracle with some limitations covering storage and memory. XE is free and even though it has a built in storage limitation, it can over nearly all functions the developer needs that are found in the enterprise edition. This seminar covers how to make effective use of the XE database, integrate it with other databases to increase storage and save on costs by deploying it in an organisation and moving to a decentralised model. Target Audience: For any database administrator and developer.

10. Security – SSO and LDAP for HTTP The Oracle database has built into it a number of tools that enable it to integrate with Single Sign On systems. But how do you use these for Web Development? This seminar will go through how to configure Apache to enable SSO for a stateless web based application and then use LDAP to retrieve client information. Target Audience: For the advanced database administrator, network administrator and developer.

The Oracle Database and Developer Webinar Series.

Page 11 of 33

C. Oracle PL/SQL Basics This series of webinars is designed for developers who are new to PL/SQL or ones who are just coming up to speed with it. PL/SQL is a fully functional language that runs inside the Oracle database. It can be compiled and is the fastest language available to access the Oracle database. It is also a language that is easy to use and is just not limited to database access. It can be used for web development and even image manipulation. The Oracle database comes bundled with a huge range of support packages for the language enabling both the database administrator and developr to perform highly complex and efficient tasks. By it's nature and it's tight integration with the Oracle database, PL/SQL is also a language that actively encourages efficient and safe programming. It's object/relational structure enables it to be easily integrated with other language such a PHP, Python and C. PL/SQL is an underrated language which has been overshadowed by other languages such as Java and PHP. The effectiveness, scalability and efficiency of the language results in a strong and loyal following by those who use it for serious database work.

1. Introduction to PL/SQL for Java, C and Object Oriented Programmers This seminar will go through a number of PL/SQL language constructs and their C/Java equivalents. It will also go through PL/SQL best practices and common mistakes made when programming and having a C background. Also covered will be developing object routines, webservices and developing object methods routines. Target Audience: Experienced Developers who wish to program some or all of their code in PL/SQL.

2. Packages, procedures, functions and types This seminar covers a number of basic concepts including: • • • • • •

What are packages and when to use them Package dependency Crucial data dictionary views Manual debugging methods Security and grants Creating and using types, arrays, record types and object arrays

Target Audience: Novice to intermediate developers

3. Efficient PL/SQL coding practices This seminar covers a variety of core PL/SQL development concepts: • •

Temporary tables (and tuning in queries) Optimizer hints (when and when not to use)

The Oracle Database and Developer Webinar Series.

Page 12 of 33

• • •

Writing PL/SQL code quickly and with less errors Developing code that can be maintained Modular code, goto's, object code and branching constructs

Target Audience: Intermediate to advanced developers

4. Dynamic SQL There are a number of methods for creating dynamic SQL. A number are security prone or inefficient. This seminar will covers a variety of dynamic PL/SQL methods: • • •

Execute immediate Dynamic cursors dbms_sql package

Also covered is how to use these methods at the right time and how to create efficient dynamic PL/SQL that works well within Oracle. This includes dynamic bind variables and retrieving execution info before statements are run. As well as how to retrieve execution and plan information as a statement runs. Target Audience: Experienced developers

5. FOR Loop PL/SQL provides a number of loop methods. This seminar coves the most common ones and explains why using the FOR loop is will be the one you use the most. Target Audience: Novice developers

6. Recursion and Scope PL/SQL allows for the use of recursion, but is it more efficient than an iterative loop? SQL uses scope constructs in sub queries and PL/SQL uses them extensively throughout the language with the use of variables and program declarations. But do developers actually understand what scope is and how it can be used for good and bad things? At times recursion and scope work together and yet at times this efficiency can be masked by confusion caused when maintaining the code. This seminar covers what scope is, what recursion entails and when to use and when not to use it when programming. Target Audience: Intermediate to advanced developers

7. Hierarchical Statements Oracle supported hierarchical or ancestor statements since as early as Oracle v4. Yet a

The Oracle Database and Developer Webinar Series.

Page 13 of 33

lot of developers are loathe to use them because of the difficulty in working with and understanding them. Yet hierarchical statements can solve a number of relational query constructs and when built for Oracle, can run incredibly fast. This seminar will cover the basics of hierarchical statements, going through simple to advanced queries using them. Also covered are common mistakes made using them and how to tune them to run very fast. Target Audience: Novice to intermediate developers

8. Prevention techniques (infinite loops) A PL/SQL program that encounters an infinite loop can be one of the hardest tasks to diagnose and prevent. When encountered it can take out a database and be difficult to monitor and stop from running. It can also cause major issues with data management. This seminar will cover common programming scenarios where infinite loops are encountered, coding prevention techniques and how to detect and stop them when they are encountered. Target Audience: Novice to intermediate developers

9. Error Handling A PL/SQL program when running can encounter a variety of errors ranging from storage issues, logical errors and physical ones. The PL/SQL has an exception handler clause that is a catch all error trap. But what to do when an error is encountered? This seminar covers good and bad errors, exception handlers and building programs to automatically trap, record and report on all errors encountered. Target Audience: Novice developers

10. Conditional Compilation (Preprocessor Control) A powerful feature introduced to PL/SQL is the ability to embed conditional compilations statements into the code. For developers who have to work in a complex environment with different Oracle versions, this feature enables the one code base to used in a variety of different environments. For PL/SQL developers building code for different customers, the preprocessor enables the one code to be used but have different features enabled or disabled. This seminar covers some of the basic conditional compilation statements and how to incorporate them into your application to create an environment that is adaptable. Target Audience: Novice to intermediate developers

The Oracle Database and Developer Webinar Series.

Page 14 of 33

D. Oracle 12c Topics This series of webinars covers topics that involve using Oracle 12c. Each webinar uses Oracle 12c as a base with the core focus being on using Oracle 12c and discovering its new capabilities.

1. Database Scalability and Oracle 12c How well does the Oracle 12c database scale and can it handle typical sites with typical workloads? This session will cover what scalability truly means and how by using Oracle 12c as a base, test and show if it indeed does scale in key areas. What scalability is will never look the same again as a lot of old notions and myths will be discussed. This session covers concepts on scalability that have not been discussed before. Highlighting what scalability is and which points Oracle 12c meets and does not meet. The attendee will get a new perspective on what scalability really means and which database features help achieve it.

Target Audience: Intermediate to advanced developers

2. Breaking the 12c Database With all the new 12c features being discussed, one needs to verify that they actually do work as claimed. Using the first release of 12c as a base, this session will go through a number of 12c key features in the database and see what it takes to break them.

Target Audience: Advanced developers

3. Managing Oracle 12c on Windows Is the Windows platform as good as or better to use for running Oracle 12c on? Those brought up on Unix will say no, but each year the technology and the rules change. So maybe what was once thought is no longer valid. This session goes through the steps to manage Oracle 12c on Windows and covers key differences between Oracle Windows and Unix.

Target Audience: Advanced developers

4. Using the Oracle 12c new PL/SQL features Oracle 12c introduces a number of new SQL features and some PL/SQL ones. This session goes through those features and shows how they can be used in real world applications.

The Oracle Database and Developer Webinar Series.

Page 15 of 33

Target Audience: Novice to advanced developers

5. Building your own web services using PL/SQL in Oracle 12c. A how to guide. There are a lot of tools that bundle in web services into their product, but do not give the developer any control over performance and tuning. Sometimes the developer just wants to build and control to a fine grain their own web services. This session goes through how using PL/SQL in 12c you can build your web services, send requests between databases and even use SSL, HTTP and push multimedia objects between the client and server.

Target Audience: Advanced developers

The Oracle Database and Developer Webinar Series.

Page 16 of 33

E. Oracle PL/SQL Tutorials This series of webinars covers more advanced topics or topics not traditionally covered or experienced in PL/SQL courses.

1. 12c SQL Features – which ones work Oracle 12c introduced a number of new SQL features which are useful. But do these features work in PL/SQL? This seminar covers a number of new 12c features in SQL and PL/SQL and road tests them to the point of breaking them.

Target Audience: Novice to advanced developers

2. DBA Basics for developers Can a developer become a good database administrator? Do they actually know what is involved in managing an Oracle database? This seminar covers the basics of database administrator functions, database architecture and common commands and features available to the developer to perform basic administrative functions. Target Audience: Novice to advanced developers

3. Oracle XE for developers (series) Oracle provides a free version of the database called XE. Though it has storage and memory limitations, the database itself offers most features a developer would be expected to use. Yet a lot of developers are not aware of the existence of Oracle XE and that it can be used as a serious developmental and production platform. This seminar covers: • • • • • • •

Knowing which XE features are available and which ones aren't. Database setup and Listener configuration Embedded gateway configuration Quick setup of database parameters ODBC hookup Integration with 3rd Party tools Using PHP, Perl and other languages to integrate with the database

Target Audience: Novice to advanced developers

The Oracle Database and Developer Webinar Series.

Page 17 of 33

4. HTTP Web development In 1995 Oracle introduced a web based interface to the database. And Mod PL/SQL was born. It provided a true, dynamic interface to the database. Over time it was enhanced to support blob's and integrated with Apache. With the release of the embedded gateway, the Oracle listener could act as a HTTP server just for Mod PL/SQL. Using the power of the database and the Mod PL/SQL framework, developers have the ability to create powerful web applications, running inside the database. This underrated feature forms the base for Apex and is proven to be scalable and with the Oracle 11 ability to compile, provides a high speed web interface to the database. In comparison to other web languages such as PHP, Mod PL/SQL offers a huge range of support functions and combined with its efficient database access, can easily shown to easier if not better to program in compared to these other languages. With the release of the dbms_xa package, Mod PL/SQL expanded from stateless to being able to perform stateful calls, enabling transactions to span web pages. This seminar will go through the basics of creating web pages using Mod PL/SQL and how to quickly perform common web tasks. From building Forms, dynamic variable lists, securing the site and loading binary images. Target Audience: Novice to advanced developers

5. How build a webservice and parse it in XML Continuing from the HTTP web development seminar, this seminar focuses on how a developer can build in Mod PL/SQL their own webservice, as well as how to process a webservice being sent to the database. This seminar will also cover: • dealing with JSON • unzipping content • parsing a URL using REST format • how to send a HTTP Post call • how to send and retrieve SSL calls to banks Target Audience: Intermediate to advanced developers.

6. Creating object types and methods Developers can be stubbornly resistant to adopting and using the object features available in PL/SQL in the Oracle database. Being comfortable with relational it becomes very easy to dismiss objects as an anomaly and something to be avoided because it breaks the relational model. Yet Oracle uses objects extensively within it's own database and internal applications, because simply - using them works. And

The Oracle Database and Developer Webinar Series.

Page 18 of 33

using them works very well. In some cases they can dramatically improve performance and provide solutions to data warehousing and mining that the relational model struggles to deal with. It's a case of using the right tool for the job. So this seminar will help developers overcome the hesitation and uncertainty found when using objects by showing through examples how to create a wide variety of object structures and build methods. This seminar is designed for the those developers willing to take a bold step forward and embrace new concepts and add a whole new series of tools to their programming belt.

Target Audience: Advanced developers

7. XML, Semantics, Text, Spatial and Data mining The Oracle database comes bundled with a variety of built in features. Some of these features are even available in the XE database version. Yet it can be said that a lot of developers are not aware of their existence or even comfortable using them. Yet these features offer capabilities that can be used to resolve a variety of business issues. This seminar will go through these five products and show by example how quick and easy they are to setup and use. Target Audience: Intermediate to advanced developers

8. Using internet PL/SQL supplied packages The Oracle database comes supplied with over 250 supplied packages. Each providing a variety of useful features and capabilities. Trying to grasp what they can all do is a daunting task. This seminar will focus on some of the key ones that developers might not be familiar with and are useful to them. This includes new ones found in Oracle 12c. Target Audience: Novice to advanced developers

9. SMTP and mail servers The utl_mail package that comes supplied with Oracle is limiting for sending emails. Using the utl_smtp and utl_tcp developers can write programs that send emails with a variety of attachments and using different encoding methods. This seminar will go through how to create advanced emails including: • Embedding images in an email • Creating a bcc email • Authenticating against an SMTP server • Building email servers that can receive emails

The Oracle Database and Developer Webinar Series.

Page 19 of 33

•

Building a junk email filter

Target Audience: Advanced developers

10. Using the sys.anydata data type A catch-all datatype that can support any value entered and knows that variable's context, is an anydata type. Not well known and used, this type offers solutions to intractable problems for dealing with complex data. This seminar will go through the basics of this universal datatype then how to use it in dynamic SQL and webservice calls. Target Audience: Advanced developers

The Oracle Database and Developer Webinar Series.

Page 20 of 33

F. Museum Lectures This series of seminars is designed for those in the museum, gallery and digital imaging world. This includes those business or government sites that utilise data warehousing and are branching out into and investigating the issues related when dealing with multimedia. These seminars focus more on metadata and their structures, dealing with them and trying to make sense of all the issues involved when dealing with large amounts of digital object data.

1. Circa data type The circa data type is a variation on the date type. With circa the date is approximate or fuzzy. It can include a range of dates or a period of time. Searching against a circa date type is context sensitive as the further back in time one searches the more variable that range can become. This seminar describes this datatype, how it works and puts forward an official standard for describing it. The seminar then also covers fuzzy search techniques and how complex searches can be performed against this date data type.

2. Data forensics I – Finding the data This is a set of 4 seminars focusing on the field of data forensics for museums. This involves analyzing, identifying and matching a wide variety of relational, structured, unstructured data, along with file systems and digital images and try and found how they all relate to each other, look for patterns in the data and mine the information for useful knowledge. The first seminar focuses on where to look for this information. Data can be found in a number of areas and understanding how to get to it is important. This data can be found in documents, embedded in images, hidden in values in other data values, encoded, encrypted or held securely in databases or locked in applications that may or may not allow the data to be shared. This seminar also covers the basics of data relationships. Covering keys that link the data, uniquely identifying objects, fuzzy matching and the notion of repeating groups. Finally the seminar covers methods for linking the data. From using extraction routines that put data in csv or xml formats, passing data between sites using webservices and to using database links to enable databases to communicate with each other. The seminar discusses some of the challenges these methods require including security and the issues of handling very large volumes of data.

3. Data forensics II – Accession Numbers “In libraries and museums and other archives, an accession number or catalogue number is a unique, usually sequential, number given to each new item acquired, as it is catalogued.” (Wikipedia) This seminar covers how a simple concept of an accession number and its

The Oracle Database and Developer Webinar Series.

Page 21 of 33

implementation can vary dramatically between organisations and how its definition has also changed and grown. When looking at the accession number now we see a data field that can contain within it a variety of information. We can see how its usage has grown to give curators a method for encoding useful information in it that can reveal useful information about the item the accession number is referring to. Using experiences gained from exposure to a large number of cultural institutions this seminar introduces a standard for accession number usage and searching, and how by using this standard, curators can perform more complex searches against this value. Also introduced is a universal translation routine that can take an existing accession number and convert it to the new format.

4. Data forensics III – Cleansing the data This third data forensics seminar focuses on methods for cleaning data and making it palatable for computer systems to process it. It looks at how to merge the data, load it into a database and store it in ways that make it easier to access. As data can be missing on objects or duplicated, the notion of fuzzy matching is introduced to enable data elements to be linked together. The seminar looks in great detail at the fuzzy name matching scenario where one attempts to find duplicates of person information stored. This is a common issue when digital images containing identified people are stored for historical purposes.

5. Data forensics IV– Mining the data The final seminar in the data forensics series covers the challenges of searching against all the data loaded in. From course grain searching to fine grain searching. It involves how to efficiently search for key nuggets of information and introduces some of the well known data mining concepts. Using museum data as examples, the seminar covers search techniques that help find anomalies in the data and help the curator better understand strange or inconsistent answers. The seminar uses simple set theory to show how data searching works. Of course, the biggest issue one has when searching is, now that I have an answer to my query, what was my original question?

6. Multilingual issues The ability to store, search on and manage metadata in different languages is now becoming an important capability as museums open their virtual doors to the general public and attempt to get more customers in from different world regions. For some museums in countries there is a legal requirement to support multiple languages whereas others the changing demographics in their region are opening them up to a need to support different languages in their systems. Two additional languages starting to be included are French and Spanish. There is though an emerging need to support languages native to the residents of a region.

The Oracle Database and Developer Webinar Series.

Page 22 of 33

This seminar covers the issues associated with translation and discusses the topic as to whether computer systems can automate this translation. The seminar also covers methods for metadata storage of mixed languages and search challenges that curators may not be aware of.

7. Diacritics and character sets A character set is a well defined set of symbols. Different languages can make use of different characters and some character sets have attempted to unify them all. One well known character set is UTF-8 used for storing data in an XML format. A diacritic is a special symbol embedded above or below a character and is used to indicate a change in pronunciation. A number of languages make use of diacritics and their usage is making inroads into the english language. For museums and galleries there is a requirement to be able to display, store and search on these characters properly. This seminar focuses on the issues involved in storing, managing and handling a diacritic character and how search engines need to efficiently deal with these values in a consistent manner. Discussed are common mistakes make when working with them and how one can ensure they are not transposed when migrated between data systems.

8. Working with Social Networks As museums add a virtual component to their sites the need is appearing to integrate in with the popular social networks. These include Facebook, Flickr and YouTube. With a large number of social networks in the market and their long term viability questionable, it becomes a balancing act to determine whether resources should be spent integrating with them tightly or loosely. One key advantage is to leverage of these networks and use crowdsourcing to identify and tag hundreds of thousands of museum objects, as well as adding intelligence to the objects. From identifying insects and birds in audio tracks to manually indexing, translating and tagging videos, the use of these social networks can enable the fast tracking of these museum items. This seminar discusses these issues of social network integration and proposes a number of standards to ensure consistency in crowdsource tagging as well as improve the accuracy of this process.

9. Data protection and security When museum decides to open up their collections to the general public by making them available on the internet, some key issues regarding security and intellectual knowledge protection are raised. This seminar goes through some of the methods available for safe guarding the data and the digital objects. Also covered is a simplified description of some of the security safe guard and hacks, with definitions of the terms used by computer people.

The Oracle Database and Developer Webinar Series.

Page 23 of 33

Also covered is one of the greatest security holes in organisation and how to deal with it.

10. Digital image processing This seminar goes through all the issues a museum needs to know about when it comes to processing digital images, video, audio and documents. Also covered is the new field of three dimensional objects. There are a large number of dependencies that most people are not aware of when handling digitized objects. Some of these issues include: • Ensuring efficiency in storage (compression and codecs) • Compatibility with web browsers and ensuring correct color balance on monitors, • Effort to transcode and create derivatives • Storage management This seminar discusses these issues and provides a thorough introduction to the initial requirements of digital image processing.

The Oracle Database and Developer Webinar Series.

Page 24 of 33

G. Unstructured Data in the Database This series of seminars takes the attendee through a large number of the concepts when it comes to dealing with unstructured data. This includes ways of dealing with digital images, video, audio and documents. This is a new field that is not well understood and these seminars give the attendee new insight into this area and provides them with a greater understanding of a large number of the issues they will face.

1. Oracle Multimedia Primer - understanding the basics (1.5hr) The Oracle database is well known for dealing with relational data. In the last five years there has been a massive increase in multimedia usage and the pain of managing it is only now starting to be understood. What is not well known by many is that the Oracle database is equipped to deal with multimedia and provides a great platform for these digital objects. This seminar will cover the basics on multimedia. It will cover terminology, architectures and methods for best working in this new and exciting environment. . Provide an introduction to Oracle Multimedia for anyone not familiar with it . To highlight the capabilities of Oracle 11 and how it can be used to manage unstructured data . To give an overview of all the potential business applications Oracle Multimedia can be used in Target Audience: Managers, Data/Storage Architects, Database Administrators, Developers

2. Oracle Multimedia Warehouses - An introduction (1.5 hr) With the large increase in usage of multimedia in the last five years, the need has grown to handle and process, analyze and understand multimedia digital objects. The traditional data warehouse deals with relational data and is ill-equipped to handle the intricacies of multimedia. This seminar will provide an introduction to the basic concepts behind a multimedia warehouse, how to load objects, search against and deliver them. . To highlight the differences between a multimedia warehouse and the traditional data warehouse . To detail the different multimedia architectures and how businesses can use them . To describe loading and delivery methods of digital objects Target Audience: Managers, Data/Storage Architects, Database Administrators, Developers

The Oracle Database and Developer Webinar Series.

Page 25 of 33

3. Integrating the Oracle database with a Social Network Facebook, Flickr, YouTube, Google Maps. There are a large number of social network sites, each with their APIs for sharing data with them. Most developers do not realise that the Oracle database has the base tools for communicating with these sites enabling all manner of information including multimedia to be passed back and forth between them. This technical seminar will go through the methods in PL/SQL for connecting to, then sending and retrieving all types of data between these sites. Including: . how to build web services in PL/SQL to access popular social network sites . how multimedia can be integrated with social network sites . describing the security methods needed to integrate with social network sites Target Audience: Developers

4. Storing and Tuning Unstructured data and multimedia in Oracle Database administrators need to learn new skills and techniques when the decision is made in their organization to let the Oracle database manage their unstructured data. The DBA will have to deal with scalability challenges. One row in a table can become larger than a whole database. This seminar will cover the techniques a DBA needs to manage the large volume of data in a standard Oracle database. . Provide an introduction to tuning, storage management and virtualizations. . Detail the new skills and challenges DBAs need when working with unstructured data . To show how most DBAs lack basic understanding and tuning skills for managing unstructured data Target Audience: Data/Storage Architects, Database Administrators, Developers

5. Using PHP, Perl, Visual Basic, Ruby and Python to use Multimedia inside Oracle These five programming languages are just some of the most popular ones in use at the moment in the marketplace. This seminar will detail how you can use them to access and retrieve multimedia from Oracle. The seminar will cover programming techniques and methods to achieve faster development against Oracle. . To detail how to easily integrate multimedia with these programming languages . To show best methods for loading and retrieving any digital object

The Oracle Database and Developer Webinar Series.

Page 26 of 33

. To discuss optimal methods for integration with objects in Oracle Target Audience: Developers

6. Connecting Oracle to other databases using ODBC gateway - tips and techniques MySQL, Postgres and SQLServer are three popular databases that organizations are using in additional to Oracle. Gone are the days of one organization using just the one database. This seminar will detail how to configure an Oracle database (including Oracle XE) to use the built in ODBC gateway to share data with these databases. Also covered will be transaction management and how to share multimedia. . To show how a DBA can configure the gateway and diagnose issues with it . To show basic tricks for transaction management when the databases are linked together . To detail how binary objects can be passed between different databases Target Audience: Data/Storage Architects, Database Administrators

7. Unstructured Data - database or filesystem? The debate continues 15 years after the release of Oracle8i and the capability to store large volumes of unstructured data in the database was introduced, the debate still continues today about the best location for storing it. Should it be the file system or the database? This seminar will address both these issues and offer a future direction for all sites to follow regarding this controversial topic.

8. Tips and techniques for searching against a Multimedia Warehouse The art of searching against a multimedia warehouse requires a different strategy and attitude compared to standard relational, document and spatial searching found in the Oracle database. This seminar will describe the techniques and skills required to correctly search against such an environment covering why using a Google style engine is not sufficient and how the introduction of meta data changes the search behaviour completely. Target Audience: Data/Storage Architects, Database Administrators, Developers

The Oracle Database and Developer Webinar Series.

Page 27 of 33

9. How to configure and tune a multi-terabyte database containing unstructured data Do Oracle Database Administrators have sufficient knowledge to properly tune a database that contains terabytes of unstructured data? This seminar will discuss techniques and strategies for creating, configuring and tuning a large scale database. It will cover techniques not found in Oracle manuals and breaks the boundaries for database management. Target Audience: Data/Storage Architects, Database Administrators

10. Understanding how indexing on unstructured data works What is well known and written about are all the methods for indexing structured data. From B-Trees to hash indexes. These structures are well understood. What is often not covered is how to index the vast majority of data that is unstructured. This can include text data (found in documents) and extend to binary data (including digital images, video and program files). Appearing now in very large volumes is raw data, which is proprietary stored textual or binary data that originates from applications. This seminar will discuss a number of the indexing strategies that exist in the marketplace and what the options are available for those organisations who need to manage and access this information quickly. Target Audience: Data Architects

The Oracle Database and Developer Webinar Series.

Page 28 of 33

H. Digital Imaging and Multimedia This set of seminars covers a number of introductory concepts for when working with multimedia. These seminars are of a shorter duration (approx 30 minutes) with questions/answers sessions at the end. They cover concepts most data architects and executives ask when first encountering the notion of setting up and creating digital image warehouses.

1. How to build and run efficient searches against multimedia using metadata This seminar discusses search techniques, search engines and what is involved in metadata and digital object searching. It covers course grain versus fine grain searching and all the issues involved from fuzzy searching to reducing false positives. Target Audience: Data Architects

2. How to design an efficient Multimedia Image Warehouse There is more than one way to climb a mountain and more than one way to design an effective multimedia warehouse. This seminar discusses a number of different ways a multimedia image warehouse can be built and used. Target Audience: Data Architects

3. How to load multimedia into the Oracle database - a primer This seminar goes through a number of different methods for identifying and loading digital images into a database. This includes business issues encountered on loading, security, pre and post load issues and integration with collection management systems. Target Audience: Developers and DBAs

4. The case for storing multimedia in the database This seminar covers the discussion points for whether multimedia should be stored in a database or stored in a file system. A number of critical points for and against are raised. Target Audience: Data Managers

The Oracle Database and Developer Webinar Series.

Page 29 of 33

5. How to do object querying of multimedia objects stored in the database This seminar goes through by example the basics of using a command line tool to query image objects stored in the Oracle database. This is a very technical session and only for those with in-depth knowledge in SQL and Object query writing. Target Audience: Developers and DBAs

6. How your business can sell digital images store in the Oracle database This seminar covers all the issues involved with selling a digital image. From how to price it, the business rules associated with that selling, to managing copyright and post sales and shipping issues. Target Audience: Senior Executives

7. How a business can leverage their digital assets This seminar discusses the methods available to a business to make more effective use of their digital assets. Options include: • adding intelligence through data mining and setting up data warehouses • identifying security issues • selling the assets • adding exposure through public access • education Target Audience: Senior Executives

8. How a DBA can overcome their fear of working with multimedia It is fair to say most database administrators have limited experience with multimedia as most of their skill set covers relational and structured data. As most databases only deal with text data, the idea of handling binary data can be challenging and will push the administrator outside of their comfort zone. DBAs can be obstructionist and insist multimedia and relational data should never mix. Thus consigning the multimedia and its management to the file system. The goal of this seminar is to help the DBA overcome their initial fear and show that working with multimedia in the database is not a showstopper, but is a capability that will add to the environment. Target Audience: Developers and DBAs

The Oracle Database and Developer Webinar Series.

Page 30 of 33

9. How to secure your digital assets that are stored in the Oracle database This seminar will discuss encryption methods, roles and general security and how to use these to secure digital assets. Also covered is handling metadata and securing searches. Target Audience: Developers and DBAs

10. Understanding the basics of what a digital image is This seminar goes through the basics of what a digital image, video, audio and document are. It describes the different storage formats, compression methods and image types available in the market as well as methods for conversion from one format to another. Also discussed is: • watermarking • formatting • image modification tracking • extracting and embedding metadata Target Audience: Senior Executives

The Oracle Database and Developer Webinar Series.

Page 31 of 33

I. High School Series This series of seminars has been put together for those in their final year of high school and are contemplating a career in computing. Each seminar goes for 30min and covers the basic concepts, but assumes the attendees have core knowledge in computer and smart devices.

1. A career in computing This seminar covers all the disciplines available in computing in the marketplace. From coding, to programming, to managing a team and even becoming an entrepreneur, this seminar goes through what is needed to achieve this, what key skills are required and how one can start down these paths. This seminar also addresses some key points about the psychology and discipline required to do serious programming, having the personality to manage a team and how to work in an environment which has mixed computing skills.

2. Gaming When I first started in computing, what drove my passion was a desire to write and build my own games. With ideas coming from a variety of sources and creative energy born out of the frustration of seeing traditional programming as just plain boring, I went down the path of game programming wondering if that was going to be my future path. But what is involved in writing a game, making it a great game, and is such a career path viable? With today's games what knowledge and skills are needed with computing to build the next winning app? The seminar covers these points and paints a realistic view on this discipline and outlines the skills needed to make this career happen.

3. An introduction to computer hardware The first computers originated long before mobile phones appeared in the marketplace. With designs originating in the 1800s and even earlier, the evolution of the computer into what we see today has been shaped by the marketplace and pushed by the need to go smaller and faster. But what will computers look like in the next twenty years and will we ever be able to build an electronic brain? This seminar gives a historical view and a peek into the future as to what computers will look like and provides a fascinating perspective in this fast paced environment.

4. An introduction to computer software Computer software is what controls and drives a computer. Akin to a verbal language, a lot of these languages we see today have grown and evolved from computer languages first formulated fifty years ago.

The Oracle Database and Developer Webinar Series.

Page 32 of 33

This seminar provides an introduction to the computer languages used today and how they all once originated from binary code.

5. Excite the imagination What does the future hold for computer technology? What will it look like and can we even begin to predict what will happen in the next 25 years? This seminar explores that idea by showing what is driving the changes we see now and how by looking at concepts like disruptive technologies and paradigm shifts, as well as learning from the past, we can start to see what the new trends are and make some bold predictions about what is coming. Also discussed are methods one can use to think outside the box, how to be a creative thinker and strategies one can use to come up with new ideas.

The Oracle Database and Developer Webinar Series.

Page 33 of 33