Results 1 to 4 of 4

Thread: cursor

  1. #1
    Join Date
    Jan 2005
    Posts
    10

    Unanswered: cursor

    pl/sql problem

    Tables:
    EI_ES URR_ROLES URR_ROLES_TEXT
    ES_ID ROLE_ID ROLE_ID
    NAME ROLE_NAME

    For each ES_ID in the EI_ES table, I would Like to create an entry in the URR_ROLES table
    After this part,I would like to create an entry in the URR_ROLES_TEXT table, With the URR_ROLE'S ROLE_ID and EI_ES NAME. but When I do this, Its creating the ROle_ID for all the records in the EI_ES table.

    For Example,

    Procedure MYTEST

    BEGIN
    select count(*) from EI_ES into a;
    for i in 1..a loop
    p_roles_id :=Sequence (Iam getting)+1

    inserting into URR_ROLES ( ) values ();

    inserting into URR_ROLES_TEXT () () from EI_ES;

    URR_ROLES (ROLE_ID) EI_ES(NAME)
    1 abc
    2 def

    but its inserting like in URR_ROLES_TEXT table
    1 abc
    1 def
    2 abc
    2 def, Where as i want it to insert like
    1 abc
    2 def

    Every one is saying that to write a crusor,

    What Am i supposed to do, Please any Help would be great.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    So are you saying that you want tables URR_ROLES and URR_ROLES_TEXT both to contain exactly the same data? i.e.
    1 abc
    2 def

    That sounds pointless, but if so, why don't you just do this:
    Code:
    begin
      insert into urr_roles (role_id, role_name)
      select rownum, name from ei_is order by name;
    
      insert into urr_roles_text (role_id, role_name)
      select rownum, name from ei_is order by name;
    end;
    If you do need a cursor, you don't count the rows and then use a numeric loop, you do this:
    Code:
    begin
      for r in (select es_id, name from ei_es) loop
        ..
      end loop;
    end;
    Your second insert inserts every row from EI_IS each time round the loop because you didn't attach a WHERE clause.

  3. #3
    Join Date
    Jan 2005
    Posts
    10

    Thanks Andrew

    Thanks Andrew, The second Loop statement worked.

  4. #4
    Join Date
    Jan 2005
    Posts
    10

    loop with dblink

    I have one more question , Regarding the Loops with Dblink

    I have one more quesiton regarding for loops:

    This table is in the Other Schema and Iam accessing it through the DBLINK.

    A (Other Schema)

    ES_ID
    USER_ID are the columns

    Here ES_ID and ROLES_ID both are different, Iam just getting the ROLES_ID from the SEQUENCE

    So For each ES_ID ,user_id in the Table A, I want to create an entries at the other table called B
    execute immediate 'select ES_id,user_id from' ||a||'@'||dblink into e,f;

    for k in (e,f ) loop

    insert into table B(User_Id,ROLES_ID);

    Table A
    1 john
    1 paul
    2 kim

    I want to insert like

    John 101
    Paul 101
    Kim 102

    This 101 and 102 are the ROLE_ID which Iam generating from the sequence. Any help would be great.

Posting Permissions

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