#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    7
    Rep Power
    0

    PLS-00103: Encountered the symbol "SELECT"


    I am trying to compile a procedure but it gives the following error

    Errors for PROCEDURE CPL: LINE/COL ERROR -------- ----------------------------------------------------------------- 17/18 PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + mod not null others <an identifier> avg count current exists max min prior sql stddev sum variance execute forall time timestamp interval date The code for the procedure is mentioned below... however when i run the same query in SQL plus enviourment everything runs fine....

    oracle8 Code:
     
    CREATE OR REPLACE PROCEDURE cpl (
    start_date IN DATE DEFAULT SYSDATE,
    end_date IN DATE DEFAULT SYSDATE
    )
    IS
    start_month NUMBER;
    end_month NUMBER;
    BEGIN
    start_month := TO_NUMBER (TO_CHAR (start_date, 'yyyymm'));
    end_month := TO_NUMBER (TO_CHAR (end_date, 'yyyymm'));
     
    FOR booking_period_counter IN start_month .. end_month
    LOOP
    INSERT INTO tmp_rep12
    (account_no, no_of_cons, package_plan, vpn_plan, ff_plan,
    mkt_code, booking_period)
    SELECT k.account_no, k.no_of_cons,
    (SELECT MAX (c.package_id)
    FROM cmf_packages c
    WHERE c.package_id IN
    (SELECT int_value
    FROM system_parameters
    WHERE module = 'REP'
    AND parameter_name LIKE 'REP12_PKG_PLAN%')
    AND c.account_no = k.account_no
    AND TO_NUMBER (TO_CHAR (c.active_dt, 'yyyymm')) <=
    TO_NUMBER (
    TO_CHAR (
    TO_DATE (end_date, 'dd-mm-yyyy'),
    'yyyymm'
    )
    )
    AND ( c.inactive_dt IS NULL
    OR TO_NUMBER (TO_CHAR (c.inactive_dt, 'yyyymm')) >=
    TO_NUMBER (
    TO_CHAR (
    TO_DATE (start_date, 'dd-mm-yyyy'),
    'yyyymm'
    )
    )
    ))
    package_plan,
    (SELECT MAX (c.package_id)
    FROM cmf_packages c
    WHERE c.package_id IN
    (SELECT int_value
    FROM system_parameters
    WHERE module = 'REP'
    AND parameter_name LIKE 'REP12_VPN_PKG_ID%')
    AND c.account_no = k.account_no
    AND TO_NUMBER (TO_CHAR (c.active_dt, 'yyyymm')) <=
    TO_NUMBER (
    TO_CHAR (
    TO_DATE (end_date, 'dd-mm-yyyy'),
    'yyyymm'
    )
    )
    AND ( c.inactive_dt IS NULL
    OR TO_NUMBER (TO_CHAR (c.inactive_dt, 'yyyymm')) >=
    TO_NUMBER (
    TO_CHAR (
    TO_DATE (start_date, 'dd-mm-yyyy'),
    'yyyymm'
    )
    )
    ))
    vpn_plan,
    (SELECT MAX (c.package_id)
    FROM cmf_packages c
    WHERE c.package_id IN
    (SELECT int_value
    FROM system_parameters
    WHERE module = 'REP'
    AND parameter_name LIKE
    'REP12_FAMILY_FRIEND_PKG_ID%')
    AND c.account_no = k.account_no
    AND TO_NUMBER (TO_CHAR (c.active_dt, 'yyyymm')) <=
    TO_NUMBER (
    TO_CHAR (
    TO_DATE (end_date, 'dd-mm-yyyy'),
    'yyyymm'
    )
    )
    AND ( c.inactive_dt IS NULL
    OR TO_NUMBER (TO_CHAR (c.inactive_dt, 'yyyymm')) >=
    TO_NUMBER (
    TO_CHAR (
    TO_DATE (start_date, 'dd-mm-yyyy'),
    'yyyymm'
    )
    )
    ))
    ff_plan,
    NULL, booking_period_counter
    FROM (SELECT cmf.account_no,
    DECODE (
    NVL (COUNT (subscr_no), 1),
    0, 1,
    NVL (COUNT (subscr_no), 1)
    ) no_of_cons
    FROM emf, cmf
    WHERE ( service_end IS NULL
    OR (TRUNC (service_end, 'MM') >
    LAST_DAY (
    TRUNC (
    ADD_MONTHS (
    TO_DATE (end_date, 'dd-mm-yyyy'),
    -1
    )
    )
    )
    )
    )
    AND TRUNC (service_start, 'MM') <=
    TRUNC (TO_DATE (start_date, 'dd-mm-yyyy'), 'MM')
    AND cmf.account_no = emf.account_no
    AND cmf.account_no > 0
    ---AND mkt_code = 1 Commented by Manu as report required by BSNL for all market codes
    AND cmf.account_category NOT IN
    (SELECT NVL (int_value, -9999999)
    FROM system_parameters
    WHERE module = 'REP'
    AND parameter_name IN
    ('EXCLUDE_ACCT_CATEGORY',
    'EXCLUDE_ACCT_CATEGORY_PREPAID'
    ))
    AND cmf.account_no > 0
    GROUP BY cmf.account_no) k;
    END LOOP;
    END;
    Last edited by pabloj; July 19th, 2006 at 09:36 AM. Reason: Syntax highlighting added (I think it's better that code tags, but I'm open to suggestions
  2. #2
  3. No Profile Picture
    ......@.........
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2004
    Posts
    1,345
    Rep Power
    56
    code tags would make this readable. You have a syntax error:
    Code:
    insert into table (fld1, fld2...) VALUES ( ........ );

IMN logo majestic logo threadwatch logo seochat tools logo