Solution to ORACLE long string problem _oracle

Two days ago I encountered a problem in the work, we have a program is used to incrementally extract the table data in EBS, some of the full amount of extraction, that is, first delete the data in the original table, and then extract the data, sample code as follows:

TRUNCATE TABLE ods_emp drop storage;
 INSERT INTO ods_emp SELECT * from EMP;

Another way is an incremental extraction, with the merge statement, which is not written here;

The comrades who have contacted the EBS Inventory module know that the material table in Inv is Mtl_system_item_b, the field of this table is called more than one! I made a mistake before, using the first scheme to extract, which would cause my program to run after the data will be substantially reduced (because the temporary table has only one or two days of data, we understand). This is the problem that happens when you debug the program, and not just the inv that I'm responsible for, but also the other colleagues ' modules. So the project manager angry, the speaker: Today who can't finish, overtime!

I do not want to work overtime, hurriedly put the statement from insert into the merge. After writing a run, the error:

Oh, it turns out that the characters are super long. Here I will not write the previous procedures here, so as not to scare everyone. I 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
*

Line 1th Error:

Ora-06502:pl/sql: Numeric or value error: String buffer too small
ORA-06512: On line 5

I think it's a bad thing to do, just change the varchar2 to long. But the error is still:

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
*

Line 1th Error:

Ora-06502:pl/sql: Numeric or value error: String buffer too small
ORA-06512: On line 5

I checked on the internet earlier is a long type of support 2G size, I do not know why, this time the error. Usually due to work restrictions, very little contact like Long,lob,clob and other large data types, can be carefully studied later.

I was a little worried, and could not think of a suitable way to, can only consult colleagues. One of my colleagues actually met, she defined the string variable as CLOB type, see 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 to 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
 ;

Run Result: v_cnt:14

Summary: 1. Use the CLOB type to handle the case of a string exceeding the length;

Large data types for 2.ORACLE are still to be studied.