dbms_scheduler in Oracle

Could someone provide me a sample code for using dbms_scheduler to run a stored procedure which exporting XML file?
 
Thanks.
mrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
The procedure to generate the XML is up to you.

As far as scheduling the procedure, I posted this in your other question:

There are TONS of examples out there.

I would start with the online docs for dbms_scheduler.create_job:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72235

An example is in another doc:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN12384
0
sdstuberCommented:
example procedure is essentially the same anonymous block you saw in a previous question except converted into a standalone procedure

CREATE OR REPLACE PROCEDURE ftp_xml
IS
    v_conn      sdsftp.connection;
    v_clob      CLOB;

    v_host      VARCHAR2(100) := 'yourhost.com';
    v_user      VARCHAR2(100) := 'yourusername';
    v_password  VARCHAR2(100) := 'yourpassword';
    v_file_name VARCHAR2(100) := 'tables.txt';
BEGIN
    v_clob := DBMS_XMLGEN.getxml('select * from user_tables');

    v_conn := sdsftp.open(v_host, v_user, v_password);
    sdsftp.put_clob(v_conn, v_clob, v_file_name);
    sdsftp.close(v_conn);

    IF DBMS_LOB.ISOPEN(v_clob) = 1
    THEN
        DBMS_LOB.close(v_clob);
    END IF;

    DBMS_LOB.freetemporary(v_clob);
EXCEPTION
    WHEN OTHERS
    THEN
        sdsftp.close(v_conn);

        IF DBMS_LOB.ISOPEN(v_clob) = 1
        THEN
            DBMS_LOB.close(v_clob);
        END IF;

        DBMS_LOB.freetemporary(v_clob);

        RAISE;
END;

Open in new window



This is a fairly complex schedule just to show you a few of the options you can get with dbms_scheduler
If you can be more specific in your schedule I can give you a more specific example


BEGIN
    sys.DBMS_SCHEDULER.create_job(
        job_name          => 'MY_WEEKDAY_JOB',
        start_date        => SYSTIMESTAMP,
        repeat_interval   => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8,10,12,2,4; BYMINUTE=15;BYSECOND=0',
        end_date          => NULL,
        job_type          => 'STORED_PROCEDURE',
        job_action        => 'FTP_XML',
        comments          => 'Run FTP_XML every other hour at 15 minutes past the hour during business hours'
    );
END;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.