Monday, November 2, 2015

ORA-01722 when running ausy1120.sql during preparation of new target database for EBS

If you are following note Doc ID 741818.1 "Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2" and run into the follwing problem when running the ausy1120.sql script:

sqlplus system/*** @ausy1120.sql

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 2 14:19:31 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option


PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 5

the solution is to modify the script slightly and correct a typo.
Open the ausy1120.sql file in an editor and change

select
  to_number(substr(version,1,instr(version,'.')))
  into :dbver
  from v$instance
  where rownum=1;
to
select
  to_number(substr(version,1,instr(version,'.')-1))
  into :dbver
  from v$instance
  where rownum=1;

Then rerun the script. It should finish almost immediately and the output should be similar to:

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

--------------------------------------------------------------------------------
--- ausy1120 started at 02-NOV-2015 14:40:04 ---


'---AUSY1120COMPLETEDAT'||TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')||'----'
--------------------------------------------------------------------------------
--- ausy1120 completed at 02-NOV-2015 14:40:04 ----

Commit complete.

The same error seems to occur when running the aujv1120.sql and the aumsc1120.sql as well. The solution is the same for all three.

No comments:

Post a Comment