Re: Trouble coverting a Procedure into a "STORED" procedure

craig.bender_at_practiceone.com (Craig B.) wrote in message news:<6a45b902.0402041850.508bd3f_at_posting.google.com>...
> 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.

If your are going to post the full code why not also post the Oracle error being returned? Generally to turn working anonymous pl/sql code into a stored procedure you just add create or replace procedure proc_name to the beginning of the procedure along with any parameters you wish to pass in.

HTH -- Mark D Powell -- Received on Thu Feb 05 2004 - 21:34:24 CET