Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

DataSource saving error

SOLVED
Go to solution
Highlighted
Deepak Bhattad
Regular Collector

DataSource saving error

Hi,

 

I m trying to add a couple of columns to an existing custom data source and get the following error.

 

ORA - 01461 - can bind a LONG value only for insert into a LONG Column.

 

The PPM version is 8.00 SP2 and the Oracle Database verision is 10.2.0.5.

 

Any help is greatly appreciated.

 

In the query below the portion after UNION ALL is in the where clause section:

 

The datasource query is as follows:

 

SELECT 1 QUINTILES_ID, 'FIRST_NAME' FIRST_NAME, 'LAST_NAME' LAST_NAME, 'CATEGORY' CATEGORY, 'PROJECT_NAME' PROJECT_NAME, 'ROLE' ROLE_ASSIGNED, to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'Mon-YY') MONTH, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),1),'Mon-YY') MONTH1, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),2),'Mon-YY') MONTH2, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),3),'Mon-YY') MONTH3, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),4),'Mon-YY') MONTH4, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),5),'Mon-YY') MONTH5, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),6),'Mon-YY') MONTH6, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),7),'Mon-YY') MONTH7, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),8),'Mon-YY') MONTH8, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),9),'Mon-YY') MONTH_9, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),10),'Mon-YY') MONTH10, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),11),'Mon-YY') MONTH11, to_char(add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),12),'Mon-YY') MONTH12, 'AMGEN_ID' AMGEN_ID, 'PROTOCOL #' PROTOCOL_NO, 'RESOURCE_POOL_NAME' RESOURCE_POOL_NAME, 'PARENT_RESOURCE_POOL' PARENT_RESOURCE_POOL, 'THERAPEUTIC_AREA' THERAPEUTIC_AREA, 'PRODUCT_CODE' PRODUCT_CODE
FROM dual
UNION ALL
SELECT distinct ku.user_id QUINTILES_ID, ku.first_name FIRST_NAME, ku.last_name LAST_NAME, Initcap(ku.resource_category_code) CATEGORY, kfpp.prj_project_name PROJECT_NAME, substr(rr.role_name,instr(rr.role_name,'-')+2,length(rr.role_name)) ROLE_ASSIGNED
,to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'))) MONTH, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),1))) MONTH1, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),2))) MONTH2, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),3))) MONTH3, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),4))) MONTH4, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),5))) MONTH5, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),6))) MONTH6, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),7))) MONTH7, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),8))) MONTH8, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),9))) MONTH_9, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),10))) MONTH10, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),11))) MONTH11, to_char((select effort from CUST_GFR_AMG_ALLOC_DETAILS where sp_id=kfpp.prj_staff_prof_id and assignment_id=rra.resource_assignment_id and position_id=rp.position_id and start_date=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),12))) MONTH12, ku.visible_user_data1 AMGEN_ID
,(select visible_parameter7 from kcrt_req_header_Details where request_id=kfpp.request_id and batch_number=1) PROTOCOL_NO
,rrp.resource_pool_name RESOURCE_POOL_NAME
,(select resource_pool_name from rsc_resource_pools where resource_pool_id=rrp.parent_resource_pool_id) PARENT_RESOURCE_POOL
,kfpp.prj_project_class_meaning
,kfpr.ref_program_name
FROM kcrt_fg_pfm_project kfpp
     ,rsc_positions rp
     ,rsc_resource_assignments rra
     ,rsc_roles rr
     ,knta_users ku      
     ,CUST_GFR_AMG_ALLOC_DETAILS alloc
     ,rsc_resource_pools rrp
     ,kcrt_fg_prog_reference kfpr
WHERE 1=1
and rrp.resource_pool_id=rp.resource_pool_id
and ku.user_id=alloc.resource_id
and rr.role_id=rp.role_id
and alloc.sp_id=kfpp.prj_staff_prof_id
and alloc.position_id=rp.position_id
and alloc.assignment_id=rra.resource_assignment_id
and rra.position_id=rp.position_id
and rp.staffing_profile_id=kfpp.prj_staff_prof_id
and kfpr.request_id=kfpp.request_id
and kfpp.request_type_id =(select request_type_id from kcrt_request_types where request_type_name='GFR - Amgen Project')
--This section comes from the filter fields
and alloc.start_date>=to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY')
and alloc.start_date<=add_months(to_date(to_char(to_date('[P.DATE]','YYYY-MM-DD HH24:MI:SS'),'MMYYYY'),'MMYYYY'),12)
and rp.role_id in ([P.ROLE])
and kfpp.request_id in ([P.PROTOCOL_NUMBER])
--End section from the filter fields
order by quintiles_id asc

3 REPLIES
Erik Cole
Honored Contributor

Re: DataSource saving error

Which columns are you adding that are throwing the error?

Deepak Bhattad
Regular Collector
Solution

Re: DataSource saving error

 I found the issue here.

 

The issue was the "from" clause box and the "where" clause box length size was probably exceeding the prescribed length size and hence I had to rewrite my query removing spaces and some other tricks in reducing the size of the overall query.

 

however I think this is a bug and PPM should allow any size of the query. not sure what others have to think.

AlexSavencu
Honored Contributor

Re: DataSource saving error

Hi,

The limitation is by design. The query is stored in a varchar column, which has a maximum value of 4000 bytes.

For such queries we usually create custom views I the database - not only because of the query length, but also because performance is much better since Oracle views are cached.


Cheers
Alex

--remember to kudos people who helped solve your problem
//Add this to "OnDomLoad" event