Creating procedure

[Ivone]

Hello,

I am creating a procedure and there was a compilation error, I would like to know what is wrong.

Select all

 
CREATE OR REPLACE PROCEDURE PESO_VALOR 
(p_valorprato IN valor_kg.valor%TYPE) 
IS 
BEGIN 
SELECT V.DESCRICAO, B.QTDE KG, (V.VALOR * B.QTDE / (V.PESO)) VALOR 
FROM BALANCA B, VALOR_KG V 
WHERE V.CODKG = B.CODKG 
AND V.CODKG = 01 
AND v.valor = p_valorprato; 
END PESO_VALOR; 
/ 

Thanks

[victorhugomuniz]

Ivone,

Which error is giving ???

Have you taken the SELECT from within and tested it separately?

Try in this way ..
To use the entry parameter should put two points (:)

Select all

 
CREATE OR REPLACE PROCEDURE PESO_VALOR 
(p_valorprato IN valor_kg.valor%TYPE) 
IS 
BEGIN 
SELECT V.DESCRICAO, B.QTDE KG, (V.VALOR * B.QTDE / (V.PESO)) VALOR 
FROM BALANCA B, VALOR_KG V 
WHERE V.CODKG = B.CODKG 
AND V.CODKG = 01 
AND v.valor = :p_valorprato; 
END PESO_VALOR; 
/  

[Ivone]

Error:
Warning: Procedure created with compilation errors.

I made the change and continues with the same error

The SELECT runs separately

Select all

SQL> SELECT V.DESCRICAO, B.QTDE KG, (V.VALOR * B.QTDE / (V.PESO)) VALOR 
  2  FROM BALANCA B, VALOR_KG V 
  3  WHERE V.CODKG = B.CODKG 
  4  AND V.CODKG = 01; 
 
DESCRICAO                    KG      VALOR 
-------------------- ---------- ---------- 
PRATO PRINCIPAL             350     10,465

[victorhugomuniz]

Can not you run this in an IDE to see which error?

Error:
Warning: Procedure created with compilation errors.

This is not the error

[victorhugomuniz]

Correcting ..
It does not really have the two points to use the input parameter

follows an example

Select all

 
CREATE OR REPLACE PROCEDURE aumenta_sal (p_empno IN emp.empno%TYPE) IS 
BEGIN 
	UPDATE  
		scott.emp 
	SET  
		sal = sal * 1.10 
	WHERE  
		empno = p_empno; 
END aumenta_sal; 
/ 

has to know which error is giving to know the solution ...

[victorhugomuniz]

Solution ..

Select all

 
CREATE OR REPLACE PROCEDURE PESO_VALOR 
(p_valorprato IN valor_kg.valor%TYPE, 
 p_pesovalor OUT NUMBER) 
IS 
BEGIN 
SELECT (V.VALOR * B.QTDE / (V.PESO)) VALOR 
INTO p_pesovalor 
FROM BALANCA B, VALOR_KG V 
WHERE V.CODKG = B.CODKG 
AND V.CODKG = 01 
AND v.valor = p_valorprato; 
END PESO_VALOR; 
/ 

The error was this:

Select all

 
Compilation errors for PROCEDURE PESO_VALOR 
 
Error: PLS-00428: an INTO clause is expected in this SELECT statement 
Line: 5 
Text: SELECT V.DESCRICAO, B.QTDE KG, (V.VALOR * B.QTDE / (V.PESO)) VALOR 

[diegolenhardt]

To see the error uses the command:

Select all

 
show errors 

[Ivone]

Now it worked
thank you very much