hi all,
i have some big tables i am generating in excel, and i want to create
them in my DB, since they are many and every one is >20 000 rows,
i want to ask if there is any way to expand the amounts of rows oracle
takes in every copy - paste.
because as my settings are now, when copying and pasting, oracle has a
very limited number of rows that can be inserted, a time.
can this limit be extended and how?
Thanks for any help

On Jan 8, 6:44*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
How are you pasting the rows into Oracle?

If you have Microsoft Access, one simple method is:
* Create a link to the Excel spreadsheet in Access
* Create a link to the Oracle table in Access
* Create a simple query in Access that selects all rows from the Excel
spreadsheet
* Modify the query type of the simple query to be an append query, and
instruct it to append to the Oracle table
* Excute the simple query that has been modified to be an append query
Access will insert all of the rows from the Excel spreadsheet into the
Oracle table without requiring copy and paste.

Another method is to use a macro in Excel to insert each row into the
Oracle database, but this is a bit more difficult to code than the
above method.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jan 8, 6:44*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
How are you pasting the rows into Oracle?

If you have Microsoft Access, one simple method is:
* Create a link to the Excel spreadsheet in Access
* Create a link to the Oracle table in Access
* Create a simple query in Access that selects all rows from the Excel
spreadsheet
* Modify the query type of the simple query to be an append query, and
instruct it to append to the Oracle table
* Excute the simple query that has been modified to be an append query
Access will insert all of the rows from the Excel spreadsheet into the
Oracle table without requiring copy and paste.

Another method is to use a macro in Excel to insert each row into the
Oracle database, but this is a bit more difficult to code than the
above method.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jan 8, 6:44*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
How are you pasting the rows into Oracle?

If you have Microsoft Access, one simple method is:
* Create a link to the Excel spreadsheet in Access
* Create a link to the Oracle table in Access
* Create a simple query in Access that selects all rows from the Excel
spreadsheet
* Modify the query type of the simple query to be an append query, and
instruct it to append to the Oracle table
* Excute the simple query that has been modified to be an append query
Access will insert all of the rows from the Excel spreadsheet into the
Oracle table without requiring copy and paste.

Another method is to use a macro in Excel to insert each row into the
Oracle database, but this is a bit more difficult to code than the
above method.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jan 8, 6:44*am, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
How are you pasting the rows into Oracle?

If you have Microsoft Access, one simple method is:
* Create a link to the Excel spreadsheet in Access
* Create a link to the Oracle table in Access
* Create a simple query in Access that selects all rows from the Excel
spreadsheet
* Modify the query type of the simple query to be an append query, and
instruct it to append to the Oracle table
* Excute the simple query that has been modified to be an append query
Access will insert all of the rows from the Excel spreadsheet into the
Oracle table without requiring copy and paste.

Another method is to use a macro in Excel to insert each row into the
Oracle database, but this is a bit more difficult to code than the
above method.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Hi Charles these methods seem very powerful, but i m not sure i know
how to do them, but i think i am using something 95% similar to the
second methos you talked about, the 'macro' one.
i just wrote a VBA, with an insert into formula in Excel, so i
generate the data in excel, i save the rows in a txt file and from
there i do a crete table in oracle, and i paste the values i have in
the txt so the thing i am copying and pasting is kind of this:
creat table_XXX(
sal_x varchar2(12),
sal_prod ........)
insert into table table_xxx value(....,....,...);
.............
.........
commit;

thats how i know to do it at the present time,
but since you have something better, i would like you to show me some
info. on any valuable documentations about it.
Thanks a lot.

Hi Charles these methods seem very powerful, but i m not sure i know
how to do them, but i think i am using something 95% similar to the
second methos you talked about, the 'macro' one.
i just wrote a VBA, with an insert into formula in Excel, so i
generate the data in excel, i save the rows in a txt file and from
there i do a crete table in oracle, and i paste the values i have in
the txt so the thing i am copying and pasting is kind of this:
creat table_XXX(
sal_x varchar2(12),
sal_prod ........)
insert into table table_xxx value(....,....,...);
.............
.........
commit;

thats how i know to do it at the present time,
but since you have something better, i would like you to show me some
info. on any valuable documentations about it.
Thanks a lot.

Hi Charles these methods seem very powerful, but i m not sure i know
how to do them, but i think i am using something 95% similar to the
second methos you talked about, the 'macro' one.
i just wrote a VBA, with an insert into formula in Excel, so i
generate the data in excel, i save the rows in a txt file and from
there i do a crete table in oracle, and i paste the values i have in
the txt so the thing i am copying and pasting is kind of this:
creat table_XXX(
sal_x varchar2(12),
sal_prod ........)
insert into table table_xxx value(....,....,...);
.............
.........
commit;

thats how i know to do it at the present time,
but since you have something better, i would like you to show me some
info. on any valuable documentations about it.
Thanks a lot.

Hi Charles these methods seem very powerful, but i m not sure i know
how to do them, but i think i am using something 95% similar to the
second methos you talked about, the 'macro' one.
i just wrote a VBA, with an insert into formula in Excel, so i
generate the data in excel, i save the rows in a txt file and from
there i do a crete table in oracle, and i paste the values i have in
the txt so the thing i am copying and pasting is kind of this:
creat table_XXX(
sal_x varchar2(12),
sal_prod ........)
insert into table table_xxx value(....,....,...);
.............
.........
commit;

thats how i know to do it at the present time,
but since you have something better, i would like you to show me some
info. on any valuable documentations about it.
Thanks a lot.

"Totti" <saliba.toufic.george (AT) gmail (DOT) com> wrote

Use sql loader. See the documentation under utlities. Much faster. Also
it looks like in your example you are storing numbers as text. Don't do
that.
Jim