Oracle: Tips and Tricks
Here we provide a list of free articles and tutorials of tips and tricks that will make you more effective with Oracle.
Getting Oracle’s HR Schema
This article shows you how to get Oracle’s demo HR schema, which is used in Oracle tutorials, documentation, and in Webucator’s Oracle courses.
How to choose between a procedure and a function in PL/SQL
Oracle has been around so long and so many questions have been asked and answered in so many different ways that it can be difficult to find a definitive answer, especially to a best-practices type question like this one.
There are dozens of pages and blog posts explaining the basic differences between PL/SQL functions and procedures:
- Functions return a value. Procedures don’t.
- Functions are callable (with some restrictions) from within standard SQL statements. Procedures aren’t.
So when should you use which?
How to Install Oracle Express Edition and SQL Developer
Learn how to install the free version of Oracle: Oracle Database Express Edition (XE) version 21c and SQL Developer, Oracle’s free tool for developing and managing databases.
Oracle Live SQL Instructions
These are backup instructions for students who are not able to install Oracle locally. Scripts from class files can be copied and pasted into a browser editor to run them. However, the database is read-only, so no updates, inserts, or deletes will works, and you cannot create or alter tables.
Oracle’s Demo HR Schema
A schema is a collection of tables, views, procedures, indexes and other logical objects. Each schema is owned by a database user and has the same name as that user.
Because there is essentially a 1-to-1 relationship between schemas and users, you can think of a schema as a user and a user as a schema. In setting up for Webucator’s Oracle courses, students create the
C##HR user and then ran a couple of scripts to create and populate schema objects for a fictional Human Resources company. This is one of Oracle’s demo schemas, which we review in this article.