Creating a stored procedure problems

Hello. I am trying to create a stored procedure in a Oracle8 database. I am using TOAD, a database development and administration program, to write these queries and stuff. I have found example code on the Internet to write a stored procedure. However, it doesn't work. I write the procedure, and execute it to save it to the DB, and nothing happens at all.

I was wondering if someone could post some example code here for me on how to write a stored procedure. This query goes out, selects some tables with some where conditions, and then returns the data. I am not passing any variables to the stored procedure either.

As a sidenote, this data will eventually be returned to an Excel spreadsheet.

Thanks.






Jaster Mereel

Hi Jaster,
I have an easier time creating my procedures in TOAD than Designer. Here is a copy of a procedure that I run. I hope it is some help to you.

CREATE OR REPLACE PROCEDURE allocate_isbn (ITEM_ID IN NUMBER)
IS

BEGIN

UPDATE isbns_test_copy
SET
user_id = 'GDAPS',
date_used =(SELECT sysdate FROM dual)
WHERE
isbns_test_copy.ISBN_CORE =
(SELECT items_gdaps.isbn_core
FROM items_gdaps
WHERE items_gdaps.id = item_id)
AND isbns_test_copy.DATE_USED IS NULL
AND isbns_test_copy.USER_ID = 'MARG'
AND isbns_test_copy.INTERNAL_EXTERNAL = 'INT';

EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;

END allocate_isbn;
/