Sybernet / Cool things in Oracle Release 3.00 Oct 3, 2002

COOL THINGS IN ORACLE

This document represents a collection of PL/SQL examples that are probably obvious to any real Oracle programmer, but are quite new to those of us coming from a Sybase environment. They are presented here with that audience in mind.

Disclaimer

Most (if not all) of these examples are new to me since writing the translator. While these examples illustrate a better way of doing things in Oracle, I do not think they are applicable to what the translator attempts to solve. Probably the worst example--one that makes me cringe--is the way the translator handles cursors. What it does it correct, and the results that are produced are correct, but it is not the Oracle way of doing things. Cudos to those of you who changed your code to the Oracle way.

Contributors

Yamini Namasivayam.
Ajay Chaudhary.

Example 1 (not cool)

The following illustrates how the translator would convert a select statment on the HTTP_COLORS table:

declare cursor CUR is 
select NAME
,      COLOR
from   HTTP.HTTP_COLORS;
COL_1 varchar(255);
COL_2 varchar(255);
begin
    open CUR;
    fetch CUR into COL_1, COL_2;
    while (CUR%FOUND) loop
    begin
        HTTP.WRITE
        (
            COL_1
        ,   ' ('
        ,   COL_2
        ,   ')'
        ,   CHR(10)
        )   ;
        fetch CUR into COL_1, COL_2;
    end;
    end loop;
    close CUR;
end;

Example 2 (not cool)

The following block illustrates how one fluent in Sybase transact-SQL would write the same statement in Oracle:

declare
    name http.http_colors.name%type;
    color http.http_colors.color%type;
    cursor colors is select name, color from http.http_colors;
begin
    open colors;
    fetch colors into name, color;
    while (colors%found) loop
        http.writeln(name || ' (' || color || ')');
        fetch colors into name, color;
    end loop;
    close colors;
end;

Example 3

This following block illustrates how an Oracle programmer would accomplish the same thing:

begin
    for r in (select * from http.http_colors) loop
        http.writeln(r.name || ' (' || r.color || ')');
    end loop;
end;

Example 4 (not cool)

The order by clause in a select statement (or cursor declaration) cannot be dynamic. Here is how a Sybase programmer solves that problem in Oracle:

declare
    sort varchar(30) := 'name';
    r http.http_colors%rowtype;
    type colors_cursor is ref cursor;
    colors colors_cursor;
begin
    if (sort = 'name') then
        open colors for
        select *
        from   http.http_colors
        order  by name;
    else
    if (sort = 'color') then
        open colors for
        select *
        from   http.http_colors
        order  by color;
    end if;
    end if;
    fetch colors into r;
    while (colors%found) loop
        http.writeln(r.name || ' (' || r.color || ')');
        fetch colors into r;
    end loop;
end;

Example 5

An Oracle programmer would accomplish this by building the select statement dynamcially. Imagine how much code the above would take if I were sorting on 10 columns instead of 2 and if I did not have the luxury of doing a select * (which I never do).

declare
    query varchar(255);
    sort varchar(30) := 'name';
    r http.http_colors%rowtype;
    type colors_cursor is ref cursor;
    colors colors_cursor;
begin
    select 'select * from http.http_colors' ||
    
    decode
    (
         sort
    ,   'name'             , ' order by name'
    ,   'color'            , ' order by color'
    )

    into query
    from dual;
    open colors for query;
    fetch colors into r;
    while (colors%found) loop
        http.writeln(r.name || ' (' || r.color || ')');
        fetch colors into r;
    end loop;
end;

Example 6

The following example illustrates how to load all names from the color table into a collection:

declare 
    type color_table is table of http.http_colors.name%type; 
    colors color_table := color_table(NULL); 
    i binary_integer:=1; 
begin 
    for r in (select name from http.http_colors) loop 
        colors(i):=r.name; 
        colors.extend; 
        i:=i+1; 
    end loop; 
    for i in colors.first..colors.last loop 
        writeln(colors(i)); 
    end loop; 
end; 

Example 7

The following example illustrates how to load the entire color table into a collection:

declare 
    type color_table is table of http.http_colors%rowtype; 
    colors color_table := color_table(NULL); 
    i binary_integer:=1; 
begin 
    for r in (select * from http.http_colors) loop 
        colors(i):=r; 
        colors.extend; 
        i:=i+1; 
    end loop; 
    for i in colors.first..colors.last loop 
        writeln(colors(i).name || ' (' || colors(i).color || ')'); 
    end loop; 
end;