Michelle Michelle - 3 months ago 13
SQL Question

Passing Argument from Java to Sql

I've worked with sqlplus and Java but never together until now. I'm having difficulty in getting a command line argument into sql to return a regular expression-specific list of users. My if statement and error is below. I believe the program is actually searching the list of users for "^A". Any tips on resolving this would be greatly appreciated.

else if (num == 1 && !args[0].equals("-n"))
{
String cmd = "select * from all_users where regexp_like(username, " + args[0] + ", 'i') order by username";
System.out.println(cmd);
String users[] = ora.doSql(cmd);
for (String u: users)
System.out.println(u);
}


My results:

java ShowUsers ^A
select * from all_users where regexp_like(username, ^A, 'i') order by username
select * from all_users where regexp_like(username, ^A, 'i') order by username
*
ERROR at line 1:
ORA-00936: missing expression

Answer

Quotes are missing inside refexp_like. Try the following:

else if (num == 1 && !args[0].equals("-n"))
{
     String cmd = "select * from all_users where regexp_like(username, '" + args[0] + "', 'i') order by username";
     System.out.println(cmd);
     String users[] = ora.doSql(cmd);
     for (String u: users)
         System.out.println(u);
 }

However, as mentioned by all others, this is considered a bad practice and your code will be prone to sql injection.
So you should really use prepared statements instead.

Comments