Programming DBA Jobs in Stored procs

I am a little new to programming, so any help is appreciated.

Find below the code of my stored proc to delete a table and also create a DBA job which will run on a hourly basis.

CREATE OR REPLACE procedure DELETE_My_TABLE(myschema varchar2) as 
BEGIN
    BEGIN
        execute immediate 'delete from '||myschema||'.mytable where clause;';
    END;
        BEGIN
            DBMS_SCHEDULER.create_program (
            program_name        => 'DELETE_My_TABLE',
            program_type        => 'STORED_PROCEDURE',
            program_action      => 'execute DELETE_My_TABLE(myschema)',
            number_of_arguments => 1,
            enabled             => FALSE,
            comments            => 'Program to delete table using a stored procedure.');
        DBMS_SCHEDULER.define_program_argument (
        program_name      => 'DELETE_My_TABLE',
        argument_name     => 'myschema',
        argument_position => 1,
        argument_type     => 'VARCHAR2',
        default_value     => 'myschema');

        DBMS_SCHEDULER.enable (name => 'DELETE_My_TABLE');
    END;

    BEGIN
        DBMS_SCHEDULER.create_schedule (
        schedule_name   => 'DELETE_My_TABLE',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'freq=hourly; byminute=0',
        end_date        => NULL,
        comments        => 'Hourly Job to purge SEARCH_TEMP_TABLE');
    END;
END;
/

Issues:

ERROR at line 1:
ORA-0092开发者_如何学运维0: invalid relational operator
ORA-06512: at "MYSCHEMA.DELETE_My_TABLE", line 4
ORA-06512: at line 1

Will the logic (and syntax) work?

One issue I can see is that you need to take the semi-colon out of the EXECUTE IMMEDIATE string:

execute immediate 'delete from '||myschema||'.mytable where clause';
                                                                 ^^
                                                         Removed from here

thought I suspect this won't solve your immediate problem, which looks like it's your BEGIN ...END blocks.

For the Oracle Scheduler you normally create a program, once. Next you create a job that has the program as action. You can give that job a schedule like you specified in your code but you have to choose. Either you create a schedule and have the job use it, or you give the job it's own repeat interval.

I happen to know about a book ( Mastering Oracle Scheduler ) that I wrote that could be very helpful.