Oracle PL/SQL Package Translation to Postgres PL/pgSQL
Oracle Package Global Associative Array (index-by binary_integer) Translation
If you’ve explored the Postgres PL/pgSQLdocumentation, then no doubt you’ve come up with concerns regarding how to translate PL/SQL packages that contain global associative arrays into PL/pgSQL; because, there is no obvi-ous solution. We ran into this in our first major PL/SQL translation challenge which included thobvi-ousands of lines of procedure code encapsulated in a package sharing global associative arrays (Index-by tables). The following dis-cusses our solution. It’s a solution that we automated, which makes translations of these global PL/SQL arrays a breeze.
First, we solved the package issue by grouping all functions and procedures of the same PL/SQL package into a schema that was named after the package per a recommendation found in the Postgres documentation (http:// www.postgresql.org/docs/9.1/static/plpgsql-porting.html).
Next, we incorporated temp tables as our mechanism for emulating global associative arrays. We found that the real challenge to this emulation was factoring in the accesses to these tables in the translation of the PL/SQL code. If one is doing a 10 line translation, no problem, as this can be easily done by hand. But when hundreds or even thousands of lines are involved, it’s a daunting task that begged to be automated.
To incorporate a temp table as a substitute for a global associative array, one must consider the basic operations used in conjunction with the array and define a strategy for emulation using SQL. The following table gives some insight to our approach:
An Example of an Oracle Package Translation involving Global Associated Arrays
To best understand the translation of PL/SQL involving a global associate array to PL/pgSQL, please consider the following elementary Oracle package example. This package, named country, consists of an associative array and three procedures. The first procedure (sp_topSong09) takes as input a country music artist’s first and last name. It initializes the package global array (v_s09) with a DELETE operation. It then calls function sp_getAllSongs which populates the global array from a table that contains the top 25 country songs for the year 2009. This result is then globally available to function sp_findTopSong which is called to find the top song for a given artist. This is done by iterating through the global array searching for the first match for the given artist. When a match occurs, it re-turns, then the sp_topSong09 procedure returns to the caller the title of the artist’s top song.
The Oracle PL/SQL package country that we will translate which includes three procedures follows:
description PL/SQL operation emulation
array instantiation instanceName AssociativeArrayName; instanceName RECORD; array initialization instanceName.DELETE truncate or create tempTable array population instanceName(i) := aRecord INSERT into tempTable values (…); array access for 1 element aRecord := instanceName(i); SELECT * into aRecord from tempTable
where id = i;
array iteration thru complete set for aRecord IN inst.first..inst.last LOOP DECLARE CURSOR crsx as SELECT … from tempTable where id >= 0
for aRec in crsx LOOP
array update instanceName(i).item := v_item; UPDATE tempTable set item = v_item where id = i;
CREATE OR REPLACE PACKAGE BODY country IS
TYPE r_s09 IS RECORD ( rank top2009Songs.rank%TYPE, ß 3
artist top2009Songs.artist%TYPE, title top2009Songs.title%TYPE);
TYPE t_s09 IS TABLE OF r_s09 INDEX BY PLS_INTEGER; ß 6
v_s09 t_s09; ß 7
--
PROCEDURE sp_topSong09(p_artistFirstName IN artists.firstName%TYPE, p_artistLastName IN artists.lastName%TYPE,
v_title OUT varchar2) IS BEGIN dbms_output.put_line('topSong09..|begin'); v_s09.DELETE; ß 15 sp_getAllSongs(); ß 16 sp_findTopSong(p_artistFirstName, ß 17 p_artistLastName, v_title);
dbms_output.put_line('...|top song '|| v_title); dbms_output.put_line('topSong09..|end');
EXCEPTION
WHEN others THEN
dbms_output.put_line('ERROR getTopSong sqlerrm: ' || sqlerrm); END sp_topSong09;
--
PROCEDURE sp_getAllSongs IS
--
-- load array v_s09 from table top2009Songs --
CURSOR c_songs is
SELECT rank, artist, title FROM top2009Songs ORDER BY rank; v_index PLS_INTEGER; r_song r_s09; BEGIN dbms_output.put_line('getAllSongs|begin'); v_index := 0;
FOR r_song IN c_songs LOOP
dbms_output.put_line('...|rank '|| r_song.rank ||' title '|| r_song.title); v_s09(v_index) := r_song; ß 44 v_index := v_index + 1; END LOOP; dbms_output.put_line('getAllSongs|end'); EXCEPTION
WHEN others THEN
dbms_output.put_line('ERROR getAllSongs sqlerrm: ' || sqlerrm); RAISE;
END sp_getAllSongs; --
PROCEDURE sp_findTopSong(p_artistFirstName IN artists.firstName%TYPE, p_artistLastName IN artists.lastName%TYPE,
v_title OUT varchar2) IS v_artist top2009Songs.artist%TYPE; v_len PLS_INTEGER; i PLS_INTEGER; BEGIN dbms_output.put_line('findTopSong|begin');
v_artist := p_artistFirstName || ' ' || p_artistLastName; dbms_output.put_line('...|searching for '|| v_artist);
FOR i IN v_s09.first..v_s09.last LOOP ß 65
dbms_output.put_line('...|evaluate artist '|| v_s09 (i).artist);
IF v_s09(i).artist = v_artist THEN ß 67 v_title := v_s09(i).title; ß 68 EXIT; END IF; END LOOP; dbms_output.put_line('findTopSong|end'); EXCEPTION
WHEN others THEN
dbms_output.put_line('ERROR findTopSong sqlerrm: ' || sqlerrm); RAISE;
END sp_findTopSong; END;
The DDL and a subset of the data for the top2009Songs table is: create table top2009Songs(
rank NUMBER(4), artist char(30), title varchar2(40) );
INSERT INTO top2009songs VALUES (1, 'Lady Antebellum', 'I Run to You'); …
INSERT INTO top2009songs VALUES (13, 'Taylor Swift', 'You Belong with Me'); …
INSERT INTO top2009songs VALUES (25, 'Keith Urban', 'Kiss a Girl');
After loading this package into Oracle we executed it as follows and searched for the top song performed by Tay-lor Swift:
SQL> var v_title varchar2(40);
SQL> exec country.sp_topSong09('Taylor', 'Swift', :v_title); PL/SQL procedure successfully completed.
V_TITLE
---
You Belong with Me
Now our goal is to translate this package into PL/pgSQL, make the same call and obtain the same result!
Prerequisites to Translation
Because our translation involves generating a temp table for each associative array, the translation tool must have all the information necessary to translate a record definition into a table. Also, because the package contains calls from one procedure to another, the translation tool must have access to the procedure interface definitions so it can define the call type and cast if needed (see chapter on translating PL/SQL function calls). Hence, the following is needed before doing the package translation:
the Oracle procedure definitions and other global information from the package spec file. the Oracle table DDL.The user is asked to supply each file during the translation. The DDL file maybe converted to Postgres previously. If not, it will be translated to Postgres when the table definitions are inventoried.
Translation Preprocessing - Global Associative Array Identification
The translation tool scans first the package spec file then the package body definition prior to converting each pro-cedure. When it finds the following:
A RECORD definition (line 3).
A TYPE definition referencing the RECORD as being a TABLE indexed by an integer (line 6). The declaration of a global instance of the indexed TABLE of RECORDs (in this case v_s09 in line 7).it recognizes that it is processing a global associative array and generates a temp table definition from the record definition. The array’s record definition is translated into a CREATE TEMP TABLE statement and encapsulated inside a function. This function is saved into the directory specified by the config option in a file named fCre-ate_tableName.sql (where tableName matches the TYPE name associated with the TABLE of the RECORD speci-fied in line 6 of the package code).
The function for creating this particular temp table, as generated by the translation tool follows: CREATE or REPLACE FUNCTION global_util.gen_t_s09()
RETURNS void AS $$
BEGIN --
-- Create temp table for global storage of the result set (v_s09). -- This emulates an Oracle associative array.
-- BEGIN
TRUNCATE TABLE t_s09; EXCEPTION
when UNDEFINED_TABLE then BEGIN
CREATE TEMP TABLE t_s09(
ß 15
rank integer, artist char(30), title varchar(40)); EXCEPTION
when others then
RAISE EXCEPTION 'gen_t_s09 create table t_s09 issue NUM:%, DE-TAILS:%',
SQLSTATE, SQLERRM; END;
when OTHERS then
RAISE EXCEPTION 'gen_t_s09 truncate table t_s09 issue NUM:%, DE-TAILS:%',
SQLSTATE, SQLERRM; END;
END; -- gen_t_s09 $$ LANGUAGE plpgsql;
Please observe that the table is truncated before its created. This combination exists because the table will persist once created for the life of the connection. Therefore, if the connection is maintained for an extended period of time then it can be expected that if the table is frequently used the truncate will be the more common operation of the two. The code is written such that should the table not exist, then the exception that will result from the trunca-tion will drop into the create table statement. This combinatrunca-tion of truncate / exceptrunca-tion / create exhibited good per-formance when compared against alternatives investigated such as determining the table’s existence using SQL. One should also notice that in line 15 of the gen_t_s09function that the id field is defined as NOT NULL UNIQUE. This will result in the generation of a unique index that will be of value when specific entries, indexed by the id field, are accessed by the translated function code.
Analysis of sp_topSong09
The code generated by our translator for the first of the three procedures, sp_topSong09 follows: CREATE or REPLACE FUNCTION country.sp_topSong09
( p_artistFirstName IN ARTISTS.FIRSTNAME%TYPE, p_artistLastName IN ARTISTS.LASTNAME%TYPE, v_title OUT varchar)
RETURNS varchar AS $body$ DECLARE
BEGIN
RAISE NOTICE 'topSong09..|begin';
perform global_util.gen_t_s09(); -- truncate or create temp table ß 8
PERFORM country.sp_getAllSongs(); ß 9
SELECT * FROM country.sp_findTopSong ( ß 10 p_artistFirstName,
p_artistLastName) INTO v_title;
RAISE NOTICE '...|top song %', v_title; RAISE NOTICE 'topSong09..|end';
EXCEPTION
WHEN others THEN
END; -- sp_topSong09 $body$ LANGUAGE plpgsql;
Line 15 of the “country” package listing equates to line 8 in sp_topSong09. Here one can note that the
v_s09.DELETE statement in the package source has been translated to the call (perform) of gen_t_s09 (line 8 of this function). This call is to the auto generated function gen_t_s09 which results in the truncation or creation the temp table used to emulate the global associative array..
Lines 9 and 10 of sp_topSong09 function illustrate further our strategy for procedure call translation that will be discussed in detail in another chapter. The brief explanation of the generated code for the calls is as follows. The procedure sp_getAllSongs does not return any parameters, hence it was translated to a PERFORM call. The proce-dure sp_findTopSong does return a parameter, so it was translated into a SELECT INTO invocation.
Analysis of sp_getAllSongs
The PL/pgSQLcode generated for the procedure, sp_getAllSongs follows: CREATE or REPLACE FUNCTION country.sp_getAllSongs() RETURNS VOID AS $body$
--
-- load array v_s09 from table top2009Songs --
DECLARE
c_songs CURSOR is
SELECT rank, artist, title FROM top2009Songs
ORDER BY rank; v_index integer;
r_song RECORD; ß 11
v_s09 RECORD; -- global record emulation ß 12
BEGIN
RAISE NOTICE 'getAllSongs|begin'; v_index := 0;
FOR r_song IN c_songs LOOP
RAISE NOTICE '...|rank % title %', r_song.rank, r_song.title;
-- v_s09(v_index) := r_song; ß 19
v_s09 := r_song; -- set local instance ß 20 INSERT INTO t_s09 ( ß 21 id, rank, artist, title) VALUES ( v_index, r_song.rank, r_song.artist, r_song.title); ß 30 v_index := v_index + 1; END LOOP;
RAISE NOTICE 'getAllSongs|end'; EXCEPTION
WHEN others THEN
RAISE NOTICE 'ERROR getAllSongs sqlerrm: %', sqlerrm; RAISE;
END sp_getAllSongs; END;
$body$ LANGUAGE plpgsql;
Line 44 of the PL/SQL country package represents the instruction which inserts rows into the global associative array v_s09. Our emulation is seen in lines 20 through 30. Here we have translated the operation v_s09
(v_index) := r_song into an insert of the current record from the cursor c_songs. The local instance, v_s09 is set because frequently this record is processed code within the LOOP. In this case its unused so it could be deleted by the developer inspecting the translation result.
Line 12 of the sp_getAllSongs function listing was inserted by the translator to be the local instance of the record v_s09. It, like line 11 are defined as RECORDs because, unlike with Oracle, there is no formal definition of a re-cord. With Postgres, its just a placeholder until its populated, at which time it takes on the definition of the object its populated from.
The output of this function is a fully populated global temp table. The displays generated when run from Postgres are identical to the Oracle counterpart:
NOTICE: getAllSongs|begin
NOTICE: ...|rank 1 title I Run to You NOTICE: ...|rank 2 title Whatever it Is NOTICE: ...|rank 3 title Boots On
NOTICE: ...|rank 4 title It Wont Be Like This for Long NOTICE: ...|rank 5 title River of Love
NOTICE: ...|rank 6 title Sideways
NOTICE: ...|rank 7 title People are Crazy NOTICE: ...|rank 8 title Alright
NOTICE: ...|rank 9 title Sweet Thing
NOTICE: ...|rank 10 title Big Green Tractor NOTICE: ...|rank 11 title Small Town USA NOTICE: ...|rank 12 title Gettin You Home NOTICE: ...|rank 13 title You Belong with Me NOTICE: ...|rank 14 title She’s Country NOTICE: ...|rank 15 title Then
NOTICE: ...|rank 16 title Cowgirls Dont Cry NOTICE: ...|rank 17 title Its America
NOTICE: ...|rank 18 title God Love Her
NOTICE: ...|rank 19 title Only You Can Love Me This Way NOTICE: ...|rank 20 title Summer Nights
NOTICE: ...|rank 21 title Living for the Night NOTICE: ...|rank 22 title American Ride
NOTICE: ...|rank 23 title I’ll Just Hold on NOTICE: ...|rank 24 title Welcome to the Future NOTICE: ...|rank 25 title Kiss a Girl
NOTICE: getAllSongs|end
Analysis of sp_findTopSong
given artist is found. The translated PL/pgSQLcode that iterates through the temp table instead follows: CREATE or REPLACE FUNCTION country.sp_findTopSong
( p_artistFirstName IN ARTISTS.FIRSTNAME%TYPE, p_artistLastName IN ARTISTS.LASTNAME%TYPE, v_title OUT varchar )
RETURNS varchar AS $body$ DECLARE
v_artist TOP2009SONGS.ARTIST%TYPE; v_len integer;
i integer;
c_gt_v_s09 CURSOR FOR SELECT * FROM t_s09 where id >= 0 order by id; ß 10
v_s09 RECORD; -- in memory table emulation intermediate result ß 11
BEGIN
RAISE NOTICE 'findTopSong|begin';
v_artist := p_artistFirstName || ' ' || p_artistLastName; RAISE NOTICE '...|searching for %', v_artist;
FOR v_s09 IN c_gt_v_s09 LOOP ß 16
i := v_s09.id; ß 17
RAISE NOTICE '...|evaluate artist %', v_s09.artist;
IF v_s09.artist = v_artist THEN ß 19 v_title := v_s09.title; ß 20 EXIT; END IF; END LOOP;
RAISE NOTICE 'findTopSong|end'; EXCEPTION
WHEN others THEN
RAISE NOTICE 'ERROR findTopSong sqlerrm: %', sqlerrm; RAISE;
END; -- sp_findTopSong $body$ LANGUAGE plpgsql;
Line 65 in the PL/SQL package code controls the iteration through the indexed associative array. This was trans-lated by convOraclePLSQL into lines 10, 11, 16 and 17 in the sp_findTopSong listing. These lines replace the Ora-cle FOR that iterated from the first to the last entry in the indexed array with the cursor c_gt_v_s09(line 10) that scans the temp table’s id field to obtain all values from the lowest (first) to the last. The current row of the cursor is kept in a local record, v_s09. From this local position, it can be evaluated. In this example program, the current row is just tested, not modified. If it had been updated in any manner, then the translator would have gener-ated an update SQL statement of the temp table t_s09 which would be indexed by “i”, or the current record’s id field.
Test Results
After loading the 3 translated functions plus the supplemental temp table generation function into a postgres 8.4.4 database one can find the top song for Taylor Swift in the year 2009 simply by doing the following:
psql=> select * from country.sp_topSong09('Taylor', 'Swift'); NOTICE: topSong09..|begin
NOTICE: sequence "t_s09_seq" does not exist, skipping
NOTICE: CREATE TABLE / UNIQUE will create implicit index "t_s09_id_key" for "t_s09"
NOTICE: getAllSongs|begin
NOTICE: ...|rank 1 title I Run to You NOTICE: ...|rank 2 title Whatever it Is NOTICE: ...|rank 3 title Boots On
NOTICE: ...|rank 4 title It Wont Be Like This for Long NOTICE: ...|rank 5 title River of Love
NOTICE: ...|rank 6 title Sideways
NOTICE: ...|rank 7 title People are Crazy NOTICE: ...|rank 8 title Alright
NOTICE: ...|rank 9 title Sweet Thing
NOTICE: ...|rank 10 title Big Green Tractor NOTICE: ...|rank 11 title Small Town USA NOTICE: ...|rank 12 title Gettin You Home NOTICE: ...|rank 13 title You Belong with Me NOTICE: ...|rank 14 title Shes Country
NOTICE: ...|rank 15 title Then
NOTICE: ...|rank 16 title Cowgirls Dont Cry NOTICE: ...|rank 17 title Its America
NOTICE: ...|rank 18 title God Love Her
NOTICE: ...|rank 19 title Only You Can Love Me This Way NOTICE: ...|rank 20 title Summer Nights
NOTICE: ...|rank 21 title Living for the Night NOTICE: ...|rank 22 title American Ride
NOTICE: ...|rank 23 title Ill Just Hold on NOTICE: ...|rank 24 title Welcome to the Future NOTICE: ...|rank 25 title Kiss a Girl
NOTICE: getAllSongs|end NOTICE: findTopSong|begin
NOTICE: ...|searching for Taylor Swift NOTICE: ...|evaluate artist Lady Antebellum NOTICE: ...|evaluate artist Zac Brown
NOTICE: ...|evaluate artist Randy Houser NOTICE: ...|evaluate artist Darius Rucker NOTICE: ...|evaluate artist George Strait NOTICE: ...|evaluate artist Dierks Bentley NOTICE: ...|evaluate artist Billy Currington NOTICE: ...|evaluate artist Darius Rucker NOTICE: ...|evaluate artist Keith Urban NOTICE: ...|evaluate artist Jason Aldean NOTICE: ...|evaluate artist Justin Moore NOTICE: ...|evaluate artist Chris Young NOTICE: ...|evaluate artist Taylor Swift NOTICE: findTopSong|end
NOTICE: ...|top song You Belong with Me NOTICE: topSong09..|end
v_title
--- You Belong with Me
Summary
table access to provide good performance, we believe that we have an excellent approach and toolset developed for solving one of the most daunting challenges surrounding Oracle package migration to Postgres.
