Execute Select * From Dual - Error Ora-06502

[jucruzjc]

Good morning people!!

to a little problem and I do not know what else to do ...
When running the script below ... He gives an error! Can anybody help me?

Select all

 
variable   vdatainicio   varchar2(11); 
execute select '01-' || to_char( add_months(sysdate,-1),'MON-YYYY') into :vdatainicio from dual; 

ORA-06502: PL / SQL: Numeric or Value Error: Character String Buffer Too Small

Bjão!

[Trevisolli]

Jú, alright?
Try to change the size of your variable to 20, for example, and do a test there, because here it worked correctly.

Select all

 
variable   vdatainicio   varchar2(20);  
execute select '01-' || to_char( add_months(sysdate,-1),'MON-YYYY') into :vdatainicio from dual;  

Anything, send us to us.

[jucruzjc]

Hi Trevisolli !!!
Is everything okay, yes and you?

then .. I rode and now gave this error:

Select all

[1]: (Error): Error executing statement: OCI_INVALID_HANDLE

: Cry:

99]]
Hugs!

[jucruzjc]

Hi ...
Sô Dinovu!

I rode again here and still continues the error of Buffer Too Small ....
I do not know what else to do!

Select all

 
execute select '&1' into :v_planta from dual; 
execute select '01-' || to_char( add_months(sysdate,-1),'MON-YYYY') into :vdatainicio from dual; 
execute select  to_char(last_day(add_months(sysdate,-1)),'DD-MON-YYYY') into :vdatafim from dual; 

Select all

[2]: (Error): Error executing statement: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 2 
[3]: (Error): Error executing statement: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 2 
[4]: (Error): Error executing statement: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 2

Thanks for the help !!!

[Trevisolli]

Jú, how can you give us the declaration of the variables to take a test?

[jucruzjc]

Oie !!

Yes!

Select all

 
variable   v_planta      varchar2(50); 
variable   vdatainicio   varchar2(20); 
variable   vdatafim      varchar2(20); 
 
execute select '&1' into :v_planta from dual; 
execute select '01-' || to_char( add_months(sysdate,-1),'MON-YYYY') into :vdatainicio from dual; 
execute select  to_char(last_day(add_months(sysdate,-1)),'DD-MON-YYYY') into :vdatafim from dual; 

Thanks for the help !!!

Bjão!

[Trevisolli]

Jú, td well?

I tested all cases here again and OK, as below:

Select all

 
 
variable   v_planta      varchar2(50);  
 
-- sem aspas simples no &1 
execute select &1 into :v_planta from dual;  
-- valor passado para &1: to_date('01/01/2000','dd/mm/rrrr') 
select :v_planta from dual; 
 
 
 
-- segundo caso 
SQL> variable   vdatainicio   varchar2(20);  
SQL> execute select '01-' || to_char( add_months(sysdate,-1),'MON-YYYY') into :vdatainicio from dual 
; 
 
PL/SQL procedure successfully completed. 
 
SQL> select :vdatainicio from dual; 
 
:VDATAINICIO 
-------------------------------- 
01-JAN-2009 
 
SQL>  
 
 
 
-- terceiro caso 
SQL> variable   vdatafim      varchar2(20);  
SQL>  
SQL> execute select  to_char(last_day(add_months(sysdate,-1)),'DD-MON-YYYY') into :vdatafim from dua 
l; 
 
PL/SQL procedure successfully completed. 
 
SQL> select :vdatafim from dual; 
 
:VDATAFIM 
-------------------------------- 
31-JAN-2009 
 
SQL>  
SQL>  

Can you test there again in this way?
If it does not matter, we would have to check maybe something in your environment there.
later, if it does not work, go to us your environment to test in the same scheme, can it be?

Bjaum,