Oracle migration to PG problem Collation __oracle

This post is transferred from Http://bbs.pgsqldb.com, and two posts are joined together.

A lot of content is online search information, I just tidy up, there are missing places, welcome to add
First Use tool (ORA2PG) to automatically convert
Because of the large number of background programs in this project, the stored procedure + trigger has approximately 150,000 lines of code.
This tool can be used to automate some of the syntax differences between Oracle and Pgsql, but not all, and the rest need to be manually modified.

Oracle Syntax →postgresql syntax
1, Varchar2→varchar
2, Date→timestamp
3, Sysdate→localtimestamp
4, Oracle "and Null are the same, but Pgsql is different, so you need to" modify to null
5. String Connector | |
Oracle: ' A ' | | Null result is ' a '
Pgsql: ' A ' | | NULL result is NULL
So use the concat () function instead
6, Trunc (time) →date_trunc ()
7, To_char, To_number, to_date pgsql All need to specify the format
8, Decode→case
9, Nvl→coalesce ()
10, outer connection (+) →left (right) join
11, goto statement →pgsql not supported
12, Pgsql does not support procedure and package, all need to rewrite the function
When package has a global variable, it's troublesome to modify it, and we are passing it with a temporary table.
14, Commit,rollback; Savepoint→pgsql does not support
15, Oracle's system package, such as Dbms_output,dbms_sql,util_file,util_mail→pgsql does not support
16, exception handling methods are different
17, trigger syntax is different
18, the date of the addition and subtraction calculation syntax is different.

13, cursor Properties
%found→found
%notfound→not found
%isopen→pgsql does not support
%rowcount→pgsql does not support
Other differences are also found in cursor, see below:


13.1, the Pgsql cursor name is the global

For example, function A and function B have a cursor of the same name, when a opens the cursor and then calls B, and when B opens the cursor with the same name, it throws an exception.
This problem does not occur in Oracle.
WORKAROUND: Define the cursor with an implicit declaration, or ensure that all programs have cursor unique names.

13.2. The number of Cursor,loop cycles used for update in Pgsql is likely to be changed

such as a piece of code

For rec in (SELECT * out of employee for update) loop
Update employee Set dep_no = ' Test ';
End Loop;

As is often understood, if there are 100 records in the employee, the loop executes 100 times.
But pgsql only executes 1 times, because an UPDATE statement updates all the records, as if it were a lock.
If you remove the for update, it is executed 100 times.

This is related to the statement and use of cursor, see the following practice:
Tested with "for update" and not added, the result of the test is the number of times to update the cursor result set.

The experiment is as follows:


--1 Create tables and initial data
Skytf=> CREATE TABLE employee (ID serial,emp_name varchar (), dep_no int4);
Notice:create TABLE would CREATE implicit sequence "Employee_id_seq" for serial column "Employee.id"
CREATE TABLE

Skytf=> INSERT into employee (EMP_NAME,DEP_NO) Select Generate_series (1,3) | | A ', 1;
INSERT 0 3

Skytf=> INSERT into employee (EMP_NAME,DEP_NO) Select Generate_series (4,10) | | B ', 2;
INSERT 0 7

Skytf=> SELECT * from employee;
ID | Emp_name | Dep_no
----+----------+--------
1 | 1a | 1
2 | 2a | 1
3 | 3a | 1
4 | 4b | 2
5 | 5b | 2
6 | 6b | 2
7 | 7b | 2
8 | 8b | 2
9 | 9b | 2
10 | 10b | 2
(Ten rows)


--2 Create a function without a for update property
CREATE or replace FUNCTION fun_employee () RETURNS INTEGER as $$
DECLARE
Update_flag INTEGER;
Rec Refcursor;
BEGIN

Update_flag: = 0;

For rec in (SELECT * from employee) loop
Update employee Set dep_no = 3;
update_flag:=update_flag+1;

RAISE NOTICE ' The Update_flag is% ', update_flag;
End Loop;
return 0;
End;
$$ LANGUAGE ' Plpgsql ';


--3 Create a function, plus the FOR Update property
CREATE or replace FUNCTION fun_employee_for_update () RETURNS INTEGER as $$
DECLARE
Update_flag INTEGER;
Rec Refcursor;
BEGIN

Update_flag: = 0;

For rec in (SELECT * out of employee for update) loop
Update employee Set dep_no = 3;
update_flag:=update_flag+1;

RAISE NOTICE ' The Update_flag is% ', update_flag;
End Loop;
return 0;
End;
$$ LANGUAGE ' Plpgsql ';


--4 Test Fun_employee ()
Skytf=> select Fun_employee ();
Notice:the Update_flag is 1
Notice:the Update_flag is 2
Notice:the Update_flag is 3
Notice:the Update_flag is 4
Notice:the Update_flag is 5
Notice:the Update_flag is 6
Notice:the Update_flag is 7
Notice:the Update_flag is 8
Notice:the Update_flag is 9
Notice:the Update_flag is 10
Fun_employee
--------------
0
(1 row)


--5 Test Fun_employee_for_update ()
Skytf=> select Fun_employee_for_update ();
Notice:the Update_flag is 1
Notice:the Update_flag is 2
Notice:the Update_flag is 3
Notice:the Update_flag is 4
Notice:the Update_flag is 5
Notice:the Update_flag is 6
Notice:the Update_flag is 7
Notice:the Update_flag is 8
Notice:the Update_flag is 9
Notice:the Update_flag is 10
Fun_employee_for_update
-------------------------
0
(1 row)

Note: According to the test results output, two functions have executed 10 update statements.


--6
Change the function of what is cursor to the following declaration, and perform only once

CREATE or replace FUNCTION fun_employee_for_update () RETURNS INTEGER as $$
DECLARE
Update_flag INTEGER;
CUR cursor is select * from employee for update;
Rec Employee%rowtype;
BEGIN

Update_flag: = 0;

For rec in cur loop
Update employee Set dep_no = 3;
update_flag:=update_flag+1;

RAISE NOTICE ' The Update_flag is% ', update_flag;
End Loop;
return 0;
End;
$$ LANGUAGE ' Plpgsql ';


Other than that:
%rowcount PG in Oracle can be Integer_var = Row_count with Get diagnostics;
Oracle in our debugging process often with output.putline to output the value of some variables in PG can be used RAISE NOTICE ' I is% ', I; To output the variables you want to output.