Solution to the ORACLE super-long string Problem,

Solution to the ORACLE super-long string Problem,

I encountered such a problem during my work two days ago. We have a program that incrementally extracts table data from EBS, some of which are full extraction, that is, deleting the data in the original table first, then extract the data again. The sample code is as follows:

truncate table ods_emp drop storage; insert into ods_emp select * from emp;

The other method is incremental extraction, which uses the merge statement, so we will not write it here ;)

Comrades who have been familiar with the EBS inventory module know that the material table in INV is MTL_SYSTEM_ITEM_ B, and the field in this table is called one more! I made a mistake before and used the first method to extract the data. This will cause a large amount of data to be reduced after the program runs. (The reason is that the temporary table has only one or two days of data, everyone knows ). This problem occurs when debugging the program, and not only the inv I am responsible for, but also the modules of other colleagues. So the project manager got angry and said, "Who can't finish working overtime today!

I don't want to work overtime. I need to write the statement into MERGE by INSERT. An error is reported after the Migration:

Oh, it turns out that the character is too long. Here I will not write the previous program here, so as not to scare everyone. I will use the following code to simulate this error:

SQL> DECLARE  v_str VARCHAR();  v_cnt NUMBER; BEGIN  v_str := RPAD('select count(*) from emp',);  EXECUTE IMMEDIATE v_str INTO v_cnt;  dbms_output.put_line('v_cnt: '||v_cnt); END; /DECLARE*

Row 3 has an error:

ORA-06502: PL/SQL: Number or value error: string buffer is too small
ORA-06512: In line 5

I think this is not easy. Change varchar2 directly to long. However, an error is reported:

SQL> DECLARE  v_str LONG;  v_cnt NUMBER; BEGIN  v_str := RPAD('select count(*) from emp',);  EXECUTE IMMEDIATE v_str INTO v_cnt;  dbms_output.put_line('v_cnt: '||v_cnt); END; /DECLARE*

Row 3 has an error:

ORA-06502: PL/SQL: Number or value error: string buffer is too small
ORA-06512: In line 5

I checked on the Internet that the LONG type supports 2 GB. I don't know why. This error is reported. At ordinary times, due to work restrictions, there is little exposure to big data types such as LONG, LOB, and CLOB, so you can take a closer look.

I was a little anxious at the time and couldn't find a proper solution. I had to ask my colleagues. A real colleague once met her and defined the string variable as the CLOB type. For details, see the code:

DECLARE v_str CLOB; v_temp_str VARCHAR (); v_cnt NUMBER; BEGIN dbms_lob.createtemporary (v_str, true); -- create a temporary lob v_temp_str: = RPAD ('select count (*)',); dbms_lob.append (v_str, v_temp_str); -- pay the temporary string to v_str v_temp_str: = RPAD ('from emp',); dbms_lob.append (v_str, v_temp_str ); -- pay the temporary string v_str execute immediate v_str INTO v_cnt; dbms_output.put_line ('v _ cnt: '| v_cnt); dbms_lob.freetemporary (v_str); -- release lob END;

Running result: v_cnt: 14

Conclusion: 1. The CLOB type can be used to handle situations where the string is too long;

2. ORACLE's big data type also needs to be studied.