oracle11g - If I run my Query in Oracle it is running without any error but when I run it inside C# gives ORA-00911: invalid character -
using (oracleconnection conn1 = new oracleconnection(oradb1)) { conn1.open(); using (oraclecommand crtcommand = new oraclecommand("with src (select src.table_name src_table_name, src.column_name src_col_name, src.data_type src_data_type, src.data_length src_data_len, src.data_precision src_data_precision, src.data_scale src_data_scale, src.nullable src_nullable, decode(t.constraint_type, 'p', 'primary key', 'u', 'unique', '') src_cons all_tab_columns src left join (select cc.column_name, uc.constraint_type user_cons_columns cc, user_constraints uc cc.constraint_name = uc.constraint_name , cc.table_name = uc.table_name) t on t.column_name = src.column_name table_name = 'instructor' , owner = 'erhan'), tgt (select tgt.table_name tgt_table_name, tgt.column_name tgt_col_name, tgt.data_type tgt_data_type, tgt.data_length tgt_data_len, tgt.data_precision tgt_data_precision, tgt.data_scale tgt_data_scale, tgt.nullable tgt_nullable, decode(t.constraint_type, 'p', 'primary key', 'u', 'unique', '') tgt_cons all_tab_columns tgt left join (select cc.column_name, uc.constraint_type user_cons_columns cc, user_constraints uc cc.constraint_name = uc.constraint_name , cc.table_name = uc.table_name) t on t.column_name = tgt.column_name table_name = 'instructor' , owner = 'sarigul'), col_details (select src.src_table_name, nvl(tgt.tgt_table_name, first_value(tgt_table_name) over( order tgt_table_name nulls last)) tgt_table_name, src.src_col_name, src.src_data_type, src.src_data_len, src.src_data_precision, src.src_data_scale, src.src_nullable, src_cons, tgt.tgt_col_name, tgt.tgt_data_type, tgt.tgt_data_len, tgt.tgt_data_precision, tgt.tgt_data_scale, tgt.tgt_nullable, tgt_cons src full outer join tgt on ( src.src_col_name = tgt.tgt_col_name )) select * (select case when tgt_data_type != src_data_type or tgt_data_len != src_data_len or tgt_data_precision != src_data_precision or tgt_data_scale != src_data_scale or tgt_nullable != src_nullable 'alter table ' || tgt_table_name || ' modify ' || tgt_col_name || ' ' || src_data_type || ' ' || case when src_data_type in ( 'date' ) null else case when src_data_type in ( 'varchar', 'varchar2' ) ' (' || nvl(to_char(src_data_len), ' ') || ') ' else decode(nvl(src_data_precision, -1), -1, null, nvl(to_char(src_data_precision), ' ') || ', ' || nvl(to_char(src_data_scale), ' ') || ')') end end || case when tgt_nullable = 'y' ' null ' else ' not null ' end || tgt_cons when tgt_col_name null 'alter table ' || tgt_table_name || ' add ' || src_col_name || ' ' || ' ' || ' ' || src_data_type || ' ' || case when src_data_type in ( 'date' ) null else case when src_data_type in ( 'varchar', 'varchar2' )then '(' || nvl(to_char(src_data_len), ' ') || ') ' else decode(nvl(src_data_precision, -1 ), -1, null, nvl(to_char(src_data_precision), ' ') || ', ' || nvl(to_char(src_data_scale), ' ') || ')') end end || tgt_cons when src_col_name null 'alter table ' || tgt_table_name ||' drop ' ||tgt_col_name end alter_statement col_details) alter_statement not null; ", conn1)) { richtextbox1.appendtext(environment.newline); richtextbox1.appendtext(crtcommand.executenonquery().tostring()); richtextbox1.appendtext(environment.newline); } }
remove ";" semicolon @ end! oraclecommand automatically adds on @ end of query string.
Comments
Post a Comment