Home » SQL & PL/SQL » SQL & PL/SQL » Two snippets doesn't work the same
Two snippets doesn't work the same [message #271356] Mon, 01 October 2007 07:28 Go to next message
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 #271359 is a reply to message #271356] Mon, 01 October 2007 07:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What happens if you do this:

set role none

execute your first snippet


Remember: roles are NOT taken into account when creating stored procedures
Re: Two snippets doesn't work the same [message #271368 is a reply to message #271359] Mon, 01 October 2007 08:00 Go to previous messageGo to next message
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 #271375 is a reply to message #271368] Mon, 01 October 2007 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus, execute "set role none;" (don't forget ";") and copy and paste the result.

Regards
Michel
Re: Two snippets doesn't work the same [message #271379 is a reply to message #271356] Mon, 01 October 2007 08:28 Go to previous messageGo to next message
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 #271387 is a reply to message #271379] Mon, 01 October 2007 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So now you have the same behaviour.

Repeat after me:
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.
roles are not enabled in stored procedure.

Regards
Michel
Re: Two snippets doesn't work the same [message #271487 is a reply to message #271356] Mon, 01 October 2007 13:56 Go to previous messageGo to next message
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
Member
Great...

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?

Thanks.
Re: Two snippets doesn't work the same [message #271494 is a reply to message #271487] Mon, 01 October 2007 14:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"authid current_user"

Regards
Michel
Re: Two snippets doesn't work the same [message #271503 is a reply to message #271487] Mon, 01 October 2007 14:36 Go to previous messageGo to next message
joy_division
Messages: 4644
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #271582 is a reply to message #271571] Tue, 02 October 2007 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then the user executing the procedure does not have the privilege on the table/view.

Regards
Michel
Re: Two snippets doesn't work the same [message #271605 is a reply to message #271582] Tue, 02 October 2007 03:09 Go to previous messageGo to next message
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 #271610 is a reply to message #271356] Tue, 02 October 2007 03:25 Go to previous messageGo to next message
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
Member
Stupid me... I must grant to the v_$-table/view, and not the v$table/view.

I apologise.
Re: Two snippets doesn't work the same [message #271612 is a reply to message #271605] Tue, 02 October 2007 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, connect as SYS and grant on the underlying v$_logfile.

Regards
Michel
Re: Two snippets doesn't work the same [message #271666 is a reply to message #271570] Tue, 02 October 2007 07:51 Go to previous messageGo to next message
joy_division
Messages: 4644
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).
Re: Two snippets doesn't work the same [message #271824 is a reply to message #271666] Wed, 03 October 2007 01:39 Go to previous message
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
Member
Thank you for the help. The code now compiles.
Previous Topic: CALLING STORED PROCEDURE
Next Topic: using array in pl/sql doesnt work - not urgent at all so PLEASE, PLEASE take your time.
Goto Forum:
  


Current Time: Sat Dec 10 18:34:02 CST 2016

Total time taken to generate the page: 0.08601 seconds