#1
  1. PHP & Java Error Master
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    My Computer
    Posts
    1,219
    Rep Power
    0

    Problem with oracle query via Java


    I'm trying to run this query in java (connected via jdbc) and I get the ORA-00936 missing expression error.
    Code:
    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:
    Code:
     SELECT db_id, (
     *
    ERROR at line 4:
    ORA-00936: missing expression
    Help?
  2. #2
  3. SwollenMember
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    the master control
    Posts
    264
    Rep Power
    15
    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.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    New York
    Posts
    19
    Rep Power
    0
    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.

IMN logo majestic logo threadwatch logo seochat tools logo