Advertisements

Working with special character letters in Oracle database (grave accent, acute accent, circumflex accent, tilde, and umlaut)

We have some external data feeds that we receive as csv files, and we load them into our data warehouse and process them.    It turns out that data values that include special characters were getting messed up at some point before or during the load to the Oracle database.

In this post, I will go over one way to select, insert or update data values that contain these special characters.  The “special characters” I am referring to are the grave accent, acute accent, circumflex accent, tilde, and umlaut.

The most common one we come across is the “acute accent” which is present in words such as café or entrée (accent over the e in both words).

If you want to insert these words without the accents, into an example table, WORDS, with a single column, SPECIAL_CHAR_WORD, it would simply be:

insert into WORDS (SPECIAL_CHAR_WORD) values ('cafe');
insert into WORDS (SPECIAL_CHAR_WORD) values ('entree');

But if you want to insert these words with the accents, then you would need to do this:

insert into WORDS (SPECIAL_CHAR_WORD) values UNISTR('cafe\0301');
insert into WORDS (SPECIAL_CHAR_WORD) values UNISTR('entre\0301e');

To select the word café with the accent, run this statement:

select UNISTR('cafe\0301') from dual;

Once a column value is already in a table in the proper format, you can simply select the column name using a normal SQL select statement, and the output will show properly (maybe with just the need for some formatting as you will later in the article).

And for update – to update the word entree with the word entrée, run this statement:

update WORD set SPECIAL_CHAR_WORD = UNISTR('entre\0301e') where SPECIAL_CHAR_WORD = 'entree';

To see several special characters (grave accent, acute accent, circumflex accent, tilde, and umlaut), run this statement …

select UNISTR('bare\0300ge') from dual   -- barège    -- grave accent
union
select UNISTR('entre\0301e') from dual   -- entrée    -- acute accent
union
select UNISTR('pa\0302turer') from dual  -- pâturer   -- circumflex accent
union
select UNISTR('jalapen\0303o') from dual -- jalapeño  -- tilde
union 
select UNISTR('fu\0308r') from dual;          -- für    --umlaut

… the output would look like this … which seems not quite right …
Special_Characters_UNISTR

Add the COMPOSE statement (which is just for formatting) as shown below …

select COMPOSE(UNISTR('bare\0300ge')) as SPECIAL_CHAR_WORD from dual   -- barège    -- grave accent
union
select COMPOSE(UNISTR('entre\0301e')) from dual   -- entrée    -- acute accent
union
select COMPOSE(UNISTR('pa\0302turer')) from dual  -- pâturer   -- circumflex accent
union
select COMPOSE(UNISTR('jalapen\0303o')) from dual -- jalapeño  -- tilde
union 
select COMPOSE(UNISTR('fu\0308r')) from dual;     -- führer    --umlaut

and the output will look like this …

Special_Characters_UNISTR_and_COMPOSE2

As  you can see, the key to this is knowing the code for the special character you need, and then using the UNISTR function to add the special character to the rest of the text, and if necessary, use COMPOSE for display purposes.

Thanks for reading. Hope you found this helpful.

Advertisements

Change an Oracle database user password

This is a simple post for how to change the password for an Oracle database user.  Every now and again we need to do this, but may forget how to or forget the exact syntax needed.

When you need to change the password for an Oracle database user, you can use the following command after logging into the database using SQL Developer, Toad, or SQL*Plus for example.

alter user [user_name] identified by "[new password]" replace "[old password]";

Put the double quotes around the password values, particularly if your password includes special characters.

You may also use a GUI option in SQL Developer. In SQL Developer, right-click the appropriate database and select “Reset Password” from the menu.
SQL_Developer_Reset_Password

And then enter the user, old and new passwords in the “Enter New Password” dialog.
SQL_Developer_ChangePassword_Dialog

I am updating this post to add another method for changing a user’s Oracle password.

In SQL Developer, when connect to the database, enter and run the command “password”.  This will then pop-up a “New password” dialog in which you can enter the new password.
OracleDatabase_ChangePassword_passwordcommand

Thanks for reading!

How to determine your database version for various RDBMS’: Oracle, SQL Server, MySQL, DB2

Occasionally you may need to check one of your database’s version for the purpose of creating a ticket with the software vendor, for checking compatibility with other software, preparing for upgrades, getting database client software, and other reasons.

Below are commands for identifying the version of your database for a few of the more popular RDBMS’s.  Please keep in mind that these may or may not work on your version of database or type of operating system.

 

ORACLE

  • SELECT * FROM V$VERSION;

Your output will be something like this …
version_output_oracle

SQL SERVER

Try one of the following:

  • Select “Help -> About” from the SQL Server Management Studio menu.
  • select @@version
  • You may also connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will show the version information in parentheses, together with the user name that is used to connect to the specific instance of SQL Server.

 

MYSQL

Try one of the following:

  • shell> mysql –version
  • mysql> SHOW VARIABLES LIKE ‘%version%’;
  • mysqladmin version  -or- mysqladmin –v

 

DB2

Try one of the following:

  • SELECT * FROM TABLE(SYSPROC.ENV_GET_INST_INFO());
  • SELECT GETVARIABLE(‘SYSIBM.VERSION’) FROM SYSIBM.SYSDUMMY1;

 

OBIEE data source types and data retrieval methods

OBIEE is capable of connecting to and retrieving data from a variety of data sources.  The type of data sources that OBIEE can connect to are OLTP, OLAP, Data Warehouses (ROLAP), and Files.

  • OLTP databases – these include the normalized-design databases including ERP, CRM and other LOB systems.
    – The relational databases supported are: Oracle databases, Microsoft SQL Server, IBM DB2, and Teradata Warehouse.
    – And the ERP/CRM sources supported are: Oracle E-Business Suite, Oracle Peoplesoft, Oracle Siebel CRM, Oracle JD Edwards, and SAP.  Note: any ERP/CRM system running on one the databases mentioned above can be supported, but those mentioned here are special ERP/CRM sources.
  • OLAP databases – these include dimensional-databases including applications based on dimensional databses.
    – The OLAP datases supported are: Oracle Essbase, Oracle OLAP, Microsoft Analysis Services, and SAP Netweaver BI.
    – And the OLAP applications sources supported are: Oracle Hyperion Planning and Oracle Hyperion Financial Management.
  • Dimensionally-modeled data warehouses – these are relational databases designed with a star-schema / dimensional model, on one of the 4 supported relational databases mentioned above.
  • Files – Microsoft Excel, XML files, Flat files.

The data retrieval methods used to connect to these sources are:

  • OLTP – SQL
  • OLAP – MDX
  • Data Warehouse – SQL
  • Files – ODBC

OBIEE has the ability to connect to multiple of these data sources at the same time, and the data sources can be of the same or different types.  So, for example, an OBIEE Server can source data from an Oracle 11g Data Warehouse, and from an Oracle Essbase 11g OLAP cube at the same time, and join the data together for user consumption.
Similarly, file datasources can also be added to provide additional information, for example from an external source, and joined to data from the other sources mentioned above.  This “joining” of data is handled by the OBIEE BI Repository and BI Server.

To the end user accessing the data from a front-end tool (Analysis Editor / Answers), it seems like a single data source. That is one of the features that makes OBIEE such a great tool particularly for heterogeneous database environments.

Error Message when try to perform actions in Oracle Enterprise Manager 11g on Windows XP

If you get the following error when attempting to perform database actions in Oracle 11g Enterprise Manager on Windows XP …

RemoteOperationException: ERROR: Invalid username and/or password

… then, assuming your Username/password combination is correct … and you can verify this by logging into SQLPLUS … follow these steps to resolve:

1. Open Control Panel
2. Administrative Tools -> Local Security Policy (Local Security Setting window opens)
3. Local Policies -> User Rights Assignment
4. Double click ‘Log on as a batch job’ (Properties window opens)
5. Click on ‘Add user or group’ (Select user/group window opens)
6. Add the username(s) including the domain name
7. Click ‘Check names’ to verify what you have added
8. Save (click OK, click OK)

Hope this helps.