Main Region

1 error has occurred Context Men u SQL Query gives ORA-01461: can bind a LONG value only for insert into a LONG column
1 answer | 406 views
AW
Alan W
Jan 24, 2014 14:55 +00:00

Hi Matt,

I have a context menu SQL query which is fairly large (see below), when I try to apply changes to this I get the above error. If I remove one of the Union Alls (e.g. menu_id 4) it's all OK. It seems I'm hitting a query size limit, but surprised that LONGs are being referenced. Any ideas?

Thanks

Alan

select * from (select * from ( with comment_id as (select forecast_comment_id from gcf_forecast_comment where reporting_name=:P50_CONTEXT_REPORTING_NAME and solution_set=:P50_CONTEXT_SOLUTION_SET and solution_detail=:P50_CONTEXT_SOLUTION_DETAIL and fiscal_year=:P50_FORECAST_YEAR) select -1 menu_id,null menu_pid, 'Edit comments for '|| :P50_CONTEXT_SOLUTION_DETAIL text, 'f?p='||:APP_ID||':115:'||:APP_SESSION||'::'||:DEBUG||'::P115_FORECAST_COMMENT_ID:'||forecast_comment_id url ,0 display_seq,'fx-icon page-white-text' css_class ,'View existing comments for this solution' info,null custom from comment_id union all select 0 menu_id,null menu_pid, 'Add new comments for '||:P50_CONTEXT_SOLUTION_DETAIL text, 'f?p='||:APP_ID||':115:'||:APP_SESSION||'::'||:DEBUG||'::P115_FORECAST_COMMENT_ID,P115_REPORTING_NAME,P115_SOLUTION_SET,P115_SOLUTION_DETAIL,P115_FISCAL_YEAR,P115_JAN_COMMENT,P115_FEB_COMMENT,P115_MAR_COMMENT,P115_APR_COMMENT,P115_MAY_COMMENT,P115_JUN_COMMENT,P115_JUL_COMMENT,P115_AUG_COMMENT,P115_SEP_COMMENT,P115_OCT_COMMENT,P115_NOV_COMMENT,P115_DEC_COMMENT:'||NULL||','||:P50_CONTEXT_REPORTING_NAME||','|| :P50_CONTEXT_SOLUTION_SET||','||:P50_CONTEXT_SOLUTION_DETAIL||','||:P50_FORECAST_YEAR||','||NULL||','||NULL||','||NULL||','||NULL||','||NULL||','||NULL||','||NULL||','||NULL||','||NULL||','||NULL||','||NULL||','||NULL url ,0 display_seq,'fx-icon page-white-text' css_class ,'Create new comments for this solution' info,null custom from dual order by display_seq)) where rownum=1 union all

select 1 menu_id,null menu_pid, 'Dec: '||dec_comment text, NULL url, --'f?p='||:APP_ID||':115:'||:APP_SESSION||'::'||:DEBUG||'::P115_FORECAST_COMMENT_ID:'||forecast_comment_id url, 1 display_seq,'fx-icon page-white-text' css_class ,'View existing comments for this solution' info,null custom from gcf_forecast_comment where reporting_name=:P50_CONTEXT_REPORTING_NAME and solution_set=:P50_CONTEXT_SOLUTION_SET and solution_detail=:P50_CONTEXT_SOLUTION_DETAIL and fiscal_year=:P50_FORECAST_YEAR and dec_comment is not null union all
select 2 menu_id,null menu_pid, 'Nov: '||nov_comment text, NULL url, --'f?p='||:APP_ID||':115:'||:APP_SESSION||'::'||:DEBUG||'::P115_FORECAST_COMMENT_ID:'||forecast_comment_id url, 1 display_seq,'fx-icon page-white-text' css_class ,'View existing comments for this solution' info,null custom from gcf_forecast_comment where reporting_name=:P50_CONTEXT_REPORTING_NAME and solution_set=:P50_CONTEXT_SOLUTION_SET and solution_detail=:P50_CONTEXT_SOLUTION_DETAIL and fiscal_year=:P50_FORECAST_YEAR and nov_comment is not null union all
select 3 menu_id,null menu_pid, 'Oct: '||oct_comment text, NULL url, --'f?p='||:APP_ID||':115:'||:APP_SESSION||'::'||:DEBUG||'::P115_FORECAST_COMMENT_ID:'||forecast_comment_id url, 1 display_seq,'fx-icon page-white-text' css_class ,'View existing comments for this solution' info,null custom from gcf_forecast_comment where reporting_name=:P50_CONTEXT_REPORTING_NAME and solution_set=:P50_CONTEXT_SOLUTION_SET and solution_detail=:P50_CONTEXT_SOLUTION_DETAIL and fiscal_year=:P50_FORECAST_YEAR and oct_comment is not null union all
select 4 menu_id,null menu_pid, 'Sep: '||sep_comment text, NULL url, --'f?p='||:APP_ID||':115:'||:APP_SESSION||'::'||:DEBUG||'::P115_FORECAST_COMMENT_ID:'||forecast_comment_id url, 1 display_seq,'fx-icon page-white-text' css_class ,'View existing comments for this solution' info,null custom from gcf_forecast_comment where reporting_name=:P50_CONTEXT_REPORTING_NAME and solution_set=:P50_CONTEXT_SOLUTION_SET and solution_detail=:P50_CONTEXT_SOLUTION_DETAIL and fiscal_year=:P50_FORECAST_YEAR and sep_comment is not null union all select * from ( with rbo_changes_log as (select distinct id,event_date from gcf_forecast_calc_log_text where reporting_nme=:P50_CONTEXT_REPORTING_NAME and solution_set=:P50_CONTEXT_SOLUTION_SET order by id desc) select id menu_id,null menu_pid, 'View log for ' || to_char(event_date,'DD-MON-YYYY HH24:MI:SS') text, apex_util.prepare_url('f?p='||:APP_ID||':110:'||:APP_SESSION||'::'||:DEBUG||'::P110_LOG_ID:'||id) url ,rownum display_seq,'fx-icon pencil' css_class ,null info,null custom from rbo_changes_log)

4 comments
LS
Lata S May 1, 2015 03:05 +00:00 

Hi, Is there any way to resolve this. I am also a big query for context menu and not able to save it. please suggest...

Thanks in Advance, Lata

MN
Matt Nolan May 2, 2015 07:48 +00:00 

Create a view to remove the bulk of the SQL text.

LS
Lata S May 4, 2015 04:18 +00:00 

Thanks Matt, But In our query I have dynamic where clause which is based on the selection of row i.e. Node Id item.. In our case, we don't have anything big other than dynamic where clause which can't be escaped by using View.

Thanks

MN
Matt Nolan May 4, 2015 07:11 +00:00 

You will have to work around this issue since there is a 4K limit and this will not change as it is enforced by APEX. You need to get more creative at trying to solve this problem.

PR
Peter Raganitsch
Jan 25, 2014 20:59 +00:00

Hi Alan,

this is APEX's way to tell you the length of the string exceeded it's 4k limit.

4k is the maximum we can store in a plugin parameter currently.

0 comments
You must log in or sign up to post questions and answers.