Create Store Procedure 4 Select Command
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Create Store Procedure 4 Select Command

  1. #1
    Join Date
    Feb 2006
    Posts
    1

    Create Store Procedure 4 Select Command

    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

  2. #2
    Join Date
    Jan 2006
    Posts
    14

    Talking

    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

  3. #3
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width