Different execution results of PLSQL programs in Oracle8i and 9i

When upgrading ORACLE databases from 8i to 9i and above, it is generally considered that the original PLSQL program should be fully compatible, that is, the running process and the running result are completely consistent. Unfortunately, this is not the case. Due to the oracle plsql engine upgrade, it makes changes to some code interpretations, resulting in different running results for some code. You must pay attention to it when upgrading the database. Otherwise, immeasurable losses and irreparable disasters will occur.

1. PLSQL table passed as a parameter

First read the following code to run the result in ORACLE 8i and 9i.

declaretype test_rec is record(col_1 varchar2(100));type test_tbl is table of test_rec index by binary_integer;l_tbl test_tbl;procedure change_valueisbeginl_tbl(1).col_1 := 'I am changed!';end;procedure sub_test(pi_str in varchar2)isbegindbms_output.put_line('before: '||pi_str);change_value;dbms_output.put_line('after : '||pi_str);end;beginl_tbl(1).col_1 := 'I am ok!';sub_test(l_tbl(1).col_1);exceptionwhen others thendbms_output.put_line(sqlerrm);end;

The sample code is very simple, that is, a member variable of the PLSQL table is treated as a parameter to another process. This process changes the value of the original PLSQL table, but the value of the input parameter cannot be changed, the value of the input parameter cannot be changed). Before and after the change, the value of the input parameter changes IN Oracle 8i and 9i.

Running result:

Oracle 8i Oracle 9ibefore: I am ok! before: I am ok!after : I am ok! after : I am changed!

Obviously, the running results are different! In Oracle 8i, the value of input parameters does not change before and after the value of the original PLSQL table. in Oracle 9i, the value of input parameters is changed.

IN Oracle 8i, all parameters represented as IN are passed with values, including PLSQL table parameters. When Oracle 9i was reached, they thought that parameter passing of PLSQL table type should be passed for reference. This problem was caused by such modification on PLSQL engine.

Let's recall the following: Passing values means that two variables pass real values and each has a different memory space, which is equivalent to copying a variable. Each variable is its primary and independent from each other. Transferring reference means that the two variables pass the address of the memory space and point to the same memory space. If the value in the memory space is changed, then the values of the two variables are changed.

It is easier to understand the cause of the problem and read the previous sample code. The same program produces different running results after Database Upgrade. We believe that everyone can understand the danger level of this problem and must pay attention to it.

2. the return value of PLSQL table type is NO_DATA_FOUND.

See the following code:

DECLAREl_test VARCHAR2(10);type test_rec is record (col_a varchar2(100));TYPE test_tab IS TABLE OF test_rec INDEX BY BINARY_INTEGER;l_test_tab test_tab;FUNCTION return_tbl ( pi_dummy IN VARCHAR2 )RETURN test_tabISl_tbl test_tab;BEGINl_tbl.DELETE;l_tbl(1).col_a := 'I am ok!';RETURN l_tbl;EXCEPTIONWHEN OTHERS THENl_tbl.DELETE;RETURN l_tbl;END;BEGINl_test_tab := return_tbl('');l_test := l_test_tab(1).col_a;DBMS_OUTPUT.PUT_LINE ( 'before: ' || l_pol_num );l_test := return_tbl('')(1).col_a;DBMS_OUTPUT.PUT_LINE ( 'after : ' || l_test );EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE ( 'NO_DATA_FOUND exception!' );END;

This Code indicates that a function returns a PLSQL table type value, and then displays it on the screen in different reference methods.

Running result:

Oracle 8i Oracle 9ibefore: I am ok! before: I am ok!after : I am ok! NO_DATA_FOUND exception!

In Oracle 8i, it is legal to directly use the function name and subscript to access the member variables of the PLSQL table, but in Oracle 9i, this method will lead to a runtime NO_DATA_FOUND accident, different running results are generated.

Oracle does not provide a strict explanation for this problem, but it just points out that this method is no longer legal. We also need to pay attention to this issue to avoid falling into this trap.

Database upgrades lead to different running results of PLSQL programs. This problem is worrying, and the way ORACLE operates is a headache. If there are many FORM, REPORT, and PLSQL stored procedures, the workload caused by this problem is huge, and the subsequent testing also consumes a lot of resources.

(