1 person found this helpful
If the excel file is in CSV format or if it can be converted to CSV file format then you can create a external table using that file and do a direct join (outer join in your case) to the table in your DB and obtain the matching rows.
Thanks for reply.That will work but i am looking for solution without having external table.
select col2,'Y' from t1 where col2 in(.... ).Let us assume i copied all 1000 values within braces.i am able to get required below o/p
But i need 6,n also in o/p.
To obtain what you want you need to have the value from Your Excel file in a relational structure. External table is a elegant way to do that. If you wish to do a copy past then you can use a WITH clause like this to obtain the relational structure.
with xl_file_output as ( select 1 col_from_xl from dual union all select 2 col_from_xl from dual ... and so on .. ) select col2 , decode(col_from_xl, null, 'N', Y') from t1 left join xl_file_output t on t1.col2 = t.col_from_xl;
You can try your cut and past method in this:
select column_value , case when exists ( select null from table1 where col1 = column_value ) then 'y' else 'n' end from table( dbmsoutput_linesarray( 2, 3, 4, 5, 6 ) )