Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ed.

Asked: September 16, 2004 - 2:29 pm UTC

Last updated: February 06, 2012 - 8:12 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,
I guess I'm being thickheaded but I am having trouble understanding why I get errors executing a procedure through an Oracle 8.1.7.4 job vs. executing in sql*plus.

My user (kate_ml) has select_catalog_role so it can see dba_objects.
Here's a simple Proc (created by kate_ml):
create or replace procedure ewh_1
authid current_user
is
v_object_name all_objects.object_name%TYPE;
begin
execute immediate 'select object_name from dba_objects where rownum < 2'
into v_object_name;
dbms_output.put_line(v_object_name);
end;

and it happily executes within SQL:
begin
ewh_1;
end;
PL/SQL procedure successfully completed

dbms_output =
/1001a851_ConstantDefImpl


However, I set up an Oracle job as kate_ml:
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'ewh_1;'
,next_date => to_date('17/09/2004 03:09:56','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)+6/24'
,no_parse => FALSE
);
END;
/


Here's what my alert log has:
Errors in file /oracle/dba/dba_ora/databases/UKDEV188/bdump/s000_4736.trc:
ORA-12012: error on auto execute of job 988
ORA-00942: table or view does not exist
ORA-06512: at "KATE_ML.EWH_1", line 6
ORA-06512: at line 1

line 6 of EWH_1 is, of course, the EXECUTE IMMEDIATE.

I can't find anything describing this difference. Please help.
Thanks,
Ed

P.S. I tried this on a 9i database too -- same result

and Tom said...

roles are not enabled via dbms_job, problem is if you:

a) directly execute the stored procedure - all of your privs and all currently enabled roles are in place.

b) execute that same stored procedure called from a definers rights routine -- the DR routine zaps all roles, you are back to just "your base privs"



No roles are on:

ops$tkyte@ORA9IR2> create table t ( job int, txt varchar2(30) );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p( p_JOB in number )
2 authid CURRENT_USER
3 as
4 begin
5 insert into t
6 select p_job, role from session_roles
7 union all
8 select p_job, USER from dual;
9 end;
10 /

Procedure created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec p(-1)

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2> exec dbms_job.submit(:n,'p(JOB);');

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> exec dbms_job.run(:n);

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;

JOB TXT
---------- ------------------------------
-1 CONNECT
-1 DBA
-1 SELECT_CATALOG_ROLE
-1 HS_ADMIN_ROLE
-1 EXECUTE_CATALOG_ROLE
-1 DELETE_CATALOG_ROLE
-1 EXP_FULL_DATABASE
-1 IMP_FULL_DATABASE
-1 GATHER_SYSTEM_STATISTICS
-1 WM_ADMIN_ROLE
-1 JAVA_ADMIN
-1 JAVA_DEPLOY
-1 XDBADMIN
-1 OPS$TKYTE
24 OPS$TKYTE

15 rows selected.

Soooo, ok you say -- thats the problem, whats the solution.....


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p( p_JOB in number )
2 authid CURRENT_USER
3 as
4 begin
5 execute immediate 'set role ALL';

6 insert into t
7 select p_job, role from session_roles
8 union all
9 select p_job, USER from dual;
10 end;
11 /

Procedure created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable n number
ops$tkyte@ORA9IR2> exec dbms_job.submit(:n,'p(JOB);');

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> exec dbms_job.run(:n);

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t where job = :n;

JOB TXT
---------- ------------------------------
25 CONNECT
25 DBA
25 SELECT_CATALOG_ROLE
25 HS_ADMIN_ROLE
25 EXECUTE_CATALOG_ROLE
25 DELETE_CATALOG_ROLE
25 EXP_FULL_DATABASE
25 IMP_FULL_DATABASE
25 GATHER_SYSTEM_STATISTICS
25 WM_ADMIN_ROLE
25 JAVA_ADMIN
25 JAVA_DEPLOY
25 XDBADMIN
25 OPS$TKYTE

14 rows selected.


there you go, roles are back on...


Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

It looked so promising

Ed Hoelker, September 16, 2004 - 4:29 pm UTC

Hi Tom,
Still not working in my environment.  I did some more research concerning roles & dbms_job and came up empty-handed.  I took your suggestion and did this:

create table t ( job int, txt varchar2(30) );

create or replace procedure ewh_1 
authid current_user
is
v_object_name  all_objects.object_name%TYPE;
begin
execute immediate 'set role ALL';
ewh_1_write;
insert into t values(99,'DONE Outer');
execute immediate 'select object_name from dba_objects where rownum < 2'
into v_object_name;
dbms_output.put_line(v_object_name);
end;

create or replace procedure ewh_1_write
authid current_user
is
pragma autonomous_transaction;
p_job    NUMBER;
begin
p_job := 1;
insert into t
       select p_job, role from session_roles
       union all
       select p_job, USER from dual;
insert into t values(98,'DONE Inner');   
commit;
end ewh_1_write;

Straight sql*plus:
SQL> truncate table t;

Table truncated.

Elapsed: 00:00:00.91
SQL> 
SQL> begin
  2  ewh_1;
  3  end;
  4  /
/1001a851_ConstantDefImpl

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL> 
SQL> select * from t;

       JOB TXT
---------- ------------------------------
         1 CONNECT
         1 SELECT_CATALOG_ROLE
       <snip>
         1 KATE_ML
        98 DONE Inner
        99 DONE Outer

14 rows selected.

Elapsed: 00:00:00.00
SQL> 

But as a job...
SQL> @u:\data\devml
Connected.
SQL> variable n number
SQL> truncate table t;

Table truncated.

Elapsed: 00:00:00.60
SQL> exec dbms_job.submit(:n,'ewh_1;');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> exec dbms_job.run(:n);
BEGIN dbms_job.run(:n); END;

*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 405
ORA-06512: at "SYS.DBMS_JOB", line 267
ORA-06512: at line 1


Elapsed: 00:00:00.40
SQL> select * from t;

       JOB TXT
---------- ------------------------------
         1 KATE_ML
        98 DONE Inner

Elapsed: 00:00:00.90
SQL>  
 
The alert log is still pointing to the execute immediate 'select object_name from dba_objects.. statement 

Tom Kyte
September 16, 2004 - 7:30 pm UTC

version and OS?

Environment Info

Ed Hoelker, September 17, 2004 - 9:21 am UTC

Compaq PROLIANT DL380 G2, running Red Hat Linux AS2.1

>uname -a
Linux hlwsdlx001 2.4.9-e.27enterprise #1 SMP Tue Aug 5 15:39:21 EDT 2003 i686 unknown

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_TIM STATUS  PAR    THREAD# ARCHIVE LOG_SWITCH_
----------------- ----------- ------- --- ---------- ------- -----------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE
---------- --- ----------------- ------------------
              1 UKDEV188
hlwsdlx001
8.1.7.4.0         12-SEP-2004 OPEN    NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE
 

Tom Kyte
September 17, 2004 - 10:15 am UTC

hmm, i ran this on 8174 as well

if you run *just my example* what happens? roles or no?

Only Userid

Ed Hoelker, September 17, 2004 - 12:09 pm UTC

Hi Tom,
I ran your exact sql from above and only got the userid back. (I can post the results if you'd like).
Ed

Tom Kyte
September 17, 2004 - 1:13 pm UTC

arg, don't ask me why I did that test in 9i :(

it works in 9i
the roles just won't come on in 8i, even if we nuke the stored procedure:

ops$tkyte@ORA817DEV> variable n number
ops$tkyte@ORA817DEV> begin
  2          dbms_job.submit(:n,'
  3          dbms_session.set_role(''DBA'');
  4          insert into t
  5          select JOB, role from session_roles
  6          union all
  7          select JOB, USER||'',''||userenv(''schemaid'') from dual;' );
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA817DEV> exec dbms_job.run(:n);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select * from t where job = :n;
 
       JOB TXT
---------- ------------------------------
       390 OPS$TKYTE,759
 
http://asktom.oracle.com/Misc/RolesAndProcedures.html
I'm afraid that for this, the person scheduling the job is going to need the grant *directly* to them in 8i 

Well too bad for me

Ed Hoelker, September 17, 2004 - 1:27 pm UTC

I got into a 9i db and concur with your findings.  Please note that the link above does not really describe this problem with dbms_job vs. executing the proc in sql*plus.  I had seen that article before and it contains this little clause that gave me hope:

SQL> set role none; 
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure.  If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure (unless you are using Invokers rights in Oracle8i.  See the PLSQL documentation for more information on this feature and make sure you understand the ramifications).  To be able to perform that operation in a typical procedure, you need to have that privelege granted directly to you. 
  

Anyway, thanks and I'll request specific grants.
Ed 

Still dont understand why this wont work

syed, September 18, 2004 - 10:22 am UTC

Tom

user1 has direct grant to execute user2's package
but it still fails on the call to dbms_refresh.

I dont understand whats going on ? (8.1.7 Solaris)
The test case is ..

create user user1 identified by user1;
grant connect, resource to user1;

create user user2 identified by user2;
grant connect , resource to user2;
grant create materialized view to user2;

connect user1/user1
create table a (x int constraint x_pk primary key);
insert into a values (1);
commit;

grant select on a to user2;

connect user2/user2
create materialized view mv_a refresh force on demand as select * from user1.a;

exec dbms_refresh.make (name => 'REFRESH_GROUP', list => 'MV_A', next_date => null, interval => null);

create or replace procedure refresh_mv as
begin
dbms_refresh.refresh('REFRESH_GROUP');
end;
/

grant execute on refresh_mv to user1;

connect user1/user1
exec user2.refresh_mv

ORA-23404: refresh group "user1"."REFRESH_GROUP" does not exist

This implies that the dbms_refresh.refresh procedure is being executed under the security domain of user1 rather than user1 ???

Thanks

Syed

Tom Kyte
September 18, 2004 - 11:24 am UTC

user2@ORA8I> create or replace procedure refresh_mv as
2 begin
3 dbms_refresh.refresh('"USER2"."REFRESH_GROUP"');
4 end;
5 /

Procedure created.

user2@ORA8I>
user2@ORA8I> grant execute on refresh_mv to user1;

Grant succeeded.

user2@ORA8I>
user2@ORA8I> connect user1/user1
Connected.
user2@ORA8I> exec user2.refresh_mv

PL/SQL procedure successfully completed.



it used the currently logged in users name by default, tell it otherwise.

thanks, but...

Syed, September 18, 2004 - 2:25 pm UTC

Is that expected behaviour ? I dont quite see why it uses
the currently logged in user ?

Thanks

Syed

Tom Kyte
September 18, 2004 - 5:13 pm UTC

because it does. it is just a "string", not an "object". so at runtime, it is saying "lets add the current users schema name here"




Fair Enough !!!

syed, September 19, 2004 - 2:31 pm UTC


Made the difference

Frederik Damgaard, March 17, 2006 - 2:20 am UTC

I was about giving up on using invoker rights in packages that should be executed using dbms_job as I always got "ORA-01031: insufficient privileges" when trying to manipulate data.

Then via GOOGLE I found this article and it was spot on: adding 'set role ALL' to my code within the package simply made the difference

Reader, April 17, 2008 - 9:40 am UTC

create table tst
(serial_no number
,nm varchar2(5)
,val number
,comments varchar2(30)
);


insert into tst
values
(1,'AB',10,'serial_no=1;nm=AB;val=10;');

insert into tst
values
(2,'BC',20,'serial_no=2;nm=BC;val=20;');

insert into tst
values
(3,'EF',10,'serial_no=3;nm=EF;val=10;');

insert into tst
values
(4,'AB',30,'serial_no=4;nm=AB;val=30;');

insert into tst
values
(5,'BC',40,'serial_no=5;nm=BC;val=40;');

insert into tst
values
(6,'EF',50,'serial_no=6;nm=EF;val=50;');

insert into tst
values
(7,'GH',30,'serial_no=7;nm=GH;val=30;');

insert into tst
values
(8,'PQ',60,'serial_no=8;nm=PQ;val=60;');

commit;

--DATA
serial_no nm val comments
1 AB 10 serial_no=1;nm=AB;val=10;
2 BC 20 serial_no=2;nm=BC;val=20;
3 EF 10 serial_no=3;nm=EF;val=10;
4 AB 30 serial_no=4;nm=AB;val=30;
5 BC 40 serial_no=5;nm=BC;val=40;
6 EF 50 serial_no=6;nm=EF;val=50;
7 GH 30 serial_no=7;nm=GH;val=30;
8 PQ 60 serial_no=8;nm=PQ;val=60;


I have a around 32 million records in tst table. I keep adding about 200,000 records everyday to tst table.

In one report I need to use all distinct values of nm field with maximum value for val column. I created a materialized view for this and refresh this on demand.

CREATE MATERIALIZED VIEW MV_TST
REFRESH COMPLETE ON DEMAND
AS
SELECT nm
, MAX (val) max_val
FROM tst
GROUP BY nm;

MV_TST will have a maximum 15,000 records.


I created materialized view, since this has to be joined with another table which has 20 million records. Join will be based on nm filed.

Another table:
create table ord
(nm varchar2(5),
price number,
last_price number,
created_dt date);

insert into ord
values
('AB',1000,900,to_date('2008-01-01','YYYY-MM-DD'));

insert into ord
values
('BC',7000,5000,to_date('2008-01-02','YYYY-MM-DD'));

insert into ord
values
('EF',900,20,to_date('2008-01-01','YYYY-MM-DD'));


insert into ord
values
('GH',100000,90000,to_date('2008-01-01','YYYY-MM-DD'));

insert into ord
values
('PQ',20000,21000,to_date('2008-01-01','YYYY-MM-DD'));

commit;

And, I have built view on top of MV_TST to get the desired data.

CREATE VIEW v_tst as
select nm
,y.price
,y.last_price
,y.created_dt
from MV_TST x
,ord y
where x.nm = y.nm;

In another report, I want to get all the fields for all the distinct symbols with the maximum value for val column from tst table.

Can you please tell me how to modify this materialized view's query, so as to have one materialized view for both the reports?
Tom Kyte
April 17, 2008 - 11:47 am UTC

why wouldn't you STORE THE DATA PROPERLY IN THE FIRST PLACE

rather than loading X million pieces of bad data, why don't you process it and load it as it should be in the first place?!?!?!


(I'll never get it...)

Reader, April 17, 2008 - 5:04 pm UTC

I have to store the data as it comes to us. I cannot process it.
Initially, a view was created by someone to get the distinct
values of nm field with maximum value for val colum.
This takes a long a long time to run when it is joined to ord table. So I created materialized view and refresh it on demand.


CREATE VIEW v_tst_old
AS
SELECT nm
, MAX (val) max_val
FROM tst
GROUP BY nm;

CREATE VIEW v_tst as
select nm
,y.price
,y.last_price
,y.created_dt
from v_tst_old x
,ord y
where x.nm = y.nm;
Tom Kyte
April 17, 2008 - 9:55 pm UTC

... I have to store the data as it comes to us. I cannot process it. ...

please - that is so wrong, even if true (which I doubt, come on - you are telling me you cannot store what you get AND process it into something USEFUL - really? if so, why? who would make such a silly rule??)


kill the materialized view, reformat the existing data (fine, keep the original string, I don't care about that at all - but put the other data into COLUMNS where it belongs - you are doing that (albeit grossly inefficiently) now - just do it)

Big Thanx

A reader, February 06, 2012 - 4:23 am UTC

I was having problem executing job (dbms_job.submit) within stored procedure located on non-sys user, the job is to create private synonyms for each new created user, the procedure was successfully executed on sys and not working fine on other users, so, I've changed my procedure and added authid CURRENT_USER and execute immediate 'set role ALL';
after that everything worked fine with me.
Big thanx
Tom Kyte
February 06, 2012 - 8:12 am UTC

you should have just granted create synonym to the owner of the procedure.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library