Oracle SQL Developer 19.x.x & ORA-20001: Oracle error -6502: ORA-06502: PL/SQL: numeric or value error: character string buffer too small has been detected in fnd_global.set_nls

Hi guys

Okay, so you switched to Oracle SQL Developer for the main reason that the suite is from Oracle & it is absolutely free and putting loads of efforts to get accustomed with certain “JAVA” platform limitations (as per developers).

Everything is fine until you start getting “ORA-20001: Oracle error -6502: ORA-06502: PL/SQL: numeric or value error: character string buffer too small has been detected in fnd_global.set_nls” or errors those WERE never happening while you were trying to execute years old PL/SQL blocks which were always completing successfully using Quest TOAD or at SQL prompt itself.

Before scavenging through your archives to find the Toad Installer for a re-installation, give the following exercise a try.

Applicable to Windows ONLY!

Go to user specific “AppData\Roaming” folder, eg: C:\Users\rajesh\AppData\Roaming

and delete both the folders, “SQL Developer” & “sqldeveloper”

Usually whenever you download and start the latest version of SQL Developer, the new version checks for the older versions under Roaming profile & if found, prompts the user asking whether the existing preferences should be copied (that includes already saved connections and other setting you may have made), which may cause errors like the ONE I have had once after migrating 19.2.x to 19.4.x

Regardless whether you were using previous versions or NOT, deleting all folders for “SQL Developer” under the roaming profile will force the latest version of Oracle SQL Developer to start afresh & most probably will take care of unreliable error messages (Confirmed by running the PL/SQL blocks without outputting the same errors using Quest TOAD)

Thank me later ;)

rajesh

Migrate from Microsoft SQL Server to Oracle 11g

Hi guys

This time I am going to share my experience with migrating a small MS SQL database to Oracle database using Oracle’s SQL Developer

Scenario

Microsoft SQL Server 2008 or later

Oracle database 11g (or later, 12c not tested)

Requirement

Business requires a 3rd party software that depends upon MS SQL Server to be migrated to Oracle platform

Pre-requisites

Oracle database 11g installed and instance is up and online

Oracle SQL Developer 4.2 (Used for this demonstration). Cannot confirm whether 4.1 uses the same approaches. Try it and let me also know

JDBC driver for MS SQL connectivity. For SQL Developer 4.x you need to download the driver from following link

http://sourceforge.net/projects/jtds/files/

Please follow https://kentgraziano.com/2013/01/14/tech-tip-connect-to-sql-server-using-oracle-sql-developer/ to learn how to install and configure the driver in order to establish a connection from SQL developer to MS SQL server.

Scope

I am going to migrate a database called “OPMS” from SQL Server 2008 R2 express edition to Oracle database 11g R2 64Bit

image

Please note, the JDBC driver fails to connect to the SQL Server using Windows Authentication, hence you must define a login with for your database, change the instance authentication methods to mixed in order to establish a successful connection.

image

As you could see with the above image, I don’t have any connections to Oracle database defined.

For the database migration, We must define two connection. One connection using SYSTEM user & the 2nd connection using MWREP user that we will create in like following

[code language=”sql” gutter=”false”]
DROP USER MWREP CASCADE
/
CREATE USER MWREP IDENTIFIED BY mwrep
DEFAULT TABLESPACE BAYAN
TEMPORARY TABLESPACE TEMP1
QUOTA UNLIMITED ON BAYAN
/
GRANT DBA,CONNECT,RESOURCE TO MWREP
[/code]

Once the migration is over, you may drop this repository safely. Hence don’t worry about the grants given to the new user. I failed to successfully get the things done properly without DBA role assigned to this migration schema

I’ve struggled hours to understand why the “tables” were not created as a part of the migration process which said “Successfully completed” after the execution and my probing through the log files pointed towards a schema OPMS not existing in indirect wordings.

(I’m pretty sure that I missed something & the intended schema was NOT created during the migration process run. Regardless, creating the Schema manually gives many tuning choices like tablespace selection, quota setting etc)

So, to get the migration work successfully, you need to create a schema with the the same name of your SQL Database. In my example the SQL database name is “OPMS”, the same I pre-defined with the Oracle database, although the script generated by the migration has DDL for creating the user against default tablespace “USERS”. Well, I didn’t want my OPMS schema using “USERS” tablespace…

I created the OPMS user as below

[code language=”sql” gutter=”false”]
DROP USER OPMS CASCADE
/
CREATE USER OPMS IDENTIFIED BY opms
DEFAULT TABLESPACE BAYAN
TEMPORARY TABLESPACE TEMP1
QUOTA UNLIMITED ON BAYAN
/
GRANT DBA,CONNECT,RESOURCE TO OPMS
[/code]

I am all set to start the migration now, so should be you!

Created a new connection for user “System”

image

Created another connection for “MWREP” user, which will hold the migration repository

image

Now we have to create the migration repository. Right click on the MWREP connection and expand Migration Repository, then Associate Migration repository

image

Progress

image

Finished

image

If the repository association fails for any reasons, you have restart by dropping the migration schema that you have created and go through the steps once again.

As I have completed creating the migration repository, next step is to connect the SQL developer to MS SQL Server (in my case SQL Server 2008 R2 Express edition)

image

So you have all the 3 connections required for the migration now.

image

As soon as you connect the MWREP, you will notice that Migration Windows showing an entry like seen with the below image

image

Now we will start the migration.

image

The welcome screen provides you an overview of the activities those will be completed for the SQL database migration. Move ahead

image

By default your migration repository will be selected, however cross check it and click “Next”

image

Provide a meaningful name for your Project and Select output directory

image

Make sure you have selected the correct Source database

image

The default database for the currently connected SQL database user will be selected by default for capturing. Confirm and click next

image

Under the convert step (6), make necessary changes. Refer the image for more details

image

A number of objects will be selected, unless you are pretty confident about objects you don’t want to migrate, leave the default selection intact.

image

Select the “System” connection for Target Database

image

Make sure you select SQL connection for source and MWREP connection as target in the move data step (9)

image

Click finish & the migration immediately starts. Depending upon the size of your source database it may take while for the process to create and move data between the technologies.

image

 

Progress

image

 

image

 

image

Create a connection to Oracle database for the newly created (We created the OPMS schema prior the migration) schema & verify whether the objects were created by the migration process.

image

That’s all folks!

rajesh