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!

Need help with this error

Discussion in 'SQL PL/SQL' started by nicky, Feb 18, 2011.

  1. nicky

    nicky Active Member

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

    I need help in compiling this query. An error message is displayed while compiling this procedure.

    "PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

    begin case declare end exception exit for goto if loop mod"

    how do i get rid of this error ? what do I add or modify in this below given procedure to achieve successful compilation ? Pl help !!!!

    CREATE OR REPLACE PROCEDURE REPORTS.shr_get_udr_table_nil_usage (
    v_start_date date,
    v_end_date date
    )
    IS
    v_date_start_date varchar(20);
    v_date_end_date varchar(20);
    BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE shr_nil_table';

    v_date_start_date := TO_DATE (v_start_date, 'YYYYMMDD');
    v_date_end_date := TO_DATE (v_end_date, 'YYYYMMDD');


    WHILE v_date_start_date <= v_date_end_date
    LOOP
    INSERT INTO shr_nil_table
    SELECT 'sysadm.' || part_name || '@mumcallnew'
    FROM shr_lookup_table a
    WHERE a.flag = 'POST'
    AND v_date_start_date BETWEEN from_dt AND to_dt;

    COMMIT;
    v_date_start_date := v_date_start_date + 1;
    END LOOP;

    COMMIT;
    /
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    hmhm..

    You missed END; for teh main Begin Section . Add END; as follows.

    Code (SQL):

    CREATE OR REPLACE PROCEDURE REPORTS.shr_get_udr_table_nil_usage
    (
    v_start_date DATE,
    v_end_date DATE
    )
    IS
    v_date_start_date VARCHAR(20);
    v_date_end_date VARCHAR(20);
    BEGIN

     EXECUTE IMMEDIATE 'TRUNCATE TABLE shr_nil_table';

     v_date_start_date := TO_DATE (v_start_date, 'YYYYMMDD');
     v_date_end_date := TO_DATE (v_end_date, 'YYYYMMDD');


     WHILE v_date_start_date <= v_date_end_date
     LOOP
           INSERT INTO shr_nil_table
           SELECT 'sysadm.' || part_name || '@mumcallnew'
           FROM shr_lookup_table a
           WHERE a.flag = 'POST'
           AND v_date_start_date BETWEEN from_dt AND to_dt;
           COMMIT;
           v_date_start_date := v_date_start_date + 1;
     END LOOP;

     COMMIT;
    END;
    /
     
     
  3. nicky

    nicky Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    now giving this error :-

    PL/SQL: ORA-02185: a token other than WORK follows COMMIT
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Its working for me

    Code (SQL):

    SQL> CREATE OR REPLACE PROCEDURE shr_get_udr_table_nil_usage
    (
      2    3  v_start_date DATE,
      4  v_end_date DATE
      5  )
      6  IS
      7  v_date_start_date VARCHAR(20);
    v_date_end_date VARCHAR(20);
      8    9  BEGIN
     10
     11   EXECUTE IMMEDIATE 'TRUNCATE TABLE shr_nil_table';
     12
     13   v_date_start_date := TO_DATE (v_start_date, 'YYYYMMDD');
     14   v_date_end_date := TO_DATE (v_end_date, 'YYYYMMDD');
     15
     16
     17   WHILE v_date_start_date <= v_date_end_date
     18   LOOP
     19         INSERT INTO shr_nil_table
           SELECT 'sysadm.' || part_name || '@mumcallnew'
           FROM shr_lookup_table a
     20   21   22         WHERE a.flag = 'POST'
     23         AND v_date_start_date BETWEEN from_dt AND to_dt;
     24         COMMIT;
     25         v_date_start_date := v_date_start_date + 1;
     26   END LOOP;
     27
     28   COMMIT;
     29  END;
     30  /

    PROCEDURE created.

    SQL>
    SQL> DROP PROCEDURE shr_get_udr_table_nil_usage ;

    PROCEDURE dropped.

    SQL> DROP TABLE shr_nil_table;

    TABLE dropped.

    SQL> DROP TABLE shr_lookup_table;

    TABLE dropped.

    SQL>

     
     
  5. nicky

    nicky Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Ok Thx its working