Keeping Null's as it is and separating them by Comma

Shell Programming and Scripting
1

Hi,

       I am having source \(Oracle\) as given below.

SourceOBJECT_NAMESUBOBJECT_NAMEOBJECT_IDDATA_OBJECT_IDOBJECT_TYPECREATEDLAST_DDL_TIMESTAMPSTATUSTGSTEST 336559336559TABLE4/15/2009 10:374/15/2009 10:372009-04-15:10:37:57VALIDNNNUNIX 336559336559TABLE4/15/2009 10:374/15/2009 10:372009-04-15:10:37:57VALIDNNNWINDOWS 336559336559TABLE4/15/2009 10:374/15/2009 10:372009-04-15:10:37:57VALIDNNNINFORM 336559336559TABLE4/15/2009 10:374/15/2009 10:372009-04-15:10:37:57VALIDNNN

While i am using the below functions, as the 2nd column SUBOBJECT_NAME is having NULL values, the 3rd column values are moving into 2nd column and so on.

Commands:

perl -pne 's/\s+([^\s+])/,\1/g' out.txt
awk '$1=$1' OFS=\, file
awk -F, 'NF==6{$0=gensub ("([0-9]+, [[:alpha:]]+),","\\1",1);$0=gensub (", ([[:alpha:]]+, [0-9]+$)"," \\1",1)}1' file

Target data:

OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_,TIMESTAMP,STATUS,T,G,S
TEST,336559,336559,TABLE,15-Apr-09,15-Apr-09,2009-04-15:10:37:57,VALID,N,N,N,
UNIX,336559,336559,TABLE,15-Apr-09,15-Apr-09,2009-04-15:10:37:57,VALID,N,N,N,
EBILITE,336559,336559,TABLE,15-Apr-09,15-Apr-09,2009-04-15:10:37:57,VALID,N,N,N,
EBILITE,MIGRATION,336559,336559,TABLE,15-Apr-09,15-Apr-09,2009-04-15:10:37:57,VALID,N,N,N

My Requirement: I want the null values to be present in the file and should be separated by comma.

Thanks in advance for your replies.

2

You should look for a way to directly output in the desired format from Oracle.
There are lot of formatting option available in sql and other sql related languages.

You can for example have look at http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11\_QUESTION_ID:88212348059

1 Like
3

Thanks for your reply. But the thing is after fetching the data from Oracle into UNIX into a flatfile and applying the above mentioned commands are giving wrong results.

4

The link i referred to, demonstrate that you need to setup a script at oracle level so that when you call it, it generate the *.csv file into the expected format.

the specified command need to be entered into and *.sql file, NOT a *.sh nor a .bash nor <whatever>.shell

So the commands should be applied and run at Oracle (sql) level, not OS level ...