This message was very much useful for me when I was searching the net for a task assigned to me - to write a cron job in solaris5.7 which checks a particular table in oracle 8i
create table table1(
emailid varchar2(50),
tender_closing_date DATE
);
 
and If the tender_closing_date is the current date, then I have to send an alert mail(emailid) that the tender is closing today. After reading this message I came to know that dbms_job is similar to cron. So as a Step 1, I thought I will write a stored procedure which sends mail and execute it and as a step 2, I will create a dbms_job.
I wrote a procedure compiled it and executed it. It gave me an error message "ORA-29540: class oracle/plsql/net/TCPConnection does not exist"
SQL> exec send2('david@i2pl.net','david@i2pl.net','test','test')
ORA-29540: class oracle/plsql/net/TCPConnection does not exist

PL/SQL procedure successfully completed.

what do I do to overcome this. 

Thanxs and regards,
David 

What is this error 12012 means after I started the oracle817 database and did a full import of oracle734 database into the 817 database? I also received a SIDsnp0.trc message from udump, the following are some cut off from Alrt.log and then snp0.trc file.
What do I have to do to fix this problem?
Hope it is not too messy -
As always thank you very much....

Dump file D:\oracle\admin\FOCUS91\bdump\focus91ALRT.LOG
Tue Apr 16 09:01:24 2002
ORACLE V8.1.7.0.0 - Production vsnsta=0
vsnsql=e vsnxtr=3
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Starting up ORACLE RDBMS Version: 8.1.7.0.0.
System parameters with non-default values:
processes = 100
shared_pool_size = 83886080
large_pool_size = 614400
java_pool_size = 52428800
control_files = D:\oracle\oradata\FOCUS91\control01.ctl, D:\oracle\oradata\FOCUS91\control02.ctl, D:\oracle\oradata\FOCUS91\control03.ctl
db_block_buffers = 6550
db_block_size = 8192
compatible = 8.1.0
log_buffer = 32768
log_checkpoint_interval = 100000
log_checkpoint_timeout = 1800
db_files = 1024
db_file_multiblock_read_count= 8
rollback_segments = RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6, RBS7, RBS8, RBS9, RBS10, RBS11, RBS12, RBS13, RBS14
max_enabled_roles = 30
remote_login_passwordfile= EXCLUSIVE
db_domain = WORLD
global_names = FALSE
distributed_transactions = 10
instance_name = FOCUS91
service_names = FOCUS91.WORLD
mts_dispatchers = (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)
sort_area_size = 65536
sort_area_retained_size = 65536
db_name = FOCUS91
open_cursors = 500
os_authent_prefix =
optimizer_mode = rule
remote_dependencies_mode = SIGNATURE
job_queue_processes = 7
job_queue_interval = 60
parallel_max_servers = 5
background_dump_dest = D:\oracle\admin\FOCUS91\bdump
user_dump_dest = D:\oracle\admin\FOCUS91\udump
max_dump_file_size = 10240
oracle_trace_collection_name=
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
SNP0 started with pid=8
SNP1 started with pid=9
SNP2 started with pid=10
SNP3 started with pid=11
SNP4 started with pid=12
SNP5 started with pid=13
SNP6 started with pid=14
Tue Apr 16 09:01:28 2002
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Apr 16 09:01:28 2002
alter database mount exclusive
Tue Apr 16 09:01:34 2002
Successful mount of redo thread 1, with mount id 844445230.
Tue Apr 16 09:01:34 2002
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Tue Apr 16 09:01:34 2002
alter database open
Beginning crash recovery of 1 threads
Tue Apr 16 09:01:42 2002
Thread recovery: start rolling forward thread 1
Recovery of Online Redo Log: Thread 1 Group 1 Seq 181 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORADATA\FOCUS91\REDO01.LOG
Tue Apr 16 09:01:47 2002
Thread recovery: finish rolling forward thread 1
Thread recovery: 1741 data blocks read, 306 data blocks written, 3601 redo blocks read
Crash recovery completed successfully
Tue Apr 16 09:01:53 2002
Thread 1 advanced to log sequence 182
Thread 1 opened at log sequence 182
Current log# 2 seq# 182 mem# 0: D:\ORACLE\ORADATA\FOCUS91\REDO02.LOG
Successful open of redo thread 1.
Tue Apr 16 09:01:57 2002
SMON: enabling cache recovery
SMON: enabling tx recovery
Tue Apr 16 09:02:10 2002
Completed: alter database open
Tue Apr 16 09:04:05 2002
CREATE TABLESPACE "RBS" DATAFILE '/u03/oradata/SCOPE/rbs01_1.dbf' SIZE 209715200 DEFAULT STORAGE (INITIAL 2097152 NEXT 2097152 MINEXTENTS 2 MAXEXTENTS 249 PCTINCREASE 0) ONLINE PERMANENT
ORA-1543 signalled during: CREATE TABLESPACE "RBS" DATAFILE '/u03/oradata/SC...
Tue Apr 16 09:04:06 2002
CREATE TABLESPACE "TEMP" DATAFILE '/u04/oradata/SCOPE/temp01_1.dbf' SIZE 419430400 DEFAULT STORAGE (INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50) ONLINE PERMANENT
ORA-1543 signalled during: CREATE TABLESPACE "TEMP" DATAFILE '/u04/oradata/S...
Tue Apr 16 09:04:06 2002
CREATE TABLESPACE "TOOLS" DATAFILE '/u03/oradata/SCOPE/tool01_1.dbf' SIZE 41943040 DEFAULT STORAGE (INITIAL 20480 NEXT 20480 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50) ONLINE PERMANENT
ORA-1543 signalled during: CREATE TABLESPACE "TOOLS" DATAFILE '/u03/oradata/...
Tue Apr 16 09:04:06 2002
CREATE TABLESPACE "SCOPETOOLS" DATAFILE '/u02/oradata/SCOPE/SCOPETOOLS_1.dbf' SIZE 12582912 DEFAULT STORAGE (INITIAL 20480 NEXT 20480 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50) ONLINE PERMANENT
ORA-1543 signalled during: CREATE TABLESPACE "SCOPETOOLS" DATAFILE '/u02/ora...
Tue Apr 16 09:04:06 2002
CREATE TABLESPACE "SCOPE_3503" DATAFILE '/u03/oradata/SCOPE/SCOPE_3503_1.dbf' SIZE 209715200 DEFAULT STORAGE (INITIAL 20480 NEXT 20480 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50) ONLINE PERMANENT
ORA-1543 signalled during: CREATE TABLESPACE "SCOPE_3503" DATAFILE '/u03/ora...
Tue Apr 16 09:04:06 2002
CREATE TABLESPACE "SCOPE_3503_IDX" DATAFILE '/u02/oradata/SCOPE/SCOPE_3503_IDX_1.dbf' SIZE 209715200 DEFAULT STORAGE (INITIAL 20480 NEXT 20480 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50) ONLINE PERMANENT
ORA-1543 signalled during: CREATE TABLESPACE "SCOPE_3503_IDX" DATAFILE '/u02...
Tue Apr 16 09:04:06 2002
CREATE TABLESPACE "SCOPE_9000" DATAFILE '/u02/oradata/SCOPE/SCOPE_9000_1.dbf' SIZE 10485760 DEFAULT STORAGE (INITIAL 20480 NEXT 20480 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50) ONLINE PERMANENT
ORA-1543 signalled during: CREATE TABLESPACE "SCOPE_9000" DATAFILE '/u02/ora...
Tue Apr 16 09:04:06 2002
.....
CREATE ROLLBACK SEGMENT R10 STORAGE (INITIAL 2097152 NEXT 2109440 MINEXTENTS 2 MAXEXTENTS 249) TABLESPACE "RBS"
Completed: CREATE ROLLBACK SEGMENT R10 STORAGE (INITIAL 20971
Tue Apr 16 09:04:24 2002
Thread 1 advanced to log sequence 183
Current log# 3 seq# 183 mem# 0: D:\ORACLE\ORADATA\FOCUS91\REDO03.LOG
Tue Apr 16 09:04:35 2002
Thread 1 advanced to log sequence 184
Current log# 1 seq# 184 mem# 0: D:\ORACLE\ORADATA\FOCUS91\REDO01.LOG
Tue Apr 16 09:04:49 2002
Thread 1 advanced to log sequence 185
Current log# 2 seq# 185 mem# 0: D:\ORACLE\ORADATA\FOCUS91\REDO02.LOG
Tue Apr 16 09:05:04 2002
Thread 1 advanced to log sequence 186
Current log# 3 seq# 186 mem# 0: D:\ORACLE\ORADATA\FOCUS91\REDO03.LOG
Tue Apr 16 09:05:57 2002
Thread 1 advanced to log sequence 187
Current log# 1 seq# 187 mem# 0: D:\ORACLE\ORADATA\FOCUS91\REDO01.LOG
Tue Apr 16 09:06:12 2002
Thread 1 advanced to log sequence 188
Current log# 2 seq# 188 mem# 0: D:\ORACLE\ORADATA\FOCUS91\REDO02.LOG
Tue Apr 16 09:06:17 2002
Errors in file D:\oracle\admin\FOCUS91\bdump\focus91SNP0.TRC:
ORA-12012: error on auto execute of job 7
ORA-12154: TNS:could not resolve service name
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674
ORA-06512: at "SYS.DBMS_IREFRESH", line 577
ORA-06512: at "SYS.DBMS_REFRESH", line 211
ORA-06512: at line 1

Tue Apr 16 09:06:28 2002
Thread 1 advanced to log sequence 189
Current log# 3 seq# 189 mem# 0: D:\ORACLE\ORADATA\FOCUS91\REDO03.LOG
Tue Apr 16 09:06:41 2002
Thread 1 advanced to log sequence 190
Current log# 1 seq# 190 mem# 0: D:\ORACLE\ORADATA\FOCUS91\REDO01.LOG
Tue Apr 16 09:07:03 2002
Thread 1 advanced to log sequence 191
Current log# 2 seq# 191 mem# 0: D:\ORACLE\ORADATA\FOCUS91\REDO02.LOG
Tue Apr 16 09:07:26 2002
.....

This one is from snp0.trc -

Dump file D:\oracle\admin\FOCUS91\bdump\focus91SNP0.TRC
Tue Apr 16 09:06:17 2002
ORACLE V8.1.7.0.0 - Production vsnsta=0
vsnsql=e vsnxtr=3
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Oracle8i Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Instance name: focus91

Redo thread mounted by this instance: 1

Oracle process number: 8

Windows thread id: 1420, image: ORACLE.EXE


*** SESSION ID:(7.19) 2002-04-16 09:06:17.296
*** 2002-04-16 09:06:17.296
ORA-12012: error on auto execute of job 7
ORA-12154: TNS:could not resolve service name
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674
ORA-06512: at "SYS.DBMS_IREFRESH", line 577
ORA-06512: at "SYS.DBMS_REFRESH", line 211
ORA-06512: at line 1
*** SESSION ID:(7.27) 2002-04-16 09:08:33.859
*** 2002-04-16 09:08:33.859
ORA-12012: error on auto execute of job 7
ORA-12154: TNS:could not resolve service name
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674
ORA-06512: at "SYS.DBMS_IREFRESH", line 577
ORA-06512: at "SYS.DBMS_REFRESH", line 211
ORA-06512: at line 1
*** SESSION ID:(7.43) 2002-04-16 09:12:53.343
*** 2002-04-16 09:12:53.343
ORA-12012: error on auto execute of job 7
ORA-12154: TNS:could not resolve service name
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674
ORA-06512: at "SYS.DBMS_IREFRESH", line 577
ORA-06512: at "SYS.DBMS_REFRESH", line 211
ORA-06512: at line 1
*** SESSION ID:(7.75) 2002-04-16 09:21:18.656
*** 2002-04-16 09:21:18.656
ORA-12012: error on auto execute of job 7
ORA-12154: TNS:could not resolve service name
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 617
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 674
ORA-06512: at "SYS.DBMS_IREFRESH", line 577
ORA-06512: at "SYS.DBMS_REFRESH", line 211
ORA-06512: at line 1

Here is my tnsnames.ora from d:\oracle\ora81\admin\network
I did the tnsping from server I could ping and login to sqlplus or import dump file, but the above error message is keeping showing on my Alrt.log during the import process. -

# TNSNAMES.ORA Network Configuration File: D:\oracle\ora81\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
FOCUS91 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = kprhou-foc000)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = FOCUS91.WORLD)
)
)

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = kprhou-foc000)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = FOCUS91.WORLD)
(PRESENTATION = </code> http://admin <code>
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

i have a procedure which runs successfully in oracle.In does not have any "out" parameter. if i tried to execute through DBMS_JOB i get an error as shown below

SQL> declare
  2  v_job_num  BINARY_INTEGER;
  3  v_period varchar2(6);
  4  begin
  5  v_period:='200207';
  6  DBMS_JOB.SUBMIT(v_job_num,'BEGIN month_qd(''200207''); END;', SYSDATE, null, NULL, NULL,null);
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-23319: parameter value  is not appropriate
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_JOB", line 125
ORA-06512: at line 6

 

Well, why are you passing all of those NULL's and did you even try seeing what happens when you let them default or you pass appropriate values to them?

In this case, it is the INSTANCE parameter -- to which you are passing null and Oracle is saying "whoa -- thats not right"

ops$tkyte@ORA920.US.ORACLE.COM> declare
  2    l_job binary_integer;
  3  begin
  4          dbms_job.submit( l_job, 'null;', sysdate, instance => null );
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-23319: parameter value  is not appropriate
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_JOB", line 130
ORA-06512: at line 4


<b>to queue a one time job you should call dbms_job simply like this:</b>

ops$tkyte@ORA920.US.ORACLE.COM> declare
  2    l_job binary_integer;
  3  begin
  4          dbms_job.submit( l_job, 'null;' );
  5          commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.


I would go a step further, if you plan on scheduling this month_qd job over and over and add "bind variable support" to it.  You should code:

ops$tkyte@ORA920.US.ORACLE.COM> create table month_qd_parameters(job int primary key, period date );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace procedure month_qd( p_job in number )
  2  as
  3          l_parameters month_qd_parameters%rowtype;
  4  begin
  5          select * into l_parameters from month_qd_parameters where job = p_job;
  6
  7          /* code here.... */
  8          delete from month_qd_parameters where job = p_job;
  9          commit;
 10  end;
 11  /

Procedure created.

ops$tkyte@ORA920.US.ORACLE.COM> declare
  2          l_job  number;
  3  begin
  4          dbms_job.submit( l_job, 'month_qd( p_job => JOB );' );
  5          insert into month_qd_parameters values ( l_job, to_date( '200207', 'yyyymm' ) );
  6          commit;
  7
  8  end;
  9  /

PL/SQL procedure successfully completed.
 

>>"bind variable support"
When a procedure is called from JOB do you think it will parsed each time when it executes.
E.g
dbms_refresh.refresh('"COMPS"."DEAL_DTL_REFRESH"');

Hi Tom,

Good explanation as usual. Tom I have a few question to ask on snapshot refresh on Oracle 7.3 or 8.0.4. I've got a view (V) came from different tables (T1,T2,T3,...). When I query this view, it takes me several minutes to execute, so I thought creating a snapshot as soon as a value has been updated or changed from the master table will be my solution, so that it can run on the background. How to achieve that ? or any other solution ?
Please Could u provide me a simple code.

Thanks
Zo A.

Can you tell me what is happening ?
1. The problem is in the job system or in the package ?
2. What is J000 ?
3. The problem is in line 1 or 35 ?
4. What is "job 21" ?
5. What is ora 6502 ?


With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
ORACLE_HOME = /ora/92
System name: SunOS
Node name: srv16
Release: 5.8
Version: Generic_108528-15
Machine: sun4u
Instance name: oraprd
Redo thread mounted by this instance: 1
Oracle process number: 59
Unix process pid: 20784, image: oracle@srv16 (J000)

*** SESSION ID:(181.33074) 2004-06-01 05:45:16.261
*** 2004-06-01 05:45:16.261
ORA-12012: error on auto execute of job 21
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APP_REDE.PKG_DNS", line 35
ORA-06512: at line 1

your code, which was submitted as the 21'st job on your system, has a bug in it on line 35.

It is trying to put too many bytes into some variable.  the variable isn't large enough to hold them all.  


select line, text
  from all_source
  where owner = 'APP_REDE'
    and name = 'PKG_DNS'
    and type = 'PACKAGE BODY' (or procedure, which ever it is)
    and line beteween 30 and 40
 order by line;

will show you the offending code.


1) it is a problem in the package PKG_DNS
2) j000 is the name of the os process that was trying to run it
3) line 35 of your code
4) the job number (select job, what from dba_jobs)
5) should be obvious?  the error message follows it.

 
ops$tkyte@ORA9IR2> declare
  2  x varchar2(5);
  3  begin
  4     x := 'abcdef';
  5  end;
  6
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
 

 

Hi Tom ,

We are running Oracle 8.1.7 on a SUN Box. Lately I have encountered these errors in the alert log

==============
ORA-12012: error on auto execute of job 1526
ORA-23421: job number 1526 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 525
ORA-06512: at "SYS.DBMS_IJOB", line 258
ORA-06512: at line 1
====================

From my deduction this Error generally occurs when one tries to RUN a non-existent job.

But since in my case this error is logged in the alert log does this mean that some stored procedure is trying to execute this non existent job?

I tried selecting from dba_source but was not able to find a procedure which made an explicit call to DBMS_JOB.RUN

SQL> select name from dba_source where lower(text) like '%dbms_job.run%' and owner <> 'SYS';

no rows selected.

Now I am really stuck as I dont know why this error keeps on surfacing in the alert log file. Can you please tell me if there is any other way by which I can diagonise as to why I am getting this Error in my alert log file.

Thanks. 

Hi Tom,

<TOM>
variable n number
exec dbms_job.submit( :n, 'dbms_job.remove(JOB);' );
commit;
</TOM>

After executing this procedure a job is created in the job queue and is visible in dba_jobs, but in my case I am not able to view the offending job number from the alert log in dba_jobs.

Below are some more Errors I found today in the alert log.It is almost a a routine , everyday 2 errors are there in the log.

<ALERT LOG>
Sat Jul 17 04:12:08 2004
Errors in file /u01/oracle/admin/prod/bdump/prod_snp2_9696.trc:
ORA-12012: error on auto execute of job 1662
ORA-23421: job number 1662 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 525
ORA-06512: at "SYS.DBMS_IJOB", line 258
ORA-06512: at line 1
Sat Jul 17 04:12:12 2004
Errors in file /u01/oracle/admin/prod/bdump/prod_snp3_9698.trc:
ORA-12012: error on auto execute of job 1661
ORA-23421: job number 1661 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 525
ORA-06512: at "SYS.DBMS_IJOB", line 258
ORA-06512: at line 1


Sun Jul 18 04:08:58 2004
Errors in file /u01/oracle/admin/prod/bdump/prod_snp2_9696.trc:
ORA-12012: error on auto execute of job 1664
ORA-23421: job number 1664 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 525
ORA-06512: at "SYS.DBMS_IJOB", line 258
ORA-06512: at line 1
Sun Jul 18 04:09:00 2004
Errors in file /u01/oracle/admin/prod/bdump/prod_snp3_9698.trc:
ORA-12012: error on auto execute of job 1663
ORA-23421: job number 1663 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 525
ORA-06512: at "SYS.DBMS_IJOB", line 258
ORA-06512: at line 1

Mon Jul 19 04:10:53 2004
ORA-12012: error on auto execute of job 1666
ORA-23421: job number 1666 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 525
ORA-06512: at "SYS.DBMS_IJOB", line 258
ORA-06512: at line 1
Mon Jul 19 04:10:58 2004
Errors in file /u01/oracle/admin/prod/bdump/prod_snp2_9696.trc:
ORA-12012: error on auto execute of job 1665
ORA-23421: job number 1665 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 525
ORA-06512: at "SYS.DBMS_IJOB", line 258
</ALERT LOG>

But when I select from dba_jobs I get only the following 2 jobs which are executing.

SQL> select job from dba_jobs;

       JOB
----------
       137
       138


Is there any other way by means of which I can track exactly who/what is trying to submit a job that is trying to remove itself and that is afterwards being deleted from the queue?

Thanks. 

Tom,

I am submitting a job using dbms_job package as a non-SYS user. But it does not work. But it works without any problem if the job is submitted as 'SYS' user. The job calls a procedure that updates two rows a table. The procedure works in sqlplus. What could be the problem?

Thanks

Tom,

We have a logon trigger, that is created in (non-SYS schama) SYSTEM.

create or replace trigger sp_logon_trigger
after logon
on database
when (upper(USER) = 'SH')
declare
n number;
begin
dbms_job.submit(n,'null;',sysdate + 0.50/24/60,null);
end;

So when user SH logs on a job is submitted.

The job is created without any problem and the user can login.

select job,what,failures,next_date,sysdate from dba_jobs;
====>

JOB LOG_USER PRIV_USER SCHEMA_USER
---------- ------------------------------ ------------------------------ ------------------------------
WHAT

FAILURES NEXT_DATE SYSDATE
---------- ---------------------- ----------------------
11 SH SYSTEM SYSTEM
null;
3 30-Oct-2004 03:17:00pm 30-Oct-2004 03:12:48pm


1 row selected.
<====

The job is supposed to run 30 sec. after loggin in, but it fails as seen in failures = 3.

And this is content of trace file.

<======= Start =======>
Dump file c:\oracle\admin\prashant\udump\prashant_j000_672.trc
Sat Oct 30 15:09:00 2004
ORACLE V9.2.0.3.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 , CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Windows 2000 Version 5.2 , CPU type 586
Instance name: prashant

Redo thread mounted by this instance: 1

Oracle process number: 13

Windows thread id: 672, image: ORACLE.EXE (J000)


*** SESSION ID:(13.346) 2004-10-30 15:09:00.000
*** 2004-10-30 15:09:00.000
ORA-12012: error on auto execute of job 11
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6
<======= End =======>

If the logon trigger is created in SYS schema. Then the job executes without any error.

Please help.

Tom,

This is the actual script of the trigger. The exceptions shown in trace were raised while executing the logon trigger.

create or replace trigger sp_logon_trigger
after logon
on database
when (upper(USER) = 'SH')
declare
n number;
v_sid number;
v_serial# number;
begin
select sid,serial#
into v_sid,v_serial#
from sys.v_$session
where audsid = userenv('sessionid');
-- Log sid,serial# in a table
dbms_job.submit(n,'null;',sysdate + 0.50/24/60,null);
end;

The job fails in 'select into' when job session logs in. Why should this happen? Are there more than one 'audsid' rows in v$session matching userenv('sessionid') when job session logs in? Why does the trigger execute when there is condition (upper(USER) = 'SH')?

Now the job works without any problem after following change. And there are no trace files. Also after 'grant select on v_$session to <non-SYS/non-SYSTEM>;' the trigger/job works from any other non-SYS and non-SYSTEM schema too.

These changes are done to above script:
1) select-into is changed to open cursor-fetch
2) there is 'if v_sid <> 1 then' condition to prevent recursive submission of the job. Why is the 'v_sid' value 1 when the job executes?

job_queue_processes = 10 and there is only one job is being run in database for this test.

create or replace trigger sp_logon_trigger
after logon
on database
when (upper(USER) = 'SH')
declare
n number;
v_sid number;
v_serial# number;
type typcur is ref cursor;
cursession typcur;
begin
open cursession for 'select sid,serial# from sys.v_$session where audsid = userenv(''sessionid'')';
fetch cursession into v_sid,v_serial#;
close cursession;
if v_sid <> 1 then
-- Log sid,serial# in a table
dbms_job.submit(n,'null;',sysdate + 0.50/24/60,null);
end if;
end;

Thanks

Tom,

We have created a view and the view is used in the logon trigger.

create or replace view vsession_info as
select *
from sys.v_$session
where sid = (select sid from sys.v_$mystat where rownum=1)
/
create or replace trigger sp_logon_trigger
after logon
on database
when (upper(USER) = 'SH')
declare
n number;
v_sid number;
v_serial# number;
begin
select sid,serial#
into v_sid, v_serial#
from vsession_info;
-- Log sid,serial# in a table
dbms_job.submit(n,'null;',sysdate + 0.50/24/60,null);
end;
/

Now the trigger executes when SH logs in and a job is submitted.

But when the job executes, the logon trigger is executed again and another job is submitted. The value of 'audsid' column is 0 when the job executes. Is 'audsid' always 0 when the job executes? If so, can the trigger below prevent recursive submission of job ('where audsid <> 0' is added in select-into statement).

create or replace trigger sp_logon_trigger
after logon
on database
when (upper(USER) = 'SH')
declare
n number;
v_sid number;
v_serial# number;
begin
select sid,serial#
into v_sid, v_serial#
from vsession_info
where audsid <> 0;
-- Log sid,serial# in a table
dbms_job.submit(n,'null;',sysdate + 0.50/24/60,null);
exception
when no_data_found then
null;
when others then
raise;
end;
/

Thanks

declare
l_job NUMBER;
begin
dbms_job.submit(l_job,'declare
l_job_id number := JOB;
l_err_msg VARCHAR2(100);
l_sid NUMBER;
begin
   SELECT sid INTO l_sid FROM sys.v_$mystat WHERE rownum=1 ;
INSERT INTO job_parameters( job_id, job_name, worker_id, job_status, sid )
   VALUES (l_job_id, ''p1'', 0 , ''S'', l_sid);
   COMMIT;
   p1;
   DELETE FROM job_parameters WHERE job_id = l_job_id;
   COMMIT;
exception
   WHEN OTHERS THEN
      l_err_msg := SUBSTR(SQLERRM,1,100);
      broken := TRUE;
      UPDATE job_parameters SET job_status=''F'', err_msg = l_err_msg
    WHERE job_id = l_job_id;
    COMMIT;
end;');
COMMIT;
end;
/

Scripts
-----------
CREATE TABLE JOB_PARAMETERS
(
  JOB_ID      NUMBER,
  JOB_NAME    VARCHAR2(60 BYTE),
  WORKER_ID   NUMBER,
  JOB_STATUS  VARCHAR2(1 BYTE),
  ERR_MSG     VARCHAR2(200 BYTE),
  SID         NUMBER
);

CREATE OR REPLACE procedure p1 
IS

BEGIN
     dbms_lock.sleep(10);
EXCEPTION
     WHEN OTHERS THEN
         ROLLBACK;
            RAISE_APPLICATION_ERROR (-20001, SUBSTR(SQLERRM,1,100));
END;
/

However when I comment out the line
 SELECT sid INTO l_sid FROM sys.v_$mystat WHERE rownum=1 ;    
it works.

  1  declare
  2  l_sid NUMBER;
  3  begin
  4     SELECT sid INTO l_sid FROM sys.v_$mystat WHERE rownum=1 ;
  5     dbms_output.put_line(l_sid);
  6* end;
SQL> /
21

PL/SQL procedure successfully completed.

The funny thing is it doesn't give me any error. Neither does it put anything in trace.

Please let me know what is the problem, and how do I detect if there is anything wrong with the submission, where can I get the reason for the failure (in this case alert log doesn't record anything)?

Thanks and Regards
Prashant 

Hi Tom,

Thanks for the answer.....I again messed it up, I asked privileges to be granted to me directly not through a role for v_$mystat. But the DBA didn't do that, and he again did it through a role. Gosh....this is really killing me.....how bad I didn't realize this....hopefully I will learn...Tom you are really helpful.

Thanks
Prashant

Hi Tom,
I was going through the DBMS_JOB package.

style 1:
begin exec sp_test(1,3); end; (eg. 3 instances)

style 2:
begin dbms_job.submit(job_id, sp_test(1,3)); end;
(eg. 3 instances)

Can you please tell me from above 2 styles, which would be more effective and why? or both are actually same?

I have read some documentation but your answer is very valuable.


Thankyou very much.

Tom, I am just trying to understand that if I executed a process using
1 exec sp_test
2 dbms_job.submit(job_id, sp_test)

In both above 2 methods I achieve my goal sp_test is executed, but is any one of them a better way than other or both are of same i.e performance wise or so.
One benefit I know is if I used dbms_job.submit () then I could suspend a job if required which I cannot do using exec.

Thankyou

Hi Tom

We have similar issues. The developer used dbms_job to submit the job to be run immeidately. It was claimed that it is easier for one to monitor it using dba_jobs, dba_jobs_running instead of just using v$session. Here is the code:

----------------------
begin
insert into log_table (pk, ...) values(id, .....);
dbms_jobs(job_id, 'sp_test;'); -- No time delay here.
update log_table set job_column = job_id where pk=id;
commit
end;
/
----------------------

By using log_table, they can get job_id and monitor the session in dba_jobs_running view.

If they use this code below, they can not match session id with the record in the log_table. It is hard for them to monitor the long running session.

---------------------------
begin
insert into log_table (pk, ...) values(id, .....);
sp_test;
commit
end;
/
---------------------------

Thanks so much for your help.

Hi Tom,

As I showed in the code, they have a log_table to indicate the status of the process. They can query that log_table to see the status of the session (which is updated by sp_test), then used the job_id in that table to query dba_jobs_running to see whether it is running or not. If they execute sp_test directly, they won't know which session id in v$session corresponded to the record in the log_table.

Here is the scenario: A front user asks why his session never finish. The developer looks the log_table, seeing that the user's session is in "running state". But the developer don't execatly which session in v$session belongs to the front user. But with dbms_job, they can query dba_jobs_running to see whether that session is running or not.

Thanks so much for your help.

Hi Tom,

You are right. I just looked throuhg p52 of your book "Effective design". It seems that this package is the function we need.

Thanks so much for your help.

Hi Tom

i have schedule a job at 11 AM per day,
but after running job at 11 AM it automatically set next_date to (sysdate + 4-5 mins) so that after avery
4 or 5 minutes job is running automatically while i had set interval parameter as (sysdate + 1)
what is the problem ?

ravi

Tom,
Why the following piece of code works in 8.1.7.4.0 and not in 9i or 10G...In 9i or 10g it
raises a 6502 numberic conversion error?

declare
t_var number;
begin
t_var := ' ';
end;

you sure about that

ops$tkyte@ORA817DEV> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

ops$tkyte@ORA817DEV> declare
t_var number;
begin
t_var := ' ';
end;  2    3    4    5
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion
error
ORA-06512: at line 4


Now, in 8.0.6 and before - it did "silently succeed", so it looks like it was a bug that was fixed - ' ' is not a number:

ops$tkyte@ORA806> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
PL/SQL Release 8.0.6.0.0 - Production
CORE Version 4.0.6.0.0 - Production
TNS for Solaris: Version 8.0.6.0.0 - Production
NLSRTL Version 3.3.3.0.0 - Production

ops$tkyte@ORA806> declare
t_var number;
begin
t_var := ' ';
end;  2    3    4    5
  6  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA806> select to_number( ' ' ) from dual;
select to_number( ' ' ) from dual
                  *
ERROR at line 1:
ORA-01722: invalid number

 

Yes..i am sure....


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for IBM/AIX RISC System/6000: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

Elapsed: 00:00:00.00
SQL> declare
  2      t_var number;
  3     begin
  4      t_var := ' ';
  5      end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

-----------------------------

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 - Production

Elapsed: 00:00:00.01
SQL> declare
  2      t_var number;
  3     begin
  4      t_var := ' ';
  5      end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4


Elapsed: 00:00:00.00 

Hi Tom!

I just want to ask where can i see the specific error of the job i submitted using dbms_job? Thanks and God bless!

Sir Thom! Thanks for replying, I'm sorry I don't exactly know where i can find the alert logs? Please help me. Thanks!

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /home/ora10gr2/admin/ora10gr2/
                                                 bdump
ops$tkyte%ORA10GR2>

Thanks ThoM! Sir I already found the alert logs in our server,however,i don't understand the error. Can u please explain to me what this mean, particularly the last error.

Errors in file d:\oracle\admin\ora_hp\udump\orahp_j000_336.trc:
ORA-12012: error on auto execute of job 322
ORA-12005: may not schedule automatic refresh for times in the past

Thanks and God bless!

Hi Tom,
I have a procedure
CREATE OR REPLACE PROCEDURE SS_TEST_PROC_JOB(i_question IN VARCHAR_ARRAY)
IS
p_sample varchar(30):='Test Procedure : ';
BEGIN
dbms_output.put_line(p_sample);
END ss_TEST_PROC_JOB;

Where VARCHAR_ARRAY is
CREATE OR REPLACE
type VARCHAR_ARRAY as table of varchar2(4000);

Now When I try to call this procedure via DBMS_JOB.Submit I am not getting the value of VARCHAR_ARRAY type into the procedure

declare
i_question VARCHAR_ARRAY;
N_job_no NUMBER:=0;
BEGIN
DBMS_JOB.SUBMIT(n_job_no,'BEGIN execute immediate TEST_PROC_JOB(:a) using i_question; End;');
COMMIT;
END;

Can you please help me out. Ultimately what I am trying to do is cal the dbms_job from Java so that I can fire the procedure asynchronously.
Thanks in Advance
Sudip

ops$tkyte%ORA9IR2> create or replace type varchar_array as table of varchar2(4000)
  2  /

Type created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create table job_parameters( job number, seq number, val varchar2(4000), primary key(job,seq) );

Table created.

ops$tkyte%ORA9IR2> create table msg ( txt varchar2(80) );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace procedure test_proc_job( p_job in number )
  2  as
  3      l_question varchar_array;
  4      l_txt msg.txt%type;
  5  begin
  6      select val bulk collect into l_question
  7        from job_parameters
  8       where job = p_job
  9       order by seq;
 10
 11      l_txt := 'there are ' || l_question.count || ' elements';
 12      insert into msg (txt) values ( l_txt );
 13      delete from job_parameters where job = p_job;
 14  end;
 15  /

Procedure created.

ops$tkyte%ORA9IR2> select * from msg;

no rows selected

ops$tkyte%ORA9IR2> declare
  2      l_question varchar_array := varchar_array( 'hello', 'world' );
  3      l_job number;
  4  begin
  5      dbms_job.submit( l_job, 'begin test_proc_job(JOB); end;' );
  6      for i in 1 .. l_question.count
  7      loop
  8          insert into job_parameters (job,seq,val) values (l_job, i, l_question(i) );
  9      end loop;
 10      commit;
 11  end;
 12  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> exec dbms_lock.sleep( 5 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> select * from msg;

TXT
-------------------------------------------------------------------------------
there are 2 elements

Hi Tom,
Thanks a lot for your answer.
Can we pass the VARCHAR_ARRAY as a parameter in dbms_job in the above example given by you. This would provide a way to directly passing the VARACHAR_ARRAY from Java instead of writing the values in a temp table first (matters complicate more when we try to do this in multi user environment).

Thanks and Regards
Sudip Sarkar

Thanks a lot Tom. Your suggestion indeed was very very helpful.

Best regards
Sudip

SQL> show user
USER is "DP2000"
SQL> BEGIN
  2    SYS.DBMS_JOB.REMOVE(111245);
  3  COMMIT;
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    X NUMBER;
  3  BEGIN
  4    SYS.DBMS_JOB.SUBMIT
  5    ( job       => X
  6     ,what      => 'CDSIL.POPULATE_CDSL_HRMS_DETAILS;
  7  '
  8     ,next_date => to_date('01-01-4000 00:00:00','dd/mm/yyyy hh24:mi:ss')
  9     ,interval  => 'TRUNC(SYSDATE+1)+19/24'
 10     ,no_parse  => FALSE
 11    );
 12    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
 13    SYS.DBMS_JOB.BROKEN
 14     (job    => X,
 15      broken => TRUE);
 16  COMMIT;
 17  END;
 18  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  DBMS_JOB.RUN(135643);
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 637
ORA-06512: at "SYS.DBMS_JOB", line 284
ORA-06512: at line 2

I got this error when I changed the call parameters to a procedure referenced by the DBMS_JOB. Overlooked correcting the parms for the procedure in the job. Problem went away after I fixed it.

Hi Tom
version:Oracle 11g( 11.2)
I have a stored procedure to update a table in parallel based on a id supplied. Below is the calling program
DECLARE
V_I VARCHAR2(20);
V_NAME VARCHAR2(20);
BEGIN
while v_i <10
LOOP
V_NAME:='DMO'||v_i;
DBMS_JOB.SUBMIT(JOB=> V_NAME , WHAT=>'BEGIN DOC_DATA_TRANSFER_PROC ('||''''|| V_I ||''''||'); END; ');
COMMIT;
v_i:=v_i+1;
END LOOP;
END;
/

I supply a job name and execute the job.
1) After a day , How do I track whether this job was executed and its status
2) How do I track the status of this job when given for execution

Thanks in advance

Hi Tom
followup on the above with subject line
history of Standalone Job submitted

How can I track the status of the jobs submitted using dbms_job.submit. Thanks