9 Replies Latest reply on Dec 1, 2010 10:22 AM by 727876

    Execute sql script in oracle server

    OrakelNewbie
      hi how can i run an SQL script in oracle server in every first day of the month? i dont want to run that script manually every first day of the month.
        • 1. Re: Execute sql script in oracle server
          Tubby
          OrakelNewbie wrote:
          hi how can i run an SQL script in oracle server in every first day of the month? i dont want to run that script manually every first day of the month.
          http://tahiti.oracle.com/

          Find the appropriate version and then search for DBMS_SCHEDULER, or DBMS_JOB if you're rockin' a sufficiently ancient version of Oracle.
          • 2. Re: Execute sql script in oracle server
            OrakelNewbie
            :( sorry im new to oracle DB.
            • 3. Re: Execute sql script in oracle server
              Sayan Malakshinov.
              Very clearely wrote on psoug:
              http://psoug.org/reference/dbms_job.html
              http://psoug.org/reference/dbms_scheduler.html

              Regards,
              Sayan M.
              • 4. Re: Execute sql script in oracle server
                OrakelNewbie
                Im came from ancient world.

                Edited by: OrakelNewbie on Nov 30, 2010 9:50 PM
                • 5. Re: Execute sql script in oracle server
                  Sayan Malakshinov.
                  For example:
                  create table job_log(dt date);
                  /
                  declare
                     job_id number;
                  begin
                     dbms_job.submit(
                        job_id,
                        'begin insert into job_log values(sysdate); end;', 
                        sysdate,
                        'add_months(sysdate,1)'
                     );
                     dbms_output.put_line(job_id);
                  end;
                  Regards,
                  Sayan M.
                  1 person found this helpful
                  • 6. Re: Execute sql script in oracle server
                    727876
                    What OS and version are you running?

                    You can schedule this from the OS side as well if you don't want to use the scheduler.

                    In linux you can make use of the crontab:
                    crontab -e
                    
                    #then paste the following lines with the path to your scripts
                    
                    #.---------------- minute (0 - 59)
                    #|   .------------- hour (0 - 23)
                    #|   |   .---------- day of month (1 - 31)
                    #|   |   |   .------- month (1 - 12) OR jan,feb,mar,apr ...
                    #|   |   |   |  .----- day of week (0 - 7) (Sunday=0 or 7)  OR sun,mon,tue,wed,thu,fri,sat
                    #|   |   |   |  |
                    #*   *   *   *  *  command to be executed
                    
                    00 01 1 * * /<path to your scripts>/script_name.sh
                    The above script will run on the 1st day of every month at 01h00.

                    your script (the one you are calling in the cron) will have to call the sql script and should look similar to this:
                    #export all your environment variables here
                    # for example
                    export ORACLE_BASE=/u01/app/oracle
                    export ORACLE_HOME=$ORACLE_BASE/product/<your path>
                    export ORACLE_SID=ORCL
                    export PATH=$ORACLE_HOME/bin:$PATH
                    
                    sqlplus user/password@DB @/path/your_sql_script.sql
                    Hope this helps
                    • 7. Re: Execute sql script in oracle server
                      Please do not post info to run stupid scripts using stupid crontab.

                      Dbms_scheduler is much, much, much, much more powerful than crontab.

                      -----------
                      Sybrand Bakker
                      Senior Oracle DBA
                      • 8. Re: Execute sql script in oracle server
                        OrakelNewbie
                        Thanks for the reply. im using windows 2003 server and oracle 10g release 2 DB.

                        this is the sql script that i want to execute on the server:

                        SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') FROM DUAL;

                        REVOKE SELECT,ALTER ON SAMPLE_SEQ FROM PUBLIC;

                        DROP PUBLIC SYNONYM SAMPLE_SEQ;

                        DROP SEQUENCE SAMPLE_SEQ;

                        CREATE SEQUENCE SAMPLE_SEQ
                        INCREMENT BY 1
                        START WITH 1
                        MAXVALUE 99999
                        MINVALUE 0
                        NOCYCLE
                        NOCACHE;

                        CREATE PUBLIC SYNONYM SAMPLE_SEQ FOR TEST.SAMPLE_SEQ

                        GRANT SELECT,ALTER ON SAMPLE_SEQ TO PUBLIC;

                        COMMIT;

                        SPOOL OFF

                        EXIT;

                        Edited by: OrakelNewbie on Nov 30, 2010 11:23 PM
                        • 9. Re: Execute sql script in oracle server
                          727876
                          Wow Sybrand, you must always attack other peoples posts to make a point!

                          You will see that I have said in my post "if you don't want to use the scheduler" and not "you must use the crontab".
                          I am merely showing that there are more than one possibility to achieve his goal and I haven't said anywhere that the crontab is better, although I feel it has it's place when you want to use certain bash scripts.

                          This is a forum to learn and why not learn of all the possibilities, the more options you have the better solution you can choose at the end for your situation.