Home » SQL & PL/SQL » SQL & PL/SQL » insert statements
insert statements [message #266630] Tue, 11 September 2007 04:39 Go to next message
gstijl
Messages: 3
Registered: September 2007
Junior Member
I created a txt.file with multiple insert commands
insert into table values (value1)
insert into table values (value2)
insert into table values (......)
insert into table values (valuex)

The seperate insert statement are errorfree.
When I start via sql-plus the txt.file an error message appears:
sql-command not properly ended a line 1.
Even when I put an ; at every end the same error appears.
How can I start my file so I can start all the insert statements with one command?

Rgds,

Gerry

Re: insert statements [message #266631 is a reply to message #266630] Tue, 11 September 2007 04:41 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

insert all
Re: insert statements [message #266635 is a reply to message #266630] Tue, 11 September 2007 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
put a ; at the end of the statements.

Regards
Michel
Re: insert statements [message #266647 is a reply to message #266631] Tue, 11 September 2007 05:23 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Arju,
sorry but this makes no sense. (And Yes, I am well aware of INSERT ALL statement) but simply answering with insert all is pointless.
My 2c
Re: insert statements [message #266653 is a reply to message #266630] Tue, 11 September 2007 05:36 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

No Pablolee, you are not right. We should not give full answer.We should give Just a hint and the questioner should search based on that.He may use google by searching "insert all oracle" keyword.

We are not dedicated to help.

After that he may get this info about insert all.Like,


INSERT ALL
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date, sales_sun)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
      INTO sales (prod_id, cust_id, time_id, amount)
      VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
   SELECT product_id, customer_id, weekly_start_date, sales_sun,
      sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
      FROM sales_input_table;


A little more we can proceed by providing link like,
url
Re: insert statements [message #266671 is a reply to message #266653] Tue, 11 September 2007 06:40 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
I strongly disagree with you Arju. The level of the OPs knowledge appears to be relatively basic. Simply answering with insert all makes no sense. If you had replied with something like
"Check out the documentation for INSERT ALL" That would have been useful. Your post was not.
[Edit]
Addendum
In addition to it being ridiculously vague, it is an innappropriate solution in this instance.

[Updated on: Tue, 11 September 2007 06:42]

Report message to a moderator

Re: insert statements [message #266696 is a reply to message #266630] Tue, 11 September 2007 07:44 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Show us the definition of your table, the real insert statements, not what they "look like" and the full error message.
Re: insert statements [message #266964 is a reply to message #266630] Wed, 12 September 2007 02:19 Go to previous messageGo to next message
gstijl
Messages: 3
Registered: September 2007
Junior Member
the definition of the table :

CREATE TABLE TB_CRN002
(
PROFILEUID VARCHAR2(10 BYTE),
TARGETID VARCHAR2(50 BYTE),
USERNAME VARCHAR2(96 BYTE),
NOTES VARCHAR2(128 BYTE)
)
TABLESPACE TS_CRN001
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

The insert statements :
insert into tb_crn002 (profileuid,targetid,username) values ('***','****','****');
insert into tb_crn002 (profileuid,targetid,username) values ('****','****','****')
error ; invalid character
If I leave the ; then i get sql command not properly ended.

The insert all is not an option because I have one big textfile with all insert commands and not a table where i'm copying from.

Any help appreciated...

Gerry
Re: insert statements [message #266971 is a reply to message #266964] Wed, 12 September 2007 02:28 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In SQL*Plus, everything seems to be fine with my 10g:
SQL> CREATE TABLE TB_CRN002
  2  (
  3  PROFILEUID VARCHAR2(10 BYTE),
  4  TARGETID VARCHAR2(50 BYTE),
  5  USERNAME VARCHAR2(96 BYTE),
  6  NOTES VARCHAR2(128 BYTE)
  7  );

Table created.

SQL> edit p.sql

SQL> @p

1 row created.


1 row created.

SQL> $type p.sql
insert into tb_crn002 (profileuid,targetid,username) values ('***','****','****');
insert into tb_crn002 (profileuid,targetid,username) values ('****','****','****');
SQL>

However, two consecutive INSERT (or any other) statements (with no blank line between them) will raise an "invalid character" error when ran separately from TOAD using the "Execute statement" button.

Could you do the copy-paste as I did so that we could see how it looks like on your database?
Re: insert statements [message #266973 is a reply to message #266964] Wed, 12 September 2007 02:31 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In addition,

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).
What is your client tool?

Regards
Michel
Previous Topic: How to unify a query so that it would work on both Oracle and MS SQL Server? (merged b
Next Topic: Datatypes
Goto Forum:
  


Current Time: Thu Feb 06 13:58:52 CST 2025