Re: Case sensitive user defined types, 11g imp and my current nightmare!

My first suggestion would actually be to try with a parameter file. It is possible that the command line tries to interpret the quotes so they in reality is not passed on to imp. That might be circumventable with escaping quotes like \", but it quickly becomes horrible because you might have to do wrap the entire parameter in quotes and escape the quotes within the parameter - something like:

"TOID_NOVALIDATE=('\"USER\".\"TypeNameWithCamelCase\"')"

You avoid having to do that with a parameter file.

Can't say for sure it'll work, but it's the first thing I would try myself for such a (camel)case ;-)

Regards

Kim Berg Hansen

http://www.kibeha.dk
kibeha_at_kibeha.dk
_at_kibeha <http://twitter.com/kibeha>

On Wed, Apr 27, 2016 at 9:31 AM, Norman Dunbar <oracle_at_dunbar-it.co.uk> wrote:

> Morning All,
>
> I'm working on "upgrading" a Solaris 9i database to 11g on Windows in the
> (Azure) cloud.
> I have to use exp/imp due to the 9i database.
>
> I have the "IMP-00061 Object user.TypeNameWithCamelCase already exists
> with a different identifier" followed by "IMP-00063 Skipping table
> user.tablename because object type user.TypeNameWithCamelCase cannot be
> created or has a different identifier." error.
>
> Fine, I can use the TOID_NOVALIDATE parameter for this one as I know that
> the type in question is valid. And should exist as other tables depend on
> it being there. Those have imported quite happily.
>
> Sadly, some developer or other deemed it necessary that the type in
> question absolutely, without question, really had to be created in case
> sensitive manner with CamelCase naming. (My advice, if you double quote
> your identifiers, object names etc, be prepared to be severely ranted at by
> me!)
>
> So, I specify the username and type name in double quotes:
>
> imp ... TOID_NOVALIDATE=("USER"."TypeNameWithCamelCase") ...
>
> and ran the imp again. Same error exactly. Hmm. I then consulted the docs
> and discover that to preserve case I need to wrap the double quotes in
> single quotes. Off I go again:
>
> imp ... TOID_NOVALIDATE=(' "USER"."TypeNAmeWithCamelCase" ') ... (Spaces
> shown for clarity, not used in actual command line.)
>
> and after running the imp again, I get exactly the same error messages.
> Sigh.
>
> So, how the hell do I preserve CamelCase naming for types that I want to
> ignore the TOID? I'm doing this on the command line at the moment, but I'm
> working on the assumption that a parameter file will have the same
> problems. I have been wrong before!
>
> What makes this all the more galling is the fact that this is only a 400Gb
> database (ie, small) but the export file is 160 Gb in size. It takes this
> poor little cloud server around 23 hours to skip the billions of rows of
> data it doesn't need to get to the table I want to import. Of course, my
> table has a name that is at the end of the alphabet, so appears at the end
> of the dump file after the vast majority of the data. Sigh.
>
> My latest run is simply:
>
> drop table .... cascade constraints purge;
> drop type ... force;
>
> Followed by the import. Which hopefully will work, but might affect the
> other tables that depend on this type and which have imported fine.
> However, there might be others - there are indeed other types with these
> naming conventions - that will affect things in future and I'd like to be
> armed with the correct manner of preserving the
> DamnedCamelCaseNamingConventions that SomeFlippingDeveloperThoughWasCute!
> ;-)
>
> I am rapidly losing the will to live!
>
>
> Cheers,
> Norm.
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.

--
http://www.freelists.org/webpage/oracle-l