Ora-06502 PL/SQL numeric or value error

ORA-06502: PL/SQL: Number or value error

Hello everyone:

Today, I designed a stored procedure and used dynamic statements. The above error is found and the corresponding solution is found. Express it and hope that others will not take a detour.

The key code is as follows:

Note: vi_date is the input parameter of the process.

Executeimmediate 'insert/* + append */into kr_114_order_all nologging

(Statice_month, -- month

Tele_type, -- business type

Total_sum, -- when the monthly income

User_cnt -- number of users in the current month

)

-- Insert hotel business

Select/* + first_rows */

''' | Vi_date | ''', -- month

''02'', -- business type

Sum (T. total_sum), -- when the monthly income

Count (distinct T. destine_tel) -- number of users

From dw_pai_order t

Where T. leav_date like ''' | vi_date | '%''

And T. order_type = '01''

Union all

-- Insert ticket service

Select/* + first_rows */

''' | Vi_date | ''', -- month

''03'', -- business type

Sum (T. settlement_price), when the monthly income

Count (distinct T. linkman_phono) -- number of users

From dw_ticket_detail_collect t

Where T. date_of_issue like ''' | vi_date | '%''

And T. passenger_ticket = '01''

Union all

-- Insert Food Business

Select/* + first_rows */

''' | Vi_date | ''', -- month

''04 '', -- business type

Sum (T. Monetary), -- when the monthly income

Count (distinct T. roscoe_people_phono) -- number of users

From dw_cate_rice_detail t

Where T. placing_time like ''' | vi_date | '%''

And T. order_status = '01''

Union all

-- Insert cake business

Select/* + first_rows */

''' | Vi_date | ''', -- month

''05 '', -- business type

Sum (T. total_price), -- when the monthly income

Count (distinct T. book_phone) -- number of users

From dw_cake_order t

Where T. order_date like ''' | vi_date | '%''

And T. order_type_id = '1''

Union all

-- Insert agricultural product business

Select/* + first_rows */

''' | Vi_date | ''', -- month

''06'', -- business type

Sum (T. pay_sum), -- when the monthly income

Count (distinct T. destine_tel) -- number of users

From dw_agriculture_order t

Where T. update_time like ''' | vi_date | '%''

And T. order_status = '01''

Union all

-- Insert flowers

Select/* + first_rows */

''' | Vi_date | ''', -- month

''07 '', -- business type

Sum (T. total_sum), -- when the monthly income

Count (distinct T. destine_tel) -- number of users

From dw_flower_order t

Where T. update_time like ''' | vi_date | '%''

And T. order_status = '01''

Union all

-- Insert magazine Business

Select/* + first_rows */

''' | Vi_date | ''', -- month

''09'', -- business type

Sum (T. one_magazine_price), when the monthly income

Count (distinct T. link_phone) -- number of users

From dw_magazine_order t

Where T. order_date like ''' | vi_date | '%''

And T. order_type_id = '1''

Union all

-- Insert a Video Service

Select/* + first_rows */

''' | Vi_date | ''', -- month

''11'', -- business type

Sum (T. amount), -- when the monthly income

Count (distinct T. Phone) -- number of users

From dw_ticket_movie t

Where T. pay_time like ''' | vi_date | '% ''';

The process can run smoothly, and there is no problem with data quality. Later, I was wondering why not define a variable for saving SQL statements and simply execute the variable. Therefore, I performed the following operations.

V_ SQL
Varchar2 (2000 );

V_ SQL: = 'insert/* + append */into .. Like ''' | vi_date |'
% ''';

The statement after execute immediate is all paid to the variable v_ SQL.

Executeimmediate
V_ SQL;

At this moment, the problem arises. The ORA-06502: PL/SQL:
Incorrect number or value ". I am very depressed. Is my logic incorrect? What's going on?

Later, I changed the variable type to varchar2 (100000) and used the length function to check the variable length. The result is 2290. This shows that the length of the variable I defined is small. Next, modify it to varchar2 (3000.

To sum up, when using dynamic statements, you must know the length of the statement to be executed before defining the length of the variable.