Oracle “create table as” with null column value

When creating new tables with from a query output you may use "create table as" statement.

Sometimes you need to specify hardcoded null values for some columns. For example, in the following query we want null value for col_3:

create table NewTable as 
select 
   col_1, 
   col_2, 
   null col_3
from OldTable;

If you create an Oracle table using "create as" where one of your fields is null you will get the error:

ORA-01723: zero-length columns are not allowed

To fix this issue, you can cast the null value to a target data type and specify the size of the field where required. For example:

create table NewTable as 
select 
   col_1,
   col_2,
   cast(null as varchar2(100)) col_3,
   cast(null as date) col_4,
   cast(null as number) col_5
from OldTable;

How to find the PublicKeyToken for .Net assembly

The public key token is a unique 16-character key that is given to the assembly when it is built and signed in Microsoft Visual Studio.

Often you need to specifiy Public-Key-Token, like when you have to define a provider in connectionString in web.config, or referencing external dlls in web.config, in these cases you need to know the Public-Key-Token for that particular assembly or dll.

There are two ways you can find the Public-Key-Token for an assembly:

Using Powershell

You can execute this statement:

([system.reflection.assembly]::loadfile("C:\FullPath..\Newtonsoft.Json.dll")).FullName

The output will provide the Version, Culture and PublicKeyToken as shown below:

Newtonsoft.Json, Version=13.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed

Using the Strong Name tool (SN.exe)

Open the Visual Studio Command Prompt and then point to the dll’s folder you want to get the public key, use the following command,

sn –T Newtonsoft.Json.dll

Or specify the full path of the dll.

sn –T "C:\FullPath..\Newtonsoft.Json.dll"

This will give you the public key token.

Remember this only works if the assembly is strongly signed.