| Two snippets doesn't work the same [message #271356] |
Mon, 01 October 2007 07:28  |
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
|
Member |
|
|
I am working on a package for extracting undo-/redo-data through logminer, but I have met some obstacles.
First, I need to add all redo-files to logminer. I use a simple cursor for this.
How come this works:
declare
CURSOR curs_get_member IS
SELECT member FROM v$logfile;
vfilename varchar2(255);
begin
FOR v_member_rec IN curs_get_member LOOP
select CHr(39)||v_member_rec.member||chr(39) into vfilename from dual;
dbms_output.put_line(vfilename);
END LOOP;
end;
(not running as a procedure)
...but this does not:
create or replace
PROCEDURE load_online IS
vfilename varchar2(255);
CURSOR curs_get_member IS
SELECT member FROM v$logfile;
begin
FOR v_member_rec IN curs_get_member LOOP
select CHr(39)||v_member_rec.member||chr(39) into vfilename from dual;
dbms_output.put_line(vfilename||' is added to logmnr');
END LOOP;
END load_online;
?
The last is compiled as a procedure and the first one is not. What is the reason for this? And what is the recommendations for getting this to work.
I get the following errors for the last code snippet:
Error(7,5): PL/SQL: SQL Statement ignored
Error(7,24): PL/SQL: ORA-00942: table or view does not exist
Error(11,7): PL/SQL: SQL Statement ignored
Error(11,23): PL/SQL: ORA-00904: "V_MEMBER_REC"."MEMBER": invalid identifier
Error(11,23): PLS-00364: loop index variable 'V_MEMBER_REC' use is invalid |
|
|
|
|
|
| Re: Two snippets doesn't work the same [message #271368 is a reply to message #271359] |
Mon, 01 October 2007 08:00  |
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
|
Member |
|
|
| Frank wrote on Mon, 01 October 2007 14:35 | What happens if you do this:
set role none
execute your first snippet
Remember: roles are NOT taken into account when creating stored procedures
|
Nothing happens. I have tried executing both through SQL Developer and through SQLPlus (@d:\work\addlogfiles.sql) after issuing "set role none".
|
|
|
|
|
|
| Re: Two snippets doesn't work the same [message #271379 is a reply to message #271356] |
Mon, 01 October 2007 08:28 |
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
|
Member |
|
|
Result:
SQL> set role none;
Role set.
SQL> declare
2 CURSOR curs_get_member IS
3 SELECT member FROM v$logfile;
4
5 vfilename varchar2(255);
6 begin
7 FOR v_member_rec IN curs_get_member LOOP
8 begin
9 select CHr(39)||v_member_rec.member||chr(39) into vfilename from dual;
10 dbms_output.put_line(vfilename);
11
12 sys.dbms_logmnr.ADD_LOGFILE(vfilename);
13 --EXCEPTION
14 --WHEN OTHERS THEN NULL;
15 end;
16 END LOOP;
17 end;
18 /
SELECT member FROM v$logfile;
*
ERROR at line 3:
ORA-06550: line 3, column 24:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 23:
PLS-00364: loop index variable 'V_MEMBER_REC' use is invalid
ORA-06550: line 9, column 23:
PL/SQL: ORA-00904: "V_MEMBER_REC"."MEMBER": invalid identifier
ORA-06550: line 9, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 12, column 7:
PLS-00201: identifier 'SYS.DBMS_LOGMNR' must be declared
ORA-06550: line 12, column 7:
PL/SQL: Statement ignored |
|
|
|
|
|
|
|
|
|
| Re: Two snippets doesn't work the same [message #271503 is a reply to message #271487] |
Mon, 01 October 2007 14:36 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| Kingfisher wrote on Mon, 01 October 2007 14:56 |
So are you telling me the code will never work as a procedure? If so, are there ways to achieve the same functionality that will work?
|
If you just have the execute rights granted to you directly and not through a role as stated.
|
|
|
|
| Re: Two snippets doesn't work the same [message #271570 is a reply to message #271503] |
Tue, 02 October 2007 01:38 |
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
|
Member |
|
|
| joy_division wrote on Mon, 01 October 2007 21:36 | | Kingfisher wrote on Mon, 01 October 2007 14:56 |
So are you telling me the code will never work as a procedure? If so, are there ways to achieve the same functionality that will work?
|
If you just have the execute rights granted to you directly and not through a role as stated.
|
How do I achieve this while compiling the procedure? I can grant execute on the procedure after it has been compiled, but during or before?
|
|
|
|
| Re: Two snippets doesn't work the same [message #271571 is a reply to message #271494] |
Tue, 02 October 2007 01:41 |
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
|
Member |
|
|
| Michel Cadot wrote on Mon, 01 October 2007 21:00 | "authid current_user"
|
Thanks. Tried it, and it still gives the error when I define the header like this:
create or replace PROCEDURE load_online AUTHID CURRENT_USER IS
This error is the "bump in the road" that invalidates the rest of the procedure:
Error(7,24): PL/SQL: ORA-00942: table or view does not exist
Thank you for helping.
Regards,
Rolf
|
|
|
|
|
|
| Re: Two snippets doesn't work the same [message #271605 is a reply to message #271582] |
Tue, 02 October 2007 03:09 |
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
|
Member |
|
|
| Michel Cadot wrote on Tue, 02 October 2007 09:07 | Then the user executing the procedure does not have the privilege on the table/view.
|
Is it at all possible to grant someone this privilege?
SQL> grant select on v$logfile to system;
grant select on v$logfile to system
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views |
|
|
|
|
|
|
|
| Re: Two snippets doesn't work the same [message #271666 is a reply to message #271570] |
Tue, 02 October 2007 07:51 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| Kingfisher wrote on Tue, 02 October 2007 02:38 | | joy_division wrote on Mon, 01 October 2007 21:36 | | Kingfisher wrote on Mon, 01 October 2007 14:56 |
So are you telling me the code will never work as a procedure? If so, are there ways to achieve the same functionality that will work?
|
If you just have the execute rights granted to you directly and not through a role as stated.
|
How do I achieve this while compiling the procedure? I can grant execute on the procedure after it has been compiled, but during or before?
|
Looks like I was having one of my bad days or just didn't really read the question. I am sorry, as you have seen, it wasn't that you needed execute privileges on the procedure, you needed select privileges on the table (underlying table at that).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
