Insert in the CloB field

[cleberz]

Good afternoon guys,
I'm doing an insert and I have a CLOB type camp. The value to be inserted is wellmmmmmmmmmmm big, is a very large query and I am doing the following way below, but even so it did not work:

Select all

DECLARE 
  TextoGigante CLOB := ' minha query grande com  
quebras 
de 
linha  '; 
BEGIN 
  INSERT INTO TEMP VALUES (TextoGigante); 
END;

99] Someone can help me?

[dr_gori]

The way you passed works.
See:

Select all

 
SQL> create table thomas( n number, c clob); 
Table created 
  
SQL> 
SQL> declare 
  2    t clob :='teste1 
  3    teste 2 
  4    teste 3'; 
  5  begin 
  6    --insert direto 
  7    insert into thomas values (1, 'teste1 
  8    teste2 
  9    teste3'); 
10    -- 
11    --insert com variavel 
12    insert into thomas values (2, t ); 
13    commit; 
14  end; 
15  / 
PL/SQL procedure successfully completed 
  
SQL> select * from thomas; 
         N C 
---------- -------------------------------------------------------------------------------- 
         1 teste1 
             teste2 
             teste3 
         2 teste1 
             teste 2 
             teste 3 
  
SQL>

What is the error message that is happening?
I think the problem is being the way the variable is being set.
Are you putting a giant text within a procedure?

[cleberz]

I'm doing inside an anonymous block. procedure does not. Query is quite large 1735 lines.
See the error:
Error report -

Select all

ORA-06550: linha 2, coluna 25: 
PLS-00172: string literal too long 
06550. 00000 -  "line %s, column %s:\n%s" 
*Cause:    Usually a PL/SQL compilation error. 
*Action:

[dr_gori]

The problem is that you can not put a text block so large within a plsql block.
See this example:

Select all

 
declare  
  t clob; 
begin 
  t:=   lpad('1', 4000, '1'); 
  t:=t||lpad('2', 4000, '2'); 
  t:=t||lpad('3', 4000, '3'); 
  t:=t||lpad('4', 4000, '4'); 
  t:=t||lpad('5', 4000, '5'); 
  t:=t||lpad('6', 4000, '6'); 
  t:=t||lpad('7', 4000, '7'); 
  t:=t||lpad('8', 4000, '8'); 
  t:=t||lpad('9', 4000, '9');               
  t:=t||lpad('0', 4000, '0'); 
  --insert com variavel 
  insert into thomas values (3, t ); 
  commit; 
end;

See how he inserted 40,000 characters:

Select all

SQL> select a.*, length(a.c) from thomas a; 
         N C                                       LENGTH(A.C) 
---------- --------------------------------------- ----------- 
         1 teste1                                  24 
             teste2                                     
             teste3                                     
         2 teste1                                  26 
             teste 2                                   
             teste 3                                 
         3 111111111111111111111111111111111111111 40000 
 
SQL> 

]
In summary, I believe you will have to break in smaller blocks that your super text.
or put the information within a table.
is an idea.

[cleberz]

Brother, I start in blocks, as you said, and it worked perfectly. Thank you very much. I broke head yesterday all day. Thanks! Hug!