read array of "n" elements from user input

Question

How to read array of n elements dynamically and display the elements in plsql.

Below is my code (i am new to plsql programming)

set serveroutput on
set verify on
declare
   type myarray is table of number index by binary_integer;
   x myarray;
   i pls_integer;
   n number;

begin
   -- populate array
   dbms_output.put_line('Enter number of array elements');
   n := &n;
   dbms_output.put_line('Enter elements one by one');
   for i in 1..n loop
  dbms_output.get_lines(&&x(i),n);
   end loop;
   i :=0;

   -- print array
   loop
           i := i + 1;
           begin
                   dbms_output.put_line(x(i));
           exception
                   when no_data_found then exit;
           end;
   end loop;

   end;
   /
  quit;

dbms_output.get_line does not read data from input. It reads lines from the dbms_output buffer. You are confusing sql*plus commands with pl/sql. — OldProgrammer, Mar 29 '14 at 20:07
In words of one syllable - you can't. PL/SQL is not set up to accept interactive user input. I suggest that you put your array elements in a file and read the file line-by-line (see the UTL_FILE package), use TO_NUMBER to convert the character strings read from the file to numbers, and store those numbers into your array. But there is no way I'm familiar with to read input from a terminal using PL/SQL. Best of luck. — Bob Jarvis - Слава Україні, Mar 30 '14 at 02:12
@Bob Jarvis. Hi sir, thanks for the reply. please help me how to use utl file in my case. hoe to pass file data as array elements. — user3422419, Mar 30 '14 at 04:55
Possible duplicate of [In oracle 10g, how do I accept user input in a loop?](http://stackoverflow.com/questions/13764681/in-oracle-10g-how-do-i-accept-user-input-in-a-loop) — J. Chomel, Aug 24 '16 at 12:56

score 0 · Answer 1 · answered Aug 24 '16 at 12:54

I stumble into the same issue and wanted to try something.

You could do this with a brutal approach that consists in using you shell to call the client (i.e. sqlplus) any time you want to add a value to your array. I wrote a little package that stores the values of the array in a table. Then you can change the way you store the values, but principle would remain the same:

 -- this package uses "execute immediate" statements for everything
 -- because it assumes the temporary table T_MY_ARRAY can be non-existent.
 create or replace package my_array
  authid current_user
is
  TYPE a_TBL_Number IS TABLE OF Number INDEX BY BINARY_INTEGER; 
  procedure init;
  procedure add(v in number);
  procedure display;
  function to_var return a_TBL_Number;
end my_array;
/
create or replace package body my_array
is     
  procedure init is
  -- create table if needed, then make sure its empty;
  begin
    begin
      execute immediate 'create table T_MY_ARRAY(c1 number)';
    exception when others then
      -- dbms_output.put_line(sqlerrm);
      -- we're never sure the temp. table already exists. So create it and catch if existing.
      null;
    end;
    execute immediate 'truncate table T_MY_ARRAY';
  end init;

  procedure add(v in number) is
  -- add new value
  begin
    execute immediate 'insert into T_MY_ARRAY (c1) values ('||v||')';
  end add;

  function to_var return a_TBL_Number is
  -- hand out an array with the values
    t_TBL_n a_TBL_Number; 
  begin
    execute immediate 'select c1 from T_MY_ARRAY ' bulk collect into t_TBL_n;
    return t_TBL_n;
  end to_var;

  procedure display is
    t_TBL_n a_TBL_Number; 
  begin
    t_TBL_n:=my_array.to_var();
    for i in 1..t_TBL_n.count loop
      dbms_output.put_line(t_TBL_n(i));
    end loop;
  end display;

end my_array;
/

Then here is how you could call this from a shell (here it is for an old ksh):

read SQL_CONNECT?"Enter your SQL connection string:"
read n?"Enter number of array elements:"
# first initialize the array:
sqlplus -s ${SQL_CONNECT} <<_EOF
  set serveroutput off
  set termout off
  set feedback off
  begin
    my_array.init;
  end;
/
_EOF

# then loop on the given number of elements
typeset -i10 i=0
while [[ ${i} -lt ${n} ]]
do
  i=i+1
  echo iter:${i}
  read MY_VALUE?"Enter elements one by one:"
  sqlplus -s ${SQL_CONNECT} <<_EOF
  set serveroutput off
  set termout off
  set feedback off
  begin
    my_array.add(${MY_VALUE});
  end;
/
commit;
_EOF
done

# at the end, use stored values to display result:
  sqlplus -s ${SQL_CONNECT} <<_EOF
  set serveroutput on
  set feedback off
  begin
    dbms_output.put_line('---------------');
    dbms_output.put_line('your input was:');
    my_array.display;
  end;
/
_EOF

Another solution would be to process with a recursive call to a sql script like advised here: http://stackoverflow.com/a/1873019/6019417 ; but you still can't do it with plsql only, and need to use a client (here also `sqlplus`). — J. Chomel, Aug 24 '16 at 13:28

read array of "n" elements from user input

1 Answers1