SQL & PL/SQL :: ORA-31603 When Using DBMS_JOB.SUBMIT

Jul 5, 2013

I'm using dbms_metadata.get_ddl inside a package and I have a problem: if I execute the package directly, like "exec pkg_util.read_ddl('TA_DL_IDP', 'RMI_KUNDE') it works fine.

If I submit the same procedure using DBMS_JOB.SUBMIT, I get an error: Err_ -31603 - MSG: ORA-31603: object "RMI_KUNDE" of type TABLE not found in schema "TA_DL_IDP"

I know that in order to use dbms_metadata.get_ddl I need to have SELECT_CATALOG_ROLE and I know about the necessity to give explicit grants to objects rather than using roles when running pl/sql code from inside a package, but this is different: the different behavior is between running the same package in foreground and submitting it using DBMS_JOB.SUBMIT.

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Dbms_job.submit How To Receive Output

Jan 2, 2011

Discreet use of dbms_job.submit (to call a procedure), can be used to multi thread several instances of same proc in pl sql. I am able to do it.The problem is that dbms_output.put_line of the proc which is multi threaded does not reflects in the outer proc (the proc which parallels this proc and then waits for their completion).How do I receive the output?

View 3 Replies View Related

DBMS_JOB.SUBMIT Can Pass Dynamic Argument Procedure

Aug 10, 2010

'Can we pass a dynamic collection variable to the procedure that is called from dbms_job.submit'

I have a package my_package with:

1. record type: ocv_rec

2. collection type: varr_ocv_rec varray(100) of ocv_rec

3. record type: ext_id_rec

4. collection type: varr_ext_id_rec table of ext_id_rec index by pls_integer

5. procedure: analyze_error_152_ll

6. procedure: get_ext_ids_152(
v_db_ssa_dtl in varr_ocv_rec,
ext_id_type in varchar2,
ext_ids out varr_ext_id_rec)

Now here is where the issue resides:Within the definition of analyze_error_152_ll (in body of my_package), I call get_ext_ids_152 using dbms_job.submit

procedure analyze_error_152_ll
is
vJob varchar2(400);
vJobNum binary_integer;
v_db_ssa_dtl2 varr_ocv_rec := varr_ocv_rec();
ext_ids varr_ext_id_rec;
ext_id_type varchar2(5) := '(1)';
/*OTHER DECLARATONS*/

[code]......

View 7 Replies View Related

ORA-31603 On Dbms_metadata.get_dll For Dblinks Only

Feb 22, 2012

I have user U1 with dblink DBL1 (private dblink, not public).

CODECREATE DATABASE LINK DBL1
CONNECT TO U1 IDENTIFIED BY XX USING 'TNS1';

I have user U2 with dblink DBL2 (private dblink, not public).

CODECREATE DATABASE LINK DBL2
CONNECT TO U2 IDENTIFIED BY XX USING 'TNS2';

Both dblinks works fine, it means I can do select.

When I logged in with U1 and try to execute the following statement :

CODESELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME)
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME   = 'DBL1';

I get ORA-31603 error:

CODEORA-31603: object "DBL1" of type DB_LINK not found in schema "U1"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

same happens when I pass user name to get_ddl:

CODESELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME, [b]USER[/b])
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME   = 'DBL1';

When I select from user_objects with the following query , it is there.

CODESELECT *
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME   = 'DBL1';

DBMS_METADATA.GET_DDL works for every other objects of U1 (tables, views, ... for example) except for dblinks. DBMS_METADATA.GET_DDL works for every objects of U2, includes DBL2.

I tried to add "select catalog role" to U1, even when I know that I need it only for objects from other users.

I tried to create DBL2 in U1 - same results.

I tried to re-create dblink using full connection string - with same result.

View 1 Replies View Related

SQL & PL/SQL :: DBMS_METADATA.GET_DDL Fails With ORA-31603 On Dblink

Feb 21, 2012

I have user U1 with dblink DBL1 (private dblink, not public).When I logged in with U1 and try to execute the following statement :

SELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME)
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME = 'DBL1';

I get ORA-31603 error:

ORA-31603: object "DBL1" of type DB_LINK not found in schema "U1"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

DBL1 script is as follows:

CREATE DATABASE LINK DBL1
CONNECT TO U1 IDENTIFIED BY XX USING 'TNS1';

when I select from user_objects with the following query , it is there.

SELECT *
FROM SYS.USER_OBJECTS
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OBJECT_NAME = 'DBL1';

DBMS_METADATA.GET_DDL works for every other objects (tables for example) in this schema except for dblinks.

View 10 Replies View Related

SQL & PL/SQL :: Extract DDL Of DBMS_JOB

Dec 20, 2011

How to extract DDL of DBMS_JOB in sqlplus ?

View 30 Replies View Related

SQL & PL/SQL :: DBMS_JOB Not Executing On Time?

Jul 30, 2012

I've defined DBMS_JOB in Oracle it is not starting on time. As per query it should start at 09:00 PM as given below.

SQL> SELECT TRUNC(SYSDATE) + 21/24 FROM DUAL;

TRUNC(SYSDATE)+21/24
--------------------
7/31/2012 9:00:00 PM

But instead it was started on 7/31/2012 1:14:10 AM. Which is wrong.

Following is script which I am using to submit this job.

DECLARE
X NUMBER;
BEGIN

[Code]....

View 6 Replies View Related

PL/SQL :: How To Run DBMS_JOB In Parallel And Serial

Jul 26, 2012

I have total 8 procedure to run in parallel . and after that my 9th procedure should run.

below is my job submission procedure

create or replace procedure DURATION_ALARM_WEEKLY as
l_job number ;
begin
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_CALL_OUT ; end;');
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_CALL_IN ; end;');
dbms_job.submit(l_job,'begin ALARMS_WEEKLY_DURATIN_OUT ; end;');
[code].......

what is the syntax I have to do in my FINALE procedure . using DBMS_ALERT.REGISTER , DBMS_ALERT.WAITANY .....?

View 9 Replies View Related

Starting A Function In Parallel Using DBMS_JOB

Jun 30, 2011

We have Data Migration for our application coded in PL/SQL. The DB server has 64 Cores available (Solaris 10 OS) however running the migration code written as a function, utilizes very little CPU and CPU utilization is to max 2%. To utilize CPU power available to increase the speed of migration, we are using DBMS_JOB to schedule this function multiple times.

However scheduling the function 10 times, we are seeing that at any moment only 4/5 oracle processes are active and utilizing the CPU and CPU utilization has gone up to 5-6%. The speed of migration is increased but not to a great extend which I feel would work if we could utilize more CPU.

I see a parameter job_queue_processes is set to 10 currently in the database and am planning to increase this (currently to 25 as I don't have exact count of how many other jobs may be running in the database).

View 1 Replies View Related

Server Administration :: DBMS_JOB Using Sys Account

Jun 17, 2010

I have to change the execution of a job, I have the sys access.The job is in another user, say scott.how to use dbms_change using sys account to change the execution of a job in Scott user..

View 9 Replies View Related

SQL & PL/SQL :: DBMS_JOB / Maximum Number Of Failures A Job Can Allow

Mar 9, 2010

What is the maximum number of failures a job can allow,when we are scheduling jobs using DBMS_JOB.

View 1 Replies View Related

PL/SQL :: Enable Dbms_job To Run On Every Day At 4am Excluding Fridays?

Jun 7, 2013

how to enable dbms_job to run on every day at 4am excluding Fridays.

View 7 Replies View Related

DBMS_JOB Running At Wrong Time

Jan 7, 2013

We have a bunch of jobs scheduled using DBMS_JOB (yes, I know I should be using DBMS_SCHEDULER, but we haven't migrated there yet). We are running Oracle 11.2.0.3 on Windows Server 2003 x64.

For example, we have a job that is supposed to run every Wednesday at 20:00. The Interval we have set up is "NEXT_ DAY (TRUNC(SYSDATE), 'WEDNESDAY')+20/24". This has been working as intended. Today (Monday), however, the job kicked off at 11:52. It was the wrong day and the wrong time.

I don't see anything weird in my alert log. Where else should I check to figure out why this job ran today?

JOB     LAST_DATE     LAST_SEC     NEXT_DATE     NEXT_SEC     INTERVAL     WHAT
293     1/7/2013 11:52:46 AM     11:52:46     1/9/2013 8:00:00 PM     20:00:00     NEXT_DAY(TRUNC(SYSDATE), 'WEDNESDAY')+20/24     ACQUISITIONS.WORKLOAD_STATUS_UPDATE_NOTIF;

View 5 Replies View Related

Forms :: Oracle Checkbox / Submit Button?

Aug 1, 2012

My requirement is I have placed a check Box in the Detailed Block and i have assigned values for the check box as

Checked - R
Unchecked - P
Initial Value - R

by default the check box would be displayed as Checked while inserting the values in to table the value of check box is not getting inserted but when the check box is unchecked the the value is getting inserted as P when the submit button is pressed.

View 11 Replies View Related

SQL & PL/SQL :: Multiple Submit Button In A Single Form

Jul 21, 2011

My current system have 1 submit button with single form. This submit button will call *file_content.upload*.

htp.p('function on_submit() {');
htp.p('...the rest of my code here..');
htp.p('document.forms[0].submit();');
htp.p('return true;');
htp.p('}');
htp.p('<form enctype="multipart/form-data" method="post" action="file_content.upload">');
htp.p('...the rest of my code here..');
htp.p('<input type="button" id="btn_Submit" value="Submit" onclick="on_submit()"></form>');

My question is, i want to enhance this by adding additional 1 submit button with value "Save". But this new "Save" button will call/trigger different file. This new button will call *file_content.update*. How to accomplish this and how to differentiate these 2 button?

htp.p('<input type="button" id="btn_Submit" value="Submit" onclick="on_submit()"> 
<input type="button" id="btn_Save" value="Save"></form>');

View 12 Replies View Related

Client Tools :: Submit Request Via SQL Developer?

Oct 3, 2012

I'm using the below code in SQL Developer, trying to submit a request:

Declare
vRequest_id NUMBER;
BEGIN
Fnd_Global.apps_initialize(9205,20639,200);

[code]...

However, when i check the fnd_concurent_request table, nothing is there.as I just used the following code in SQL Developer and it worked:

Declare
L_NUMBER number;
BEGIN
L_NUMBER := WF_ROLE_HIERARCHY.AddRelationship('UMX|XXR|GLFSA_RADG_BA','FND_RESP|SQLAP|XXR_GL_JNL_ADMIN|STANDARD');
END;

Is it due to the different types of packages im using? i.e. public/private? or is there an api i can use in SQL Developer to submit the request?

View 8 Replies View Related

Application Express :: Page To Submit Not Found

Nov 29, 2012

I am using Oracle Application Express 11g

I don't have a option for "Page item to submit" in Chart Region

View 5 Replies View Related

Application Express :: Default Submit Button

Jun 16, 2013

I am new in apex i create form with report when i create record it submit page to its report its ok but can i change page on condition base.

View 8 Replies View Related

Application Express :: How To Submit Page On Load

Oct 4, 2012

i m using apex 4.1, and want to submit page when page load. i tried dynamic action but it continuously refreshing page. is there any other javascript or anything else from which i submit page on load

dynamic action used, event on load and action submit page but it continuously submitting page.

View 5 Replies View Related

Application Express :: Radio Group With Submit?

Oct 4, 2013

I have an updateable report based on a collection.

One of the columns is a radio group and is the only updateable column

My code for the column is currently

apex_item.radiogroup(7,seq_id,c008)

I also have a button that fires an update process.

Ideally, I would like to get rid of the button and fire the update, after the user has changed the radio group.

So I changed my code to

apex_item.radiogroup(
7
, seq_id
, c008
, null
, null
, null
, 'doSubmit(''SUBMIT'')'
, null
, null
, null)

If I click on a different radio group nothing happens.

If I then click on another one, the update fires, but with the value of the previous radio.

Do I need to add something else to

'doSubmit(''SUBMIT'')'

View 2 Replies View Related

Server Administration :: Find Last Run Duration Of Job Which Was Scheduled Using DBMS_JOB

Jun 20, 2010

Is there any way to find the last run duration of a job which was scheduled using DBMS_JOB?

View 3 Replies View Related

Server Administration :: Advantages Of Using DBMS_schedular Ovet Dbms_job

Oct 11, 2012

we had one problem one of oracle job scheduled using dbms_job was inactive for so long time,As one of our developer noticed this and reached me as i checked the dba_job was running without an process

SQL> select sid,serial#,paddr from gv$session where osuser='oracle';

SID SERIAL# PADDR
---------- ---------- ----------------
626 753

i suggested a developer to drop job and reschedule it,now question was raised from them why process was not allocated to job.

View 6 Replies View Related

Server Administration :: DBMS_JOB Restarting / Database Shutdown

Aug 8, 2010

I was monitoring a database job to collect statistics, it was scheduled using DBMS_JOBS..I found that it was running during business hours so i got the session ID of the job using;

select sid from dba_jobs_running where job=11;

I then i killed the job using;

select serial# from v$session where sid =232;
alter system kill session '232, 10852';
select sid from dba_jobs_running where job=11;

no rows selected...After some time i again fired the same command

select sid from dba_jobs_running where job=11;

SID
----------
232

and found that the same job is again running..This behavior was repeated again N again. i have attached the spool file for the same...

what could be the reason that the job is starting all over again even after killing the session and what should be done to stop it..I understand that once the database shuts down and if the job is still running then it will restart once the database is up..In this case, Should i remove the job and re submit it again..

View 8 Replies View Related

DBMS_JOB - Pass Date As Parameter To Another Stored Procedure?

Mar 28, 2012

I have created a stored procedure that checks if a file exists and gets a date from the file if it exists. The date is then used as a paramter. See below:

CODEcreate or replace
PROCEDURE                     "P_Load_Stamp" AS
v_exists BOOLEAN;
v_length NUMBER;
v_blocksize NUMBER;

[code]...

The above codes works perfectly and I scheduled it using SQLPLUS as follows:

CODEvariable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;

[code]...

My problem is that I need to pass the date from the above procedure as a parameter to another stored procedure. So I modified my code as follows (the parts in red):

CODEcreate or replace
PROCEDURE                     "P_Load_Stamp" (vCTIDATE OUT varchar2) AS
v_exists BOOLEAN;
v_length NUMBER;

[code]...

Now it doesn't strike me as a rights issue since I created it in the schem schema. What could I be doing wrong here?

View 1 Replies View Related

Performance Tuning :: Can Limit Dbms_job To Use Defined Amount Of CPU%

Dec 14, 2010

I am creating 5 dbms_job at run time in side a stored procedure.But when I execute that procedure, all the job get 100%cpu and as a result other process does not get response. so my question is can we limit the dbms_job to use a defined amount of cpu%.

View 9 Replies View Related

SQL & PL/SQL :: How To Fetch Values From Other Tables In Application On-submit Process

May 4, 2012

In Oracle Apex 4.1,The Leave_transaction Table has the following Fields,

1.Leave_id
2.Emp_name
3.From_date
4.To_date
5.Remaining_days

The Emp_Master Table has the following columns,

1.Emp_id
2.Emp_Name
3.Remaining_days

Holiday_master table has the list of holiday dates as "From_Date"

I have the form based on the Leave_Transaction Table, and I have created the Process, as "On-submit-after computations and validations" and posted the following PLSQL code,

declare
days number(3);
ex_days emp_master.remaining_days%type;
new_rem_days emp_master.remaining_days%type;
begin

[code]....

If the Dates is between from_date and To_date comes in Saturday and sunday and/or if any Date is exist in the Hpliday_master table it will exclude and return the count(*) remaining dates, For example,

If the From_date is 04-may-2012' and To_date is 08-may-2012,

Here the dates 5th may and 6th may are "saturday" and "sunday"

and if any date between From_date and To_date is exist in Holiday_Master Table i.e say here it is 07-may-2012, Then the remaining dates are(excluding sat,sunday and dates in holiday_table),

04-may-2012,
08-may-2012.

so the count(*) is 2.

I am using the above code but still it returning 5,I think this

...where to_char(dt,'fmday') not in ('sunday','saturday') minus (select holiday_start from holiday_master))

code is not working.

View 39 Replies View Related

Forms :: SUBMIT Buttons Which Is Disabled / Gets Enabled Automatically

Apr 3, 2012

I have a scroll bar in my form. When i scroll the bar, one of SUBMIT buttons which is disabled gets enabled automatically. Do we have any triggers on scroll bar where i can disable the button again.

View 4 Replies View Related

Application Express :: Message Display Before Submit To Record

Nov 5, 2012

I have created two page process with two buttons "APPROVE" AND "CANCEL" button. first page process running approve invoice procedure on approve button and second page process running cancel invoice procedure on cancel button. i want to display massage before approve invoice and cancel invoice (DO you want to approve invoice) on approve button and (Do you want to cancel invoice on ) cancel button.

Working with apex4.1.

View 4 Replies View Related

Application Express :: Showing New Rows Added To Tabular Form After Submit?

Jan 30, 2013

How i can show the new added rows to tabular form after submit? what I want is to show my customer new added rows that recently added. but according to how I sort the tabular form, the new added rows distributed in multi pagination in tabular form. I'm not good in English, excuse me for my mistakes. I use apex 4.2.0 On Oracle 11g r2 on windows Server 2008

View 2 Replies View Related

Application Express :: How To Force APEX To Fire Submit After Processes Before On-Demand

Jul 2, 2013

We have an application that has "After Submit" and AJAX "On-Demand" processes.  We need the "After Submit" to fire and complete first, set an application item (by referencing the next value in a sequence), and then fire the "On-Demand" processes.  We have the sequence number set accordingly, with all of the "After Submit" processes given a lower (APEX) sequence number than the "On-Demand" processes; however, it seems the "After Submit" processes are still not firing and completing because only intermittently are the "On-Demand" processes able to access the application item's value--80% of the time this value is simply blank.  Making matters more complicated is that the application item is derived from an Oracle sequence, so in my "On-Demand" processes, I cannot simply re-run some of the same logic used in one of the "After Submit" processes that sets the sequence--the sequence may only be set once, and only by this "After Submit" process; we have no choice in that regard. Is there any way possible in APEX to 100% force processes to fire AND complete, one after another, in the order in which they are listed by their (APEX) sequence?  In other words, if we have this: 

(APEX) Seq Name & Process Type

1 process A ("After Submit")  
2 process B ("After Submit")--this sets application item :XYZ
3 process C ("On-Demand)--this uses application item :XYZ and MUST have this item value available to do any processing Can we instruct APEX to fire process A, complete it, fire process B, complete it, and only then, fire process C? 

View 2 Replies View Related







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