Kbase P37956: Records containing null values do not insert correctly into Oracle using bulk insert feature
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/09/2008 |
|
Status: Verified
SYMPTOM(s):
Records do not insert correctly into Oracle
Records are inserted using bulk insert
Records that don not insert correctly contain ? (unknown) values
Records containing null values are inserted as not containing null values
Records not containing null values are inserted as containing null values
Records are read from Progress database then bulk inserted into Oracle database
Simplied code used to perform bulk insert
DEFINE VARIABLE v_handle AS INTEGER NO-UNDO.
RUN STORED-PROC SEND-SQL-STATEMENT
v_handle = PROC-HANDLE ("--Bulk-insert Start").
CLOSE STORED-PROC SEND-SQL-STATEMENT WHERE PROC-HANDLE = v_handle.
FOR each progress_table no-lock:
create oracle_table.
assign oracle_table.field1 = progress_table.field1
oracle_table.field2 = progress_table.field2.
END.
RELEASE progress_table. /* Flush out the last one before --Bulk-insert End */
RUN STORED-PROC SEND-SQL-STATEMENT
v_handle = PROC-HANDLE ("--Bulk-insert End").
CLOSE STORED-PROC SEND-SQL-STATEMENT WHERE PROC-HANDLE = v_handle.
Using bulk insert from dictionary menu produces the same result
Regular insert works correctly
FACT(s) (Environment):
Progress 9.1C
Progress 9.1D
Oracle DataServer
CAUSE:
This is a known issue being investigated by Development
FIX:
Use regular insert or modify the code checking null values of records read from progress database, modify the records to not contain null values, then bulk insert into Oracle
For example,
DEFINE VARIABLE v_handle AS INTEGER NO-UNDO.
RUN STORED-PROC SEND-SQL-STATEMENT
v_handle = PROC-HANDLE ("--Bulk-insert Start").
CLOSE STORED-PROC SEND-SQL-STATEMENT WHERE PROC-HANDLE = v_handle.
FOR each progress_table no-lock:
create oracle_table.
assign oracle_table.field2 = IF progress_table.field2 = ? then "" ELSE
progress_table.field2.
END.
RELEASE progress_table. /* Flush out the last one before --Bulk-insert End */
RUN STORED-PROC SEND-SQL-STATEMENT
v_handle = PROC-HANDLE ("--Bulk-insert End").
CLOSE STORED-PROC SEND-SQL-STATEMENT WHERE PROC-HANDLE = v_handle.