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!

Source does not have a runnable target.

Discussion in 'MySQL Forums' started by littlerose, Feb 10, 2011.

  1. littlerose

    littlerose Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi, I am writing an procedure to archive the data and the condition is like the data should be archived after 1 month. and date of creation is older than 1 month and i am trying to write like below code

    CREATE OR REPLACE PROCEDURE PROCEDURE1 IS

    BEGIN

    INSERT
    INTO XXDDE_MATERIALS_ARCHIVE
    SELECT *
    FROM XXDDE_MATERIALS
    WHERE XMT_ACTIVE = 'N'
    AND TO_CHAR(DATE_CREATED, 'mm') = add_months(sysdate, -1)

    EXCEPTION
    WHEN others THEN
    NULL;

    END PROCEDURE1;

    but itgives the error source does not have a runnable target

    could you please help me

    Regards
    Sri
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    Your procedure needs to be fixed as your WHERE clause won't match anything and you are missing a necessary semicolon:


    Code (SQL):
    CREATE OR REPLACE PROCEDURE PROCEDURE1 IS

    BEGIN

    INSERT
    INTO XXDDE_MATERIALS_ARCHIVE
    SELECT *
    FROM XXDDE_MATERIALS
    WHERE XMT_ACTIVE = 'N'
    AND trunc(DATE_CREATED) = trunc(add_months(sysdate, -1));

    EXCEPTION
    WHEN others THEN
             dbms_output.put_line('Displaying the error stack:');
             dbms_output.put(dbms_utility.format_error_stack);
             dbms_output.put_line(dbms_utility.format_error_backtrace);

    END PROCEDURE1;
    /
    That should perform the insert you want and also reveal any errors you might encounter so you can address them.
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    HI littlerose

    Is your database MySQL? This seems to be in the wrong category here.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    If it is not working in SQL Deveploper, try the same in SQL*Plus and see if it is lucky there :)
     
  5. littlerose

    littlerose Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    thanks for the reply. I have changed the procedure and iam able to get the values but when i try to insert it is not happening and also doesnt give any error msg Below is the sql i have tried

    create or replace PROCEDURE ARCHIVE IS

    BEGIN

    INSERT
    INTO XXDDE_MATERIALS_ARCHIVE
    SELECT *
    FROM XXDDE_MATERIALS
    WHERE XMT_ACTIVE = 'N'
    AND TO_CHAR(to_date(DATE_CREATED,'dd-mm-yy'), 'mm') = to_char(add_months(sysdate,-1),'mm');
    EXCEPTION
    WHEN others THEN
    NULL;

    END ARCHIVE;
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Is it Oracel /My SQL question ?

    Have you tried in SQL Plus ?

    Whast is the data type of XXDDE_MATERIALS.DATE_CREATED ?
     
  7. littlerose

    littlerose Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    It is MySQL and i hve tried in SQL * plus too... the data type of XXDDE_Material.Date_created is Number
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why is a column named DATE_CREATED typed as a NUMBER?
     
  9. littlerose

    littlerose Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I was able to insert the data...and now i have a requirement like this procedure should be executed every month on a particular day... how can i perform this task.. could anyone help me out in this regard

    Thanks
    Sri
     
  10. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    You can schedule the procedure via cron if you are on Linux or via scheduled tasks on windows. Newer versions of MySQL have a feature called events, which i believe allows one to schedule inside mysql but I am not sure about that.
     
  11. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Be ready to take following issue in case Date_created is number. Date should be always date field.

    Code (SQL):

    SQL> DESC MDATE;
     Name                                                                                                                                                     NULL?    TYPE
     ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     NDATE                                                                                                                                                     NUMBER

    SQL> SELECT * FROM MDATE;

         NDATE
    ----------
       1102010
       2042011
      14042009
      18082007
       5051980

    SQL> SELECT NDATE, TO_DATE(NDATE,'ddmmyyyy') D FROM MDATE;
    ERROR:
    ORA-01843: NOT a valid MONTH



    no ROWS selected

    SQL> -- Actually 18AUG2007
    SQL> SELECT NDATE, TO_DATE(NDATE,'ddmmyyyy') D  FROM MDATE WHERE NDATE = 18082007;

         NDATE D
    ---------- ---------
      18082007 18-AUG-07

    SQL> -- Actually 18AUG2007
    SQL> SELECT NDATE, TO_DATE(NDATE,'dd-mm-yyyy') D  FROM MDATE WHERE NDATE = 18082007;

         NDATE D
    ---------- ---------
      18082007 18-AUG-07

    SQL> -- Actually 02APR2011
    SQL> SELECT NDATE, TO_DATE(NDATE,'ddmmyyyy') D  FROM MDATE WHERE NDATE = 2042011;
    SELECT NDATE, TO_DATE(NDATE,'ddmmyyyy') D  FROM MDATE WHERE NDATE = 2042011
                          *
    ERROR at line 1:
    ORA-01843: NOT a valid MONTH


    SQL> -- Actually 02APR2011
    SQL> SELECT NDATE, TO_DATE(NDATE,'dd-mm-yyyy') D  FROM MDATE WHERE NDATE = 2042011;
    SELECT NDATE, TO_DATE(NDATE,'dd-mm-yyyy') D  FROM MDATE WHERE NDATE = 2042011
                          *
    ERROR at line 1:
    ORA-01861: literal does NOT MATCH format string


    SQL>

     
     
  12. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Btw , I hope MySQL follows same conversion logic as oracle. I am not an expert in MySQL. Not even a beginner :(
     
  13. kavin60

    kavin60 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    its really nice...:)