JDBC query to Oracle

Question

Hi, We are planning to migrate our DB to Oracle.We need to manually check each of the embedded SQL is working in Oracle as few may follow different SQL rules.Now my need is very simple.

I need to browse through a file which may contain queries like this.

String sql = "select * from test where name="+test+"and age="+age;

There are nearly 1000 files and each file has different kind of queries like this where I have to pluck the query alone which I have done through an unix script.But I need to convert these Java based queries to Oracle compatible queries.

ie. select * from test where name="name" and age="age"

Basically I need to check the syntax of the queries by this.I have seen something like this in TOAD but I have more than 1000 files and can't manually change each one.Is there a way?

I will explain more i the question is not clear

Answer 1

For performance and security reasons you should use PreparedStatement.bind(...) rather than string concatenation to build your SQL strings.

I don't know of a way to tackle this problem other than fixing the code that needs to be fixed. If you can find common patterns then you can automate some of the editing using find/replace or sed or some other tool, as long as you diff the result before checking it in.

If there are thousands of files I guess that there is a reasonable sized team that built the code this way. It seems fair to share the workload out amongst the people that built the system, rather than dump it all on one person. Otherwise you will end up as the "SQL fixing guy" and nobody else on the team will have any incentive to write SQL code in a more portable way.

Answer 2

Does your current application execute SQL through a common class? Could you add some logging to print out the raw SQL in this common class? From that output you could write a small script to run each statement against Oracle.