Results 1 to 4 of 4

Thread: Inserting value using FOR LOOP in PROCEDURE

  1. #1
    Junior Member
    Join Date
    Oct 2007
    Answers
    2

    Inserting value using FOR LOOP in PROCEDURE

    Dear all
    i need help,
    i created a table named ABC with one column named A.
    now i want to insert 10 values in this column using procedure.
    i am using this code with errors.


    1 create or replace procedure ins
    2 (i IN number)
    3 AS
    4 -- i number :=2;
    5 BEGIN
    6 LOOP
    7 insert into abc(a)
    8 values (i);
    9 i :=i+1;
    10 EXIT WHEN i>11;
    11 END LOOP;
    12* END ins;
    SQL> /

    Warning: Procedure created with compilation errors.

    SQL> show error;
    Errors for PROCEDURE INS:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    9/2 PLS-00363: expression 'I' cannot be used as an assignment target
    9/2 PL/SQL: Statement ignored


    please write me correct code how to enter values using procedure.
    thank


  2. #2
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Inserting value using FOR LOOP in PROCEDURE

    In this procedure you have used i as in paramater. You can not assign a value to in parameter . Rewrite the procedure as follows

    create or replace procedure ins
    AS
    i number :=1;
    BEGIN
    LOOP
    insert into abc(a) values (i);
    i :=i+1;
    EXIT WHEN i>11;
    END LOOP;
    COMMIT;
    END ins;

    The above procedure will insert 10 recods.


  3. #3
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Inserting value using FOR LOOP in PROCEDURE

    Try this
    set serveroutput on
    create or replace procedure ins
    AS
    j number;
    BEGIN
    j:=1;
    LOOP
    insert into abc (a)
    values (j);
    j :=j+1;
    EXIT WHEN j>10;
    END LOOP
    commit;
    END ins;

    exec ins;

    select * from abc;


  4. #4
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Inserting value using FOR LOOP in PROCEDURE

    If you want to insert 10 values starting with number that was passed as parameter write it as follows
    create or replace procedure ins (x in number)
    AS
    i number :=1;
    y number;
    BEGIN
    y := x;
    LOOP
    insert into abc(a) values (x);
    y :=y+1;
    i :=i+1;
    EXIT WHEN i>11;
    END LOOP;
    COMMIT;
    END ins;

    But do not use same identifier as in paramter and variable.

    Last edited by krishnaindia2007; 01-04-2008 at 03:51 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact