Get Exception while executing a oracle procedure in java

Question

I am trying to execute a procedure in oracle, but i get a exception while executing the procedure.Please help me where i am wrong.

Error :

    java.sql.SQLException: ORA-06550: line 1, column 36:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:

   . ( ) , * @ % & = - + < / > at in is mod remainder not rem =>
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || indicator multiset member submultiset
The symbol ")" was substituted for ";" to continue.

Procedure :

CREATE OR REPLACE PROCEDURE "TEST_101"."AVG_UNLOADING" 
(
P_CODE IN VARCHAR2,
P_DATE IN VARCHAR2,
P_VANID IN NUMBER,
P_CURSOR OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN P_CURSOR
FOR
select CARGO_NAME,IMP_EXP_NAME,'TEMP_CARGO_Import',ROUND(SUM(QTY)/((((extract(day from max(END_TIME)-DISCHRG_CMNCD_ANCHRG_TM)*(24*60))+
(extract(day from max(END_TIME)- DISCHRG_CMNCD_ANCHRG_TM)*(60)) + (EXTRACT(DAY FROM max(END_TIME)- DISCHRG_CMNCD_ANCHRG_TM)))-DELAYS)/(24*60))) QTY
from(SELECT (SELECT C.CARGO_CATEGORY_NAME FROM IPT_CARGOMASTER C WHERE C.CARGO_CODE =LL.CARGO_CODE )CARGO_NAME,IL.IMP_EXP_NAME,CASE L.CARGO_TYPE_CODE WHEN 'VC001' THEN LL.DISCHARGE_QUANTITY WHEN 'VC002' THEN LL.QUANTITY_GMT WHEN 'VC004' THEN LL.QUANTITY_GMT 
else LL.DISCHARGE_QUANTITY end QTY ,L.DISCHRG_CMNCD_ANCHRG_TM,LL.END_TIME ,(SELECT SUM(T3.TOTAL_TIME) FROM IPT_LOADUNLOADDELAYLINES T3 WHERE T3.ID = L.ID AND T3.MINUS_DELAY_HOURS = 'true') AS DELAYS    
,LL.LINE_ID,L.VAN_ID FROM IPT_LOADINGUNLOADING L JOIN IPT_LOADUNLOADOPERATIONLINES LL ON L.ID=LL.ID  LEFT JOIN IPT_IMPORTEXPORTFORM I ON I.VAN_ID=L.VAN_ID JOIN IPT_IGMEPCARGOLINES IL ON I.ID=IL.ID AND LL.CARGO_CODE=IL.CARGO_CODE  
where L.PORTDETAIL_CODE= P_CODE and I.PORTDETAIL_CODE=L.PORTDETAIL_CODE and IL.IMP_EXP_NAME like '%KKR%' AND LL.END_TIME<=TO_TIMESTAMP(TO_CHAR(P_DATE || ' 06:59'),'dd/MM/yyyy HH:MI')   
and L.VAN_ID in (P_VANID )
)t group by CARGO_NAME, IMP_EXP_NAME, 'TEMP_CARGO_Import',DISCHRG_CMNCD_ANCHRG_TM,DELAYS;
END AVG_UNLOADING;

Executing procedure

stkagentlist = "{call AVG_UNLOADING(?,?,?,?}";
callableStatement = conn.prepareCall(stkagentlist);
callableStatement.setString(1, portCode);  
callableStatement.setString(2, dt );  
callableStatement.setString(3, vanids ); 
callableStatement.registerOutParameter(4, OracleTypes.CURSOR);
callableStatement.executeUpdate();

Group by get executed before select and you specify (DELAYS) in the group by, I think this is the error — Fady Saad
– Fady Saad, Commented Apr 17, 2017 at 7:03
Your query is an absolute mess. Neat layout is not some fashionable extra, it's a real help when it comes to debugging code. — APC
– APC, Commented Apr 17, 2017 at 8:50
stkagentlist = "{call AVG_UNLOADING(?,?,?,?}"; Is this right? Your call is missing a matching right bracket. — APC
– APC, Commented Apr 17, 2017 at 8:57

Floern · Accepted Answer · 2017-04-17 17:28:19Z

1

stkagentlist = "{call AVG_UNLOADING(?,?,?,?}";

I think in this statement the closing round bracket is missing, it should be:

stkagentlist = "{call AVG_UNLOADING(?,?,?,?)}";

edited Apr 17, 2017 at 17:28

Floern

34k24 gold badges107 silver badges122 bronze badges

answered Apr 17, 2017 at 16:26

vanita jeshnani

386 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get Exception while executing a oracle procedure in java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related