Home » SQL & PL/SQL » SQL & PL/SQL » Spool from SP (10.2.0.3)
Spool from SP [message #335186] Mon, 21 July 2008 06:38 Go to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
Hi all,
I wish to spool the result of a dynamic sql that is generated in one of my stored procedures. Basically, I have a task of removing some grants for the user from my production environment. But before I do so, i wish to save the current grants in order to rollback them in future. And I have the permission of only running the SP in one shot. I cannot access production database and store the already existing grants.


here is my procedure....

DECLARE

	SQL_STMT VARCHAR2(100);
	cursor cur_rev is select privilege, owner, table_name, grantee from dba_tab_privs where
						table_name = 'XYZ' AND GRANTEE LIKE '%ABC%';
						
						 
BEGIN

	dbms_output.put_line('----- Rollback Grant statements ------'||chr(10));

	for c1 in cur_rev loop
		
	    sql_stmt := 'GRANT '||c1.PRIVILEGE||' ON '||c1.OWNER||'.'||c1.TABLE_NAME||' TO '||c1.GRANTEE||';';
		dbms_output.put_line(sql_stmt);
	end loop;
	
	dbms_output.put_line(chr(10)||chr(10)|| '----- Revoking Grants for OG_PAY_VARIABLE_SV REMOVED FROM ABC ------');
	
	for c1 in cur_rev loop
		
		sql_stmt := 'REVOKE '||c1.PRIVILEGE||' ON '||c1.OWNER||'.'||c1.TABLE_NAME||' FROM '||c1.GRANTEE;
		dbms_output.put_line(sql_stmt);
		execute immediate sql_stmt;
	end loop;
	
	dbms_output.put_line(chr(10)||chr(10)||' All grants for XYZ REMOVED FOR ABC');
	
	exception when others then 
		dbms_output.put_line('Error :'||sqlerrm);
		
	
END;
/
			 
Re: Spool from SP [message #335188 is a reply to message #335186] Mon, 21 July 2008 06:45 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

set serveroutput on
spool spoolfilename

<your code>

spool off
Re: Spool from SP [message #335215 is a reply to message #335188] Mon, 21 July 2008 08:36 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
I can user spool <file_name> only in SQL, not inside a procdure. And i dont want to spool the whole thing.
I just want to spool the grant statements that are generated dynamically.
Re: Spool from SP [message #335216 is a reply to message #335215] Mon, 21 July 2008 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
utl_file

Regards
Michel
Re: Spool from SP [message #335222 is a reply to message #335215] Mon, 21 July 2008 08:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
adit_me1 wrote on Mon, 21 July 2008 15:36
I can user spool <file_name> only in SQL, not inside a procdure. And i dont want to spool the whole thing.
I just want to spool the grant statements that are generated dynamically.


What's "the whole thing"?!
Anyway, if you run this on production and you don't want to see any output, then you should get rid of those dbms_output calls. If you don't spool it, they are no good anyway.
Re: Spool from SP [message #335265 is a reply to message #335222] Mon, 21 July 2008 11:57 Go to previous messageGo to next message
gaperumal
Messages: 8
Registered: June 2008
Location: Chennai India
Junior Member
Here is my idea. Please note: code NOT tested.


create or replace package pkgtext is
type r_cursor is ref cursor;
procedure get_accounts_procedure (o_cursor out r_cursor);
end pkgtext;
/

create or replace package body pkgtext is
procedure get_text (o_cursor out r_cursor) 
as
begin
open o_cursor for 
select * from emp;
end get_text;
end pkgtext;
/

in sqlplus ( client side)

var rc ref cursor
spool ...\output.txt
exec get_text(rc);
print :rc
spool off 



Arangaperumal G
Chennai,India
Re: Spool from SP [message #335269 is a reply to message #335265] Mon, 21 July 2008 12:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Apart from non-valid code (I know you claimed it was not tested, but I can see at first sight, without running that it wil not work; there is no get_text that can be exec'ed.) and the fact that this has nothing to do with what was asked originally, the original post quite clearly states that he is not able/allowed to run anything but one-off procedures.
Re: Spool from SP [message #335355 is a reply to message #335222] Mon, 21 July 2008 23:23 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
yes thats right. I am not allowed to run anything but one-off procedures. Michel suggested some reading on the utl_file and I found this. Thought I could use this to store the dynamically generated in the SPs sql_stmt s in a file.

create or replace procedure utl_file_test_write (
  path       in varchar2,
  filename   in varchar2,
  firstline  in varchar2, 
  secondline in varchar2)
is
    output_file  utl_file.file_type;
begin
    output_file := utl_file.fopen (path,filename, 'W');

    utl_file.put_line (output_file, firstline);
    utl_file.put_line (output_file, secondline);
    utl_file.fclose(output_file);

  --exception
  --  when others then null;
end;
/



But when I execute this SP, i get an error saying invalid directory path.

exec utl_file_test_write('c:\','test.sql','adi','aggar');
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "ORDER_SCHEMA.UTL_FILE_TEST_WRITE", line 9
ORA-06512: at line 1


I tried various combinations of formats of path such as 'c:\\', '//temp','/temp'... but all return the same error.

Can someone direct me as to what is the correct format for path.
Re: Spool from SP [message #335357 is a reply to message #335186] Mon, 21 July 2008 23:27 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow the Posting Guidelines as stated in URL above


http://asktom.oracle.com has many fine coding examples.

[Updated on: Mon, 21 July 2008 23:28] by Moderator

Report message to a moderator

Re: Spool from SP [message #335388 is a reply to message #335355] Tue, 22 July 2008 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As explained in the documentation, the first parameter must be either an Oracle directory or a value present in "utl_file_dir" parameter.

Regards
Michel
Re: Spool from SP [message #335426 is a reply to message #335388] Tue, 22 July 2008 03:01 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
Got it... Thanks to all!!
Re: Spool from SP [message #335517 is a reply to message #335426] Tue, 22 July 2008 07:08 Go to previous messageGo to next message
ubsacc2004
Messages: 3
Registered: July 2008
Location: malaysia
Junior Member

in my company procedures all codes will have 1 standard script name to call real program.
Re: Spool from SP [message #335523 is a reply to message #335517] Tue, 22 July 2008 07:41 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ubsacc2004 wrote on Tue, 22 July 2008 08:08
in my company procedures all codes will have 1 standard script name to call real program.


And what relevance does this have?
Previous Topic: pair records from 2 tables
Next Topic: inserting from one table to another (merged)
Goto Forum:
  


Current Time: Sat Dec 03 14:18:26 CST 2016

Total time taken to generate the page: 0.10025 seconds