How to return SQLPLUS Null as \N

Question

I have a script that moves that from one DB to another by a SQL. I'm querying from one side and load it into another. TSV style. I'm expanding that script now to support Oracle as a source. For that to work I need two things:

1. make the result return as tab.
2. return null as \N

mysql does both as default. psql has a flags for then -AF $'\t' -P 'null=\N'.

For (1) in sqlplus I can add before the SQLs set colsep. But for (2) I have no idea how to do it. It can't be modification to SQL itself, either something to add before the SQL, sqlplus flag, or post command (maybe sed) that will happen when the data returns to the client (this is the least preferred option as there might be bugs in it).

Would love some help.

What I could find is

SQL> set null "\N"
SQL> select null from dual;
N
-
\
N
SQL> set null '\\N'
SQL> select null from dual;
N
-
\
\
N

But as you can see it doesn't like that backslash

Answer 1

It is just about column width.

By default:

SQL> select null from dual;
N
-

Then you did this:

SQL> set null "\N"

and got this:

SQL> select null from dual;
N
-
\
N

If you fix column size ("NULL" is a string, 4 characters in length) :

SQL> col null format a4

you get what you wanted:

SQL> select null from dual;
NULL
----
\N
SQL>

• gsiems: Fixing the column size using format isn't necessary. Simply ensuring that the select returns columns that are defined as longer than char(1) (i.e. select cast ( null as varchar2 ( 10 ) ) as x from dual; ) also works fine.
• Littlefoot: Likewise, @gsiems, casting isn't necessary - simply fix column size (i.e. both ways will do; as usual, one problem may have one, two or more solutions). Thank you for your comment!
• Nir: Unfortunately both aren't ideal. I have no idea what the column name is/will be, and I can't manipulate the SQL. As the SQL is an argument in the script. I can only add code before the SQL, or to sqlplus.