Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

dbms_scheduler in Oracle

Posted on 2014-08-26
2
Medium Priority
?
523 Views
Last Modified: 2014-09-23
Could someone provide me a sample code for using dbms_scheduler to run a stored procedure which exporting XML file?
 
Thanks.
0
Comment
Question by:mrong
2 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40286974
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40287066
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 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