Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    10

    Unanswered: Stored Proc Help Please

    1 create or replace procedure BS_UPDATE_LOOKUPTABLE
    2 (account IN bankrec.bs_lookup%value,
    3 val IN bankrec.bs_lookup%value,
    4 tableid IN bankrec.bs_lookup%table_id,
    5 columnid IN bankrec.bs_lookup%x)
    6 AS
    7 DECLARE
    8 thisrow bankrec.bs_lookup%y;
    9 CURSOR myrow IS
    10 select y into thisrow from bankrec.bs_lookup
    11 where value = account;
    12 BEGIN
    13 OPEN myrow;
    14 LOOP
    15 FETCH myrow INTO thisrow;
    16 EXIT WHEN myrow%NOTFOUND;
    17 update bankrec.bs_lookup set value = val where
    18 table_id = tableid and x = columnid and y =thisrow;
    19 END LOOP;
    20 CLOSE myrow;
    21* END BS_UPDATE_LOOKUPTABLE;
    SQL> /

    Warning: Procedure created with compilation errors.



    I am getting the above warning when compiling the procedure.
    Please help me spot the culprit.
    Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Please help me spot the culprit.
    How can we help you when you do not post the actual error(s)?
    SQL> SHOW ERROR
    returns what? Please use CUT & PASTE!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is this?
    Code:
    account IN bankrec.bs_lookup%value
    Correct syntax is
    Code:
    account IN bankrec.bs_lookup.value%TYPE
    The same goes for the rest of your IN parameters.

    Furthermore, it appears that loop is not necessary. Here is just a sample script (as you didn't provide much information, such as table description) which *should* do the same. You might, however, expect the TOO-MANY-ROWS if "y" subquery returns more than one value.
    Code:
    CREATE OR REPLACE PROCEDURE bs_update_lookuptable (
       ACCOUNT    IN   bankrec.bs_lookup.ACCOUNT%TYPE,
       val        IN   bankrec.bs_lookup.val%TYPE,
       tableid    IN   bankrec.bs_lookup.tableidid%TYPE,
       columnid   IN   bankrec.bs_lookup.columnid%TYPE
    )
    AS
    BEGIN
       UPDATE bankrec.bs_lookup
          SET VALUE = val
        WHERE table_id = tableid 
          AND x = columnid 
          AND y = (SELECT y
                   FROM bankrec.bs_lookup
                   WHERE VALUE = ACCOUNT);
    END;
    Finally, why do you insist on explicit cursor? You'd rather use cursor FOR loop and get rid of declaring a cursor, cursor variable, opening the cursor, closing it, exiting the loop ... Perhaps you'd be interested in checking the PL/SQL User's Guide and Reference book.

  4. #4
    Join Date
    May 2004
    Posts
    10
    Thank you all for the responses. I removed the cursor as it can be avoided. Its working now.


    CREATE OR REPLACE PROCEDURE bs_update_lookuptable (
    ACCOUNT IN bankrec.bs_lookup.value%TYPE,
    val IN bankrec.bs_lookup.value%TYPE,
    tableid IN bankrec.bs_lookup.table_id%TYPE,
    columnid IN bankrec.bs_lookup.x%TYPE
    )
    AS
    BEGIN
    UPDATE bankrec.bs_lookup
    SET VALUE = val
    WHERE table_id = tableid
    AND x = columnid
    AND y = (SELECT y
    FROM bankrec.bs_lookup
    WHERE VALUE = ACCOUNT);
    END;
    /

Posting Permissions

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