Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    93

    Unanswered: creating a procedure --QUERY

    I am trying to create a procedure for the following, to get all the accounts that are set (,inactive, lower and upper) and setting forward to the attributes (where attribute11 is the card renewal date)
    as below.
    I am getting a few errors :can someone correct my syntax


    CREATE OR REPLACE PROCEDURE CARD_RENEWAL_DATA AS

    DECLARE
    CURSOR cur_BLANK
    IS
    SELECT ca.cin, p.attribute2
    from ar.hz_cust_accounts ca, hz_parties p
    where ca.party_id =p.party_id
    and p.attribute9 is null
    and p.attribute11 is null


    begin

    for i in cur_blank

    loop

    if i.attribute2 ='Inactive'
    then
    update ar.hz_cust_accounts
    set attribute9=sysdate
    attribute11=sysdate + 6
    where cin =i.cin;
    END IF;

    IF
    if i.attribute2 ='Lower'
    then
    update ar.hz_cust_accounts
    set attribute9=sysdate
    attribute11=sysdate + 12
    attribute6='RLL'
    where cin =i.cin;
    end if;

    if i.attribute2 ='Upper'
    then
    update ar.hz_cust_accounts
    set attribute9=sysdate
    attribute11=sysdate + 12
    attribute6='RUU'
    where cin =i.cin;
    end if;

    END LOOP;
    END;
    /
    Cheers
    Etravels

  2. #2
    Join Date
    Mar 2004
    Location
    Concord, MA
    Posts
    27
    what is the error that you get

    bagchi

  3. #3
    Join Date
    Feb 2004
    Posts
    93

    errors encountered

    Originally posted by bagchi
    what is the error that you get

    bagchi

    Compilation errors for PROCEDURE APPS.CARD_RENEWAL_DATA

    Error: PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following:

    begin function package pragma procedure subtype type use
    <an identifier> <a double-quoted delimited-identifier> cursor
    form current external language
    The symbol "begin" was substituted for "DECLARE" to continue.
    Line: 3
    Text: DECLARE CURSOR cur_BLANK IS

    Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:

    ; for and or group having intersect minus order start union
    where connect
    The symbol ";" was substituted for "BEGIN" to continue.
    Line: 13
    Text: loop

    Error: PLS-00103: Encountered the symbol "ATTRIBUTE11" when expecting one of the following:

    . ( , * @ % & - + ; / at mod rem return returning
    <an exponent (**)> where ||
    The symbol "(" was substituted for "ATTRIBUTE11" to continue.
    Line: 23
    Text: end if;

    Error: PLS-00103: Encountered the symbol "WHERE" when expecting one of the following:

    ) , * & - + / at mod rem <an exponent (**)> and or using ||
    The symbol ")" was substituted for "WHERE" to continue.
    Line: 24

    Error: PLS-00103: Encountered the symbol "IF" when expecting one of the following:

    ( - + mod not null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute forall time timestamp interval date
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string>
    Line: 28

    Error: PLS-00103: Encountered the symbol "ATTRIBUTE11" when expecting one of the following:

    . ( , * @ % & - + ; / at mod rem return returning
    <an exponent (**)> where ||
    The symbol "(" was substituted for "ATTRIBUTE11" to continue.
    Line: 32

    Error: PLS-00103: Encountered the symbol "ATTRIBUTE6" when expecting one of the following:

    ) , * & - + / at mod rem <an exponent (**)> and or using ||
    Line: 33




    hope this helps
    Cheers
    Etravels

  4. #4
    Join Date
    Mar 2004
    Location
    Concord, MA
    Posts
    27
    remove declare, add comma (,) at the end of the each set stmt in the update stmt .. let me know ....

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Also add a ";" after the cursor definition

  6. #6
    Join Date
    Feb 2004
    Posts
    93

    thanks all

    Originally posted by gbrabham
    Also add a ";" after the cursor definition
    fix it up with ; and right column names..

    thanx
    Cheers
    Etravels

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •