Trouble coverting a Procedure into a "STORED" procedure

I'm pulling my hair trying to take this working procedure I wrote and turn it into a stored procedure. The procedure is listed below. Please somebody tell me what I need to add to it to turn it into a Working Stored procedure. I was under ther impression that I could just add "CREATE OR REPLACE PROCEDURE grants4readgrp () IS" to the top of my procedure. But I get errors big time when adding the "Create" line. The procedure I wroted doesn't need anything passed to it, so no "IN" or "OUT". I just want to run it on demand and have it do it's stuff. Like I said it works perfectly with me just running it from SQLPlus. Please help:

• START-- DECLARE
  • cursors CURSOR grant_cursor IS SELECT table_name FROM dba_tables WHERE owner = 'DBA1' AND table_name NOT IN ('CREATE$JAVA$LOB$TABLE','JAVA$CLASS$MD5$TABLE') MINUS SELECT table_name FROM dba_tab_privs WHERE grantee='READGRP';
  • variables v_grant_record dba_tables.table_name%TYPE; v_skip_record dba_tables.table_name%TYPE; v_counter NUMBER := 0; grant_stmt VARCHAR2(32767);
  • exceptions e_no_record EXCEPTION; BEGIN OPEN grant_cursor; LOOP FETCH grant_cursor INTO v_grant_record; IF v_counter = 0 AND grant_cursor%NOTFOUND THEN RAISE e_no_record; END IF; EXIT WHEN grant_cursor%NOTFOUND; grant_stmt := 'GRANT SELECT ON DEV.' || v_grant_record || ' TO DEVREAD' ; DBMS_OUTPUT.PUT_LINE (grant_stmt) ; EXECUTE IMMEDIATE grant_stmt ; v_counter := v_counter + 1; END LOOP; CLOSE grant_cursor;

EXCEPTION
  WHEN e_no_record THEN

        DBMS_OUTPUT.PUT_LINE ('NO RECORDS FOUND'); END grants4readgrp;
/
-- END--
Thanks a lot in advance.
  Craig. Received on Wed Feb 04 2004 - 20:50:30 CST