Troubleshooting of inconsistent data query results in ORACLE databases

Troubleshooting of inconsistent data query results in ORACLE databases

I. Problem Description
In a software development project, you need to create ten identical employee information tables in the ORACLE database tb_employeeinfo0 ~ Tb_employeeinfo9 and create a stored procedure for inserting data into these ten tables. The ORACLE database is installed in the Linux operating system.
For ease of operation, developers can create tables and stored procedures on PL/SQL Developer (ORACLE database development software. Then, developers use SQL statements to insert data into a data table in PL/SQL Developer software. The data is successfully inserted into the database using the select statement.
After a period of time, the developer logs on to the database using the command line method in Linux, and queries data from the employee information table using the select statement. It is found that the number of data entries is 0. "Is the data deleted ?" The developer is confused. He used the select statement on PL/SQL Developer to query data from the employee information table and found that the data exists.
What is the problem?

Ii. troubleshooting
We restored the entire problem process in the self-testing environment of the development team. Let's take a look at it step by step.
The table creation statement of the employee information table is as follows:

-- tb_employeeinfo0~9begin     declare i int;tmpcount int;tbname varchar2(50);strsql varchar2(1000);     begin         i:=0;         while i<10 loop         begin             tbname := 'tb_employeeinfo'||to_char(i);             i := i+1;             select count(1) into tmpcount from user_tables where table_name = Upper(tbname);             if tmpcount>0 then             begin                 execute immediate 'drop table '||tbname;                 commit;             end;             end if;             strsql := 'create table '||tbname||             '(                  employeeno      varchar2(10)  not null,         -- employee number                  employeeage     int           not null          -- employee age              )';             execute immediate strsql;                strsql := 'begin                   execute immediate ''drop index idx1_'||tbname || ' '''                  || ';exception when others then null;                  end;';             execute immediate strsql;             execute immediate 'create unique index idx1_'||tbname||' on '||tbname||'(employeeno)';         end;         end loop;     end;end;/

The stored procedure statement for data insertion is as follows:

begin    declare v_i int;v_procname varchar(50);v_employeeinfotbl varchar(50);strsql varchar(4000);begin    v_i := 0;    while v_i < 10 loop        v_procname        := 'pr_insertdata'||substr(to_char(v_i),1,1);        v_employeeinfotbl := 'tb_employeeinfo'||substr(to_char(v_i),1,1);        v_i := v_i + 1;        strsql := 'create or replace procedure '||v_procname||'(            v_employeeno   in   varchar2,            v_employeeage  in   int,            v_retcode      out  int      -- 0_success, 1,2_fail        )        as            v_employeecnt     int;        begin            v_retcode := 0;            select count(*) into v_employeecnt from '||v_employeeinfotbl||' where employeeno = v_employeeno;            if v_employeecnt > 0 then       -- the employeeno is already in DB            begin                v_retcode := 1;                return;            end;            else                            -- the employeeno is not in DB            begin                insert into '||v_employeeinfotbl||'(employeeno, employeeage) values(v_employeeno, v_employeeage);            end;            end if;        exception when others then            begin                rollback;                v_retcode := 2;                                return;            end;        end;';        execute immediate strsql;    end loop;    end;end;/

After executing the preceding SQL statement (Note: Create a table first and then create a stored procedure) on pl/SQL Developer, we can use the following SQL statement to insert data to the table tb_employeeinfo6:

set serveroutput ondeclare v_retcode     int;beginpr_insertdata6('123456', 25, v_retcode);dbms_output.put_line( v_retcode);end;/

Run the "select * from tb_employeeinfo6;" statement to query data. The result is as follows:

SQL> select * from tb_employeeinfo6;EMPLOYEENO      EMPLOYEEAGE---------------------------123456          25

The data is successfully inserted.

Next, we use the following command line to log on to the ORACLE database from the Linux system (Note: username refers to the database user name, password refers to the database password, and databaseservername refers to the Database Service name ):

sqlplus /nologconnect username/password@databaseservername

Then execute the following query statement:

select * from tb_employeeinfo6;

The returned value is null, that is, there is no data in the data table.
It's strange. Why are the execution results on both sides of the same query statement inconsistent?
Let's go back and read the table creation and stored procedure code in detail. We didn't see any obvious problems. We told an old employee who has been working for many years to help us analyze the cause of the problem. After reading our SQL statements in detail, he pointed out that the code of the stored procedure was a bit problematic. After inserting data into the table, he forgot to submit it. That is to say, "insert…" In the Stored Procedure ..." After the statement, add "commit ;".
Is it because of this "commit;" statement?

Iii. Cause
Modify the Stored Procedure Code as follows:

begin    declare v_i int;v_procname varchar(50);v_employeeinfotbl varchar(50);strsql varchar(4000);begin    v_i := 0;    while v_i < 10 loop        v_procname        := 'pr_insertdata'||substr(to_char(v_i),1,1);        v_employeeinfotbl := 'tb_employeeinfo'||substr(to_char(v_i),1,1);        v_i := v_i + 1;        strsql := 'create or replace procedure '||v_procname||'(            v_employeeno   in   varchar2,            v_employeeage  in   int,            v_retcode      out  int      -- 0_success, 1,2_fail        )        as            v_employeecnt     int;        begin            v_retcode := 0;            select count(*) into v_employeecnt from '||v_employeeinfotbl||' where employeeno = v_employeeno;            if v_employeecnt > 0 then       -- the employeeno is already in DB            begin                v_retcode := 1;                return;            end;            else                            -- the employeeno is not in DB            begin                insert into '||v_employeeinfotbl||'(employeeno, employeeage) values(v_employeeno, v_employeeage);                commit;            end;            end if;        exception when others then            begin                rollback;                v_retcode := 2;                                return;            end;        end;';        execute immediate strsql;    end loop;    end;end;/

Then, we run the preceding SQL statement on PL/SQL Developer and use the following SQL statement to insert data to the tb_employeeinfo9 table:

set serveroutput ondeclare v_retcode     int;beginpr_insertdata9('123469', 25, v_retcode);dbms_output.put_line( v_retcode);end;/

Similarly, execute the "select * from tb _ employeeinfo9;" Statement on the software to query data. The results are as follows:

SQL> select * from tb_employeeinfo9;EMPLOYEENO     EMPLOYEEAGE--------------------------123469         25

Then, run the "select * from tb_employeeinfo9;" Statement on Linux. The result is as follows:

SQL> select * from tb_employeeinfo9;EMPLOYEENO   EMPLOYEEAGE------------------------123469       25

The data is successfully inserted into the employee information table.

Iv. Summary
The following is a summary of the problems caused by "commit:
First, you must understand the syntax rules before writing code. Do not let a small problem cause the exception of the entire software function.
Second, experience is very important in software development. A new employee can solve the problem after several hours. A veteran may be able to solve it in just a few minutes. Therefore, when encountering problems that cannot be solved by ourselves, we must be diligent in opening our mouths and ask more experienced employees.

My public account: zhouzxi. Please scan the following QR code: