Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    10

    Question Unanswered: Sql*plus okey... but Not Java

    Hello,
    I can run a sql in sql*plus
    but when I execute the same sql in java,
    I got java.sql.SQLException: ORA-00900: invalid SQL statement

    Here is the sql:
    ---------------------------------------------------
    VARIABLE visitor1 NUMBER;
    VARIABLE buyer1 NUMBER;
    VARIABLE visitor2 NUMBER;
    VARIABLE buyer2 NUMBER;
    DECLARE
    V1 NUMBER;
    B1 NUMBER;
    V2 NUMBER;
    B2 NUMBER;
    BEGIN
    SELECT COUNT(*) INTO V1
    FROM (SELECT
    userUin USER_1
    FROM RPSUserProperty
    WHERE (value='Male')) User_t1,
    (SELECT
    DISTINCT userUin USER_UIN, itemUin ITEM_UIN1
    FROM ActionHistory10
    WHERE (actionTime > to_date('1999-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS') AND
    actionTime < to_date('2004-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS'))) User_t, (
    SELECT itemUIN ITEM_UIN FROM Item WHERE catUIN = '1') Item_t
    WHERE (User_t.ITEM_UIN1 = Item_t.ITEM_UIN) AND (User_t1.USER_1 = User_t.USER_UIN
    );
    :visitor1 :=V1;
    SELECT COUNT(*) INTO B1
    FROM (SELECT
    userUin USER_1
    FROM RPSUserProperty
    WHERE (value='Male')) User_t1,
    (SELECT
    DISTINCT userUin USER_UIN, itemUin ITEM_UIN1
    FROM ActionHistory16
    WHERE (actionTime > to_date('1999-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS') AND
    actionTime < to_date('2004-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS'))) User_t, (
    SELECT itemUIN ITEM_UIN FROM Item WHERE catUIN = '1') Item_t
    WHERE (User_t.ITEM_UIN1 = Item_t.ITEM_UIN) AND (User_t1.USER_1 = User_t.USER_UIN
    );
    :buyer1 :=B1;
    SELECT COUNT(*) INTO V2
    FROM (SELECT
    userUin USER_1
    FROM RPSUserProperty
    WHERE (value='Female')) User_t1,
    (SELECT
    DISTINCT userUin USER_UIN, itemUin ITEM_UIN1
    FROM ActionHistory10
    WHERE (actionTime > to_date('1999-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS') AND
    actionTime < to_date('2004-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS'))) User_t, (
    SELECT itemUIN ITEM_UIN FROM Item WHERE catUIN = '1') Item_t
    WHERE (User_t.ITEM_UIN1 = Item_t.ITEM_UIN) AND (User_t1.USER_1 = User_t.USER_UIN
    );
    :visitor2 :=V2;
    SELECT COUNT(*) INTO B2
    FROM (SELECT
    userUin USER_1
    FROM RPSUserProperty
    WHERE (value='Female')) User_t1,
    (SELECT
    DISTINCT userUin USER_UIN, itemUin ITEM_UIN1
    FROM ActionHistory16
    WHERE (actionTime > to_date('1999-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS') AND
    actionTime < to_date('2004-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS'))) User_t, (
    SELECT itemUIN ITEM_UIN FROM Item WHERE catUIN = '1') Item_t
    WHERE (User_t.ITEM_UIN1 = Item_t.ITEM_UIN) AND (User_t1.USER_1 = User_t.USER_UIN
    );
    :buyer2 :=B2;
    END;
    /
    ---------------------------------------------------
    is that any keyword can recognize by the sql*plus only
    but not java?
    thank you.

  2. #2
    Join Date
    Apr 2003
    Posts
    10

    Talking Re: Sql*plus okey... but Not Java

    It's me again, I fixed the sql
    I think java only accept the sql begin with some keywords,
    e.g. SELECT, CREATE..
    the sql below is begin with VARIABLE
    therefore java think it is invalid

    now I change the sequence of the sql and it's ok
    thank you for your attention


    Originally posted by idd0060
    Hello,
    I can run a sql in sql*plus
    but when I execute the same sql in java,
    I got java.sql.SQLException: ORA-00900: invalid SQL statement

    Here is the sql:
    ---------------------------------------------------
    VARIABLE visitor1 NUMBER;
    VARIABLE buyer1 NUMBER;
    VARIABLE visitor2 NUMBER;
    VARIABLE buyer2 NUMBER;
    DECLARE
    V1 NUMBER;
    B1 NUMBER;
    V2 NUMBER;
    B2 NUMBER;
    BEGIN
    SELECT COUNT(*) INTO V1
    FROM (SELECT
    userUin USER_1
    FROM RPSUserProperty
    WHERE (value='Male')) User_t1,
    (SELECT
    DISTINCT userUin USER_UIN, itemUin ITEM_UIN1
    FROM ActionHistory10
    WHERE (actionTime > to_date('1999-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS') AND
    actionTime < to_date('2004-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS'))) User_t, (
    SELECT itemUIN ITEM_UIN FROM Item WHERE catUIN = '1') Item_t
    WHERE (User_t.ITEM_UIN1 = Item_t.ITEM_UIN) AND (User_t1.USER_1 = User_t.USER_UIN
    );
    :visitor1 :=V1;
    SELECT COUNT(*) INTO B1
    FROM (SELECT
    userUin USER_1
    FROM RPSUserProperty
    WHERE (value='Male')) User_t1,
    (SELECT
    DISTINCT userUin USER_UIN, itemUin ITEM_UIN1
    FROM ActionHistory16
    WHERE (actionTime > to_date('1999-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS') AND
    actionTime < to_date('2004-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS'))) User_t, (
    SELECT itemUIN ITEM_UIN FROM Item WHERE catUIN = '1') Item_t
    WHERE (User_t.ITEM_UIN1 = Item_t.ITEM_UIN) AND (User_t1.USER_1 = User_t.USER_UIN
    );
    :buyer1 :=B1;
    SELECT COUNT(*) INTO V2
    FROM (SELECT
    userUin USER_1
    FROM RPSUserProperty
    WHERE (value='Female')) User_t1,
    (SELECT
    DISTINCT userUin USER_UIN, itemUin ITEM_UIN1
    FROM ActionHistory10
    WHERE (actionTime > to_date('1999-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS') AND
    actionTime < to_date('2004-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS'))) User_t, (
    SELECT itemUIN ITEM_UIN FROM Item WHERE catUIN = '1') Item_t
    WHERE (User_t.ITEM_UIN1 = Item_t.ITEM_UIN) AND (User_t1.USER_1 = User_t.USER_UIN
    );
    :visitor2 :=V2;
    SELECT COUNT(*) INTO B2
    FROM (SELECT
    userUin USER_1
    FROM RPSUserProperty
    WHERE (value='Female')) User_t1,
    (SELECT
    DISTINCT userUin USER_UIN, itemUin ITEM_UIN1
    FROM ActionHistory16
    WHERE (actionTime > to_date('1999-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS') AND
    actionTime < to_date('2004-01-01 00:00:00', 'YYYY/MM/DD HH24:MIS'))) User_t, (
    SELECT itemUIN ITEM_UIN FROM Item WHERE catUIN = '1') Item_t
    WHERE (User_t.ITEM_UIN1 = Item_t.ITEM_UIN) AND (User_t1.USER_1 = User_t.USER_UIN
    );
    :buyer2 :=B2;
    END;
    /
    ---------------------------------------------------
    is that any keyword can recognize by the sql*plus only
    but not java?
    thank you.

  3. #3
    Join Date
    Apr 2003
    Posts
    10

    Arrow Got another problem

    After I change the sql,
    I can execute it in Java,
    but I got another problem...

    when I call method:
    resultSet.next();

    it said "no statement parsed"
    May anyone tells me what's wrong?
    Thank you.

  4. #4
    Join Date
    Apr 2003
    Posts
    10

    Arrow Re: Got another problem

    Is it because I use the keyword "VARIABLE" which cannot read by Java ?

  5. #5
    Join Date
    Apr 2003
    Posts
    10

    Question Re: Sql*plus okey... but Not Java

    or I cannot do the things(CREATE function and SELECT)
    in one statement in Java?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •