I am using below query in preparedStatment. earlier I was using same query in PL/SQL procedure.
INSERT INTO dynamicentitygtt
(entity_type, entity_id, entity_code, synonyms, action)
WITH data_view AS
( -- ITEM table
SELECT 'ITEMENTITY' entity_type, -- This separates inserted values
item_id data_id,
item_name data_name,
item_desc data_desc,
creation_date
FROM itemde
UNION ALL
-- ORG table
SELECT 'ORGENTITY' entity_type, -- This separates inserted values
org_id,
org_name,
org_desc,
creation_date
FROM orgde
UNION ALL
-- Feature table
SELECT 'FEATUREENTITY' entity_type, -- This separates inserted values
FEATURE_id data_id,
FEATURE_NAME data_name,
FEATURE_DESC data_desc,
CREATION_DATE
FROM FEATURESDE
)
SELECT upper(t.entity_type),
t.data_id,
t.data_name,
t.data_desc,
CASE UPPER(p_update_mode)
WHEN 'INCREMENTAL' THEN
CASE
WHEN t.creation_date > b.last_update_date THEN
'modify'
WHEN t.creation_date < b.last_update_date THEN
'add'
END
WHEN 'FULL' THEN
'add'
END action
FROM data_view t
LEFT JOIN ODA_REFRESH_DETAILS b
ON b.entity_type = t.entity_type
AND lower(p_update_mode )='INCREMENTAL'
WHERE (upper(p_entity_type) = t.entity_type OR p_entity_type IS NULL)
AND (UPPER(p_update_mode) = 'FULL'
OR (UPPER(p_update_mode) = 'INCREMENTAL' AND b.entity_type IS NOT NULL)
);
Here 2 variables p_entity_type and p_update_mode I am receiving from upper stream. so I am using ? to set these values.
my code is below. Whereever these variables are I put ? and later set the corresponding values.
public int insertDataToGTT(String p_entity_type, String p_update_mode) throws Exception
{
String sql= "INSERT INTO dynamicentitygtt
(entity_type, entity_id, entity_code, synonyms, action)
WITH data_view AS( -- ITEM table
SELECT 'ITEMENTITY' entity_type, -- This separates inserted values
item_id data_id,
item_name data_name,
item_desc data_desc,
creation_date
FROM itemde
UNION ALL
-- ORG table
SELECT 'ORGENTITY' entity_type, -- This separates inserted values
org_id,
org_name,
org_desc,
creation_date
FROM orgde
UNION ALL
-- Feature table
SELECT 'FEATUREENTITY' entity_type, -- This separates inserted values
FEATURE_id data_id,
FEATURE_NAME data_name,
FEATURE_DESC data_desc,
CREATION_DATE
FROM FEATURESDE
)
SELECT upper(t.entity_type),
t.data_id,
t.data_name,
t.data_desc,
CASE UPPER(?) //p_update_mode
WHEN 'INCREMENTAL' THEN
CASE
WHEN t.creation_date > b.last_update_date THEN
'modify'
WHEN t.creation_date < b.last_update_date THEN
'add'
END
WHEN 'FULL' THEN
'add'
END action
FROM data_view t
LEFT JOIN ODA_REFRESH_DETAILS b
ON b.entity_type = t.entity_type
AND lower(?)='INCREMENTAL' //p_update_mode
WHERE (upper(?) = t.entity_type OR ? IS NULL) //p_entity_type(both places)
AND (UPPER(?) = 'FULL' //p_update_mode
OR (UPPER(?) = 'INCREMENTAL' AND b.entity_type IS NOT NULL) //p_update_mode
) "
PreparedStatement statement= connection.prepareStatement(sql);
statement.setString(1, p_update_mode);
statement.setString(2, p_update_mode);
statement.setString(3, p_update_mode);
statement.setString(4, p_entity_type);
statement.setString(5, p_entity_type);
statement.setString(6, p_update_mode);
int x=statement.executeUpdate();
System.out.println("Number of rows inserted: "+x);
return x;
}
It is giving me below exception. I checked parameter I have set proper. Actually I am using same values multiple places. is there anyway or any better way to do it?
Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1052)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:537)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:255)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:610)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:253)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:86)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:928)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1136)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3640)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1384)
at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3730)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3710)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1061)
at oracle.apps.vof.v1.oda.dao.NewCallableDAONEW.insertDataToGTT(NewCallableDAONEW.java:106)
at oracle.apps.vof.v1.oda.server.ODADynamicEntity.getDynamicEntities(ODADynamicEntity.java:143)
at oracle.apps.vof.v1.oda.server.ODADynamicEntity.main(ODADynamicEntity.java:43)
Caused by: Error : 923, Position : 1028, Sql = INSERT INTO dynamicentitygtt
(entity_type, entity_id, entity_code, synonyms, action)
WITH data_view AS
( -- ITEM table
SELECT 'ITEMENTITY' entity_type, -- This separates inserted values
item_id data_id,
item_name data_name,
item_desc data_desc,
creation_date
FROM itemde
UNION ALL
-- ORG table
SELECT 'ORGENTITY' entity_type, -- This separates inserted values
org_id,
org_name,
org_desc,
creation_date
FROM orgde
UNION ALL
-- Feature table
SELECT 'FEATUREENTITY' entity_type, -- This separates inserted values
FEATURE_id data_id,
FEATURE_NAME data_name,
FEATURE_DESC data_desc,
CREATION_DATE
FROM FEATURESDE
)
SELECT upper(t.entity_type),
t.data_id,
t.data_name,
t.data_desc,
CASE UPPER(:p_update_mode) //p_update_mode
WHEN 'INCREMENTAL' THEN
CASE
WHEN t.creation_date > b.last_update_date THEN
'modify'
WHEN t.creation_date < b.last_update_date THEN
'add'
END
WHEN 'FULL' THEN
'add'
END action
FROM data_view t
LEFT JOIN ODA_REFRESH_DETAILS b
ON b.entity_type = t.entity_type
AND lower(:p_update_mode)='INCREMENTAL' //p_update_mode
WHERE (upper(:p_entity_type) = t.entity_type OR :p_entity_type IS NULL) //p_entity_type(both places)
AND (UPPER(:p_update_mode) = 'FULL' //p_update_mode
OR (UPPER(:p_update_mode) = 'INCREMENTAL' AND b.entity_type IS NOT NULL) //p_update_mode
), OriginalSql = INSERT INTO dynamicentitygtt
(entity_type, entity_id, entity_code, synonyms, action)
WITH data_view AS
( -- ITEM table
SELECT 'ITEMENTITY' entity_type, -- This separates inserted values
item_id data_id,
item_name data_name,
item_desc data_desc,
creation_date
FROM itemde
UNION ALL
-- ORG table
SELECT 'ORGENTITY' entity_type, -- This separates inserted values
org_id,
org_name,
org_desc,
creation_date
FROM orgde
UNION ALL
-- Feature table
SELECT 'FEATUREENTITY' entity_type, -- This separates inserted values
FEATURE_id data_id,
FEATURE_NAME data_name,
FEATURE_DESC data_desc,
CREATION_DATE
FROM FEATURESDE
)
SELECT upper(t.entity_type),
t.data_id,
t.data_name,
t.data_desc,
CASE UPPER(:p_update_mode) //p_update_mode
WHEN 'INCREMENTAL' THEN
CASE
WHEN t.creation_date > b.last_update_date THEN
'modify'
WHEN t.creation_date < b.last_update_date THEN
'add'
END
WHEN 'FULL' THEN
'add'
END action
FROM data_view t
LEFT JOIN ODA_REFRESH_DETAILS b
ON b.entity_type = t.entity_type
AND lower(:p_update_mode)='INCREMENTAL' //p_update_mode
WHERE (upper(:p_entity_type) = t.entity_type OR :p_entity_type IS NULL) //p_entity_type(both places)
AND (UPPER(:p_update_mode) = 'FULL' //p_update_mode
OR (UPPER(:p_update_mode) = 'INCREMENTAL' AND b.entity_type IS NOT NULL) //p_update_mode
), Error Msg = ORA-00923: FROM keyword not found where expected
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
... 17 more
same query is working properly in pl/sql