https://wiki.postgresql.org/api.php?action=feedcontributions&user=Tuxinose&feedformat=atomPostgreSQL wiki - User contributions [en]2024-03-29T04:48:44ZUser contributionsMediaWiki 1.35.13https://wiki.postgresql.org/index.php?title=Oracle_Functions&diff=33117Oracle Functions2019-03-06T16:44:12Z<p>Tuxinose: /* Numeric Functions */</p>
<hr />
<div>''This is work in progress''<br />
<br />
== Numeric Functions ==<br />
<br />
{| class="wikitable sortable; float-right"<br />
|-<br />
! Oracle Function<br />
! Postgres<br />
|-<br />
|'''ABS'''<br />
|'''ABS'''<br />
|-<br />
|'''ACOS'''<br />
|'''ACOS'''<br />
|-<br />
|'''ASIN'''<br />
|'''ASIN'''<br />
|-<br />
|'''ATAN'''<br />
|'''ATAN'''<br />
|-<br />
|'''ATAN2'''<br />
|'''ATAN2'''<br />
|-<br />
|'''BITAND'''<br />
|'''&''' (Operator)<br />
|-<br />
|'''CEIL'''<br />
|'''CEIL'''<br />
|-<br />
|'''COS'''<br />
|'''COS'''<br />
|-<br />
|'''COSH'''<br />
|by extension orafce<br />
|-<br />
|'''EXP'''<br />
|'''EXP'''<br />
|-<br />
|'''FLOOR'''<br />
|'''FLOOR'''<br />
|-<br />
|'''LN'''<br />
|'''LN'''<br />
|-<br />
|'''LOG<br />
|'''LOG<br />
|-<br />
|'''MOD'''<br />
|'''MOD''' or '''&''' (Operator)<br />
|-<br />
|'''NANVL'''<br />
|Currently no known implementation<br />
|-<br />
|'''POWER'''<br />
|'''POWER'''<br />
|-<br />
|'''REMAINDER'''<br />
|Currently no known implementation<br />
|-<br />
|'''ROUND''' (number)<br />
|'''ROUND''' (number)<br />
|-<br />
|'''SIGN'''<br />
|'''SIGN'''<br />
|-<br />
|'''SIN'''<br />
|'''SIN'''<br />
|-<br />
|'''SINH'''<br />
|by extension orafce<br />
|-<br />
|'''SQRT'''<br />
|'''SQRT'''<br />
|-<br />
|'''TAN'''<br />
|'''TAN'''<br />
|-<br />
|'''TANH'''<br />
|by extension orafce<br />
|-<br />
|'''TRUNC''' (number)<br />
|'''TRUNC''' (number)<br />
|-<br />
|'''WIDTH_BUCKET'''<br />
|'''WIDTH_BUCKET'''<br />
|}<br />
<br />
== Character Functions Returning Character Values ==<br />
<br />
== Character Functions Returning Number Values ==<br />
<br />
== Character Set Functions ==<br />
<br />
== Collation Functions ==<br />
<br />
== Datetime Functions ==<br />
<br />
== General Comparison Functions ==<br />
<br />
== Conversion Functions ==<br />
<br />
== Large Object Functions ==<br />
<br />
== Collection Functions ==<br />
<br />
== Hierarchical Functions ==<br />
<br />
== Data Mining Functions ==<br />
<br />
== XML Functions ==<br />
<br />
== JSON Functions ==<br />
<br />
== Encoding and Decoding Functions ==<br />
<br />
== NULL-Related Functions ==<br />
<br />
== Environment and Identifier Functions ==</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_Functions&diff=33116Oracle Functions2019-03-06T15:21:46Z<p>Tuxinose: /* Numeric Functions */</p>
<hr />
<div>''This is work in progress''<br />
<br />
== Numeric Functions ==<br />
<br />
{| class="wikitable sortable; float-right"<br />
|-<br />
! Oracle Function<br />
! Postgres<br />
|-<br />
|'''ABS'''<br />
|'''ABS'''<br />
|-<br />
|'''ACOS'''<br />
|'''ACOS'''<br />
|-<br />
|'''ASIN'''<br />
|'''ASIN'''<br />
|-<br />
|'''ATAN'''<br />
|'''ATAN'''<br />
|-<br />
|'''ATAN2'''<br />
|'''ATAN2'''<br />
|-<br />
|'''BITAND'''<br />
|Operator '''&'''<br />
|-<br />
|'''CEIL'''<br />
|'''CEIL'''<br />
|-<br />
|'''COS'''<br />
|'''COS'''<br />
|-<br />
|'''COSH'''<br />
|by extension orafce<br />
|-<br />
|'''EXP'''<br />
|'''EXP'''<br />
|-<br />
|'''FLOOR'''<br />
|'''FLOOR'''<br />
|-<br />
|'''LN'''<br />
|'''LN'''<br />
|-<br />
|'''LOG<br />
|'''LOG<br />
|-<br />
|'''MOD'''<br />
|'''MOD''' or Operator '''&'''<br />
|-<br />
|'''NANVL'''<br />
|Currently no known implementation<br />
|-<br />
|'''POWER'''<br />
|'''POWER'''<br />
|-<br />
|'''REMAINDER'''<br />
|Currently no known implementation<br />
|-<br />
|'''ROUND''' (number)<br />
|'''ROUND''' (number)<br />
|-<br />
|'''SIGN'''<br />
|'''SIGN'''<br />
|-<br />
|'''SIN'''<br />
|'''SIN'''<br />
|-<br />
|'''SINH'''<br />
|by extension orafce<br />
|-<br />
|'''SQRT'''<br />
|'''SQRT'''<br />
|-<br />
|'''TAN'''<br />
|'''TAN'''<br />
|-<br />
|'''TANH'''<br />
|by extension orafce<br />
|-<br />
|'''TRUNC''' (number)<br />
|'''TRUNC''' (number)<br />
|-<br />
|'''WIDTH_BUCKET'''<br />
|'''WIDTH_BUCKET'''<br />
|}<br />
<br />
== Character Functions Returning Character Values ==<br />
<br />
== Character Functions Returning Number Values ==<br />
<br />
== Character Set Functions ==<br />
<br />
== Collation Functions ==<br />
<br />
== Datetime Functions ==<br />
<br />
== General Comparison Functions ==<br />
<br />
== Conversion Functions ==<br />
<br />
== Large Object Functions ==<br />
<br />
== Collection Functions ==<br />
<br />
== Hierarchical Functions ==<br />
<br />
== Data Mining Functions ==<br />
<br />
== XML Functions ==<br />
<br />
== JSON Functions ==<br />
<br />
== Encoding and Decoding Functions ==<br />
<br />
== NULL-Related Functions ==<br />
<br />
== Environment and Identifier Functions ==</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_Functions&diff=33115Oracle Functions2019-03-06T15:19:39Z<p>Tuxinose: /* Numeric Functions */</p>
<hr />
<div>''This is work in progress''<br />
<br />
== Numeric Functions ==<br />
<br />
{| class="wikitable sortable; float-right"<br />
|-<br />
! Oracle Function<br />
! Postgres<br />
|-<br />
|'''ABS'''<br />
|'''ABS'''<br />
|-<br />
|'''ACOS'''<br />
|'''ACOS'''<br />
|-<br />
|'''ASIN'''<br />
|'''ASIN'''<br />
|-<br />
|'''ATAN'''<br />
|'''ATAN'''<br />
|-<br />
|'''ATAN2'''<br />
|'''ATAN2'''<br />
|-<br />
|'''BITAND'''<br />
|Operator '''&'''<br />
|-<br />
|'''CEIL'''<br />
|'''CEIL'''<br />
|-<br />
|'''COS'''<br />
|'''COS'''<br />
|-<br />
|'''COSH'''<br />
|by extension orafce<br />
|-<br />
|'''EXP'''<br />
|'''EXP'''<br />
|-<br />
|'''FLOOR'''<br />
|'''FLOOR'''<br />
|-<br />
|'''LN'''<br />
|'''LN'''<br />
|-<br />
|'''LOG<br />
'''|LOG<br />
|-<br />
|'''MOD'''<br />
|'''MOD''' or Operator '''&'''<br />
|-<br />
|'''NANVL'''<br />
|Currently no known implementation<br />
|-<br />
|'''POWER'''<br />
|'''POWER'''<br />
|-<br />
|'''REMAINDER'''<br />
|Currently no known implementation<br />
|-<br />
|'''ROUND''' (number)<br />
|'''ROUND''' (number)<br />
|-<br />
|'''SIGN'''<br />
|'''SIGN'''<br />
|-<br />
|'''SIN'''<br />
|'''SIN'''<br />
|-<br />
|'''SINH'''<br />
|by extension orafce<br />
|-<br />
|'''SQRT'''<br />
|'''SQRT'''<br />
|-<br />
|'''TAN'''<br />
|'''TAN'''<br />
|-<br />
|'''TANH'''<br />
|by extension orafce<br />
|-<br />
|'''TRUNC''' (number)<br />
|'''TRUNC''' (number)<br />
|-<br />
|'''WIDTH_BUCKET'''<br />
|'''WIDTH_BUCKET'''<br />
|}<br />
<br />
== Character Functions Returning Character Values ==<br />
<br />
== Character Functions Returning Number Values ==<br />
<br />
== Character Set Functions ==<br />
<br />
== Collation Functions ==<br />
<br />
== Datetime Functions ==<br />
<br />
== General Comparison Functions ==<br />
<br />
== Conversion Functions ==<br />
<br />
== Large Object Functions ==<br />
<br />
== Collection Functions ==<br />
<br />
== Hierarchical Functions ==<br />
<br />
== Data Mining Functions ==<br />
<br />
== XML Functions ==<br />
<br />
== JSON Functions ==<br />
<br />
== Encoding and Decoding Functions ==<br />
<br />
== NULL-Related Functions ==<br />
<br />
== Environment and Identifier Functions ==</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_Functions&diff=33114Oracle Functions2019-03-06T15:16:39Z<p>Tuxinose: /* Numeric Functions */</p>
<hr />
<div>''This is work in progress''<br />
<br />
== Numeric Functions ==<br />
<br />
{| class="wikitable sortable; float-right"<br />
|-<br />
! Oracle Function<br />
! Postgres<br />
|-<br />
|ABS<br />
|ABS<br />
|-<br />
|ACOS<br />
|ACOS<br />
|-<br />
|ASIN<br />
|ASIN<br />
|-<br />
|ATAN<br />
|ATAN<br />
|-<br />
|ATAN2<br />
|ATAN2<br />
|-<br />
|BITAND<br />
|Operator &<br />
|-<br />
|CEIL<br />
|CEIL<br />
|-<br />
|COS<br />
|COS<br />
|-<br />
|COSH<br />
|by extension orafce<br />
|-<br />
|EXP<br />
|EXP<br />
|-<br />
|FLOOR<br />
|FLOOR<br />
|-<br />
|LN<br />
|LN<br />
|-<br />
|LOG<br />
|LOG<br />
|-<br />
|MOD<br />
|MOD or Operator &<br />
|-<br />
|NANVL<br />
|Currently no known implementation<br />
|-<br />
|POWER<br />
|POWER<br />
|-<br />
|REMAINDER<br />
|Currently no known implementation<br />
|-<br />
|ROUND (number)<br />
|ROUND (number)<br />
|-<br />
|SIGN<br />
|SIGN<br />
|-<br />
|SIN<br />
|SIN<br />
|-<br />
|SINH<br />
|by extension orafce<br />
|-<br />
|SQRT<br />
|SQRT<br />
|-<br />
|TAN<br />
|TAN<br />
|-<br />
|TANH<br />
|by extension orafce<br />
|-<br />
|TRUNC (number)<br />
|TRUNC (number)<br />
|-<br />
|WIDTH_BUCKET<br />
|WIDTH_BUCKET<br />
|}<br />
<br />
== Character Functions Returning Character Values ==<br />
<br />
== Character Functions Returning Number Values ==<br />
<br />
== Character Set Functions ==<br />
<br />
== Collation Functions ==<br />
<br />
== Datetime Functions ==<br />
<br />
== General Comparison Functions ==<br />
<br />
== Conversion Functions ==<br />
<br />
== Large Object Functions ==<br />
<br />
== Collection Functions ==<br />
<br />
== Hierarchical Functions ==<br />
<br />
== Data Mining Functions ==<br />
<br />
== XML Functions ==<br />
<br />
== JSON Functions ==<br />
<br />
== Encoding and Decoding Functions ==<br />
<br />
== NULL-Related Functions ==<br />
<br />
== Environment and Identifier Functions ==</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_Functions&diff=33113Oracle Functions2019-03-06T15:14:13Z<p>Tuxinose: /* Numeric Functions */</p>
<hr />
<div>''This is work in progress''<br />
<br />
== Numeric Functions ==<br />
<br />
{| class="wikitable sortable; float-right"<br />
|-<br />
! Oracle Function<br />
! Postgres<br />
|-<br />
|ABS<br />
|ABS<br />
|-<br />
|ACOS<br />
|ACOS<br />
|-<br />
|ASIN<br />
|ASIN<br />
|-<br />
|ATAN<br />
|ATAN<br />
|-<br />
|ATAN2<br />
|ATAN2<br />
|-<br />
|BITAND<br />
|Operator &<br />
|-<br />
|CEIL<br />
|CEIL<br />
|-<br />
|COS<br />
|COS<br />
|-<br />
|COSH<br />
|*<br />
|-<br />
|EXP<br />
|EXP<br />
|-<br />
|FLOOR<br />
|FLOOR<br />
|-<br />
|LN<br />
|LN<br />
|-<br />
|LOG<br />
|LOG<br />
|-<br />
|MOD<br />
|MOD & **<br />
|-<br />
|NANVL<br />
|Currently no known implementation<br />
|-<br />
|POWER<br />
|POWER<br />
|-<br />
|REMAINDER<br />
|-<br />
|-<br />
|ROUND (number)<br />
|ROUND (number)<br />
|-<br />
|SIGN<br />
|SIGN<br />
|-<br />
|SIN<br />
|SIN<br />
|-<br />
|SINH<br />
|*<br />
|-<br />
|SQRT<br />
|SQRT<br />
|-<br />
|TAN<br />
|TAN<br />
|-<br />
|TANH<br />
|*<br />
|-<br />
|TRUNC (number)<br />
|TRUNC (number)<br />
|-<br />
|WIDTH_BUCKET<br />
|WIDTH_BUCKET<br />
|}<br />
<br />
== Character Functions Returning Character Values ==<br />
<br />
== Character Functions Returning Number Values ==<br />
<br />
== Character Set Functions ==<br />
<br />
== Collation Functions ==<br />
<br />
== Datetime Functions ==<br />
<br />
== General Comparison Functions ==<br />
<br />
== Conversion Functions ==<br />
<br />
== Large Object Functions ==<br />
<br />
== Collection Functions ==<br />
<br />
== Hierarchical Functions ==<br />
<br />
== Data Mining Functions ==<br />
<br />
== XML Functions ==<br />
<br />
== JSON Functions ==<br />
<br />
== Encoding and Decoding Functions ==<br />
<br />
== NULL-Related Functions ==<br />
<br />
== Environment and Identifier Functions ==</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_Functions&diff=33112Oracle Functions2019-03-06T15:03:29Z<p>Tuxinose: Created page with "''This is work in progress'' == Numeric Functions == {| class="wikitable sortable; float-right" |- ! Oracle Function ! Postgres |- |ABS |ABS |- |ACOS |ACOS |- |ASIN |ASIN |-..."</p>
<hr />
<div>''This is work in progress''<br />
<br />
== Numeric Functions ==<br />
<br />
{| class="wikitable sortable; float-right"<br />
|-<br />
! Oracle Function<br />
! Postgres<br />
|-<br />
|ABS<br />
|ABS<br />
|-<br />
|ACOS<br />
|ACOS<br />
|-<br />
|ASIN<br />
|ASIN<br />
|-<br />
|ATAN<br />
|ATAN<br />
|-<br />
|ATAN2<br />
|ATAN2<br />
|-<br />
|BITAND<br />
|& **<br />
|-<br />
|CEIL<br />
|CEIL<br />
|-<br />
|COS<br />
|COS<br />
|-<br />
|COSH<br />
|*<br />
|-<br />
|EXP<br />
|EXP<br />
|-<br />
|FLOOR<br />
|FLOOR<br />
|-<br />
|LN<br />
|LN<br />
|-<br />
|LOG<br />
|LOG<br />
|-<br />
|MOD<br />
|MOD & **<br />
|-<br />
|NANVL<br />
|-<br />
|-<br />
|POWER<br />
|POWER<br />
|-<br />
|REMAINDER<br />
|-<br />
|-<br />
|ROUND (number)<br />
|ROUND (number)<br />
|-<br />
|SIGN<br />
|SIGN<br />
|-<br />
|SIN<br />
|SIN<br />
|-<br />
|SINH<br />
|*<br />
|-<br />
|SQRT<br />
|SQRT<br />
|-<br />
|TAN<br />
|TAN<br />
|-<br />
|TANH<br />
|*<br />
|-<br />
|TRUNC (number)<br />
|TRUNC (number)<br />
|-<br />
|WIDTH_BUCKET<br />
|WIDTH_BUCKET<br />
|}<br />
<br />
== Character Functions Returning Character Values ==<br />
<br />
== Character Functions Returning Number Values ==<br />
<br />
== Character Set Functions ==<br />
<br />
== Collation Functions ==<br />
<br />
== Datetime Functions ==<br />
<br />
== General Comparison Functions ==<br />
<br />
== Conversion Functions ==<br />
<br />
== Large Object Functions ==<br />
<br />
== Collection Functions ==<br />
<br />
== Hierarchical Functions ==<br />
<br />
== Data Mining Functions ==<br />
<br />
== XML Functions ==<br />
<br />
== JSON Functions ==<br />
<br />
== Encoding and Decoding Functions ==<br />
<br />
== NULL-Related Functions ==<br />
<br />
== Environment and Identifier Functions ==</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=33111Oracle to Postgres Conversion2019-03-06T14:38:32Z<p>Tuxinose: Functions</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Functions ==<br />
<br />
Oracle has more than 250 built-in single row functions and more than 50 aggregate functions. See [[Oracle Functions]].<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the function sysdate to get the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the table '''DUAL''' for selects where actually no table name would be necessary, since the ''FROM'' clause in Oracle is mandatory. In PostgreSQL we can omit the ''FROM'' clause at all. This table can be created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
In any case when migrating, if possible, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare - and peculiar.<br />
<br />
== ROWNUM and ROWID ==<br />
<br />
Oracle's pseudocolumn '''ROWNUM''' returns a number which is assigned at the moment when data is read, but before the ORDER BY is executed. You might consider to replace it by using ROW_NUMBER() OVER (ORDER BY ...) which might work in many cases. Emulations with sequences are potentially slower.<br />
<br />
Oracle's pseudocolumn '''ROWID''' returns the physical address of a table row, encoded in base64. In applications it can be used to (temporarily) cache the address of rows to locate them easier a second time. Postgres has '''ctid''' which serves the same purpose.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) AS foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in old (pre version 9i) Oracle work as follows:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a, b<br />
WHERE a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql and Oracle 9i and younger:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a<br />
LEFT OUTER JOIN b<br />
ON a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
SELECT a.field1, sum (b.field2)<br />
FROM a, b<br />
WHERE a.item_id = b.item_id (+)<br />
GROUP BY a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
SELECT a.field1, b_sum_field2_by_item_id (a.item_id)<br />
FROM a<br />
<br />
where you've defined the function:<br />
<br />
CREATE FUNCTION b_sum_field2_by_item_id (integer)<br />
RETURNS integer<br />
AS '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
RETURN sum(field2) FROM b WHERE item_id = v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
Starting with 9i Oracle also supports SQL 99 outer join syntax. Unfortunately some programmers still stick to the old syntax (even nearly 20 years after it was superseded). That's the reason why this chapter still is necessary.<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) is much higher than in Oracle and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== Date and Time ==<br />
Oracle mostly uses the data type DATE which is a combination of date + time. In most cases it should be sufficient to replace it with Postgres' TIMESTAMP. In some cases it will yield not exactly the correct result, because date consists only of second, minute, hour, day, month and year. No fractions of seconds, no DST, no time zones.<br />
<br />
Oracle's TIMEZONE is very much similar to Postgres'.<br />
<br />
Oracle only knows INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND, so this can be used directly in Postgres too.<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.<br />
; [https://github.com/cybertec-postgresql/ora_migrator ora_migrator]<br />
: PostgreSQL extension in PL/pgSQL providing a database migration toolkit that makes use of the Oracle Foreign Data Wrapper.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=33109Oracle to Postgres Conversion2019-03-06T10:08:05Z<p>Tuxinose: /* BLOBs */ format</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the function sysdate to get the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the table '''DUAL''' for selects where actually no table name would be necessary, since the ''FROM'' clause in Oracle is mandatory. In PostgreSQL we can omit the ''FROM'' clause at all. This table can be created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
In any case when migrating, if possible, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare - and peculiar.<br />
<br />
== ROWNUM and ROWID ==<br />
<br />
Oracle's pseudocolumn '''ROWNUM''' returns a number which is assigned at the moment when data is read, but before the ORDER BY is executed. You might consider to replace it by using ROW_NUMBER() OVER (ORDER BY ...) which might work in many cases. Emulations with sequences are potentially slower.<br />
<br />
Oracle's pseudocolumn '''ROWID''' returns the physical address of a table row, encoded in base64. In applications it can be used to (temporarily) cache the address of rows to locate them easier a second time. Postgres has '''ctid''' which serves the same purpose.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) AS foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in old (pre version 9i) Oracle work as follows:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a, b<br />
WHERE a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql and Oracle 9i and younger:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a<br />
LEFT OUTER JOIN b<br />
ON a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
SELECT a.field1, sum (b.field2)<br />
FROM a, b<br />
WHERE a.item_id = b.item_id (+)<br />
GROUP BY a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
SELECT a.field1, b_sum_field2_by_item_id (a.item_id)<br />
FROM a<br />
<br />
where you've defined the function:<br />
<br />
CREATE FUNCTION b_sum_field2_by_item_id (integer)<br />
RETURNS integer<br />
AS '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
RETURN sum(field2) FROM b WHERE item_id = v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
Starting with 9i Oracle also supports SQL 99 outer join syntax. Unfortunately some programmers still stick to the old syntax (even nearly 20 years after it was superseded). That's the reason why this chapter still is necessary.<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) is much higher than in Oracle and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== Date and Time ==<br />
Oracle mostly uses the data type DATE which is a combination of date + time. In most cases it should be sufficient to replace it with Postgres' TIMESTAMP. In some cases it will yield not exactly the correct result, because date consists only of second, minute, hour, day, month and year. No fractions of seconds, no DST, no time zones.<br />
<br />
Oracle's TIMEZONE is very much similar to Postgres'.<br />
<br />
Oracle only knows INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND, so this can be used directly in Postgres too.<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.<br />
; [https://github.com/cybertec-postgresql/ora_migrator ora_migrator]<br />
: PostgreSQL extension in PL/pgSQL providing a database migration toolkit that makes use of the Oracle Foreign Data Wrapper.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=33108Oracle to Postgres Conversion2019-03-06T10:01:40Z<p>Tuxinose: /* Sysdate */ sysdate is afunction</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the function sysdate to get the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the table '''DUAL''' for selects where actually no table name would be necessary, since the ''FROM'' clause in Oracle is mandatory. In PostgreSQL we can omit the ''FROM'' clause at all. This table can be created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
In any case when migrating, if possible, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare - and peculiar.<br />
<br />
== ROWNUM and ROWID ==<br />
<br />
Oracle's pseudocolumn '''ROWNUM''' returns a number which is assigned at the moment when data is read, but before the ORDER BY is executed. You might consider to replace it by using ROW_NUMBER() OVER (ORDER BY ...) which might work in many cases. Emulations with sequences are potentially slower.<br />
<br />
Oracle's pseudocolumn '''ROWID''' returns the physical address of a table row, encoded in base64. In applications it can be used to (temporarily) cache the address of rows to locate them easier a second time. Postgres has '''ctid''' which serves the same purpose.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) AS foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in old (pre version 9i) Oracle work as follows:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a, b<br />
WHERE a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql and Oracle 9i and younger:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a<br />
LEFT OUTER JOIN b<br />
ON a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
SELECT a.field1, sum (b.field2)<br />
FROM a, b<br />
WHERE a.item_id = b.item_id (+)<br />
GROUP BY a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
SELECT a.field1, b_sum_field2_by_item_id (a.item_id)<br />
FROM a<br />
<br />
where you've defined the function:<br />
<br />
CREATE FUNCTION b_sum_field2_by_item_id (integer)<br />
RETURNS integer<br />
AS '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
RETURN sum(field2) FROM b WHERE item_id = v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
Starting with 9i Oracle also supports SQL 99 outer join syntax. Unfortunately some programmers still stick to the old syntax (even nearly 20 years after it was superseded). That's the reason why this chapter still is necessary.<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) is much higher than in Oracle and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== Date and Time ==<br />
Oracle mostly uses the data type DATE which is a combination of date + time. In most cases it should be sufficient to replace it with Postgres' TIMESTAMP. In some cases it will yield not exactly the correct result, because date consists only of second, minute, hour, day, month and year. No fractions of seconds, no DST, no time zones.<br />
<br />
Oracle's TIMEZONE is very much similar to Postgres'.<br />
<br />
Oracle only knows INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND, so this can be used directly in Postgres too.<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.<br />
; [https://github.com/cybertec-postgresql/ora_migrator ora_migrator]<br />
: PostgreSQL extension in PL/pgSQL providing a database migration toolkit that makes use of the Oracle Foreign Data Wrapper.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31392Oracle to Postgres Conversion2018-01-08T22:00:39Z<p>Tuxinose: /* The Dual Table */ +ROWNUM, ROWID</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the table '''DUAL''' for selects where actually no table name would be necessary, since the ''FROM'' clause in Oracle is mandatory. In PostgreSQL we can omit the ''FROM'' clause at all. This table can be created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
In any case when migrating, if possible, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare - and peculiar.<br />
<br />
== ROWNUM and ROWID ==<br />
<br />
Oracle's pseudocolumn '''ROWNUM''' returns a number which is assigned at the moment when data is read, but before the ORDER BY is executed. You might consider to replace it by using ROW_NUMBER() OVER (ORDER BY ...) which might work in many cases. Emulations with sequences are potentially slower.<br />
<br />
Oracle's pseudocolumn '''ROWID''' returns the physical address of a table row, encoded in base64. In applications it can be used to (temporarily) cache the address of rows to locate them easier a second time. Postgres has '''ctid''' which serves the same purpose.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle, and actually, anybody using DECODE in times of Oracle 12c should roast in hell.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) AS foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in old (pre version 9i) Oracle work as follows:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a, b<br />
WHERE a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql and Oracle 9i and younger:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a<br />
LEFT OUTER JOIN b<br />
ON a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
SELECT a.field1, sum (b.field2)<br />
FROM a, b<br />
WHERE a.item_id = b.item_id (+)<br />
GROUP BY a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
SELECT a.field1, b_sum_field2_by_item_id (a.item_id)<br />
FROM a<br />
<br />
where you've defined the function:<br />
<br />
CREATE FUNCTION b_sum_field2_by_item_id (integer)<br />
RETURNS integer<br />
AS '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
RETURN sum(field2) FROM b WHERE item_id = v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
Starting with 9i Oracle also supports SQL 99 outer join syntax. Unfortunately some programmers still stick to the old syntax (even nearly 20 years after it was superseded). That's the reason why this chapter still is necessary.<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) is much higher than in Oracle and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== Date and Time ==<br />
Oracle mostly uses the data type DATE which is a combination of date + time. In most cases it should be sufficient to replace it with Postgres' TIMESTAMP. In some cases it will yield not exactly the correct result, because date consists only of second, minute, hour, day, month and year. No fractions of seconds, no DST, no time zones.<br />
<br />
Oracle's TIMEZONE is very much similar to Postgres'.<br />
<br />
Oracle only knows INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND, so this can be used directly in Postgres too.<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31388Oracle to Postgres Conversion2018-01-07T22:59:26Z<p>Tuxinose: /* Date and Time */</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
In any case, if possible, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare - and peculiar.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle, and actually, anybody using DECODE in times of Oracle 12c should roast in hell.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) AS foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in old (pre version 9i) Oracle work as follows:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a, b<br />
WHERE a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql and Oracle 9i and younger:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a<br />
LEFT OUTER JOIN b<br />
ON a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
SELECT a.field1, sum (b.field2)<br />
FROM a, b<br />
WHERE a.item_id = b.item_id (+)<br />
GROUP BY a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
SELECT a.field1, b_sum_field2_by_item_id (a.item_id)<br />
FROM a<br />
<br />
where you've defined the function:<br />
<br />
CREATE FUNCTION b_sum_field2_by_item_id (integer)<br />
RETURNS integer<br />
AS '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
RETURN sum(field2) FROM b WHERE item_id = v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
Starting with 9i Oracle also supports SQL 99 outer join syntax. Unfortunately some programmers still stick to the old syntax (even nearly 20 years after it was superseded). That's the reason why this chapter still is necessary.<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) is much higher than in Oracle and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== Date and Time ==<br />
Oracle mostly uses the data type DATE which is a combination of date + time. In most cases it should be sufficient to replace it with Postgres' TIMESTAMP. In some cases it will yield not exactly the correct result, because date consists only of second, minute, hour, day, month and year. No fractions of seconds, no DST, no time zones.<br />
<br />
Oracle's TIMEZONE is very much similar to Postgres'.<br />
<br />
Oracle only knows INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND, so this can be used directly in Postgres too.<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31387Oracle to Postgres Conversion2018-01-07T22:58:47Z<p>Tuxinose: /* Numeric Types */ date and time</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
In any case, if possible, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare - and peculiar.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle, and actually, anybody using DECODE in times of Oracle 12c should roast in hell.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) AS foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in old (pre version 9i) Oracle work as follows:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a, b<br />
WHERE a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql and Oracle 9i and younger:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a<br />
LEFT OUTER JOIN b<br />
ON a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
SELECT a.field1, sum (b.field2)<br />
FROM a, b<br />
WHERE a.item_id = b.item_id (+)<br />
GROUP BY a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
SELECT a.field1, b_sum_field2_by_item_id (a.item_id)<br />
FROM a<br />
<br />
where you've defined the function:<br />
<br />
CREATE FUNCTION b_sum_field2_by_item_id (integer)<br />
RETURNS integer<br />
AS '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
RETURN sum(field2) FROM b WHERE item_id = v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
Starting with 9i Oracle also supports SQL 99 outer join syntax. Unfortunately some programmers still stick to the old syntax (even nearly 20 years after it was superseded). That's the reason why this chapter still is necessary.<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) is much higher than in Oracle and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== Date and Time ==<br />
Oracle mostly uses the data type DATE which is a combination of date + time. In most cases it should be sufficient to replace it with Postgres' TIMESTAMP. In some cases it will yield not exactly the same result, because date consists only of second, minute, hour, day, month and year. No fractions of seconds, no DST, no time zones.<br />
<br />
Oracle's TIMEZONE is very much similar to Postgres'.<br />
<br />
Oracle only knows INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND, so this can be used directly in Postgres too.<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31385Oracle to Postgres Conversion2018-01-05T14:32:09Z<p>Tuxinose: /* Numeric Types */</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
In any case, if possible, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare - and peculiar.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle, and actually, anybody using DECODE in times of Oracle 12c should roast in hell.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) AS foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in old (pre version 9i) Oracle work as follows:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a, b<br />
WHERE a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql and Oracle 9i and younger:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a<br />
LEFT OUTER JOIN b<br />
ON a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
SELECT a.field1, sum (b.field2)<br />
FROM a, b<br />
WHERE a.item_id = b.item_id (+)<br />
GROUP BY a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
SELECT a.field1, b_sum_field2_by_item_id (a.item_id)<br />
FROM a<br />
<br />
where you've defined the function:<br />
<br />
CREATE FUNCTION b_sum_field2_by_item_id (integer)<br />
RETURNS integer<br />
AS '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
RETURN sum(field2) FROM b WHERE item_id = v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
Starting with 9i Oracle also supports SQL 99 outer join syntax. Unfortunately some programmers still stick to the old syntax (even nearly 20 years after it was superseded). That's the reason why this chapter still is necessary.<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) is much higher than in Oracle and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31384Oracle to Postgres Conversion2018-01-05T14:30:54Z<p>Tuxinose: /* Outer Joins */ formatting</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
In any case, if possible, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare - and peculiar.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle, and actually, anybody using DECODE in times of Oracle 12c should roast in hell.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) AS foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in old (pre version 9i) Oracle work as follows:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a, b<br />
WHERE a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql and Oracle 9i and younger:<br />
<br />
SELECT a.field1, b.field2<br />
FROM a<br />
LEFT OUTER JOIN b<br />
ON a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
SELECT a.field1, sum (b.field2)<br />
FROM a, b<br />
WHERE a.item_id = b.item_id (+)<br />
GROUP BY a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
SELECT a.field1, b_sum_field2_by_item_id (a.item_id)<br />
FROM a<br />
<br />
where you've defined the function:<br />
<br />
CREATE FUNCTION b_sum_field2_by_item_id (integer)<br />
RETURNS integer<br />
AS '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
RETURN sum(field2) FROM b WHERE item_id = v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
Starting with 9i Oracle also supports SQL 99 outer join syntax. Unfortunately some programmers still stick to the old syntax (even nearly 20 years after it was superseded). That's the reason why this chapter still is necessary.<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres is much higher than i Oracle (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31383Oracle to Postgres Conversion2018-01-05T14:27:11Z<p>Tuxinose: /* Subquery in FROM */ format</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
In any case, if possible, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare - and peculiar.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle, and actually, anybody using DECODE in times of Oracle 12c should roast in hell.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) AS foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in old (pre version 9i) Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
Starting with 9i Oracle also supports SQL 99 outer join syntax. Unfortunately some programmers still stick to the old syntax (even nearly 20 years after it was superseded). That's the reason why this chapter still is necessary.<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres is much higher than i Oracle (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31382Oracle to Postgres Conversion2018-01-05T14:25:10Z<p>Tuxinose: /* The Dual Table */ advice</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
In any case, if possible, just remove the "FROM DUAL" clause from the statement. Joins with dual are really rare - and peculiar.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle, and actually, anybody using DECODE in times of Oracle 12c should roast in hell.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) as foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in old (pre version 9i) Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
Starting with 9i Oracle also supports SQL 99 outer join syntax. Unfortunately some programmers still stick to the old syntax (even nearly 20 years after it was superseded). That's the reason why this chapter still is necessary.<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres is much higher than i Oracle (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31376Oracle to Postgres Conversion2018-01-05T00:11:08Z<p>Tuxinose: /* Outer Joins */</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle, and actually, anybody using DECODE in times of Oracle 12c should roast in hell.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) as foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in old (pre version 9i) Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
Starting with 9i Oracle also supports SQL 99 outer join syntax. Unfortunately some programmers still stick to the old syntax (even nearly 20 years after it was superseded). That's the reason why this chapter still is necessary.<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres is much higher than i Oracle (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31375Oracle to Postgres Conversion2018-01-04T23:51:12Z<p>Tuxinose: /* Decode */</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
There is one tiny discrepancy between DECODE and its emulation with CASE:<br />
<br />
DECODE (x,NULL,'null','else')<br />
<br />
will return 'null' in case x is NULL, while<br />
<br />
CASE x WHEN NULL THEN 'null' ELSE 'else' END<br />
<br />
results in 'else'. This also applies within Oracle, and actually, anybody using DECODE in times of Oracle 12c should roast in hell.<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) as foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres is much higher than i Oracle (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31374Oracle to Postgres Conversion2018-01-04T23:31:10Z<p>Tuxinose: /* Transactions */ typo</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) as foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres is much higher than i Oracle (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=PostgreSQL_for_Oracle_DBAs&diff=31345PostgreSQL for Oracle DBAs2018-01-02T22:30:35Z<p>Tuxinose: /* Brief description: */ data file location</p>
<hr />
<div>= Introduction =<br />
<br />
The following article contains information to help an Oracle DBA understand<br />
some terms and the management of a PostgreSQL database. This article is<br />
intended to be an introduction to PostgreSQL, not a tutorial or a complete<br />
definition of how to administer a PostgreSQL database. For complete<br />
documentation refer to the [http://www.postgresql.org/docs/manuals/ PostgreSQL manuals].<br />
<br />
= Oracle =<br />
<br />
== Brief description: ==<br />
<br />
* An Oracle database server consists of an Oracle instance and an Oracle database.<br />
* An Oracle instance consists of the Oracle background processes and the allocated memory within the shared global area (SGA) and the program global area (PGA).<br />
* The important Oracle background processes are the following:<br />
** Database Writer Process (DBWn)<br />
** Log Writer Process (LGWR)<br />
** Checkpoint Process (CKPT)<br />
** System Monitor Process (SMON)<br />
** Process Monitor Process (PMON)<br />
** Recoverer Process (RECO)<br />
** Archiver Processes (ARCn)<br />
* An Oracle database consists of the database datafiles, control files, redo log files, archive log files, and parameter file. This may be files on a local file system, NFS files or SAN Devices. In the latter case Oracle uses ASM (Automatic Storage Management) to address data.<br />
* To remotely access an Oracle database, there exists a separate process referred to as the Oracle listener.<br />
* In the Dedicated Server configuration (versus the Shared Server configuration) every established database session has its own process executing on the server.<br />
<br />
To keep things simple any comparisons with an Oracle database will always refer to a single instance managing a single database, RAC and Data Guard will not be mentioned. Note: PostgreSQL also has the concept of a warm standby (since 8.2) with the shipping of archive logs (introduced in 8.0).<br />
<br />
= PostgreSQL =<br />
<br />
== Database Server Processes ==<br />
<br />
The database server program postgres are all of the server processes. There are no separately named processes like in Oracle for the different duties within the database environment. If you were to look at the process list (ps) the name of the processes would be postgres. However, on most platforms, PostgreSQL modifies its command title so that individual server processes can readily be identified. You may need to adjust the parameters used for commands such as ps and top to show these updated titles in place of the process name ("postgres").<br />
<br />
The processes seen in a process list can be some of the following:<br />
<br />
* Master process - launches the other processes, background and session processes.<br />
* Writer process - background process that coordinates database writes, log writes and checkpoints.<br />
* Stats collector process - background process collecting information about server activity.<br />
* User session processes.<br />
<br />
The server processes communicate with each other using semaphores and shared memory to ensure data integrity throughout concurrent data access.<br />
<br />
== PostgreSQL Database Cluster ==<br />
<br />
Within a server, one or more Oracle instances can be built. The databases are separate from one another usually sharing only the Oracle listener process. PostgreSQL has the concept of a ''database cluster''. A database cluster is a collection of databases that is stored at a common file system location (the "data area"). It is possible to have multiple database clusters, so long as they use different data areas and different communication ports.<br />
<br />
The processes along with the file system components are all shared within the database cluster. All the data needed for a database cluster is stored within the cluster's data directory, commonly referred to as ''PGDATA'' (after the name of the environment variable that can be used to define it). The PGDATA directory contains several subdirectories and configuration files.<br />
<br />
The following are some of the cluster configuration files:<br />
<br />
* postgresql.conf - Parameter or main server configuration file.<br />
* pg_hba.conf - Client authentication configuration file.<br />
* pg_ident.conf - Map from OS account to PostgreSQL account file.<br />
<br />
The cluster subdirectories:<br />
<br />
* base - Subdirectorycontaining per-database subdirectories<br />
* global - Subdirectory containing cluster-wide tables<br />
** pg_auth - Authorization file containing user and role definitions.<br />
** pg_control - Control file.<br />
** pg_database - Information of databases within the cluster.<br />
* pg_clog - Subdirectory containing transaction commit status data<br />
* pg_multixact - Subdirectory containing multitransaction status data (used for shared row locks)<br />
* pg_subtrans - Subdirectory containing subtransaction status data<br />
* pg_tblspc - Subdirectory containing symbolic links to tablespaces<br />
* pg_twophase - Subdirectory containing state files for prepared transactions<br />
* pg_xlog - Subdirectory containing WAL (Write Ahead Log) files<br />
<br />
By default, for each database in the cluster there is a subdirectory within PGDATA/base, named after the database's OID (object identifier) in pg_database. This subdirectory is the default location for the database's files; in particular, its system catalogs are stored there. Each table and index is stored in a separate file, named after the table or index's filenode number, which can be found in pg_class.relfilenode.<br />
<br />
Several components that Oracle DBAs usually equate to one database are shared between databases within a PostgreSQL cluster, including the parameter file, control file, redo logs, tablespaces, accounts, roles, and background processes.<br />
<br />
== Tablespaces and Object Data Files ==<br />
<br />
PostgreSQL introduced tablespace management in version 8.0. The physical representation of a tablespace within PostgreSQL is simple: it is a directory on the file system, and the mapping is done via symbolic links.<br />
<br />
When a database is created, the default tablespace is where by default all of the database objects are stored. In Oracle this would be similar to the System, User, and Temporary tablespaces. If no default tablespace is defined during creation, the data files will go into a subdirectory of the PGDATA/base. Preferably the location of the system catalog information and the application data structures would reside in separately managed tablespaces. This is available.<br />
<br />
As in Oracle, the definition of a PostgreSQL table determines which tablespace the object resides. However, there exists no size limitation except physical boundaries placed on the device by the OS.<br />
<br />
The individual table's data is stored within a file within the tablespace (or directory). The database software will split the table across multiple datafiles in the event the table's data surpasses 1 GB.<br />
<br />
Since version 8.1, it's possible to partition a table over separate (or the same) tablespaces. This is based on PostgreSQL's table inheritance feature, using a capability of the query planner referred to as constraint exclusion.<br />
<br />
There exists no capacity for separating out specific columns (like LOBs) into separately defined tablespaces. However, in addition to the data files that represent the table (in multiples of 1 GB) there is a separation of data files for columns within a table that are TOASTed. The PostgreSQL storage system called TOAST (The Oversized-Attribute Storage Technique) automatically stores values larger than a single database page into a secondary storage area per table. The TOAST technique allows for data columns up to 1 GB in size.<br />
<br />
As in Oracle, the definition of an index determines which tablespace it resides within. Therefore, it is possible to gain the performance advantage of separating the disks that a table's data versus its indexing reside, relieving I/O contention during data manipulation.<br />
<br />
In Oracle there exists temporary tablespaces where sort information and temporary evaluation space needed for distinct statements and the like are used. PostgreSQL does not have this concept of a temporary tablespace; however it does require storage to be able to perform these activities as well. Within the "default" tablespace of the database (defined at database creation) there is a directory called pgsql_tmp. This directory holds the temporary storage needed for the evaluation. The files that get created within the directory exist only while the SQL statement is executing. They grow very fast, and are most likely not designed for space efficiency but rather speed. Be aware that disk fragmentation could result from this, and there needs to be sufficient space on the disk to support the user queries. With the release of 8.3, there are definitions of temporary tablespaces using the parameter ''temp_tablespaces''.<br />
<br />
== REDO and Archiving ==<br />
<br />
PostgreSQL uses ''Write-Ahead Logging'' (WAL) as its approach to transaction logging. WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, when log records describing the changes have been flushed to permanent storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the log records. (This is roll-forward recovery, also known as REDO.)<br />
<br />
PostgreSQL maintains its (WAL) in the ''pg_xlog'' subdirectory of the cluster's data directory.<br />
<br />
WAL was introduced into PostgreSQL in version 7.1. To maintain database consistency in case of a failure, previous releases forced all data modifications to disk before each transaction commit. With WAL, only one log file must be flushed to disk, greatly improving performance while adding capabilities like Point-In-Time Recovery and transaction archiving.<br />
<br />
A PostgreSQL system theoretically produces an indefinitely long sequence of WAL records. The system physically divides this sequence into WAL segment files, which are normally 16MB apiece. The system normally creates a few segment files and then "recycles" them by renaming no-longer-needed segment files to higher segment numbers. If you were to perform a listing of the pg_xlog directory there would always be a handful of files changing names over time.<br />
<br />
To add archiving of the WAL files there exists a parameter within the parameter file where a command is added to execute the archival process. Once this is done, Operation System "on-line" backups even become available by executing the ''pg_start_backup'' and the ''pg_stop_backup'' commands, which suspend and resume writing to the datafiles while continuing to write the transactions to the WAL files and executing the archival process.<br />
<br />
Inclusion of WAL archiving and the on-line backup commands were added in version 8.0.<br />
<br />
== Rollback or Undo ==<br />
<br />
It is interesting how the dynamic allocation of disk space is used for the storage and processing of records within tables. The files that represent the table grow as the table grows. It also grows with transactions that are performed against it. In Oracle there is a concept of rollback or undo segments that hold the information for rolling back a transaction. In PostgreSQL the data is stored within the file that represents the table. So when deletes and updates are performed on a table, the file that represents the object will contain the previous data. This space gets reused but to force recovery of used space, a maintenance process called ''vacuum'' must be executed.<br />
<br />
== Server Log File ==<br />
<br />
Oracle has the alert log file. PostgreSQL has the server log file. A configuration option would even have the connection information we normally see within the Oracle's listener.log appear in PostgreSQL's server log. The parameters within the server configuration file (postgresql.conf) determine the level, location, and name of the log file.<br />
<br />
To help with the maintenance of the server log file (it grows rapidly), there exists functionality for rotating the server log file. Parameters can be set to determine when to rotate the file based on the size or age of the file. Management of the old files is then left to the administrator.<br />
<br />
== Applications ==<br />
<br />
The command ''initdb'' creates a new PostgreSQL database cluster.<br />
<br />
The command ''psql'' starts the terminal-based front-end to PostgreSQL or SQL command prompt. Queries and commands can be executed interactively or through files. The psql command prompt has several attractive features:<br />
<br />
* Thorough on-line help for both the psql commands and the SQL syntax.<br />
* Command history and line editing.<br />
* SQL commands could exist on multiple lines and are executed only after the semi-colon (;).<br />
* Several SQL commands separated by semi-colons could be entered on a single line.<br />
* Flexible output formatting.<br />
* Multiple object description commands that are superior to Oracle's DESCRIBE.<br />
<br />
Depending on the security configurations of the environments, connections can be established locally or remotely through TCP/IP. Due to these separate security connections passwords may or may not be required to connect.<br />
<br />
The command ''pg_ctl'' is a utility for displaying status, starting, stopping, or restarting the PostgreSQL database server (postgres). Although the server can be started through the postgres executable, pg_ctl encapsulates tasks such as redirecting log output, properly detaching from the terminal and process group, and providing options for controlled shutdown.<br />
<br />
The commands ''pg_dump'' and ''pg_restore'' are utilities designed for exporting and importing the contents of a PostgreSQL database. Dumps can be output in either script or archive file formats. The script file format creates plain-text files containing the SQL commands required to reconstruct the database to the state it was at the time it was generated. The archive file format creates a file to be used with pg_restore to rebuild the database.<br />
<br />
The archive file formats are designed to be portable across architectures. Historically, any type of upgrade to the PostgreSQL software would require a pg_dump of the database prior to the upgrade. Then a pg_restore after the upgrade. Now, for minor releases (i.e., the third decimal – 8.2.x) upgrades can be done in place. However, changing versions at the first or second decimal still requires a pg_dump/pg_restore.<br />
<br />
There exists a graphical tool called [http://www.pgadmin.org/ ''pgAdmin III''] developed separately. It is distributed with the Linux and Windows versions of PostgreSQL. Connection to a database server can be established remotely to perform administrative duties. Because the tool is designed to manage all aspects of the database environment, connection to the database must be through a super user account.<br />
<br />
The pgAdmin III tool has the following standard attractive features:<br />
<br />
* Intuitive layout<br />
* Tree structure for creating and modifying database objects<br />
* Reviewing and saving of SQL when altering or creating objects<br />
<br />
[[Category:Oracle]]</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=User:Tuxinose&diff=31344User:Tuxinose2018-01-02T22:20:41Z<p>Tuxinose: Created page with "I'm here out of interest in Postgres. I have experience in Databases since 1998 (mostly Oracle) and try to give some of it back to the community."</p>
<hr />
<div>I'm here out of interest in Postgres. I have experience in Databases since 1998 (mostly Oracle) and try to give some of it back to the community.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31343Oracle to Postgres Conversion2018-01-02T22:17:43Z<p>Tuxinose: /* Numeric Types */ typo</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses Transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) as foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres is much higher than i Oracle (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31342Oracle to Postgres Conversion2018-01-02T22:16:59Z<p>Tuxinose: /* Empty strings and NULL values */ Data Types</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses Transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) as foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
=== Data Types ===<br />
<br />
Postgres sticks mostly to SQL standards while Oracle in history choose it's own way - especially concerning data types.<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analyze all your string handling code and separate NULLs and empty strings.<br />
<br />
== Numeric Types ==<br />
<br />
Oracle programs mostly use NUMBER. The Appropriate data type in Postgres is DECIMAL or NUMERIC, both are equivalent. The limit for numbers in Postgres is much higher than i Oracle (up to 131072 digits before the decimal point; up to 16383 digits after the decimal point) and is internally stored in a similar way.<br />
<br />
FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31341Oracle to Postgres Conversion2018-01-02T22:03:24Z<p>Tuxinose: /* CONNECT BY */ iterator</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses Transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) as foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analize all Your string handling code and separate NULLs and empty strings.<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31340Oracle to Postgres Conversion2018-01-02T22:01:50Z<p>Tuxinose: /* CONNECT BY */</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses Transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) as foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
Sometimes CONNECT BY is used as a simple iterator:<br />
<br />
SELECT ... FROM DUAL CONNECT BY rownum <=10<br />
<br />
may be equivalent to the Postgres Query<br />
<br />
SELECT ... FROM generate_series(...)<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analize all Your string handling code and separate NULLs and empty strings.<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31339Oracle to Postgres Conversion2018-01-02T21:52:51Z<p>Tuxinose: /* Transactions */ Isolation levels</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses Transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
Postgres knows all the isolation levels Oracle knows (and a few more). In most cases the default isolation level of Postgres (Read Committed) will be sufficient.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) as foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analize all Your string handling code and separate NULLs and empty strings.<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31338Oracle to Postgres Conversion2018-01-02T21:38:00Z<p>Tuxinose: /* What you should know before you begin */</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Transactions =<br />
<br />
While Oracle RDBMS always uses Transactions, you have to activate that for Postgres. Every piece of code doing some DML that is not to be committed immediately must start a transaction with a BEGIN statement. ROLLBACK and COMMIT have the same semantic meaning in both systems; also SAVEPOINTS mean the same.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) as foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analize all Your string handling code and separate NULLs and empty strings.<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinosehttps://wiki.postgresql.org/index.php?title=Oracle_to_Postgres_Conversion&diff=31337Oracle to Postgres Conversion2018-01-02T21:27:48Z<p>Tuxinose: /* What you should know before you begin */ update to newer Oracle versions</p>
<hr />
<div>Note: This page was originally carried on the [http://openacs.org/ OpenACS] Website, but seems to have been lost over the ages and so is now reproduced here.<br />
by James Shannon, Ben Adida, and Don Baccus<br />
<br />
= What you should know before you begin =<br />
<br />
You should know SQL relatively well. Knowing the details of Oracle SQL and Postgres SQL are obviously tremendous advantages, but the hints in this document should quickly bring you up to speed on what the differences are.<br />
<br />
If you're porting Oracle SQL to Postgres SQL for the<br />
[http://acspg.benadida.com/ ACS/pg], you should also be quite familiar with AOLserver Tcl, especially the AOLserver database APIs.<br />
<br />
In this document, we're talking about:<br />
<br />
* Oracle 10g to 11g (most stuff will work down to 8i)<br />
* Oracle 12c is in some aspects different (Multitenent DB) but migration may be even easier<br />
* Postgres 8.4, and sometimes this also works for earlier versions.<br />
<br />
= Grammar Differences =<br />
<br />
There are a handful of grammar differences in Postgres for functionality that is actually the same. ACS/pg attempts to perform these changes automatically, leaving only the major functionality differences to be ported by hand. This is done by db_sql_prep which performs a number of regular expression substitutions on a piece of SQL.<br />
<br />
[[Category:Oracle]]<br />
<br />
== Sysdate ==<br />
<br />
Oracle uses the keyword sysdate to denote the current date and time in server's timezone. Postgres uses 'now'::timestamp for current transaction start date and time in session's timezone, which ACS/pg has conveniently wrapped in a function named sysdate().<br />
<br />
ACS/pg also includes a Tcl procedure named db_sysdate which should be used every time the sysdate term appears. Thus:<br />
<br />
set now [database_to_tcl_string $db "select sysdate from dual"]<br />
<br />
should become<br />
<br />
set now [database_to_tcl_string $db "select [db_sysdate] from dual"]<br />
<br />
== The Dual Table ==<br />
<br />
Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without ''from'' part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.<br />
<br />
== Sequences ==<br />
<br />
Oracle's sequence grammar is sequence_name.nextval.<br />
<br />
Postgres's sequence grammar is nextval('sequence_name').<br />
<br />
In Tcl, getting the next sequence value can be abstracted by calling [db_sequence_nextval $db sequence_name]. In case you need to include a sequence's value in a more complex SQL statement, you can use [db_sequence_nextval_sql sequence_name] which will return the appropriate grammar.<br />
<br />
== Decode ==<br />
<br />
Oracle's handy decode function works as follows:<br />
<br />
decode(expr, search, result [, search, result...] [, default])<br />
<br />
To evaluate this expression, Oracle compares '''expr''' to each '''search''' value one by<br />
one. If '''expr''' is equal to a '''search''', Oracle returns the corresponding '''result'''. If<br />
no match is found, Oracle returns '''default''', or, if '''default''' is omitted, returns<br />
null.<br />
<br />
Postgres doesn't have the same construct. It can be replicated with:<br />
<br />
CASE WHEN expr THEN expr [...] ELSE expr END<br />
<br />
which returns the expression corresponding to the first true predicate. For example:<br />
<br />
CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END<br />
<br />
== NVL ==<br />
<br />
Oracle has another handy function: NVL. NVL returns its first argument if it is not null, otherwise it returns its second argument.<br />
<br />
start_date := NVL(hire_date, SYSDATE);<br />
<br />
The above statement will return SYSDATE if hire_date is null. Postgres (and Oracle) has a function that performs the same thing in a more generalized way: coalesce(expr1, expr2, expr3,....) returns the first non-null expression that is passed to it.<br />
<br />
== Subquery in FROM ==<br />
<br />
PostgreSQL requires a sub-SELECT surrounded by parentheses, and an alias '''must''' be provided for it. The alias is not mandatory for Oracle. <br />
<br />
a query for Oracle:<br />
SELECT * FROM (SELECT * FROM table_a)<br />
in PostgreSQL will look like:<br />
SELECT * FROM (SELECT * FROM table_a) as foo<br />
<br />
= Functional Differences =<br />
<br />
Postgres doesn't have all the functionality of Oracle. ACS/pg is forced to deal with these limitations with specific work-arounds. Almost everything can be done under Postgres, but some features are awaiting new versions of the open-source database.<br />
<br />
== Outer Joins ==<br />
<br />
Outer Joins in Oracle work as follows:<br />
<br />
select a.field1, b.field2<br />
from a, b<br />
where a.item_id = b.item_id(+)<br />
<br />
where the (+) indicates that, if there is no row in table b that matches the correct item_id, the match should still happen, with an empty row from table b. In this case, for example, for all rows in table a where there is no matching row in b, a row will still be returned where a.field1 is correct, but b.field2 is null.<br />
<br />
In Postgresql:<br />
<br />
select a.field1, b.field2<br />
from a<br />
left outer join b<br />
on a.item_id = b.item_id;<br />
<br />
In certain other cases where only aggregate values are pulled out of the outer-joined table, it's possible to not use a join at all. If the original query is:<br />
<br />
select a.field1, sum(b.field2)<br />
from a, b<br />
where a.item_id = b.item_id (+)<br />
group by a.field1<br />
<br />
then the Postgres query can look like:<br />
<br />
select a.field1, b_sum_field2_by_item_id(a.item_id)<br />
from a<br />
<br />
where you've defined the function:<br />
<br />
create function b_sum_field2_by_item_id(integer)<br />
returns integer<br />
as '<br />
DECLARE<br />
v_item_id alias for $1;<br />
BEGIN<br />
return sum(field2) from b where item_id= v_item_id;<br />
END;<br />
' language 'plpgsql';<br />
<br />
== CONNECT BY ==<br />
Postgres doesn't have connect by statements. It does, however, have WITH RECURSIVE. As WITH RECURSIVE is Turing-complete, it is simple to translate CONNECT BY statements into WITH RECURSIVE ones.<br />
<br />
== NO_DATA_FOUND and TOO_MANY_ROWS ==<br />
This exceptions are disabled by default for selects in PLpgSQL. You need to add keyword STRICT after any keyword INTO in all selects, when You need to keep single row checking in stored PLpgSQL code. See [http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW documentation].<br />
<br />
== Empty strings and NULL values ==<br />
<br />
In Oracle empty strings ('') and NULL values in string context are the same till latest versions. You can concatenate NULL result with a string and obtain this string as result. In PostgreSQL you will obtain null in this case. In Oracle You need use IS NULL operator to check whether string is empty. In PostgreSQL You will obtain FALSE for empty string and TRUE for NULL.<br />
<br />
On conversion Oracle to PostgreSQL You will need to carefully analize all Your string handling code and separate NULLs and empty strings.<br />
<br />
== CLOBs ==<br />
Postgres has decent CLOB support in the form of TEXT.<br />
<br />
== BLOBs ==<br />
Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump. Backing up a database that makes use of Postgres large objects requires one to knock down the RDBMS and dump the files in the database directory.<br />
<br />
Don Baccus put together a hack that extends AOLserver's postgres driver with BLOB-like support, by uuencoding/decoding binary files before stuffing them into or extracting them from the database. The resulting objects can be consistently dumped by "pg_dump" while the RDBMS is up and running. There is no need to interrupt service while making your backup.<br />
<br />
To get around the one-block limit on the size of a tuple imposed by Postgres, the driver segments the encoded data into 8K chunks.<br />
<br />
Postgres large objects are scheduled for a major overhaul in summer 2000. Because of this, only the BLOB functionality used by the ACS was implemented.<br />
<br />
To use the BLOB driver extension, you must first create a column of type "integer" with the name "lob" in the table that will store the BLOB, and a trigger on it that calls "on_lob_ref". You must use the name "lob". Here's an example:<br />
<br />
create table my_table (<br />
my_key integer primary key,<br />
lob integer references lobs,<br />
my_other_data some_type -- etc<br />
);<br />
<br />
create trigger my_table_lob_trig before insert or delete or update<br />
on my_table for each row execute procedure on_lob_ref();<br />
<br />
To put a binary file into "my_table":<br />
<br />
set lob [database_to_tcl_string $db "select empty_lob()"]<br />
<br />
ns_db dml $db "begin"<br />
ns_db dml $db "update my_table set lob = $lob where my_key = $my_key"<br />
ns_pg blob_dml_file $db $lob $tmp_filename<br />
ns_db dml $db "end"<br />
<br />
Note that the call to ns_pg to stuff the file into the database MUST be wrapped in a transaction, even if you're not updating any other tables at the same time. The driver will return an error if you don't.<br />
<br />
To return a large object stored in "my_table" to the user:<br />
<br />
set lob [database_to_tcl_string $db "select lob from my_table<br />
where my_key = $my_key"]<br />
ns_pg blob_write $db $lob<br />
<br />
<br />
Note that you don't need to wrap the call to blob_write in a transaction, as the database isn't being modified.<br />
<br />
The large objects are automatically deleted when no longer used. To replace the large object stored in an existing record, just allocate a new one by calling "empty_lob()" and assign the returned key to the "lob" column in your table.<br />
<br />
= External Tools =<br />
A couple of extension tools are available for Oracle migration.<br />
<br />
; [https://www.spectralcore.com/fullconvert Full Convert]<br />
: Database conversion between Oracle (and 30+ other database engines) and PostgreSQL. With everything on localhost, typical throughput over 100k records per second.<br />
; [https://dbconvert.com/oracle/postgresql/ Oracle to Postgres data migration and sync]<br />
: Software is able to convert 1 Million of records in 4-5 minutes. Trigger-based database sync method and simultaneous Bi-directional synchronization help you to manage your data easy and efficiently.<br />
; [http://www.easyfrom.net/ ESF Database Migration Toolkit]<br />
: A toolkit migrates Oracle databae to PostgreSQL in wizard. It connects to Oracle and PostgreSQL database directly, and migrate its table structure, data, indexes, primary keys, foreign keys, comments and so on.<br />
; [http://orafce.projects.pgfoundry.org/ orafce]<br />
: The goal of the project is to implement some functions from Oracle database. Some date functions (next_day, last_day, trunc, round, ...), string functions and some modules (DBMS_ALERT, DBMS_OUTPUT, UTL_FILE, DBMS_PIPE, ...) are implemented now. Functionality was verified on Oracle 10g and module is useful for production work.<br />
; [http://ora2pg.darold.net/ ora2pg]<br />
: Ora2Pg is a Perl module to export an Oracle database schema to a PostgreSQL compatible schema. It connects your Oracle database, extracts its structure, and generates an SQL script that you can load into your PostgreSQL database.<br />
; [http://www.convert-in.com/ora2pgs.htm Oracle to Postgres]<br />
: Program to migrate Oracle databases to PostgreSQL server. It does not use ODBC or any other middleware software. Table structures, data, indexes, primary keys and foreign keys are converted. Command line support allows to script, automate and schedule the conversion process.</div>Tuxinose