Results 1 to 8 of 8

Thread: Help!!!!

  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unhappy Unanswered: Help!!!!

    Hey, has anyone got any ideas how to fix this?
    DECLARE
    v_account_id INTEGER;
    v_product_code CHAR(4);
    v_quantity INTEGER;
    BEGIN
    FOR i IN 1 .. &purchases LOOP
    v_account_id := &account_id;
    v_product_code := UPPER('&product_code');
    v_quantity := &quantity;
    INSERT INTO charges
    VALUES (charges_purchase_no_seq.nextval, v_account_id, v_product_code, v_quantity, SYSDATE);
    END LOOP;
    END;

    When I run it, I can only enter in one set of values where the &s are. It doesn't let me enter new values for the variables! In this case it INSERTs the number of rows equal to &purchases, but they're all the same!

    Does anyone know how I can enter in different things in the variables through the loop?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Help!!!!

    Originally posted by Mingle
    Hey, has anyone got any ideas how to fix this?
    DECLARE
    v_account_id INTEGER;
    v_product_code CHAR(4);
    v_quantity INTEGER;
    BEGIN
    FOR i IN 1 .. &purchases LOOP
    v_account_id := &account_id;
    v_product_code := UPPER('&product_code');
    v_quantity := &quantity;
    INSERT INTO charges
    VALUES (charges_purchase_no_seq.nextval, v_account_id, v_product_code, v_quantity, SYSDATE);
    END LOOP;
    END;

    When I run it, I can only enter in one set of values where the &s are. It doesn't let me enter new values for the variables! In this case it INSERTs the number of rows equal to &purchases, but they're all the same!

    Does anyone know how I can enter in different things in the variables through the loop?
    NO.
    Why do you expect to be prompted more than once?

  3. #3
    Join Date
    Oct 2003
    Posts
    4

    Re: Help!!!!

    Shouldn't it go throught the loop again and ask for the values to be re-entered?

    If not, do you know how I can achieve this?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Help!!!!

    Originally posted by Mingle
    Shouldn't it go throught the loop again and ask for the values to be re-entered?

    If not, do you know how I can achieve this?
    Why does it need to "ask again" when it already has the requested values?

    P.S.
    Exactly which piece of software (SQL*PLUS or PL/SQL) is prompting
    for the substituted variables?

    Rhetorical question -
    By what construct(s) does PL/SQL have the ability to interoperate with keyboard input?

  5. #5
    Join Date
    Oct 2003
    Posts
    4

    Re: Help!!!!

    Using the & operator prompts for the input. I want the script to run the loop, and then prompt for a new set of input a number of times. Currently it accepts the input for the variables, and then they are set.

  6. #6
    Join Date
    Aug 2003
    Posts
    41
    I remember doing something similar in C or Pascal. I'm not sure, try ACCEPT instead of & to read your values.

    -Sunil.

  7. #7
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    when running in the same session, the & variables are initialized once, and after that the have a value, so it runs with those values.
    The use of the & variables to prompt a user for input is not the correct way to use in PL/SQL.
    Using SQL u can define another variable to hold the value of the & variable, call a procedure and then undefine it.
    For now i don't know of any way to use this in a loop.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  8. #8
    Join Date
    Oct 2003
    Posts
    4

    Thanks

    Thanks for the help guys, just can't seem to get it. Looks like I'll try something else!!

Posting Permissions

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