CODEWITHSUNDEEP
oracle-databaseplsql
looker
looker

Reputation: 45

Errors in oracle

i have the following procedure in oracle:

CREATE OR REPLACE procedure getPrecioMayorActual(PAR_ID_SUBASTA DECIMAL, 
outi out sys_refcursor) IS
 var1 decimal;
BEGIN
open outi for
        SELECT (p.MONTO_OFRECIDO) into var1 
        FROM (SUBASTA AS s) INNER JOIN (PUJA AS p) ON (s.ID_PUJA_MAYOR = 
p.ID_PUJA) 
        WHERE (s.ID_SUBASTA = PAR_ID_SUBASTA);
END getPrecioMayorActual;

The problem is that i'm getting the following errors: 

Error(5,9): PL/SQL: SQL Statement ignored
Error(6,23): PL/SQL: ORA-00907: missing right parenthesis

I've looked everywhere but I just can't manage to see what the problem is. I'm pretty sure that it must be something quite easy, but is my first time using this and I can't find anything useful on the web. Can anyone give me a hand please? Thanks.

Upvotes: 1

Views: 50

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

Just Removing as keywords is enough like the following(for table aliasing they can not used, but can be used for column aliasing, optionally):

create or replace procedure getPrecioMayorActual(PAR_ID_SUBASTA DECIMAL,
outi out sys_refcursor) IS
 var1 decimal;
BEGIN
open outi for
        SELECT (p.MONTO_OFRECIDO) into var1
        FROM (SUBASTA s) INNER JOIN (PUJA p) ON (s.ID_PUJA_MAYOR =
p.ID_PUJA)
        WHERE (s.ID_SUBASTA = PAR_ID_SUBASTA);
END getPrecioMayorActual;

In addition, even if the above syntax is correct, there's no need using paranthesis in the SELECT statement, and PROCEDURE's name at the end. Then, you may prefer writing your PROCEDURE as the following :

CREATE or REPLACE PROCEDURE getPrecioMayorActual(PAR_ID_SUBASTA DECIMAL, outi out sys_refcursor) IS
  var1 decimal;
BEGIN
 OPEN outi FOR
 SELECT p.MONTO_OFRECIDO
   INTO var1 
   FROM SUBASTA s INNER JOIN PUJA p ON s.ID_PUJA_MAYOR = p.ID_PUJA
  WHERE s.ID_SUBASTA = PAR_ID_SUBASTA;
END;

Upvotes: 1

jose_bacoy
jose_bacoy

Reputation: 12684

You dont need var1 since you are passing cursor to outi.

CREATE OR REPLACE procedure 
getPrecioMayorActual(PAR_ID_SUBASTA DECIMAL, 
outi out sys_refcursor) AS
BEGIN
open outi for
        SELECT (p.MONTO_OFRECIDO) 
        FROM (SUBASTA AS s) INNER JOIN (PUJA AS p) ON 
(s.ID_PUJA_MAYOR = 
p.ID_PUJA) 
        WHERE (s.ID_SUBASTA = PAR_ID_SUBASTA);
END getPrecioMayorActual;

Upvotes: 0

Related Questions