1

How can I set multiple into inside a select query in Oracle according to the following sql query.

declare
    v_a out varchar2;
    v_b out varchar2;
    v_c out varchar2;

    begin
        select a , b , c into v_a, v_b, v_c from table
    end
Bill Woodger
  • 12,968
  • 4
  • 38
  • 47
Goran Zooferic
  • 371
  • 8
  • 23
  • 1
    Apart from missing semicolons and variable sizes, that is what you do, for a single row of data; so what is your question? Do you want to know how to handle multiple rows being returned? What do you want to do with the data you get back? – Alex Poole Jan 29 '14 at 10:57
  • What you have posted is correct if you get only one record from your table for the query More detail about your requerement will get you more help from SO... – Anto Raja Prakash Jan 29 '14 at 11:25
  • If you want to do this for each row in the table then you may want to put it in a loop... – Tomás Jan 29 '14 at 11:27
  • You may use the same parameter to the block of the procedure as well as the out parameter of the procedure. no need of multiple into. – ajmalmhd04 Jan 29 '14 at 11:53

1 Answers1

2

If your table contains single record then what you have given is correct, except the things mentioned by @AlexPoole in his comments. Also, you cannot use OUT in the annonymous block.

In case your table contains multiple records then you will need to create a collection to store records and do BULK COLLECT in your query. the pseudo code for that will be something like:

          Declare
               TYPE c_var is table of varchar2(100);
               v_a c_var ;
               v_b c_var ;
               v_c c_var ;

          Begin

                Select a, b, ,c BULK COLLECT INTO v_a, v_b, v_c from table;  
          END;

You can then loop through the collection to access the individual elements.

Hope it Helps

Vishad

vishad
  • 1,134
  • 7
  • 11
  • thanks @vishad i tried again my query this time fired finely.I have been specified above but i forgot to put an out parameter.Excuse me. – Goran Zooferic Jan 29 '14 at 12:18