SQL & PL/SQL :: Using Procedure Triggered By Dbms_scheduler / ORA-29283 / Invalid File Operation

Nov 10, 2010

This is regarding a problem we are facing during report(.xls) creation which is done using a procedure triggered by a job run.

The report file(.xls) file is not getting created when the job(using dbms_scheduler) calls.

The procedure uses utl_file to create an .xls file

We have a folder on the path /oracle/tata_aig_life/websales/dnld . This folder( dnld ) has the all the priviliges drwxrwxrwx.

We have a job scheduler as the attachment which in turn triggers a procedure(check the code attachment for the scheduler and the procedure).

In the procedure we first remove the file(.xls) created in the path and then recreate a new .xls file.

The reason for first removing the file and recreating is that the existing file is not getting updated with a new file when the job is run.

We capture the exceptions in a table. In the table the following exception is logged :ORA-29283: invalid file operation

The job is triggered and the files are created on the path mentioned in the procedure on the UAT Environment..

Also the files are created when we manually run the same procedure in the path.

The Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

The OS flavour is SunOS otlpsr5cora01 5.10 Generic_127111-11 sun4u sparc SUNW,Sun-Fire-V490 -------

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: ORA-29283 - Invalid File Operation?

Dec 29, 2012

We are using the below procedure to generate the files on the DB server.

SQL>
SQL> create or replace procedure write_to_file(p_dir varchar2,
2 p_file varchar2,
3 p_mode varchar2,
4 p_clob clob) as
5 l_output utl_file.file_type;
6 l_amt number default 32767;
7 l_offset number default 1;
8 l_length number default dbms_lob.getlength(p_clob);
9 new_clob clob;
10 BEGIN
11
12 l_output := utl_file.fopen(p_dir, p_file, p_mode, 32767);
13 while (l_offset < l_length) loop
14 new_clob:= SUBSTR(p_clob,l_offset,l_amt);
15 utl_file.put_line(l_output, new_clob,true);
16 l_offset := l_offset + dbms_lob.getlength(new_clob);
17 end loop;
18 utl_file.new_line(l_output);
19 utl_file.fclose(l_output);
20 end write_to_file;
21 /

Procedure created

SQL>

This works fine we call this from PL/SQL developer tool. However, when this procedure is called from Java JDBC, it is giving error

java.sql.SQLException: ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "SYS.OBP_UPGRADE_UTIL", line 2816
ORA-06512: at "SYS.OBP_UPGRADE_UTIL", line 3029
ORA-06512: at line 1

DB schema used for both are same (SYS) Verified the directory on the DB server This Procedure called from package with AUTHID CURRENT_USER option.

View 1 Replies View Related

Forms :: ORA-29283 - Invalid File Operation

May 22, 2013

My Application is running on the unix server - user oracle. My DB is in different unix box. I had created the folder called OUT with the permission of 777 in DB server. I created ORacle Directory DMPDIR for that folder /OUT. and executed grant read/write to that schema.

1) when i use the below code in sqlplus its writing the file into that folder.
DECLARE
vInHandle utl_file.file_type;
vNewLine VARCHAR2(4000);

[Code].....

But, when the same piece of code is executed from the package ( which called from the application)its throwing the ORA-29283: invalid file operation.

even i am able to copy the files into that folder with the user oracle.

View 39 Replies View Related

PL/SQL :: ORA-29283 Invalid File Operation On Windows System

Jul 31, 2012

Windows 2003, DB 10.2.0.3...I have to read some files from remote folder, so on remote Windows machine I shared folder c:est (grant Everyone first, and Administrator then) and on DB Server I mapped the remote folder with letter T..All these operations were made with administrator privileges.

In Oracle I create a directory object with this command:
CREATE OR REPLACE directory T_DIR AS 'T:';

Then I granted read and write privileges to my user
GRANT READ, WRITE ON DIRECTORY T_DIR TO <user>; (I tried also to set PUBLIC and SYSTEM)

But when I try to open a file with UTL_FILE with this command

file_handler := UTL_FILE.fopen( 'T_DIR', 'Dati.ini', 'r' ) ;

the error is
Exception: ORA-29283: operazione file non valida
ORA-06512: a "SYS.UTL_FILE", line 475
ORA-29283: operazione file non valida

If I change my directory object in 'C: est' (local folder) it works correctly.I tried also restart DB while shared folders were connected but with the same result.I tried, from sqlplus, the command host (dir t:) and it works (folder is accessible)

Is it an Oracle bug? Is it a Windows bug?

View 5 Replies View Related

SQL & PL/SQL :: Writing Data To A Network Shared Folder / ORA-29283 / Invalid File Operation

Jul 13, 2010

I am trying to write data to a network shared folder. When I write to a local file it works perfectly. Below is my procedure.

CREATE OR REPLACE procedure nbpsbp_file as
type r_cursor is ref cursor;
refr r_cursor;
tab_name varchar2(20):= null;
tab_name1 varchar2(20) := null;
tab_name2 varchar2(20) := null;

[code]....

When I execute the above procedure, it gives me the following error

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at "NBPSBP_FILE", line 36
ORA-06512: at line 1

I have also set the parameter utl_file_dir = '\10.16.10.225 emp' When I set the utl_file_sir to a local folder, for example, c: emp, and use the same path in UTL_FILE.FOPEN, then it works fine and writes the desired output to text file. But when I give it a network address, it raises the above error.

View 3 Replies View Related

SQL & PL/SQL :: Invalid File Operation

May 5, 2010

i have created an file in my local system (sys name : System1). i have created a directory as below

SQL>CREATE DIRECTORY test_dir AS '\System1TEMP';

I gave rights as

SQL>GRANT ALL ON DIRECTORY TEST_DIR TO PUBLIC;

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
IF utl_file.is_open(fileHandler) THEN
utl_file.fclose_all;
END IF;
[code]......

but while executing the above procedure, Invalid File Operation error occurs.

View 10 Replies View Related

SQL & PL/SQL :: ORA-01010 - Invalid OCI Operation During SCHEDULER Job Procedure That Selects From Database Link

Oct 18, 2012

The Oracle DB in question is 11.2.0.1, x64, Server 2008.I also have a SQL Server 2005 database that runs a third party product, "PaperVision", which we use to manage documents of various kinds. This SQL Server server is also Win 2008, x64.Now, on the server that runs SQL Server, I have a simple view which is defined as such :

select DOCID,
DOCINDEX1,
DOCINDEX2,
DOCINDEX3,
DOCINDEX4,
DOCINDEX5,
DOCINDEX6,
DOCINDEX7,
DOCINDEX8,
[code]....

This view works great from the SQL Server side. I also created a database link from Oracle to the SQL Server machine, and it also works great.It is defined as such :

CREATE PUBLIC DATABASE LINK PVE_SQLSERVER
CONNECT TO EVP_PVE_USER
IDENTIFIED BY <PWD>
USING 'PVE_SQLSERVER';

Where EVP_PVE_USER is a user created on the SQL Server machine with rights to select from this view.I know it works because I get results with a sql command like :

select * from VW_PVE_DOCS_1_1@PVE_SQLSERVER;

I also created a view on the Oracle server that refines this information. It is defined as such :

CREATE OR REPLACE FORCE VIEW EVPDBA.VW_PVE_CONTRACTS_INALERT
(
DOCID,
EFFECTIVE_DATE,
EXPIRATION_TYPE,
ALERT_PERIOD_START,
ALERT_PERIOD_END,
ACRONYM,
[code]....

This view also works fine, i.e., I can select * from it from the sql command line.Now, the problem comes in when I need to run a procedure that processes this view every night and/or week.I have stripped everything out of this procedure that is not relevant, and it is defined as such for this forum :

CREATE OR REPLACE PROCEDURE EVPDBA.TESTME
is
tnum number := 0;
begin
select count(*) into tnum from VW_PVE_CONTRACTS_INALERT;
end;
/

If I execute this procedure from the sql command line, all is well.When I run it from a scheduler job, I get

ORA-01010: invalid OCI operation
ORA-02063: preceding line from PVE_SQLSERVER
ORA-06512: at "EVPDBA.TESTME", line 5
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

I am aware that DBMS_SCHEDULER performs a commit when scheduling a job, however, this is not scheduled from a trigger.I scoured the forums and have found a few things that seemed relevant, but not much. One had to do with the version of the JDBC driver between two Oracle databases, but I wonder if the age difference between Oracle 11 and SQL Server 2005 (Express) might be an issue. The fact that all command line select statements and running the procedure work fine implies to me that there is an additional issue raised due to the scheduler.

The other posts I found talked about performing a commit just before any select that ultimately pulls across a db link. I did this, and still no luck.One other useful fact - the job appeared to run succesfully at 5am, yet trying again at 8am threw the error, so it may be sporadic. (Although during regular daytime hours it is a very repeatable error).

I am looking into reformatting things to use the older DBMS_JOB, however, I really like the log history of job details and other functionality available with SCHEDULER.

View 5 Replies View Related

PL/SQL :: Set Out Parameter In Procedure For Dbms_scheduler

Sep 6, 2013

I see example for a dbms_scheduler setting in parameter values but what about if it is an out parameter ? 

create or replace procedure vd_tst (v_id out number) asbegin   v_id := to_number(to_char(sysdate,'YYYYMMDDHH24MISS'));end vd_tst 

how do I set a hook to do an alert let say for dbms_ schedulerlet sayif v_id mod(9)==0 then <alarm>

View 9 Replies View Related

SQL & PL/SQL :: FDPSTP Failed Due To ORA-29277 / Invalid SMTP Operation

Apr 18, 2011

in the following program i am submitting a request and sending concurrent program log file (.REQ file)as an attachment but getting following error

ORACLE error 29277 in FDPSTP
Cause: FDPSTP failed due to ORA-29277: invalid SMTP operation
ORA-06512: at "SYS.UTL_SMTP", line 44
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at "SYS.UTL_SMTP", line 383
ORA-06512: at "SYS.UTL_SMTP", line 399

[code]....

View 3 Replies View Related

SQL & PL/SQL :: Invoking Oracle Bat File From Dbms_scheduler

Oct 21, 2011

I'm trying to call a simple batch file from dbms_scheduler, the job created successfully but I it doesn't invoke the batch file I've created.

This is the contents of my batch file :
EXEC hr.load_employees

And this is the job attempting to invoke it :

begin
dbms_scheduler.create_job
(job_name => 'EMP_HIST',
job_type => 'EXECUTABLE',
job_action => 'C:sql_scriptsemployees_load.sql > nul',
start_date => SYSDATE,
-- repeat_interval => 'FREQ=DAILY;BYHOUR=23', -- Every night at 23:00.
repeat_interval => 'FREQ=SECONDLY; INTERVAL=2', -- Every Minute
enabled => true,
comments => 'Copy the emp table to history');
end;
/

View 7 Replies View Related

SQL & PL/SQL :: ORA-22288 / File Or LOB Operation FILEOPEN Failed

May 3, 2011

My Login is System

Already created Directory MY_DIR in c:mydir

Showing Following error

1 DECLARE
2 src_lob BFILE := BFILENAME('MY_DIR','C:
mi2Blue.jpg');
3 dest_lob BLOB;
4 BEGIN
5 INSERT INTO lob_table VALUES(2, EMPTY_BLOB())
6 RETURNING doc INTO dest_lob;

[code].....

View 21 Replies View Related

SQL & PL/SQL :: Possible To Perform Any Operation Using Oracle Like Addition And Division In CSV File

Dec 2, 2010

Is it possible to perform any operation using oracle like addition and division in csv file before loading data in oracle. and after the operation changes must save.

Is it possible or not.

View 2 Replies View Related

SQL & PL/SQL :: Invalid Cursor Error In Procedure

Feb 2, 2011

I have this stored procedure as part of package.

PROCEDURE XCOM_X060_UPDATEOHBFAILURE( in_CALL_ID IN NUMBER,
in_SPLY_REORD_NO IN CHAR,
in_OHB_QTY_CARTONS IN NUMBER,
in_OHB_QTY_UNITS IN NUMBER,
in_SPLY_TOT_OHB_QTY IN NUMBER,
in_OHB_INPUT_CTNS_MIN IN NUMBER,
in_OHB_INPUT_CTNS_MAX IN NUMBER,
in_UNITS_PER_CARTON IN NUMBER,
in_OHB_INPUT_UNIT_CONSTANT IN NUMBER,
in_TOTAL_CARTONS IN NUMBER,
out_ALLOW_OHB_INPUT_FLAG OUT CHAR,
out_ERR_CODE OUT NUMBER,
out_ERR_MESSAGE OUT VARCHAR2)

When the stored procedure is executed it is throwing following exception OTHERS EXCEPTION in XCOM_X060_UPDATEOHBFAILURE - SQL -1001 SQLERRM: ORA-01001: invalid cursor

Here is the execution script
DECLARE
IN_CALL_ID NUMBER;
IN_SPLY_REORD_NO VARCHAR2(32767);
IN_OHB_QTY_CARTONS NUMBER;
IN_OHB_QTY_UNITS NUMBER;
IN_SPLY_TOT_OHB_QTY NUMBER;
IN_OHB_INPUT_CTNS_MIN NUMBER;
[code]...

There is no cursor used in the procedure. It just inserts records in the table for given input values.

View 10 Replies View Related

SQL & PL/SQL :: ORA-00904 / Procedure Name / Invalid Identifier

May 12, 2010

I want run a procedure from a easy sql select.I have write this code to run this procedure, but response with a oracle error:

select [procedure_name](:data1, :data2) as test from dual

the oracle error is this:

ORA-00904: "PROCEDURENAME": invalid identifier

View 12 Replies View Related

ORA-00904 / Invalid Identifier - Not Able To Run EXECUTE IMMEDIATE In PL/SQL Procedure

Feb 4, 2012

Here is the code I am using:

CREATE OR REPLACE
PROCEDURE CUBE_VIEW (VAR_DT IN VARCHAR2 DEFAULT '')
AUTHID CURRENT_USER
AS
START_DATE NUMBER;
END_DATE NUMBER;
VAR DATE;
BEGIN
IF VAR_DT IS NULL THEN

[code]....

The code complies successfully but when I am executing the code, here is the error I get. The code works successfully when I manually insert the values (as done in the commented out statement) but when use it from variable, it gives me error.

Connecting to the database LocalEnvironment.
ORA-00904: "END_DATE": invalid identifier
ORA-06512: at "TESTING.CUBE_VIEW", line 18
ORA-06512: at line 6
START DATE IS 90301
END DATE IS 111201
Process exited.
Disconnecting from the database LocalEnvironment.

View 3 Replies View Related

SQL & PL/SQL :: ALTER TABLE Causing Procedure To Go INVALID?

Aug 3, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I altered existing table EVENT_SUB - added 3 columns. After that, I noticed all the procedures which had mention of this table name went in INVALID status, even if its simple SELECT, ALTER OR INSERT as shown below..

SELECT * FROM EVENT_SUB

OR
INSERT INTO EVENT_SUB...
OR
ALTER TABLE EVENT_SUB
WHERE....

So I had to recompile all the procedures associated with it. Is there any other ways to achieve this, like a line of code to add in the procedure itself, right after this DDL statements.

Sometimes i use this:
select object_name, object_type from all_objects where owner='TOYCOM' and status='INVALID'
Then, I would simply recompile the invalid objects.

For indexes, i do...

alter index <name> rebuild;

BTW, I did try to preview message, and then click on Create Topic, it gave me error..again.

"A system error has occurred.

View 3 Replies View Related

PL/SQL :: How To Write Procedure To Load Data Into Table Using XML File As Input To Procedure

Sep 20, 2013

how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me. 

xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.

View 3 Replies View Related

Client Tools :: Error / ORA-00900 / Invalid SQL Statement After Executing Procedure

Jan 3, 2011

CREATE OR REPLACE PROCEDURE test
IS
CURSOR cusers IS SELECT user_name, user_date FROM users;
uname users.user_name%TYPE;
udate users.user_date%TYPE;
BEGIN
OPEN cusers;

[code].....

When I try to execute this procedure I get following error:

ORA-00900: invalid SQL statement

Compilation of procedure is successful.

SQL code for creating the USERS table is here:

CREATE TABLE "USERS"
("USER_ID" NUMBER(10,0) NOT NULL ENABLE,
"USER_NAME" VARCHAR2(50) NOT NULL ENABLE,
"USER_EMAIL" VARCHAR2(50) NOT NULL ENABLE,
"USER_PASS" VARCHAR2(50) NOT NULL ENABLE,
"USER_DATE" DATE NOT NULL ENABLE,
CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE
)

View 3 Replies View Related

Server Utilities :: IMPDP (ORA-31619 Invalid Dump File)

May 29, 2012

I need to recreate/ clone my database to a new machine. The two machine are not connected in the network.

Step 1. (Oracle 10.2.0.5 AIX 64-bit)
expdp username/password@db1 full=y dumpfile=dp:fpac052912_dp%U.dmp logfile=dp:fpac052912_expdp.log job_name=full_exp

Step 2.
FTP dump files to Windows

Step 3. (Oracle 10.1.0.2 Windows 32-bit)
impdp username/password@db1 dumpfile=dp:fpac052912_dp%U.dmp logfile=dp:fpac052912_impdp.log full=y

I got:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "C:P7DBfpac052912_dp01.dmp"

Done in AIX:
create directory dp as '/bak'
grant read, write on directory dp to public;
grant exp_full_database to username;

Done in Windows:
create directory dp as 'C:P7DB';
grant read, write on directory dp to public;
grant exp_full_database to username;
grant imp_full_database to username;

View 8 Replies View Related

SQL & PL/SQL :: Load CSV File Into External Table / ORA-01722 / Invalid Number

Apr 26, 2011

I'm trying to load a csv file into an external table and when I select the table 0 rows is the result.

The log file has the following errors:

KUP-04021: field formatting error for field DEPTNO
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /usr/tmpclie.csv
error processing column EMPNO in row 2 for datafile /usr/tmpclie.csv
ORA-01722: invalid number

This is the script for the table:

create table emp_ext (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,

[code]....

And this is csv:

7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10

[code]....

View 37 Replies View Related

Server Utilities :: Error / Bad Dump File Specification / Invalid Argument Value

Nov 21, 2012

I restore Dump File in Oracle 10g .

my Command is :

impdp DUMPFILE=BAK910830.DMP NOLOGFILE=Y

An error message is as follows:

"Invalid argument value"

"Bad dump file specification"

"Dump File may ba an original export Dump file "

I think the dump File is in Oracle 11g Format .

View 8 Replies View Related

SQL & PL/SQL :: Schedule A Job Using DBMS_SCHEDULER?

Jan 31, 2013

I am trying to schedule a job using DBMS_SCHEDULER.

The job has to start on SATURDAY 12:00 midnight and should run every on SATURDAY 12:00 midnight.

Even if I create the job on MONDAY first it should start on coming sunday and following sundays.

But I am getting the below error.

ORA-27452: BEGIN APPS_GLOBAL.POPULATE_TARGET144('APPS_XX','APPS_BE',5959); end; is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 2

The Job script is

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'TEST_RAM',
job_type => 'STORED_PROCEDURE',
job_action => 'BEGIN APPS_GLOBAL.POPULATE_TARGET144(''APPS_XX'',''APPS_BE'',5959); END;',

[code]......

View 6 Replies View Related

Executed Two Jobs Using Dbms_scheduler

Aug 20, 2011

I have executed two jobs using dbms_scheduler. The value of the job_queue_process is 10 in my database. It is RAC database with Oracle 10g.

My job gets executed. But the next run is not getting executed at the next run date. In the below example, the systimestamp is 12:27. The first job's next run date is 12:30 so that is fine. But the second job's run date is 12:21 which is 6 mintues less than current time. As a result, the second job does not get executed after this at all..

SQL> select job_name, next_run_date, last_start_date, last_run_duration from user_scheduler_jobs;
HISASAV_JOB
20-AUG-11 12.30.41.000000 PM ASIA/CALCUTTA
20-AUG-11 12.20.41.122959 PM ASIA/CALCUTTA
+000000000 00:00:01.307071
[code]....

View 4 Replies View Related

Daylight Savings With Dbms_scheduler?

Apr 23, 2012

During the daylight savings time our scheduler jobs are running either an hour before/after depending upon the time switch.

I went through the Oracle documentation and found below suggestions which I have already tried in vain.

Document says scheduler first picks the timezone from the start date of the job if provided so i tried setting the start date using the TO_TIMESTAMP_TZ('2012/01/22 18:50:00 US/Eastern','yyyy/mm/dd hh24:mi:ss tzr') which did not fix the problem. I have noticed that oracle automatically converted into tzh:tzm format.

second solution: setting default timezone of scheduler to the TZR i.e (US/Eastern) instead of the TZh:TZM value. I did that using below script

BEGIN
DBMS_SCHEDULER.set_scheduler_attribute (
attribute => 'default_timezone',
value => 'US/Eastern');
END;

above 2 solutions did not work for me. I have read on internet from some article that below query should return something like

"4/23/2012 11:02:13 US/Eastern" after setting the default timezone of scheduler to TZR but I am still getting "4/23/2012 11:02:13.715816000 AM -04:00".
select dbms_scheduler.stime
from dual;

View 5 Replies View Related

SQL & PL/SQL :: Error In Creating DBMS_SCHEDULER Job?

Feb 25, 2013

I have create the following procedure to create individual jobs dynamically on each schema.

CREATE OR REPLACE PROCEDURE APSP.JOB_TEST_RAM
AS
V_SQL VARCHAR2 (1000);
BEGIN

[Code]....

While executing this procedure I am getting the below error.

PLS-00103: Encountered the symbol "APPS_XX" when expecting one of the following:

View 5 Replies View Related

SQL & PL/SQL :: Create A Schedule Using Dbms_scheduler

Mar 13, 2011

I have create a schedule using dbms_scheduler and the problem is its not running on exact time that is start time , i even changed the attribute as following but its not.Its running using dbms_scheduler.run_job(veh_insp) when run directly.The schedule is as follows.

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'INTERVAL_DAILY_2000',
attribute => 'start_date',
value => TRUNC(SYSDATE)+9.50/24);
END;

View 6 Replies View Related

SQL & PL/SQL :: Repeat Interval For DBMS_SCHEDULER?

Oct 13, 2011

I have created a job using DBMS_SCHEDULER and I want it to run every 30 seconds:

begin
dbms_scheduler.create_job(job_name => 'jobu',
job_type => 'PLSQL_BLOCK',

[Code]....

My question is how can I take the value 30 from a configuration table? Let's say I have a query like select value from config_table where property = 'job_interval' that returns the number 30. How can I set this value to be the repeat interval for my job?

View 9 Replies View Related

Scheduler :: Run DBMS_SCHEDULER Job As Other OS User?

Apr 23, 2013

We use AIX 6.1 and 10.2.0.4.0 - 64bit. When we create an Job it will be execute as OS User Oracle, but this is not wanted.The file permisions are set as described in Guide to External Jobs on 10g with dbms_scheduler e.g. scripts,batch files

rdbms/admin/externaljob.ora
-rw-r----- 1 root dba rdbms/admin/externaljob.ora
egrep -v '^#|^$' rdbms/admin/externaljob.ora
run_user = nobody
run_group = nobody
bin/extjob

[code].....

We tried different Users in the rdbms/admin/externaljob.ora file but every time the same behavior, the job runs as oracle.

View 0 Replies View Related

Security :: Create A Job Using DBMS_SCHEDULER

Jun 27, 2012

I connected to Scott user & I am trying to create a job using DBMS_SCHEDULER as mentioned below.

begin
dbms_scheduler.create_job(
job_name => 'job1',
job_type => 'PLSQL_BLOCK',
job_action => 'Insert into tab2 values(70,''TR'');',
start_date => sysdate,
repeat_interval => 'FREQ = DAILY; INTERVAL = 1');
end;

When i execute this code i'm getting an Error Message as

ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at line 2

I have given grant manage scheduler to scott; Manage Scheduler privilege from SYS User.

View 5 Replies View Related

SQL & PL/SQL :: DBMS_SCHEDULER Calling Shell Script

Sep 13, 2011

I am facing issues with DBMS_SCHEDULER..I am receiving error while executing the "DBMS_SCHEDULER.run_job"..The OS folder has full permission.

OS : UNix
Database: 10g
====================================================================
Error Received:

ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit 274664
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 1
THE CODE IS LISTED BELOW:
[code]....

View 24 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved