SQL & PL/SQL

Hi,

• I have row which has 6500 characters in a column
• Now this needs to loaded in our Dimension table DIM_A

1. We managed to load data by keeping the data type of the column as LONG
2. Now this DIM_A is used to load another table as FACT_B as INSERT INTO FACT_B(id, big_txt) with AA as (select ID, Big_TXT from DIM_A) select ID , Big_txt from AA. CTE is used for some calculation.
3. This fails even if FACT_B column Big_TXT is kept as long with error as illegal use of LONG
4. We managed to change FACT_B column BIG_TXT datatype to CLOB and Select as INSERT INTO FACT_B(id, big_txt) with AA as (select ID, Big_TXT from DIM_A) select ID , TO_LOB(Big_txt) from AA.
5. Step 4 loaded data and select * from FACT_B works well but,
6. When we try quest like Select id, bug_txt from FACT_B group by big_txt it fails or if Big_txt is placed in Distinct or where with error as buffer size small

Kindly help

CREATE TABLE DIM_A (ID number(10), big_txt LONG);

INSERT INTO DIM_A VALUES (11,'3sdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasasdfsaasdafasdfasdfasdfsasaasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasasdfsaasdafasdfasdfasdfsasaddddddsadafsdafasdfasdfasdfsaasdafasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfadfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasasdfsaasdafasdfasdfasdfsasaasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdafasdfasdfasdfasdadsfasdfasdfawefasdasdfd4sdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasasdfsaasdafasdfasdfasdfsasaasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasasdfsaasdafasdfasdfasdfsasaddddddsadafsdafasdfasdfasdfsaasdafasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfadfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfaasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdfsasasdfsaasdafasdfasdfasdfsasaasdfasdfsaasdafasdfasdfasdfsaasdafasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfasdafasdfasdfasdfsaasdafasfasdfsaasdafasdfasdfa');

CREATE TABLE FACT_B (ID number(10),big_txt clob)

INSERT INTO FACT_B (ID,big_txt) with AA as (select ID, big_txt from DIM_A) Select id to_LOB(big_txt) from AA;