Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> how to insert more than one row in a table
gauravkumar
post Jun 25 2007, 07:40 AM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 25-June 07
From: bangalore,india
Member No.: 9,606



hi,
How to insert more than one row in a table using loop by taking the attributes at runtime.
Go to the top of the page
 
+Quote Post
dave
post Jun 25 2007, 08:10 AM
Post #2


Advanced Member
***

Group: Members
Posts: 5,206
Joined: 8-October 04
Member No.: 785



runtime from what?
Go to the top of the page
 
+Quote Post
gauravkumar
post Jun 25 2007, 11:09 PM
Post #3


Newbie
*

Group: Members
Posts: 5
Joined: 25-June 07
From: bangalore,india
Member No.: 9,606



QUOTE (dave @ Jun 25 2007, 01:11 PM) *
runtime from what?

runtime from keyboard.
Go to the top of the page
 
+Quote Post
burleson
post Jun 26 2007, 05:23 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,623
Joined: 26-January 04
Member No.: 13



Hi,

>> How to insert more than one row in a table using loop by taking the attributes at runtime.

Are you asking how to code it?

Do you know basic programming concepts?

It's just simple coding, a PL/SQL WHILE loop:

http://www.dba-oracle.com/concepts/pl_sql_while_loop.htm

You have many language choices:

Oracle PHP: This book has working examples of screen interactivity with Oracle:

http://www.rampant-books.com/book_2005_2_php_oracle.htm

Oracle Apex with PL/SQL: It's realy easy to solicit data from an Apex screen for storage:


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
gauravkumar
post Jun 27 2007, 05:20 AM
Post #5


Newbie
*

Group: Members
Posts: 5
Joined: 25-June 07
From: bangalore,india
Member No.: 9,606



QUOTE (burleson @ Jun 26 2007, 10:24 AM) *
Hi,

>> How to insert more than one row in a table using loop by taking the attributes at runtime.

Are you asking how to code it?

Do you know basic programming concepts?

It's just simple coding, a PL/SQL WHILE loop:

http://www.dba-oracle.com/concepts/pl_sql_while_loop.htm

You have many language choices:

Oracle PHP: This book has working examples of screen interactivity with Oracle:

http://www.rampant-books.com/book_2005_2_php_oracle.htm

Oracle Apex with PL/SQL: It's realy easy to solicit data from an Apex screen for storage:


I have used following query:-
set serveroutput on;
declare
i number;
emp_rec g_emp%rowtype;
BEGIN
I:=1;
WHILE I< 6
loop
dbms_output.put_line('enter values');
insert into G_EMP values(&empid,'&EMPNAME',&sal,&mgrno,'&GRADE',&PHONENO,'&CITY','&D_O_J',&DEPTNO);
commit;
I:=I+1;
end loop;
end;
I am getting following output:
Enter value for empid: 10000
Enter value for empname: GAURAV
Enter value for sal: 65765
Enter value for mgrno: 7656
Enter value for grade: G
Enter value for phoneno: 65456
Enter value for city: BANG
Enter value for d_o_j: 05-FEB-07
Enter value for deptno: 43
old 9: insert into G_EMP values(&empid,'&EMPNAME',&sal,&mgrno,'&GRADE',&PHONENO,'&CITY','&D_O_J
new 9: insert into G_EMP values(10000,'GAURAV',65765,7656,'G',65456,'BANG','05-FEB-07',43);
enter values
enter values
enter values
enter values
enter values

PL/SQL procedure successfully completed.
Expected:-It should ask attributes 5 times but it is asking only one times and inserting that values 5 times.
Go to the top of the page
 
+Quote Post
Laurent Schneide...
post Jun 28 2007, 08:36 AM
Post #6


Advanced Member
***

Group: Members
Posts: 243
Joined: 24-June 07
From: Switzerland
Member No.: 9,590



Hi,
& let you enter a sqlplus variable, before the plsql block is compiled. You cannot have interactive plsql (okay, I tried this in my blog, but it is very wild)

About doing a loop in sqlplus, I once suggested

x.sql
CODE
set feedb off ver off
col i nopri new_v i
col script nopri new_v script
select 0 i from dual where 1=0;
select to_char(0&i+1,'FM0') i,decode(0&i,5,null,'x.sql') script from dual;
undef col1
undef col2
insert into t(col1,col2) values (&col1,'&col2');
@&script


here it is how it works
CODE
SQL> create table t(col1 number,col2 varchar2(6));

Table created.

SQL> @x


Enter value for col1: 1
Enter value for col2: foo


Enter value for col1: 2
Enter value for col2: bar


Enter value for col1: 3
Enter value for col2: baz


Enter value for col1: 4
Enter value for col2: bop


Enter value for col1: 5
Enter value for col2: quux


Enter value for col1: 6
Enter value for col2: quuux
SQL> select * from t;
      COL1 COL2
---------- ------
         1 foo
         2 bar
         3 baz
         4 bop
         5 quux
         6 quuux


but this is not for the faint hearted ohmy.gif

Most probably, you should look for another language like perl, shell, batch, java, because there is no "loop" in sqlplus and no "user input" in plsql...

HTH
Laurent


--------------------
Go to the top of the page
 
+Quote Post
gauravkumar
post Jun 29 2007, 01:59 AM
Post #7


Newbie
*

Group: Members
Posts: 5
Joined: 25-June 07
From: bangalore,india
Member No.: 9,606



QUOTE (Laurent Schneider @ Jun 28 2007, 01:37 PM) *
Hi,
& let you enter a sqlplus variable, before the plsql block is compiled. You cannot have interactive plsql (okay, I tried this in my blog, but it is very wild)

About doing a loop in sqlplus, I once suggested

x.sql
CODE
set feedb off ver off
col i nopri new_v i
col script nopri new_v script
select 0 i from dual where 1=0;
select to_char(0&i+1,'FM0') i,decode(0&i,5,null,'x.sql') script from dual;
undef col1
undef col2
insert into t(col1,col2) values (&col1,'&col2');
@&script


here it is how it works
CODE
SQL> create table t(col1 number,col2 varchar2(6));

Table created.

SQL> @x
Enter value for col1: 1
Enter value for col2: foo
Enter value for col1: 2
Enter value for col2: bar
Enter value for col1: 3
Enter value for col2: baz
Enter value for col1: 4
Enter value for col2: bop
Enter value for col1: 5
Enter value for col2: quux
Enter value for col1: 6
Enter value for col2: quuux
SQL> select * from t;
      COL1 COL2
---------- ------
         1 foo
         2 bar
         3 baz
         4 bop
         5 quux
         6 quuux


but this is not for the faint hearted ohmy.gif

Most probably, you should look for another language like perl, shell, batch, java, because there is no "loop" in sqlplus and no "user input" in plsql...

HTH
Laurent


Thanks Laurent. You have removed lots of pressure.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th October 2014 - 01:14 AM