Oracle stored procedure

Question

Can anyone help me by telling me what is wrong with the following syntax? I am just trying to run a simple SELECT statement in a stored procedure.

CREATE OR REPLACE PROCEDURE PVSSRDB.GETBATTERYSTATUSFORALLLOGGERS
(
  p_Logger OUT e.comment_,
  p_tStamp OUT h.ts,
  p_Val    OUT h.value_number
)
AS
BEGIN
  select 
    e.comment_,
    max(h.ts),
    avg(h.value_number)
INTO
  p_Logger,
  p_tStamp,
  p_Val
FROM 
  PVSSRDB.ELEMENTS e inner join PVSSRDB.DB15MINHISTORY_00100009 h on h.element_id =e.element_id
WHERE 
  e.element_name like 'System1:H%.BatteryCondition'
GROUP BY
  e.comment_
ORDER by 2 asc
END GETBATTERYSTATUSFORALLLOGGERS;

I keep getting the same 3 errors stating:

Error(9,3): PL/SQL: SQL Statement Ignored
Error(23,18): PL/SQL: ORA_00933: SQL command not properly ended
Error(24,34): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

Ollie Ollie · Accepted Answer · 2011-11-15T13:40:58

You're missing a semicolon after the ORDER BY 2 asc

Also you need to declare your OUT parameters properly.

CREATE OR REPLACE 
PROCEDURE PVSSRDB.GETBATTERYSTATUSFORALLLOGGERS (
   p_Logger OUT PVSSRDB.ELEMENTS.comment_%TYPE,
   p_tStamp OUT PVSSRDB.DB15MINHISTORY_00100009.ts%TYPE,
   p_Val    OUT PVSSRDB.DB15MINHISTORY_00100009.value_number%TYPE
) 
AS 
BEGIN
   select e.comment_,
          max(h.ts),
          avg(h.value_number) 
     INTO p_Logger,
          p_tStamp,
          p_Val 
     FROM PVSSRDB.ELEMENTS e 
    inner join PVSSRDB.DB15MINHISTORY_00100009 h on (h.element_id =e.element_id)
    WHERE e.element_name like 'System1:H%.BatteryCondition' 
    GROUP BY   e.comment_ 
    ORDER by 2 asc; 
END GETBATTERYSTATUSFORALLLOGGERS;

Oracle stored procedure

1 Answers