I'm trying to run this query in java (connected via jdbc) and I get the ORA-00936 missing expression error.
Query:
sSQL = "INSERT INTO jn_db_user_t (db_id, user_id, user_typ_cde, row_crt_dte, row_upd_dte, row_dlet_ind) \n";
sSQL = sSQL + "VALUES \n";
sSQL = sSQL + "( \n";
sSQL = sSQL + " SELECT db_id, ";
sSQL = sSQL + " ( \n";
sSQL = sSQL + " SELECT user_id \n";
sSQL = sSQL + " FROM prj.prj_user_t \n";
sSQL = sSQL + " WHERE upper(user_lname) = 'SYSDBA' \n";
sSQL = sSQL + " AND row_dlet_ind = 'N' \n";
sSQL = sSQL + " ) as user_id, \n";
sSQL = sSQL + " 1 as user_type_cde, \n";
sSQL = sSQL + " SYSDATE, \n";
sSQL = sSQL + " SYSDATE, \n";
sSQL = sSQL + " 'N' as row_dlet_ind \n";
sSQL = sSQL + " FROM db_t \n";
sSQL = sSQL + " WHERE upper(db_nme) " + op2[j] + " \n";
sSQL = sSQL + " AND row_status_cde <> 2\n";
sSQL = sSQL + " AND db_id IN \n";
sSQL = sSQL + " ( \n";
sSQL = sSQL + " SELECT db_id \n";
sSQL = sSQL + " FROM db_t db, db_srvr_t s, sftw_prod_vrsn_t spv, sftw_prod_t sp \n";
sSQL = sSQL + " WHERE db.db_srvr_id = s.db_srvr_id \n";
sSQL = sSQL + " AND s.sftw_prod_vrsn_id = spv.sftw_prod_vrsn_id \n";
sSQL = sSQL + " AND spv.sftw_prod_id = sp.sftw_prod_id \n";
sSQL = sSQL + " AND upper(sp.sftw_prod_nme) = '" + en2[j] + "' \n";
sSQL = sSQL + " AND db.row_status_cde <> 2 \n";
sSQL = sSQL + " ) \n";
sSQL = sSQL + " )\n";If i strip the java coding out and run the query, this is the error I get:
SELECT db_id, (
*
ERROR at line 4:
ORA-00936: missing expressionHelp?
you need either another field or a 'FROM' clause. you are saying:
SELECT db_id,
SELECT user_id
try
SELECT db_id, user_id
you might also want to consider reading the query in from a file, storing the query as a view, or something else. looks kinda messy how it is right now.
Are you trying to create an Insert statement that uses the return from queries as the values, or do you intend to have the text of those queries be the values you're inserting?
Also, what are the values of op2[j] and en2[j] ? One has an equal sign before it, the other doesn't, in your code. Can you post your target query that you had and tested before you put it into a Java variable?
What you're trying to do seems strange to me; putting in select statements to get values for an insert statement. I'd do it in two separate queries, one to get the values and the other to insert the row, unless processing speed was important down to the nano-second (in which case you wouldn't be using java in the first place!:-)).
Can you use PL/SQL for this? It's great for building queries and cursors.