2 Replies Latest reply: Jan 14, 2014 1:35 PM by 0dafeelin RSS

execute immediate 'execute dbms_scheduler.disable

0dafeelin Newbie
Currently Being Moderated

1.   I am getting oracle error ORA-00900 ..not sure of the syntax for running executing the dbms_scheduler procedures inside pl/sql

2.   To troubleshoot syntax, am I able to use DBMS_OUTPUT to see what the execute immediate will see?

 

BEGIN

  FOR cur_rec IN (SELECT owner,

                        job_name

                FROM

                        dba_scheduler_jobs

                where

                        owner='xxxxxxx' and state='SCHEDULED'

                  )

  LOOP

    BEGIN

        -- DBMS_OUTPUT.put_line('exec dbms_scheduler.disable('''||cur_rec.owner||'.'||cur_rec.job_name||''')');

        EXECUTE IMMEDIATE 'execute dbms_scheduler.disable('''||cur_rec.owner||'.'||cur_rec.job_name||''')';

    END;

  END LOOP;

END;

/

 

BEGIN

*

ERROR at line 1:

ORA-00900: invalid SQL statement

ORA-06512: at line 12

  • 1. Re: execute immediate 'execute dbms_scheduler.disable
    spajdy Pro
    Currently Being Moderated

    Why you are using dynamic SQL ?

    You can simply use static

    BEGIN
      FOR cur_rec IN (SELECT owner,
                            job_name
                    FROM
                            dba_scheduler_jobs
                    where
                            owner='xxxxxxx' and state='SCHEDULED'
                      )
      LOOP
        BEGIN
            -- DBMS_OUTPUT.put_line('exec dbms_scheduler.disable('''||cur_rec.owner||'.'||cur_rec.job_name||''')');
            dbms_scheduler.disable(cur_rec.owner||'.'||cur_rec.job_name);
        END;
      END LOOP;
    END;
    /

     

    Execute is not PL/SQL command. When you want to run PL/SQL procedure in dynamic SQL use:

    execute immediate 'begin <procedure>; end; '
  • 2. Re: execute immediate 'execute dbms_scheduler.disable
    0dafeelin Newbie
    Currently Being Moderated

    Outstanding... my apologies for such a simple problem; however I am new to the Oracle DBA brotherhood, but what I lack in knowledge I try to make up in diligence.

     

    Sincere Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points