Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2005
    Posts
    17

    Unanswered: While loop & select 2 columns and assign to variable

    Why does this not work?????

    v_counter INTEGER;
    WHILE v_counter <= 5 LOOP
    v_counter := v_counter + 1
    END LOOP;


    Also, I want to select colA, colb from tablesource into variable1 and variable2.
    I can do this for 1 variable but the second one seems to error out...


    v_id varchar(50);
    v_id2 varchar(50);
    --this works
    select modulename into v_id
    from f.tablesource
    where number = 'AGGG001';

    --this does not
    select modulename into v_id, modulename2 into v_id2
    from f.tablesource
    where number = 'AGGG001';


    Thanks for the help.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    First answer:
    it doesn't work because v_counter starts as NULL

    try this:
    Code:
    v_counter INTEGER := 0;
    Second answer:
    select all of your columns and then use your INTO statement (NOT one at a time)

    try:
    Code:
    select modulename, modulename2
    into v_id, v_id2 
    from f.tablesource
    where number = 'AGGG001';
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by chocobot
    Why does this not work?????

    v_counter INTEGER;
    WHILE v_counter <= 5 LOOP
    v_counter := v_counter + 1
    END LOOP;


    Also, I want to select colA, colb from tablesource into variable1 and variable2.
    I can do this for 1 variable but the second one seems to error out...


    v_id varchar(50);
    v_id2 varchar(50);
    --this works
    select modulename into v_id
    from f.tablesource
    where number = 'AGGG001';

    --this does not
    select modulename into v_id, modulename2 into v_id2
    from f.tablesource
    where number = 'AGGG001';


    Thanks for the help.
    You can only have a loop within a sql block

    Code:
    declare
    v_counter INTEGER;
    begin
      v_counter := 0;
      WHILE v_counter <= 5 LOOP
      v_counter := v_counter + 1
    END LOOP;
    end;
    /
    select modulename , modulename2
    into v_id,v_id2
    from f.tablesource
    where number = 'AGGG001';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2005
    Posts
    17
    Thank you for the quick replies.

    I figured out the loop issue but the select into helped a ton.

    Here is another dumb q: how to i print the output of v_counter
    I tried this but i dont see a thing.
    DBMS_OUTPUT.PUT_LINE( 'The answer is: ' || TO_CHAR(v_counter) );

    Thanks.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by beilstwh
    You can only have a loop within a sql block
    yeah, I assumed you were throwing it in a sql-block but it is good
    to point that out (just in case!)

    PHP Code:
      1  declare
      
    2  v1 integer;
      
    3  v2 integer;
      
    4  begin
      5  select 1
    2 into v1v2 from dual;
      
    6  dbms_output.put_line(to_char(v1)||' '||to_char(v2));
      
    7end;
    system@qa> /
    1 2

    PL
    /SQL procedure successfully completed
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    And, eventually, before running this script, enable dumping the output by issuing (at the SQL*Plus prompt)

    SET SERVEROUTPUT ON;

    You can read a nice Tom Kyte's article here.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    SET SERVEROUTPUT ON
    i prefer to put that line in my $ORACLE_HOME/sqlplus/admin/glogin.sql
    This way it is always on.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why? There's nothing better than running a procedure and seeing PL/SQL procedure successfully completed without desired output

Posting Permissions

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