Hi all.
This is my first post on this forum. I've previously found great help in the huge knowledgebase that is here, but this time I have not been able to find a solution to my problem.
I have a large text file that looks like this:
typedef struct ABC_struct_nbr1_ {
char attr1[12+1]; /* KEY=Y, INIT="", MANDATORY=Y */
char attr2[12+1]; /* KEY=Y, INIT="", MANDATORY=Y */
double attr3; /* KEY=N, INIT=0.0, MANDATORY=Y */
int attr4; /* KEY=N, INIT=0.0, MANDATORY=Y */
char attr5[20+1]; /* KEY=N, INIT="", MANDATORY=N */
} ABC_struct_nbr1;
typedef struct ABC_struct_nbr2_ {
char attr1[10+1]; /* KEY=Y, INIT="", MANDATORY=Y */
char attr2[20+1]; /* KEY=Y, INIT="", MANDATORY=Y */
char attr3[1+1]; /* KEY=N, INIT="Y", MANDATORY=Y */
} ABC_struct_nbr2;
typedef struct ABC_struct_nbr299_ {
char attr1[150+1]; /* KEY=Y, INIT="", MANDATORY=Y */
int attr2; /* KEY=N, INIT=0, MANDATORY=Y */
char* attr3; /* KEY=N, INIT="", MANDATORY=N */
} ABC_struct_nbr299;
My goal is to turn this file into SQL insert statements like these:
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
values('ABC_struct_nbr1','attr1','varchar2',12,'N',1,'/* KEY=Y, INIT="", MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
values('ABC_struct_nbr1','attr2','varchar2',12,'N',2,'/* KEY=Y, INIT="", MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
values('ABC_struct_nbr1','attr3','number',22,'Y',null,'0.0/* KEY=N, INIT=0.0, MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
values('ABC_struct_nbr1','attr4','number',22,'Y',null,'0.0 /* KEY=N, INIT=0.0, MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
values('ABC_struct_nbr1','attr5','varchar2',20,'Y',null,'/* KEY=N, INIT="", MANDATORY=N */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
values('ABC_struct_nbr2','attr1','varchar2',10,'N',1,'/* KEY=Y, INIT="", MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
values('ABC_struct_nbr2','attr2','varchar2',20,'N',2,'/* KEY=Y, INIT="", MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
values('ABC_struct_nbr2','attr3','varchar2',1,'Y',null,'Y /* KEY=N, INIT="Y", MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
values('ABC_struct_nbr299','attr1','varchar2',150,'N',1,'/* KEY=Y, INIT="", MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
values('ABC_struct_nbr299','attr2','number',22,'N',null,'0 /* KEY=N, INIT=0, MANDATORY=Y */');
insert into abc_structure(table_name, column_name, data_type, data_length, data_nullable, pk_seq, data_default)
values('ABC_struct_nbr299','attr3','clob',null,'Y',null,'/* KEY=N, INIT="", MANDATORY=N */');
These should be used to insert data into a table like this (I include the DDL for your reference):
create table abc_structure(
table_name varchar2(30) not null
,column_name varchar2(30) not null
,data_type varchar2(30)
,data_lenght number
,data_nullable varchar2(1)
,pk_seq number
,data_default varchar2(4000)
)
Each block of source text should generate a set of SQL DML statements, and as you see, there are a number of conversions that have to be made. E.g:
char -> varchar
char* -> clob
double -> number
int -> number
I thougth about using awk for this, but my knowledge of scripting in general and awk in particular falls short. I would highly appreciate some pointers from any users of the forum. Thanks!