I'm converting a db from 10.2 (WE8ISO8859P1) to 220.127.116.11 (AL32UTF8).
As the real data inserted in the original DB is WE8MSWIN1252 encoded, I took this steps:
1) created destination db 18.104.22.168 in WE8ISO8859P1 charset
2) expdp/imdp the database from sorg (10gR2) to dest (11gR2)
3) prepared the db for conversion: at this point there are NO invalid objects
4) DMU - converted the db from WE8ISO8859P1 to AL32UTF8, assuming WE8MSWIN1252 encoded data
5) at the end I have some packages which specification is merged with body.
The body for example looks like create or replace PACKAGE "mypackage" is
+.....+ end mypackage; CREATE OR REPLACE PACKAGE BODY "mypackage" is
+....+ end mypackage;
instead of create or replace PACKAGE "mypackage" is
+.....+ end mypackage;
which of course invalidates the package.
Any ideas other than modify the package manually ?
Developers says me that all these packages are created with PLSQL developer, and gives no error.
I searched on MOS, google'd but can't find anything...
There is nothing wrong with using lowercase names in double-quotes provided the syntax is used consistently. This may be not the best practice but it is fully supported. Poorly written code may have problems with such names but these problems should be reported and fixed.
Just to keep you informed.
The PL/SQL code contains latin characters like 'è', 'à', 'ù' in the comment.
I tried to create a package without those characters (so the encoding of the file .sql is ASCII) and migrate to AL32UTF8.
After the conversion:
- the package with latin characted is invalid
- the package in plain ASCII is valid.
I exported the sys.source$ table, and I'll take a look at it to find out where it go into trouble, although DMU says that the dictionary is ok.
DMU is version 1.1.
I find out that the Post-Conversion step of DMU recreates the packages with body merged with specification
Edited by: user627529 on Jul 25, 2012 7:54 AM
A more interesting table would actually be SYSTEM.DUM$SQLTEXT. We have asked you to log a service request with Support so that we have a proper channel to request diagnostic information from your database.
OK, I will open an SR.
The problem is reproducible very simply.
Meanwhile I found this workaround:
1) dpimp in sqlfile the sql code to recreate the packages
2) modify and execute those statements in the new db (AL32UTF8)
To filter out the packages that need conversion I used an impdp parfile like this:
include=package:" in (select distinct object_name from dba_objects o, sys.source$ s where o.object_type like 'PACKAGE%' and o.object_id =s.obj# and (s.source like '%à%' or s.source like '%è%'or s.source like '%é%' or s.source like '%ì%' or s.source like '%ò%' or s.source like '%ù%' or s.source like '%¿%'))"