Solved

Execute immediate within a  DBMS_JOB

Posted on 2008-10-24
13
4,790 Views
Last Modified: 2013-12-19
Can I do a dynamic SQL WITHIN A DBMS_JOB.  Here is how my job looks like.
------------------------------------------------------------------------------------------------------------
DECLARE
  X NUMBER;
  viewDDL VARCHAR2(30000);
BEGIN
  viewDDL := myPkg.getViewDDL;
  SYS.DBMS_JOB.SUBMIT
    ( job       => X
     ,what      => viewDDL
     ,next_date => to_date('24/10/2008 10:50:38','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'SYSDATE+1/1440 '
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
--------------------------------------------------------------------------------------------------------------------

The viewDDL is dynamic and can change from time to time. That's why it is in a package and myPkg.getViewDDL returns the CREATE OR REPLACE VIEW statement.
Any ideas why this is not working?
0
Comment
Question by:subirc
  • 8
  • 5
13 Comments
 
LVL 9

Expert Comment

by:MarkusId
ID: 22798523
Hi!

Does this procedure inserting in the dbms_job not work or the job itself? What is the output of the myPkg.getViewDDL look like?
0
 

Author Comment

by:subirc
ID: 22799932
The output is  a 'CREATE OR REPLACE VIEW  .......
.....
....
; '
 ending in a semicolon.

When I see the job script in script view in TOAD, I see   SYS.DBMS_JOB.BROKEN, which means the job is not running.
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22799988
You might need an 'execute immediate' around it, as you can't execute DDL-SQL as what in DBMS_JOB, only PL/SQL

like::

BEGIN execute immediate('''' || myPkg.getViewDDL || ''''); end;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:subirc
ID: 22800384
MarkusId,
Are those 4 single quotes or 2 double quotes?  because If i run the statement in SQL window, it's giving an error.

I tried to run      BEGIN execute immediate(AIM_RPT.getCargoInvReportDDL); end;  
in a SQL window because that's the string I guess the  DBMS_JOB will run eventually.
The error I got was:
ORA-00911: invalid character
ORA-06512: at line 1
0
 

Author Comment

by:subirc
ID: 22800682
This is what I have and it still says broken ...........

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X
     ,what      => 'BEGIN  EXECUTE IMMEDIATE (''''||myPkg.getViewDDL ||''''); END;'
     ,next_date => to_date('01/01/4000 00:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'SYSDATE+1/1440 '
     ,no_parse  => TRUE
    );
  SYS.DBMS_JOB.BROKEN
   (job    => X,
    broken => TRUE);
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;
0
 

Author Comment

by:subirc
ID: 22800821
This works !!  in a SQL window
BEGIN  EXECUTE IMMEDIATE (myPkg.getViewDDL); END;

why will it not work in the DBMS_JOB  shown above?

Pls help.....
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22801955
If you have

SYS.DBMS_JOB.BROKEN
   (job    => X,
    broken => TRUE);

that means, that the job is broken. You've got to set broken to 'FALSE' in this case.

Does it work if you say dbms_job.run(jobnumber) afterwards?
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22806702
Is the myPkg.getViewDDL depending on the time it is called? Because in the first code snippet you had the string given back by the package in the what, in the

Quote:
'This works !!  in a SQL window
BEGIN  EXECUTE IMMEDIATE (myPkg.getViewDDL); END;

why will it not work in the DBMS_JOB  shown above?'

is the statement only fetched from the statement when the 'execute immediate' is called.

In the first case you might need the single quotes, in the latter it may be sufficient to only have the call to the package within the brackets of the execute immediate statement.

What does the "what"-string look like?
0
 

Author Comment

by:subirc
ID: 22807017
Yes, I tried that and it dodn't work. After 16 failed attempts it goes back to broken status
0
 

Author Comment

by:subirc
ID: 22807326
what  in the DBMS_JOB looks like this:
,what      => 'BEGIN  EXECUTE IMMEDIATE aim_rpt.getInvReportDDL; END;'

I also tried as you suggested:
,what      => 'BEGIN execute immediate('''' || myPkg.getViewDDL || ''''); end;'
Looks like when you do the above you end up running  
BEGIN execute immediate('' || myPkg.getViewDDL || ''); end;  Couple of single quotes cancel each other on each side.

However, none worked.  
Yes, I repeat,  The following works if I run in a sql window.
BEGIN execute immediate  myPkg.getViewDDL; end;

MyPkg.getViewDDL will change from time to time in the sense there are columns in this view that are dependant on data in another table. If that table has 3 rows the view will have 3 columns, everytime a data is added/updated/deleted  to that table the view needs to be refreshed. But data in that table maybe added/deleted/updated let's say once in 3 months;  definitely not every minute like I have every minute in the DBMS_JOB.  Initially, I tried to do this as a trigger on the table, but I ran into permissions issue and hence I switched to a DBMS JOB option. It seems I don't have permissions to do a execute immediate from a trigger. If you have a better solution, let me know, all I want is to refresh the view everytime there is a CRUD operation on another table.
0
 

Author Comment

by:subirc
ID: 22807337
Errata:
 In the second line above,  I meant
,what      => 'BEGIN  EXECUTE IMMEDIATE aim_rpt.getInvReportDDL; END;'
0
 
LVL 9

Accepted Solution

by:
MarkusId earned 65 total points
ID: 22810687
Hi!

This seems to be a rights restrictions problem. In dbms_job, only rights directly granted to the user who created the jobs are available, but not jobs granted through roles (you might find an error message regarding this in the database's alert.log - file).

However, according to

  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:24065646637395

you can avoid the rights issues with the command

execute immediate 'set role ALL';

before the create view-command.

So the following should work:
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X
     ,what      => 'BEGIN execute immediate ''set role ALL''; EXECUTE IMMEDIATE myPkg.getViewDDL; END;'
     ,next_date => to_date('27/10/2008 00:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'SYSDATE+1/1440 '
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
 
commit;

Open in new window

0
 

Author Comment

by:subirc
ID: 22813648
And it did work. Thank you very much.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question