Inserting data in SQL*Plus correctly

When inserting data into the database, it is occasionally forgotten (especially by English-speakers) that we need to take steps to ensure we are inserting data correctly and without unexpected character translation.

For example, in SQL*Plus we need to ensure we set the NLS_LANG environment variable to the correct setting for our database before we initiate SQL*Plus.

Here’s a quick example showing what can go wrong:

[oracle@ORA122 ~]$ echo $NLS_LANG

[oracle@ORA122 ~]$ sqlplus neil/neil
SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 22 13:56:23 2016
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Thu Dec 22 2016 13:46:58 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NEIL @ pdb1 > create table lang (col1 varchar2(50 CHAR));
Table created.

NEIL @ pdb1 > insert into lang values (q'{J'ai cassé l'insert}')
  2  /
1 row created.

NEIL @ pdb1 > commit;
Commit complete.

NEIL @ pdb1 > select * from lang;

COL1
--------------------------------------------------
J'ai cass? l'insert

As we can see, the accented ” é ” has been lost in translation somewhere. This is not good.

If we set NLS_LANG correctly and repeat the insert, we get a different result:

[oracle@ORA122 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@ORA122 ~]$ sqlplus neil/neil
SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 22 13:56:23 2016
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Thu Dec 22 2016 13:46:58 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

NEIL @ pdb1 > insert into lang values (q'{J'ai cassé l'insert}')
  2  /
1 row created.

NEIL @ pdb1 > commit;
Commit complete.

NEIL @ pdb1 > select * from lang;

COL1
--------------------------------------------------------------------------------
J'ai cass� l'insert
J'ai cassé l'insert

Note how the original insert is now returning even more troublesome nonsense that it was previously!

So, how do we know what to set the NLS_LANG variable to avoid this? The answer is partly in the database.

NEIL @ pdb1 > select * from v$nls_parameters 
              where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

PARAMETER                                VALUE                    CON_ID
---------------------------------------- -------------------- ----------
NLS_LANGUAGE                             AMERICAN                      3
NLS_TERRITORY                            AMERICA                       3
NLS_CHARACTERSET                         AL32UTF8                      3

Which gives us:

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

However, this just tells you about the database character set.
That may not be what we are after!

The NLS_LANG should reflect the setting of the operating system character set of the client. In Windows there is no UTF-8 client, so you need to select the correct client code page.

On Linux, check out the value of $LANG

[oracle@ORA122 ~]$ echo $LANG
en_US.UTF-8

Chances are it’s UTF8, so it all matches up.

export NLS_LANG=AMERICAN_AMERICA.UTF8

If it is Windows, well that’s more complex as you need to match the code page of the client correctly. Windows generally matches the code page to the Unicode via its API’s, but you may have different code pages based upon whether you are on the command line (sqlplus) or OEM (SQL Developer)

A list of Windows code pages is held here.

On an English Windows client, the code page is 1252.

If you are using SQL Developer, in Tools => Preferences, check the encoding.

sqldev_encoding

You can find out what command-line code page you are running using “chcp”

Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation. All rights reserved.

C:\Users\neil>chcp
Active code page: 850

NOTE: This is the DOS (sqlplus) codepage, not the GUI Codepage.

MS-DOS codepage Oracle Client character set (3rd part of NLS_LANG)
437 US8PC437
737 EL8PC737
850 WE8PC850
852 EE8PC852
857 TR8PC857
858 WE8PC858
861 IS8PC861
862 IW8PC1507
865 N8PC865
866 RU8PC866
set NLS_LANG=american_america.WE8PC850

sqlplus ...

 

It is also worth bearing in mind that the new database characterset default in Oracle 12.2 is now AL32UTF8.

Don’t forget to ensure your terminal session won’t mess up any character translation too. Set it to the correct server character set.

putty_utf8

Happy Inserting!

Use Unicode character sets, and go read the manual on this. It’s not straightforward!

 

Oracle Conferences

So the conference season is all over for another year. I have had a bumper crop of conferences and have been a very lucky man to attend and present and learn.

 

OUGN – The Boat – a 2 day conference on a ship. We’re all in it together, and nobody can sneak off after the last talk and avoid the party as we are all in the middle of the sea. So much fun, and the less said of me finding myself in a corridor at 1.30am, wearing only a sm… [redacted on public decency grounds]

 

------------------------------------------------------------------------------------

POUG – Pint with Oracle User Group Poland in Warsaw lived up to its name. So many good parts to it; tables of beer, way too much vodka, great organization but the engagement of the audience was the highlight for me.

 

 

 

 

 

 

 

------------------------------------------------------------------------------------
bgoug_group

BGOUG-Great location, group shot

BGOUG – Bulgaria’s twice yearly conference. This one was in Pravets, an hours drive from Sofia. I cannot sing the praises of this conference highly enough. Beautiful location. Kind-of like being at a family wedding with added learning and less fights 🙂

 

 

 

 

 

 

 

 

------------------------------------------------------------------------------------

DOAG – Flew straight from Sofia to Nuremberg to the biggest Oracle conference in Europe. Extremely good conference on the outskirts of a lovely city. So much to opportunity to learn, it was amazing.

 

 

 

 

 

------------------------------------------------------------------------------------

 

 

UKOUG – Obviously the very best conference in Europe, but I am biased as  I help to organize this conference. This year I took a larger role leading the team pulling together the Database agenda, which was about 1/3 of the total content of Tech16. Lots of hard work, but it payed off with a bumper crop of speakers from Oracle, over 60 ACEs, a list of excellent topics and only a couple of room allocation problems where we got a bigger audience than we expected (but fortunately none in my streams)

 

As well as helping Organize UKOUG Tech16, I also run several 1-day community events each year as the Chairman of the UKOUG Special Interest Group (SIG) for RAC, Cloud, Infrastructure and Availability.

ukoug_communities

UKOUG Communities Meet

At the UKOUG we have a “communities” evening, with beer, wine and nibbles for a couple of hours. I also bring a bottle of whisky for people to taste (I like whisky) and several other speakers and delegates also brought favoured bottles, including Belgian and German Whisky. The German stuff was great!

ace-dinner

UKOUG Ace Dinner

I’d like to thanks all of the organizers, speakers and delegates for making the UKOUG and all of the other conferences for letting me attend, speak, learn and have a wonderful time this year.

Finally, thank to Jennifer and the Oracle ACE Program for helping all of our communities, big and small, to hold these events and provide Prawn Toast and Thai Green Curry at the UKOUG ACE Dinner 😛

 

 

I hope see all you in Ireland in March!

 

Call for Papers – UKOUG 2017 SIGs

All,

The time has come for me to plan the upcoming 2017 UKOUG Special Interest Groups.

I am chairman of the RAC, Cloud, Infrastructure and Availability (RAC-CIA) SIG and I’m after presentations for the 3 joint SIGs we will be putting on with the RDBMS SIG, plus the stand-alone SIG will we will having in the autumn.

A SIG is a single day one-or-two stream conference which we take around the UK to make it as accessible for UKOUG members and non-members alike (NOTE: there is a charge for non-members)

This year we are looking at running:

Thursday 23rd February – RDBMS + RAC, London 2 streams
Thursday 27th April – TBC: Northern Day – RDBMS + RAC + ? , Manchester (probably)
Thursday 15th June – 12.2 Themed event – TVP Reading – 2 stream event RDBMS + RAC
Wednesday 27th September – RAC-CIA SIG, London 1 stream

If you are interested in speaking, you can submit an abstract here:

http://ukoug.formstack.com/forms/submit_an_abstract

or you can email me directly : neil@chandler.uk.com

Be quick! The next event is in 10 weeks time…

UKOUG Tech 16

tech16_rd_gen_v1

It is the UKOUG Tech16 (and App16 & JDE 16) conference in a short few days.

As well as giving 2 presentations myself, the first about Disaster Recovery, Far Sync and Fast Start Failover and the second about “RAC for beginners”, I will also be arguing in the DBA v Dev round table at the end of Monday about what I would like to do with most Developers and the best way for me to achieve it without negative consequences.

Martin Widlake and I will be MC-ing the Optimizer round table, with a panel of experts consisting of Jonathan Lewis, Maria Colgan, Nigel Bayliss and Chris Antognini answering your questions (send questions in advance to Tech16@jlcomp.demon.co.uk ), talk to me on the day, or stick your hand in the air in the session.

If that panel doesn’t get you to persuade your management that you need to be at this conference, perhaps the rest of the speakers at the conference will.

We at the UKOUG work long and hard to persuade the best speaker in the world to turn up and talk to us. For Tech 16, I have managed to gather

  • Jonathan Lewis
  • Maria Colgan
  • Connor McDonald
  • Graham Wood
  • James Morle
  • Martin Bach
  • Pete Finigan
  • Chris Antognini
  • Kamil Stawiarski
  • Dom Giles
  • Lothar Flatz
  • Mike Dietrich
  • Uwe Hesse
  • Tim Hall
  • Ludo Caldara
  • Frits Hoogland
  • Martin Widlake
  • 60 ACEs! 60! In Tech alone….. so many awesome guys and girls to teach us stuff. My only sadness is that, in EVERY talk slot there is more than 2 talk that I want to see (yep – at least 3 per hour) and I can’t see them all 😦

tech16_rd_gen_v1

OTN Appreciation Day : FlashBack Query

I love OTN, and I love Flashback query. It is a feature looking for an application use-case, but it really can dig you out of a major problem, recovering data you have recently deleted.

Here’s a silly example of the power:

select * from important_data

NAME                 PHONE_NUMBER
-------------------- ------------------------------
Partner A            555-1234
Partner B            555-5678

The choice is made:
update important_data set name = 'Spouse' where name = 'Partner B'

1 row updated.

And the redundant data removed:
delete from important_data where name = 'Partner A'

1 row deleted.

And all is good.
select * from important_data

NAME                 PHONE_NUMBER
-------------------- ------------------------------
Spouse               555-5678

Wait 7... years?
host sleep 7

And the data is missing something?

NAME                 PHONE_NUMBER
-------------------- ------------------------------
Spouse               555-5678

Perhaps we need to reconstruct the deleted data using Flashback
What did the table look like?

select * from important_data AS OF TIMESTAMP (SYSTIMESTAMP - interval '7' second)

NAME                 PHONE_NUMBER
-------------------- ------------------------------
Partner A            555-1234
Partner B            555-5678

And we can use Flashback to re-create the data.
insert into important_data (name,phone_number)
(select 'Hell-eau...', phone_number from important_data AS OF TIMESTAMP (SYSTIMESTAMP - interval '7' second) where name = 'Partner A')

1 row created.

And the data is correct again.

NAME                 PHONE_NUMBER
-------------------- ------------------------------
Hell-eau...          555-1234
Spouse               555-5678

rollback?

Stats Collection Time Anomaly

Johnathan Lewis (@JLOracle) recently published a short post about Stats Collection Time, talking about the table dba_optstat_operation (and dba_optstat_operation_tasks ), which reminded me about (what I regard as) an anomaly in the output in the NOTES columns in Oracle 12C.

I won’t repeat why it’s useful to check these tables as Johnathans note and @MDWidlakes’s comment here should give you all you need to know.

The DBA_OPTSTAT_OPERATION.NOTES column contains the parameters passed into the DBMS_STATS command, so you know what was done. It also reports the DEFAULT used by the DBMS_STATS job. Well, it does if you call DBMS_STATS explicitly, but the standard overnight auto job just says “default”. Why doesn’t is expand on that the way the explicit call does? If the default was changed between runs, you may end up with very different results but with no indication why. Am I missing something?

The following 2 rows of data show the output from each run. Note that the DEFAULT for METHOD_OPT in this database has been changed from “FOR ALL COLUMNS SIZE AUTO” to “FOR ALL COLUMNS SIZE REPEAT”** but was not explicitly passed-in for either run.

DBMS_STATS.GATHER_SCHEMA_STATS – decodes the DEFAULTs

OPERATION : gather_schema_stats            
TARGET    : MYSCHEMA     
START_TIME: 15-SEP-16 07.04.47 
END_TIME  : 15-SEP-16 07.09.02 
STATUS    : COMPLETED                                   
JOB_NAME  : 
NOTES     : <params>
            <param name="block_sample" val="FALSE"/>
            <param name="cascade" val="NULL"/>
            <param name="concurrent" val="FALSE"/>
            <param name="degree" val="NULL"/>
            <param name="estimate_percent" val="DBMS_STATS.AUTO_SAMPLE_SIZE"/>
            <param name="force" val="FALSE"/>
            <param name="gather_fixed" val="FALSE"/>
            <param name="gather_temp" val="FALSE"/>
            <param name="granularity" val="AUTO"/>
            <param name="method_opt" val="FOR ALL COLUMNS SIZE REPEAT"/>
            <param name="no_invalidate" val="NULL"/>
            <param name="options" val="GATHER"/>
            <param name="ownname" val="MYSCHEMA"/>
            <param name="reporting_mode" val="FALSE"/>
            <param name="statid" val=""/>
            <param name="statown" val=""/>
            <param name="stattab" val=""/>
            <param name="stattype" val="DATA"/>
            </params>

Autotask Overnight Gather – doesn’t decode the DEFAULTs

OPERATION : gather_database_stats (auto)   
TARGET    : AUTO       
START_TIME: 15-SEP-16 22.01.20 
END_TIME  : 15-SEP-16 22.38.40 
STATUS    : COMPLETED            
JOB_NAME  : ORA$AT_OS_OPT_SY_1212  
NOTES     : <params>
            <param name="block_sample" val="FALSE"/>
            <param name="cascade" val="NULL"/>
            <param name="concurrent" val="FALSE"/>
            <param name="degree" val="DEFAULT_DEGREE_VALUE"/>
            <param name="estimate_percent" val="DEFAULT_ESTIMATE_PERCENT"/>
            <param name="granularity" val="DEFAULT_GRANULARITY"/>
            <param name="method_opt" val="DEFAULT_METHOD_OPT"/>
            <param name="no_invalidate" val="DBMS_STATS.AUTO_INVALIDATE"/>
            <param name="reporting_mode" val="FALSE"/>
            <param name="stattype" val="DATA"/>
            </params>

 

 

 

**as it should be in EVERY ORACLE DATABASE EVER from the start, to allow you to control the histograms that you need and need to maintain on your schema. The Oracle default approach of “everything is skewed, thousands of histograms everywhere please” is particularly painful for OLTP databases using Bind Variable. I’m sure some of Oracles Adaptive Optimization is to work around the bad things that happen under this particular scenario.

 

DOAG Conference + Exhibition

Another quick post to let you know I will be presenting at the German Oracle User Group Conference [DOAG], which runs from 14th-18th of November 2016

This is a huge conference and exhibition over 4 days with many DOAG_2016tracks and a vast array of great speakers.

I hope to see you in Nuremberg! It will be good. Oh, yes. Es wird gut sein

 

DOAG Konferenz + Ausstellung 2016

%d bloggers like this: