11 Replies Latest reply on Jul 21, 2010 10:04 AM by 786451

    how to insert multiple rows in a table by user input ( pl/sql)

    786451
      hi,
      I've been trying to insert multiple rows in a table from user input by some pl/sql, but failed every time. :/
      what happens is that the program suppose to ask the user every time for new value but the loop
      actually inserts the 1st values four times ( the loop is ran four times ). can any one point out what i'm
      doing wrong or some solutions will be nice ;)

      here what i wrote

      ---------------------------------

      set serveroutput on
      set verify off

      declare

      empno number;
      empname varchar2(20);

      begin

      for i in 1..4 loop
      empno:=&empno;
      empname:='&empname';

      insert into employee values(empno,empname);

      end loop;

      end;

      -------------------------------

      dont laugh at my code, i'm new to this oracle :D

      thanks, looking forward for some reply
        • 1. Re: how to insert multiple rows in a table by user input ( pl/sql)
          785682
          i think ,
          there is no loop needed
          • 2. Re: how to insert multiple rows in a table by user input ( pl/sql)
            BluShadow
            The use of &variable in a script is actually syntax for a "substitution variable" in the SQL*Plus tool (other tools may also do the same), not an inherent part of SQL or PL/SQL itself.

            Whenever SQL*Plus is given a script it parses through it and if it encounters one of these it prompts for a value. This value is then substituted into the script before the script actually get's sent to the SQL or PL/SQL engine (process) on the database server. Once the script has gone to the database server it executes there and the results are passed back for SQL*Plus to display. However, the SQL and PL/SQL processes on the database server have no way to interface to the client machine, so they themselves cannot prompt for input from the client and you can't expect to prompt inside a loop as you are doing.

            What you need is a user interface on the client that can prompt repeatedly for values and then re-send the script, or call a procedure on the database each time. This can be done using shell scripts or dos batch files (depending on your client being unix/dos based) or using a front end application tool such as Java, .NET, Powerbuilder, PHP, Application Express (APEX) etc.
            1 person found this helpful
            • 3. Re: how to insert multiple rows in a table by user input ( pl/sql)
              boopathi
              code {

              declare

              empno number;
              empname varchar2(20);
              no number;

              begin

              n :=&n --- no of emp to be inserted into the table

              for i in 1..n loop
              empno:=&empno;
              empname:='&empname';

              insert into employee values(empno,empname);

              end loop;


              end;

              }
              • 4. Re: how to insert multiple rows in a table by user input ( pl/sql)
                Spongebob
                Iregardless of the question as to why you would like this thing be done.

                I hope this helps. No need to use a loop, unless you just want to use it.
                set serveroutput on
                set verify off
                declare
                  empno number;
                  empname varchar2(20);
                begin
                  insert into employee values(&empno,'&empname');
                  insert into employee values(&empno,'&empname');
                  insert into employee values(&empno,'&empname');
                  insert into employee values(&empno,'&empname');
                  commit;
                --If emp_no is not PK or unique
                end loop;
                end;
                /
                • 5. Re: how to insert multiple rows in a table by user input ( pl/sql)
                  785682
                  do you want to enter empno and enmae at one time only?
                  • 6. Re: how to insert multiple rows in a table by user input ( pl/sql)
                    786451
                    no no, it suppose to ask the user every time ( number of loops ) for empno and empname :) and will be inserted in the table.
                    i think its possible but dunno how
                    • 7. Re: how to insert multiple rows in a table by user input ( pl/sql)
                      munky
                      Hi

                      As Blu suggested, you would be wise to use a front end tool for this. Have a look into Oracle Application Express (APEX), its very easy to learn, comes free with the Oracle database and has built in wizards to create this like tabular forms based on tables (which is what I think you are really looking for here).

                      The APEX 4.0 homepage is here...

                      http://www.oracle.com/technology/products/database/application_express/index.html

                      Cheers

                      Ben
                      1 person found this helpful
                      • 8. Re: how to insert multiple rows in a table by user input ( pl/sql)
                        BluShadow
                        Spongebob wrote:
                        Iregardless of the question as to why you would like this thing be done.

                        I hope this helps. No need to use a loop, unless you just want to use it.
                        set serveroutput on
                        set verify off
                        declare
                        empno number;
                        empname varchar2(20);
                        begin
                        insert into employee values(&empno,'&empname');
                        insert into employee values(&empno,'&empname');
                        insert into employee values(&empno,'&empname');
                        insert into employee values(&empno,'&empname');
                        commit;
                        --If emp_no is not PK or unique
                        end loop;
                        end;
                        /
                        Yeah, nice idea, but his next question would be along the lines of...

                        "So, now I want to keep asking for details until the user enters e.g. "0" for the employee number at which point it should exit the loop."

                        It's not something that should be done using SQL*Plus scripting. It needs a proper user interface.
                        • 9. Re: how to insert multiple rows in a table by user input ( pl/sql)
                          786451
                          thanks for the info, looks like a nice tool ;) will try it out
                          but i'm really want this ( my problem ) done by pl/sql
                          wondering if there any function in pl/sql to release cached data ( like fflush(stdin) in c++ )

                          Edited by: user13371438 on Jul 21, 2010 2:30 AM

                          Edited by: user13371438 on Jul 21, 2010 2:30 AM
                          • 10. Re: how to insert multiple rows in a table by user input ( pl/sql)
                            BluShadow
                            user13371438 wrote:
                            thanks for the info, looks like a nice tool ;) will try it out
                            but i'm really want this ( my problem ) done by pl/sql
                            Take a quick re-read of my post above.

                            PL/SQL is a process running on the database server. That process cannot interact with the client machine. It can't request user input and it can't display output to the client. Only client interface tools can do that. You're wanting to get input from a user, so you need an interface tool to do that. SQL*Plus can interface and request input, but only as substition variables, and it doesn't really provide a programming construct that allows you to programatically run in a loop to do that. In order to do that using SQL*Plus you would have to pair it up with shell/dos scripting languages, but more ideally you should use a proper interface like Apex.
                            wondering if there any function in pl/sql to release cached data ( like fflush(stdin) in c++ )
                            PL/SQL doesn't accept input from a client, so there is no sense in having something to release cached data. You pass values to PL/SQL code by using procedures or functions with parameters.
                            • 11. Re: how to insert multiple rows in a table by user input ( pl/sql)
                              786451
                              well i guess thats the dead end for me ;)
                              thanks everyone for their time and effort into this
                              will be back with more questions :D