AnsweredAssumed Answered

Error during migration from 6.9.1 To 7.0.2

Question asked by Tushar Sutar on Dec 22, 2017
Latest reply on Dec 22, 2017 by Tushar Sutar

Like • Show 0 Likes0
Comment • 2

Hi,

I am getting following error during migration from 6.9.1 P24 to 7.0.2

Migration Log -

--------------------------- Dbms_Output Begin ---------------------------
==========================================================================
Error(s) occured while compiling invalid object GETDATEINPUT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/23 PLS-00103: Encountered the symbol ")" when expecting one of the following:

<an identifier> <a double-quoted delimited-identifier>
current delete exists prior

7/3 PLS-00103: Encountered the symbol "END" when expecting one of the following:

. ( , % from
--------------------------- Dbms_Output End ---------------------------

Any quick help would be much appreciated.

No one else has this question

Outcomes

2 Replies

Tushar Sutar Dec 22, 2017 6:12 AM

When I opened the function GETDATEINPUT I am seeing same error and code is,

Create Or Replace
Function Getdateinput()
Return Date Is
dateinput date;
begin
Select To_Date('01/01/2015', 'MM/DD/YYYY')
Into Dateinput
END;

And most surprising thing is, I thought may be something got wrong only while migrating our DEV environment from 6.9.1 to 7.0.2

So went to check function code from UAT, there also this function has same error and UAT working absolutely fine.

Now above that I went to check our PROD database which is a remote database not a appliance like DEV & UAT, in PROD this function itself is not there.

Actions

Tushar Sutar @ Tushar Sutar on Dec 22, 2017 6:39 AM

The complete code is,

Original
Create Or Replace Function Getdateinput() Return Date Is dateinput date; begin Select To_Date('01/01/2015', 'MM/DD/YYYY') Into Dateinput END; with q as ( select crd.operand_id, crd.value_app_id, crd.request_type, crd.resource_name, crd.value_name, crd.type, crd.change_data_description, crd.state, crd.value_id, crd.value_type from avuser.pv_change_request cr inner join avuser.pv_change_request_detail crd on crd.change_request_id = cr.id where cr.current_state = 'Completed' and crd.state = 'Completed' and crd.type in ( 'AddEntToUser' , 'RemoveEntFromUser' , 'AddUserToAppRole' , 'RemoveUserFromAppRole' , 'AddUserToBizRole' , 'RemoveUserFromBizRole') ) select distinct approle.name as Application_Role , app.name as App_Name , acct.name as Account_Name , usr.user_id , (usr.first_name \|\| ' ' \|\| usr.last_name) as user_name from avuser.pv_user_application_role uarmap , avuser.pv_users usr , avuser.pv_application_role approle , avuser.pv_user_account_mapping uam , q, avuser.pv_account acct , avuser.pv_application app where uarmap.user_id = usr.id and uarmap.application_role_id = approle.id and q.operand_id = usr.id and approle.application_id = app.id and usr.id = uam.user_id and approle.name != q.value_name and uam.account_id = acct.id and ( acct.name not in ( select distinct account_name from ( select approle_added.run_id, approle_added.ent_type, decode(approle_added.resource_name, '', 'Application-Role', approle_added.resource_name) as resource_name, approle_added.for_value as account_name, approle_added.srcid, run.end_time as date_role_added, run.status, run.type from V_DC_SOURCE_INS_USER_ENT approle_added inner join V_DATA_RUN run on approle_added.run_id = run.id where approle_added.for_value is not null and (approle_added.ent_type = 'app-role' or (approle_added.ent_type = 'ent' and approle_added.resource_name in ('Role Set', 'Role') ) ) and run.status in ('Completed') and run.end_time >= (select getDateInput() from dual) ) ) or acct.name not in ( select distinct account_name from ( select approle_added.run_id, approle_added.ent_type, approle_added.resource_name, approle_added.for_value as account_name, approle_added.srcid, run.end_time as date_role_added, run.status, run.type from V_DC_SOURCE_INS_USER_ENT approle_added inner join V_DATA_RUN run on approle_added.run_id = run.id where approle_added.for_value is not null and approle_added.ent_type in ('ent') and run.status in ('Completed') and approle_added.resource_name not in ('User', 'Application', 'Role Set', 'Role') and run.end_time >= (select getDateInput() from dual) ) ) ) and ( usr.is_terminated != 'True' or usr.disableddate is null or usr.deletion_date is null or job_status != 'T' ) END;

Original

Create Or Replace
Function Getdateinput()
Return Date Is
dateinput date;
begin
Select To_Date('01/01/2015', 'MM/DD/YYYY')
Into Dateinput
END;

with q as (
select crd.operand_id, crd.value_app_id, crd.request_type,
crd.resource_name, crd.value_name, crd.type,
crd.change_data_description, crd.state, crd.value_id, crd.value_type
from avuser.pv_change_request cr inner join avuser.pv_change_request_detail crd on crd.change_request_id = cr.id
where cr.current_state = 'Completed'
and crd.state = 'Completed'
and crd.type in (
'AddEntToUser'
, 'RemoveEntFromUser'
, 'AddUserToAppRole'
, 'RemoveUserFromAppRole'
, 'AddUserToBizRole'
, 'RemoveUserFromBizRole')
)

select distinct approle.name as Application_Role
, app.name as App_Name
, acct.name as Account_Name
, usr.user_id
, (usr.first_name || ' ' || usr.last_name) as user_name
from avuser.pv_user_application_role uarmap
, avuser.pv_users usr
, avuser.pv_application_role approle
, avuser.pv_user_account_mapping uam
, q, avuser.pv_account acct
, avuser.pv_application app
where uarmap.user_id = usr.id
and uarmap.application_role_id = approle.id
and q.operand_id = usr.id
and approle.application_id = app.id
and usr.id = uam.user_id
and approle.name != q.value_name
and uam.account_id = acct.id
and (
acct.name not in (
select distinct account_name from (
select approle_added.run_id, approle_added.ent_type, decode(approle_added.resource_name, '', 'Application-Role', approle_added.resource_name) as resource_name,
approle_added.for_value as account_name, approle_added.srcid, run.end_time as date_role_added, run.status, run.type
from V_DC_SOURCE_INS_USER_ENT approle_added inner join V_DATA_RUN run on approle_added.run_id = run.id
where approle_added.for_value is not null
and (approle_added.ent_type = 'app-role'
or (approle_added.ent_type = 'ent'
and
approle_added.resource_name in ('Role Set', 'Role')
)
)
and run.status in ('Completed')
and run.end_time >= (select getDateInput() from dual)
)
)
or
acct.name not in (

select distinct account_name from (
select approle_added.run_id, approle_added.ent_type, approle_added.resource_name, approle_added.for_value as account_name, approle_added.srcid, run.end_time as date_role_added, run.status, run.type
from V_DC_SOURCE_INS_USER_ENT approle_added inner join V_DATA_RUN run on approle_added.run_id = run.id
where approle_added.for_value is not null
and approle_added.ent_type in ('ent')
and run.status in ('Completed')
and approle_added.resource_name not in ('User', 'Application', 'Role Set', 'Role')
and run.end_time >= (select getDateInput() from dual)
)
)
)
and (
usr.is_terminated != 'True'
or usr.disableddate is null
or usr.deletion_date is null
or job_status != 'T'
)

END;

And now i have changed temporarily to

Create or Replace Function Getdateinput
Return Date Is
dateinput date;
begin
Select To_Date('01/01/2015', 'MM/DD/YYYY')
Into Dateinput from dual;
End;

And commented rest all below these line. But still I will need exact solution to this problem.

Actions

Error during migration from 6.9.1 To 7.0.2

Outcomes

Related Content

Recommended Content