Re: Prompting multiples times in PL/SQL??

From: <chris.hulan_at_gmail.com>
Date: 22 Jun 2006 07:29:43 -0700
Message-ID: <1150986583.758030.288470@p79g2000cwp.googlegroups.com>

qazmlp1209_at_rediffmail.com wrote:
> The Table format is as follows:
>
> SQL> desc MyTEST;
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> NAME VARCHAR2(10 CHAR)
>
> I want to add 10 rows into this Table, with each row containing the
> 'name' that I want to enter.
> The following PL/SQL script does not work:
>
> ----------
> declare
> i number;
> begin
> for i in 1..10 loop
> insert into tst (name) values ('&name');
> end loop;
> end;
> ----------
>
> It prompts for the 'name' only once and the same name figures in all
> the rows. But, I want to be prompted 10 times and each time I want to
> enter different 'name'. How exactly I should adapt the PL/SQL script
> for this?

I don't think it is achievable in PL/SQL, as you can't prompt the user from within the PL/SQL code.

I suggest a SQL script to ge the input, then call the PL/SQL function:

--a simple script, not tested!!
ACCEPT csvNames prompt "Enter list of names separated by commas (','): "

DECLARE

   m_csvnames   VARCHAR2 (1000) := '&csvNames';
   m_name       VARCHAR2 (10);
   m_pos        PLS_INTEGER     := 0;
BEGIN
   WHILE NVL(LENGTH(m_csvnames), 0) > 0           --while there are

names to insert

   LOOP

      m_pos := INSTR (m_csvnames, ',');
                                    --find end position of first name

in list

      IF m_pos > 0
      THEN
         m_name := SUBSTR (m_csvnames, 1, m_pos - 1);
--extract name
         m_csvnames := SUBSTR (m_csvnames, m_pos + 1);
                                            --remove  name from list of
names
      ELSE
         m_name := m_csvnames;                    --extract last name
in list
         m_csvnames := NULL;                                --make list
empty
      END IF;

      INSERT INTO tst
                  (NAME
                  )
           VALUES (m_name
                  );

   END LOOP;    COMMIT;
END;
/ Received on Thu Jun 22 2006 - 09:29:43 CDT