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

[Quoted] First thing is to not cross-post. One usenet group is enough.

Second ... comments in-line

Craig B. wrote:
> 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
[Quoted] [Quoted] remove DECLARE it does not belong in any proc or function

CREATE OR REPLACE PROCEDURE <procedure_name> IS
> -- 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.

Now it is a stored procedure.

Once you get it working dump the dbms_output stuff it does not belong in production code.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)