Solved

Execute immediate within a  DBMS_JOB

Posted on 2008-10-24
13
4,519 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
 

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
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.

 
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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CREATE TABLE syntax 4 44
Pl/SQL Query 31 61
JDeveloper 12c for 32 bit 4 34
PL/SQL Search for multiple strings 5 20
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

708 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now