1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

running pl/sql code as package / script / job

Discussion in 'SQL PL/SQL' started by ecivgamer, Apr 29, 2011.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,

    I've got some code

    Now I need to save it as package / script / job in order to run it using timer.
    I'm new to server's side, I worked with Oracle at client's side before, so please give me advise.
    Actually my need is to find the best desicion and explain "why".
    Thanks ahead.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I do not understand the 'logic' of running this same statement over and over and over on a schedule. What purpose does this serve?

    As you already know (by the title of your post) you'll need to write this as a procedure to get it scheduled in Oracle as a job. Until you provide more information on why this same code needs to be repeatedly executed you'll need to wait for further assistance.
     
  3. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi zargon, this statement is brief example, 'cause my real statement is quite huge to post here. How do I write this as a procedure?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    create or replace procedure why_do_this
    as
    begin
    for i in ( SELECT 2534 as childid, 78 as sharepart FROM dual
    union all
    SELECT 1212 as childid, 56 as sharepart FROM dual
    union all
    SELECT 3434 as childid, 99 as sharepart FROM dual )
    loop
    insert into tabletest ( childid, sharepart ) values ( i.childid, i.sharepart );
    end loop;
    end;
    /
     
    ecivgamer likes this.
  5. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Thanks zargon!
    Now tell me please, how do I call my procedure from sql script.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Such topics are covered in abundance on the web and google.com can find numerous examples of how to do this. That being said:

    exec <procedure name>

    usually works, remembering to also pass required parameters.
     
    ecivgamer likes this.
  7. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    I understand it's too simple question for this community, but could you please help me, I'm newbie... :confused:
    My need is to add "delete tabletest" before inserting data. How do I change this code?

    Thanks ahead!
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The change is very simple --

    Code (SQL):
    CREATE OR REPLACE PROCEDURE why_do_this
    AS
    BEGIN
    DELETE FROM tabletest;
    FOR i IN ( SELECT 2534 AS childid, 78 AS sharepart FROM dual
    UNION ALL
    SELECT 1212 AS childid, 56 AS sharepart FROM dual
    UNION ALL
    SELECT 3434 AS childid, 99 AS sharepart FROM dual )
    loop
    INSERT INTO tabletest ( childid, sharepart ) VALUES ( i.childid, i.sharepart );
    END loop;
    END;
    /
     
     
    ecivgamer likes this.