DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Need to return more than one row!

  1. #1
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262

    Unhappy

    Hi Gurus,

    CREATE OR REPLACE procedure ppqt_test_dyn2
    (v_id in number) is
    v_quote_id varchar2(50);
    begin
    select quote_id into v_quote_id from test_dyn where id = v_id ;
    dbms_output.put_line(v_quote_id);
    end ppqt_test_dyn1;

    This works perfectly since I have the condition as

    id = v_id;

    But if I give id > v_id, the result fetches more than one record.

    How do I handle this?

    Can anyone help me with the syntax?

    Thanks!

    Hemant

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Use a cursor

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi,

    I know that I have to use a cursor but Im not familiar with the syntax I should be using here...

    Thanks!
    Hemant

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by hemanr

    CREATE OR REPLACE procedure ppqt_test_dyn2
    (v_id in number) is
    CURSOR c_my_quote_ids IS select quote_id from test_dyn
    where id > v_id;
    begin
    dbms_output.enable(1000000);
    FOR r_my_quote_ids IN c_my_quote_ids LOOP
    dbms_output.put_line(r_my_quote_ids.quote_id);
    END LOOP;
    end ppqt_test_dyn1;

    I did not declare the rowtype since it will get an implicit declaration. The rest is up to you.

  5. #5
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi Gandolf,

    This code does not work.

    Here are the records in test_dyn

    1019693
    1019699
    1019705
    1019708
    1019717
    1019720
    1019723
    1019732
    1019735

    And here is ur code slightly altered to write to a table:

    CREATE OR REPLACE procedure ppqt_test_dyn2
    (v_id in number) is
    CURSOR c_my_quote_ids IS select id,quote_id from test_dyn
    where id > v_id;
    begin

    FOR r_my_quote_ids IN c_my_quote_ids LOOP

    insert into test_dyn_hold(id,quote_id)
    values(r_my_quote_ids.id,r_my_quote_ids.quote_id);

    END LOOP;
    end ppqt_test_dyn2;

    If I SQL> exec ppqt_test_dyn2(1019717), the proc writes 16 records to the test_dyn_hold table.
    It should write only 4 records since we have only 4 greater than the input parameter in the source table...

    I guess the cursor itself is not recognizing the input parameter here.
    Last edited by hemanr; 10-24-2002 at 11:16 AM.
    Hemant

  6. #6
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Sorry Gandolf!

    I messed big on this one.

    It works!

    Thanks!
    Hemant

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Glad to be of service.

Posting Permissions

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


Click Here to Expand Forum to Full Width