Trouble coverting a Procedure into a "STORED" procedure
From: Craig B. <craig.bender_at_practiceone.com>
Date: 4 Feb 2004 18:50:30 -0800
Message-ID: <6a45b902.0402041850.508bd3f_at_posting.google.com>
[Quoted] [Quoted] 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:
Date: 4 Feb 2004 18:50:30 -0800
Message-ID: <6a45b902.0402041850.508bd3f_at_posting.google.com>
[Quoted] [Quoted] 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 [Quoted] 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 Thu Feb 05 2004 - 03:50:30 CET