ORA-06502 assigning values from SQL to PL/SQL Variables

The results returned by the recent SQL query cause a ORA-06502 error to the PL/SQL variable. The error description is ORA-06502: PL/SQL: numeric or value error: character string buffer too small. obviously, the length of the character variable definition is not enough. It is added to 20 to 100. Continue to 06502. What is the problem?

1. Problem description-the problem is that in a package, there are two parameter cursors, one parent cursor and one child cursor, when the output result of the parent cursor is passed to the child game tag, the prompt value is too large-the original SQL statement of the parent cursor is long and complex, to simplify the following description, construct the environment --> Oracle version goex_admin @ cnmmbo> select * from V $ version; banner implements Oracle Database 10g Release 10.2.0.3.0-64bit production PL/SQL release 10.2.0.3.0-production core 10.2.0.3.0 production TNS for Linux: Version 10.2.0.3.0-production n Lsrtl version 10.2.0.3.0-production --> Create a test table t and insert three records goex_admin @ cnmmbo> Create Table T (DT char (8); table created. goex_admin @ cnmmbo> insert into T select '000000' from dual; 1 row created. goex_admin @ cnmmbo> insert into T select '000000' from dual; 1 row created. goex_admin @ cnmmbo> insert into T select '000000' from dual; 1 row created. goex_admin @ cnmmbo> commit; Commit complete. --> Use The query output results of the returned ora-06502 error --> the query statement is also relatively simple, take the minimum value of the DT column T In the table, in the outer query assigned to the variable --> outer subquery seems to be superfluous, purely simulate the original environment goex_admin @ cnmmbo> declare 2 tradedate_out char (100); 3 begin 4 select tradedate 5 into tradedate_out 6 from (select Min (DT) as tradedate from T) D; 7 8 dbms_output.put_line ('trade _ date = '| tradedate_out); 9 end; 10/declare * error at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffe R too small ORA-06512: At line 42, rewrite query --> if we remove the outer query, there is no similar error --> so this way, does the value cause variation when the subquery reaches the outer layer? Goex_admin @ cnmmbo> declare 2 tradedate_out char (100); 3 begin 4 select Min (DT) into tradedate_out from t; 5 6 dbms_output.put_line ('trade _ date = '| tradedate_out ); 7 end; 8/trade_date = 20121218 PL/SQL procedure successfully completed.3. Try to execute the query in different versions --> The following is a simulated SQL statement in Oracle 11G> select * from V $ version; banner implements Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-production PL/SQL release 11.2.0.1.0-production core 11.2.0.1.0 production TNS for Linux: Version 11.2.0.1.0-production nlsrtl version 11.2.0.1.0-production --> author: robinson --> blog: http://blog.csdn.net/robinson_0612 SQL> Create Table T (DT char (8); table created. SQL> insert into T select '000000' from dual; 1 row created. SQL> insert into T select '000000' from dual; 1 row created. SQL> insert into T select '000000' from dual; 1 row created. SQL> commit; Commit complete. --> it is also the original statement, but this problem is not found in Oracle 11g SQL> set serveroutput on; SQL> declare 2 tradedate_out char (100 ); 3 begin 4 select tradedate 5 into tradedate_out 6 from (select Min (DT) as tradedate from T) D; 7 8 dbms_output.put_line ('trade _ date = '| tradedate_out ); 9 end; 10/trade_date = 20121218 PL/SQL procedure successfully completed.4. Solution --> to keep the original query statement unchanged, solve the problem after adding the trim function to the outer query goex_admin @ cnmmbo> declare 2 tradedate_out char (100); 3 begin 4 select trim (tradedate) 5 into tradedate_out 6 from (select Min (DT) as tradedate from T) D; 7 8 dbms_output.put_line ('trade _ date = '| tradedate_out); 9 end; 10/trade_date = 20121218 PL/SQL procedure successfully completed.5, Oracle bug --> Metalink slide for a moment. It's a bit rewarding. It's also a bug, sweat .... BUG: 5564384 ORA-06502 assigning values from SQL to PL/SQL variables component: RDBMS fixed Ver (s): 10204 111 symptom (s ): -When executing a SQL that contains a concatenation/max of char values and assigning the result to a PL/SQL output variable an ORA-6502 may be raised. for example: Create Table teste (T1 char (1), T2 char (1), T3 char (1); insert into teste values ('s','s ', 'S '); declare v_dummy varchar2 (3); begin select max (x) into v_dummy from (select T1 | T2 | T3 X from teste); end; /^ ORA-06502: PL/SQL: numeric or value error: character string buffer too small available workaround (s): declare the PLSQL output variables as varchar2 (4000); References: Note: 5564384.8 bug 5564384-ORA-6502 assigning values from SQL to PLSQL Variables

More references

DML error logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan