Spool from SP [message #335186] |
Mon, 21 July 2008 06:38  |
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 #335215 is a reply to message #335188] |
Mon, 21 July 2008 08:36   |
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 #335222 is a reply to message #335215] |
Mon, 21 July 2008 08:59   |
Frank
Messages: 7901 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   |
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   |
Frank
Messages: 7901 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   |
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 #335523 is a reply to message #335517] |
Tue, 22 July 2008 07:41  |
joy_division
Messages: 4963 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?
|
|
|