4

Using this code I'm not able to run an insert query multiple times; it asks for ID and name only one time ( if value in counter is > than 1 ).

declare
        counter number := 0 ;
begin 
        counter := &counter ;
        while counter > 0 loop
               insert into customer values ( &id, '&name' ) ;
               counter := counter - 1 ;
       end loop ;
end ;   

Let me explain with the help of an example :-

Say I put value 2 in counter. With this it should ask me for ID and name twice but it only asks once, and it copies the values I entered for the ID and name into table test twice.

How can I fix this? If I can't then please suggest alternative code which will fix my problem.

2
  • Can you show all of your code from the declaration? Commented Jan 14, 2016 at 12:07
  • You may want to review this information for alternative methods for getting user input: safaribooksonline.com/library/view/oracle-sqlplus-the/…. Also, I am guessing that you are using SQL/Plus. You should tag your question accordingly. Commented Jan 14, 2016 at 12:21

1 Answer 1

6

The substitution variables &counter, &id and &name are each evaluated once, when the PL/SQL block is compiled - not as it is being executed.

The variables are not, and cannot be, re-evaluated or re-promoted within the PL/SQL block. The block is executed as a single unit within the database - once it has been submitted for execution it is independent of the client, which just waits for it to complete (unless you interrupt it, which the client also handles). PL/SQL is not an interactive language, and you shouldn't confuse client functionality (e.g. substitution variables) with SQL or PL/SQL functionality.


Just for fun, you could generate a script based on counter which does the appropriate number of prompts for IDs and names, and gets them into a format that could be used by a simple insert:

set serveroutput on
set feedback off
set echo off
set verify off
set termout off

accept counter "How many value pairs do you want to insert?"

var ids varchar2(4000);
var names varchar2(4000);

spool /tmp/prompter.sql

begin
  -- prompt for all the value pairs
  for i in 1..&counter loop
    dbms_output.put_line('accept id' ||i|| ' number  "Enter ID ' ||i|| '"');
    dbms_output.put_line('accept name' ||i|| '  char "Enter name ' ||i|| '"');
  end loop;

  -- concatenate the IDs into one variable
  dbms_output.put('define ids="');
  for i in 1..&counter loop
    if i > 1 then
      dbms_output.put(',');
    end if;
    dbms_output.put('&'||'id'||i);
  end loop;
  dbms_output.put_line('"');

  -- concatenate the names into one variable
  dbms_output.put('define names="');
  for i in 1..&counter loop
    if i > 1 then
      dbms_output.put(',');
    end if;
    -- each name wrapped in single quotes
    dbms_output.put(q'['&]'||'name'||i||q'[']');
  end loop;
  dbms_output.put_line('"');
end;
/
spool off

@/tmp/prompter

insert into customer (id, name)
select i.id, n.name
from (
  select rownum as rid, column_value as id 
  from table(sys.odcinumberlist(&ids))
) i
join (
  select rownum as rid, column_value as name
  from table(sys.odcivarchar2list(&names))
) n
on n.rid = i.rid;

select * from customer;

That creates a file called prompter.sql (I've put it in /tmp; put it somewhere suitable for your environment!); with the 'number of value pairs' prompt answered as 2 that temporary script would look contain:

accept id1 number  "Enter ID 1"
accept name1  char "Enter name 1"
accept id2 number  "Enter ID 2"
accept name2  char "Enter name 2"
define ids="&id1,&id2"
define names="'&name1','&name2'"

That temporary script is then run with @, prompting the user for all those individual values. And then table collections built from the combined substitution variables are used in a select, which is used by the insert.

Sign up to request clarification or add additional context in comments.

2 Comments

so isn't there any way out of this ??
@AhtishamChishti - not a simple way. Is this an exercise or something you really need to do with interactive prompts?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.