Solved

Execute immediate within a  DBMS_JOB

Posted on 2008-10-24
13
4,923 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example, show how to take different types of Oracle backups using RMAN.
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.

710 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