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

From: Craig B. <craig.bender_at_practiceone.com>
Date: 5 Feb 2004 11:23:59 -0800
Message-ID: <6a45b902.0402051123.6cb9efdd_at_posting.google.com>

[Quoted] [Quoted] Thanks Daniel for your help, but that didn't work. I tried many variations of "CREATE OR REPLACE PROCEDURE <procedure_name> IS", like with "()" or without.

I've taken out the comments, (as I didn't understand you're statement "comments in-line.) And I've removed the DBMS_OUTPUT.PUT_LINE, which brings up other questions once I've resolved this but that's okay. I basically removed all non-necessities, like the Exceptions and stuff not required for the point of the procedure to just run.

As I've stated this workings perfectly when I just run it from SQLPlus as "_at_grants4readgrp.sql", with out the first line "CREATE OR REPLACE PROCEDURE grants4readgrp IS", however when I place that first line "CREATE..." in and run it "_at_grants4readgrp.sql", I get: "Input truncated to 1 characters
Warning: Procedure created with compilation errors." Therefore, of course when I execute it "execute grants4readgrp" I receive an error:
"BEGIN grants4readgrp; END;

      *
ERROR at line 1:

ORA-06550: line 1, column 7:
PLS-00905: object DBA1.GRANTS4READGRP is invalid
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored"

So again I'm stuck with a pl/sql script that works perfectly, but not as a stored procedure. I think it's pretty basic, any ideas on how to further troubleshoot it? It presently reads: CREATE OR REPLACE PROCEDURE grants4readgrp IS    

   CURSOR grant_cursor IS

     SELECT table_name FROM dba_tables
     WHERE owner = 'DBA1'
     MINUS
     SELECT table_name
     FROM dba_tab_privs
     WHERE grantee='READGRP';
     
   v_grant_record	dba_tables.table_name%TYPE;
   grant_stmt	VARCHAR2(32767);
   

 BEGIN
   OPEN grant_cursor;
   LOOP

     FETCH grant_cursor INTO v_grant_record;
     EXIT WHEN grant_cursor%NOTFOUND;
     grant_stmt := 'GRANT SELECT ON DEV.' 
 		|| v_grant_record || ' TO DEVREAD' ;
     EXECUTE IMMEDIATE grant_stmt ;

   END LOOP;
   CLOSE grant_cursor;
 END grants4readgrp;
/

P.S. Any recommendations to post to here besides thru Google groups? Using a newsreader.

Thanks,
  Craig.

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1075965116.461385_at_yasure>...
> 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
> 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.
Received on Thu Feb 05 2004 - 20:23:59 CET