I am trying to execute the following statements from Perl script.
alter session set constraints=deferred
update acct_owner_01.account set acct_no = substr (acct_no,1,4) where cny# = 2058
while executing the last statement, I am getting the following error. If I execute the same from SQL*Plus, it works fine. Does anyone know why the statement fails and any possible workarounds?
DBD::Oracle::db do failed: ORA-02091: transaction rolled back
ORA-02292: integrity constraint (ACCT_OWNER_01.FK_ACCOUNTLABEL_ACCT_NO) violated - child record found (DBD ERROR: OCIStmtExecute) at ./tempmig.pl line 43.
Alter table modify constraint intially deferred;
Make sure your perl script has the priveledge to run that command also.
The script (sql statements) are provided by the development team and the perl script will read each statement and execute them.
if I have to "Alter table modify constraint", then I need to parse each sql statements and will be little complex.
Anyway, thanks for the suggestion. ( I may have to go with that option, if nothing works)
alter table set constraint constraint_name deferred;
I want to deferr the constraint only for my session. Not at the entire table level.