[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.

[idd0060]

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.

[idd0060]

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.

[idd0060]

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

[idd0060]

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