Export char semantic 10.2 database

Unable to export char semantic 10.2 database

Created a fresh database with NLS_LENGTH_SEMANTICS=CHAR. If you then run an export, then will get error like below

EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully

Here are the steps to “repair” my database

Step 1: First check that the NLS_LENGTH_SEMANTICS is indeed set to CHAR. Then shutdown the database and restart as “startup migrate” You can see that the length semantics are now BYTE:

SQL> shutdown immediate
SQL> startup migrate

Step 2: Now drop the XML DB stuff.

SQL> @?\RDBMS\ADMIN\catnoqm.sql

Note: You’ll have to close SQL*Plus at the end, then reopen again

Step 3: There is a bug that says you have to restart the database after running catnoqm. Be sure to start it with “startup migrate” again:

SQL> shutdown immediate
SQL> startup migrate

Step 4: Now run the CATQM.SQL script which requires three arguments (initial password and two tablespaces):

SQL> @?\RDBMS\ADMIN\catqm.sql change_on_install SYSAUX TEMP

Step 5: Now shutdown and start the database normally. You can check that the length semantics are back to its original (CHAR) and the query from sys.ku$_xmlschema_view now works fine:

SQL> shutdown immediate
SQL> startup

After finishing the repair steps you can do export.

Find the meta link for above issue: Note 339938.1

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) August 2008, manivelcode

Error "IN is not a valid integer" on TOAD

Error "IN is not a valid integer" on TOAD

You are using as Oracle 10G release 2 database (10.2.0.2) or higher with TOAD that is less than 8.6.1. When you trying to look or access at the source of a procedure or function or package you may get this error “IN is not a valid integer”.

The solution is either one of below

-- Don’t use oracle database 10.2.0.2 or newer releases.

-- Upgrade to TOAD version 8.6.1 or higher.

-- Alter the ALL_ARGUMENTS view in your new oracle database under SYS user, moving the SUBPROGRAM_ID column to the end. 

Note: Altering the data dictionary is unsupported by Oracle, so you probably should not do this to a production database. It does; however, seem to do the trick, with no known side effects.

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) August 2008, manivelcode