Getting Started For Free

set serveroutput on;
declare
    type t_option is record(version number, text varchar2(100));
    type t_options is table of t_option index by pls_integer;
    l_options t_options;
begin

    l_options := t_options(
        t_option(23, 'container image'),
        t_option(23, 'virtual box appliance'),
        t_option(23, 'full installation'),
        t_option(21, 'oracle express'),
        t_option(21, 'oci autonomous database'),
        t_option(21, 'full installation'),
        t_option(19, 'live sql'),
        t_option(19, 'virtual box appliance'),
        t_option(19, 'oci autonomous database'),
        t_option(19, 'full installation'),
        t_option(12, 'virtual box appliance')
        );
        
    dbms_output.put_line('There are many options for setting up a development environment');
    
$if dbms_db_version.version >= 21 $then
    for i in indices of l_options loop
$else
    for i in 1..l_options.count loop
$end
        dbms_output.put_line(l_options(i).version || ' ' || l_options(i).text);
    end loop;    

end;
/

Because the intended audience for this blog is anyone that works with or wants to work with Oracle PL/SQL, from the advanced expert to the beginner, I want to take a moment before things get technical to go over some tools that are as important to database development as pots and pans are for cooking. One of the aspects of database development that I enjoy most is the opportunity for hands on learning….if you have an available environment you can immediately try out any concept.

In my opinion, Oracle is the world’s leading RDBMS and it is the database of choice when it comes to enterprise application systems. Learning Oracle SQL and PL/SQL is a powerful skillset to gain, but it doesn’t have to come with a large pricetag for the developer. Oracle provides free downloads of most of their software products for developer use, and even provides some development tools free of charge. You can start your journey as an Oracle developer by creating a free account at Oracle.com, this account will give you access to a plethora of developer resources including: downloads, developer forums, case studies, tutorials, My Oracle Support and much more. The links in this article will require an account with Oracle.

Live SQL

If you just want to try out something in SQL or PL/SQL without installing any software, Oracle Live SQL is a great option. There is no charge to use the site, you only need an Oracle account to sign in and start using SQL and PL/SQL. The SQL Worksheet gives you a non persistent session for an Oracle database starting with a blank schema and all objects that you create will only last for the duration of your session. You can create database objects and see how they work. The worksheets can also be saved as a script which can be shared with others to illustrate a technique or provide a test case for a problem. The session has read only access to the sample schemas used for Oracle documentation. These schemas are very useful if you are thinking about a process that needs some data to try out. There is also an extensive Code Library with tutorials and sample scripts to explore. At the time of this writing Live SQL is running on 19c, I really hope that Oracle continues to offer this option.

SQL Developer

I have always felt that the only tools really needed to develop PL/SQL programs were a pen and some paper (napkins in restaurants are always great to start outlining code). This equates to any simple text editing program that can be used to create scripts that you can run in any command line environment like SQL Plus. As you develop larger and more complex PL/SQL codebases, one of the main tools you will need for development is an Integrated Development Environment (IDE). Oracle SQL Developer is a free application that can be used for end-to-end Oracle development, including schema design, data modeling, database management and PL/SQL program development and debugging. Many enterprise IT departments have adopted SQL Developer as their standard toolset for Oracle development teams. You can connect to databases installed on your local machine, on your network and even on the cloud. SQL Developer uses a thin JDBC client and doesn’t need to be installed, you just put the application files on your computer and you are up and running. Oracle PL/SQL can be developed using scripts and the command line, but having a full featured IDE really makes it easier to concentrate on the quality of the code you are creating and reviewing.

Oracle Database Express Edition (XE)

Oracle XE is a completely free version of Oracle that anyone can install and even redistribute. Almost all Enterprise Edition features are enabled, and it is easy to install on most any machine. Like all Oracle distributions, it comes with SQL Developer so that you can start developing right away. The resource limits are not a problem for most development, supporting up to 2 CPUs, 2 GB of database RAM and 12 GB of data storage. Unless you are trying to prototype a high performance data warehouse with extensive use of parallel processing, you won’t notice many differences between XE and other editions of the database. This is one of the first programs that I download and install when setting up a new laptop so that I always have access to an Oracle Database environment.

Oracle Database Enterprise Edition (EE)

Some development projects will require the full Enterprise Edition of Oracle to use specific features. If you don’t mind learning how to do the installations, the Enterprise Edition is free to use for development and testing purposes. You can install them on your computer, or in a virtual machine environment. Having the different versions of Oracle installed on virtual machines using Virtual Box or another virtualization platform can help you keep up with new features and evaluate techniques to see if they work in a specific version of Oracle.

Oracle Virtual Box

Oracle Virtual Box is a an open source, cross platform, complete virtualization environment that you can use to host virtual machines on any desktop or laptop. You can install Virtual Box on Windows, Linux or Mac and use it to host multiple virtual guest machines that run different operating systems. With the amount of resources available on a laptop these days, it is easy to create a virtual lab with many virtual machines each running an Oracle database. Because of the prebuilt developer virtual appliances, this is also one of the most important applications to install when setting up a new development machine. Part of the install process to get the appliances running is to install Vagrant, a virtual machine management system. By default, Virtual Box networking will not let you access guest machines from the host, to access guest VMs from the host machine you will have to set up host-only networking.

Prebuilt Developer VMs

After you install Virtual Box, you can take advantage of prebuilt developer images for virtual machines that already have Oracle database, Oracle Rest Data Services (ORDS) and the Oracle APEX rapid application development environment installed and configured. These are complete images that you just download and open with Virtual Box. The versions of Oracle installations that are available change over time, I keep copies of virtual machines as they are release so that I can access a range of versions.

Oracle Database 23 Free Virtual Box Appliance

As of this writing, the Oracle Developer Days 19c virtual machine is no longer available, when you select the link for downloading the Oracle App Development VM, it now takes you to the Oracle Database 23 Free Virtual Box appliance which has just been released! Well, its 2023 and Oracle 23 Free has just been released as a developer preview even before the official release candidate for production systems. This is a total change in the availability of the database, allowing developers to work hands on with the new release before the production release goes into circulation. After you have Virtual Box installed, the Virtual Box image for 23 Free is literally plug and play… I downloaded the image and imported the appliance into Virtual Box and it was running immediately. After I added a host only network adapter, started the machine and configured an IP address, I was able to access the 23c database from SQL Developer and start exploring new features. In addition to the Virtual Box Appliance, Oracle 23 Free is also available in a Linux installation or as a container image.

Oracle Cloud Infrastructure: Always Free Autonomous Databases

The alternative to installing the database on your pc, or using a virtual machine is to go to the Oracle Cloud. Sign up for the Free Tier and you can have up to 2 Autonomous databases and access to other cloud features that are available with the Free Tier. The free tier gives you two databases with one processor and 20gb of data storage each. For most development purposes you wont encounter the limitations, although it would be nice to try out parallel processing in the cloud. There are a few features that need to be tested with an autonomous database and this is a solid option for your development toolset. If you dont log into your database for a week or so, it will be shut down, but it just takes a few minutes to restart it and you can go back to development.

That’s all you will need to get started with the technical discussions. In the next post I will look at cursor basics.

–Anthony Harper

Puzzle

Just for fun, the following block of code doesn’t work right now. However, it can be fixed by removing 3 words from one line. If you have your development environment set up, find the error and see what topics are coming up on practicalplsql. If you can’t figure it out, the answer is in the source code for the article.

set serveroutput on;
declare
    type t_item is record(version number, text varchar2(100));
    type t_list is table of t_item index by pls_integer;
    type t_versions is table of t_list index by pls_integer;
    l_versions t_versions;
    l_all t_list := t_list();
    i number;
    cursor c_8 return t_item is 
    select
        8 as version,
        decode(level,
            1, 'analytic functions',
            2, 'materialized views',
            3, 'partitioned tables',
            4, 'decode',
            4, 'plsql language',
            5, 'pipelined functions',
            6, 'cursor for loop',
            7, 'bulk collect and forall',
            8, 'plsql packages',
            9, 'autonomous transactions',
            10, 'connect by queries') as text
    from dual connect by level <= 10;

$if dbms_db_version.version >= 23 $then

    cursor c_23 return t_item is
    with v23_base (version, text,included) as
    ( 
        values 
        (23, 'free developer preview release',true),
        (23, 'multiple values clause',true),
        (23, 'sql boolean datatype',to_boolean(1)),
        (23, 'sql domains', true),
        (23, 'plsql simple case enhancements', true),
        (23, 'json relational duality views', true),
        (23, 'plsql returning old values', true)
    ) , base_plus as
    (
        select version, text from v23_base where included
        union all 
        select 23, 'optional from clause'
    )
    select version, text from base_plus;

$end

$if dbms_db_version.version >= 21 $then

    cursor c_21 return t_item is
    with v21_base as
    (
        select 
            21 as version,
            case level 
                when 1 then 'json constructor'
                when 2 then 'cursor iteration controls'
                when 3 then 'analytic window enhancements'
                when 4 then 'non persistable schema objects'
                when 5 then 'sql macros'
                when 6 then 'innovation release'
            end as text
        from dual connect by level <= 6
    ), v21_json_base as
    (
        select json{
                'version' : (d.version), 
                'text' : (d.text)
            } as jdoc
        from v21_base d
    ), json_relational as
    (
        select j.version, j.text
        from
            v21_json_base d,
            json_table (d.jdoc
                columns (
                    version number path '$.version.number()', 
                    text varchar2(50) path '$.text.string()'
                )
        ) j
    )
    select version, text from json_relational;
        
$end

$if dbms_db_version.version >= 19 $then

    cursor c_19 return t_item is
    with v19_base as
    (
        select 
            19 as version, 
            case level 
                when 1 then 'json_object simplifications'
                when 2 then 'json path syntax relaxation' 
                when 3 then 'listagg distinct' 
                when 4 then 'polymorphic table functions'
                when 5 then 'time for upgrade'
            end as text 
        from dual connect by level <= 5
    ), json_base as
    (
        select json_object(d.*) as jdoc
        from v19_base d
    ), json_relational as
    (
        select j.version, j.text
        from
            json_base d,
            json_table (d.jdoc
                columns (
                    VERSION number,
                    text varchar2(30) path '$.TEXT.string()'
                )
        ) j
    )
    select version, text from json_relational;
        
$end

$if dbms_db_version.version >= 12 $then

    cursor c_12 return t_item is
    with v12_base as
    (
        select 
            12 as version,
            case level
                when 1 then 'sql xml functions'
                when 2 then 'xmltype constructor'
                when 3 then 'with clause functions'
                when 4 then 'plsql optimizer'
                when 5 then 'plsql conditional compilation'
                when 6 then 'sql case'
                when 6 then 'past due for upgrade'
            end as text from dual connect by level <= 6
        union all 
        select 12, 'xmltype' from dual
    ), xml_base as
    (
        select 
            xmlelement(
                "xml_features", 
                xmlagg(
                    xmlelement(
                        "feature", 
                        xmlforest(
                            d.version as "version", 
                            d.text as "text")
                    )
                )
            ) as xdoc
        from v12_base d
    ), xml_relational as
    (
    select x.version, x.text
    from 
        xml_base b,
        xmltable('/xml_features/feature' passing b.xdoc
            columns 
                version number path 'version', 
                text varchar2(30) path 'text'
        ) x
    )
    select version, text from xml_relational;
    
$end

begin

$if dbms_db_version.version >= 23 $then
    l_versions(23) := t_list(for r in c_23 sequence => r);
$end

$if dbms_db_version.version >= 21 $then
    l_versions(21) := t_list(for r in c_21 sequence => r);
$end

$if dbms_db_version.version >= 19 $then
    open c_19;
    fetch c_19 bulk collect into l_versions(19);
    close c_19;
$end 

$if dbms_db_version.version >= 12 $then
    open c_12;
    fetch c_12 bulk collect into l_versions(12);
    close c_12;
$end 

    open c_8;
    fetch c_8 bulk collect into l_versions(8);
    close c_8;

$if dbms_db_version.version >= 21 $then
    for i in indices of l_versions loop
$else
    i := l_versions.first;
    while i is not null loop
$end
        l_all := l_all multiset union l_versions(i);
$if dbms_db_version.version < 21 $then
        i := l_versions.next(i);
$end
    end loop;

$if dbms_db_version.version >= 21 $then
    for i in indices of l_all loop
$else
    for i in 1..l_all.count loop
$end
        dbms_output.put_line(l_all(i).version || ' ' || l_all(i).text);
    end loop;    

exception 
    when others then
        dbms_output.put_line(sqlerrm);
end;
/