Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: datatypes
PL/SQL – Don’t mix and match scope
Here’s a simple little PL/SQL block where we call an inner procedure PARAMETER_TESTER from its parent block. Pay particular attention to the parameter we pass to the procedure, and it’s value throughout the execution of that procedure. SQL> set serverout on SQL> declare 2…
Another day…another "use the right datatype" post
Here’s an interesting little oddity (aka bug) with scalar queries. We’ll start with a simple working example SQL> create table t1 ( c1 number ); Table created. SQL> insert into t1 values (1); 1 row created. SQL> create table t2 ( c1 int, c2…
Correcting datatypes with minimal downtime
Just a quick post here by request of an attendee of the September Office Hours. I had a demo converting the data type of a primary key without losing data and with minimal impact to the availability of the database using DBMS_REDEFINITION. You can…
Datatype conversion laziness … yet another reason
I’ve got two words to describe my thoughts on being lazy with data types …. JUST DON’T! There’s been many blog posts, videos, AskTOM questions etc over the years with numerous examples of how the optimizer gets confused, how the SQL engine gets confused…and…
Literals and VARCHAR2
We all know that comparing two VARCHAR2 fields of different lengths will be treated by Oracle as being not EQUAL: SQL> drop table T purge; Table dropped. SQL> create table T ( x varchar2(10), y varchar2(10)); Table created. SQL> insert into T values (‘abc’,’abc…
NUMBER data type…what harm can it do ?
There’s a somewhat sour discussion going on based attached to the video at https://www.youtube.com/watch?v=jZW-uLb52xk Whether you agree or disagree with the video or the comments, or (sadly) the animosity in them, it does lead to an interesting bit of investigation when it comes to…
Datatype discipline
(This is based on some ‘issues’ a client was having, so the example is architected to make a particular point, but is relevant nonetheless) In the distributed database world, we often have different names for the same piece of information. Consider two tables which…
