Create Store Procedure 4 Select Command

[lindra]

Dear All,

I have a problem when I want to create a store procedure. I am sorry if it very simple thing, because I am new in oracle.

Here the script that I have make:

CREATE OR REPLACE PROCEDURE SYSTEM.Book_Test (@id NUMBER) AS
DECLARE
judul1 VARCHAR2;
BEGIN
SELECT judul INTO judul1 FROM BOOKS WHERE id_book=id;
END Book_Test;
/
And it error;

I am very thanx for any reply. I hope anyone can help me.

Thanx

[zettira]

CREATE OR REPLACE PROCEDURE SYSTEM.Book_Test (@id NUMBER) AS
DECLARE
judul1 VARCHAR2;
BEGIN
SELECT judul INTO judul1 FROM BOOKS WHERE id_book=id;
END Book_Test;

Pls check your parameter (@id number).
You call wrong parameter in your query 'WHERE id_book=id'.
You don't have parameter 'id' but '@id'.

HTH,

Zettira

[WilliamR]

You can't use '@' in varible names in Oracle. Use something like "p_book_id".

Also the VARCHAR2 variable needs a length constraint, e.g. VARCHAR2(100). Or better still, books.judul%TYPE. A useful convention is to prefix variables with "v_", so I would use:

v_judul books.judul%TYPE;