OraFAQ Forum: Client Tools » Can't INSERT INTO table

Home » Other » Client Tools » Can't INSERT INTO table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can't INSERT INTO table [message #270572]

Thu, 27 September 2007 10:07

simeet
Messages: 3
Registered: September 2007

Junior Member

In PL/SQL DEveloper, when I run this simple INSERT statement

INSERT INTO rel_job_owner (dept, dept_sub, job_type_code, client_class_code)
VALUES('TOA', 'SIS', 'RetailTOAINACAT', NULL);

COMMIT;

I get an ORA-00911 error saying invalid charactar.

When I take out the ; and run this

INSERT INTO rel_job_owner (dept, dept_sub, job_type_code, client_class_code)
VALUES('TOA', 'SIS', 'RetailTOAINACAT', NULL)

COMMIT;

I get an ORA-00933 error saying SQL command not properly ended

If I run without the COMMIT statement, it runs okay but does not populate the table.

Report message to a moderator

Re: Can't INSERT INTO table [message #270573 is a reply to message #270572]

Thu, 27 September 2007 10:10

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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).

Copy and paste the execution instead of telling what happened.
If you can't with your tool, do it with SQL*Plus.

Regards
Michel

Report message to a moderator

Re: Can't INSERT INTO table [message #270574 is a reply to message #270572]

Thu, 27 September 2007 10:24

scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England

Senior Member

In PL/SQL Developer you do the insert statement without the ; and to commit you use the commit button (green arrow pointing downwards onto a disk).

The rollback button is next to it. These will both highlighted when you have data in your session to commit.

Report message to a moderator

Re: Can't INSERT INTO table [message #270576 is a reply to message #270574]

Thu, 27 September 2007 10:28

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So remove "commit;" from your statements.

Regards
Michel

Report message to a moderator

Re: Can't INSERT INTO table [message #270577 is a reply to message #270572]

Thu, 27 September 2007 10:49

simeet
Messages: 3
Registered: September 2007

Junior Member

Thanks for that.

Is there specific code to insert multiple rows for PL/SQL? Or do I have to do it one row at a time?

SQL> INSERT INTO rel_job_owner
2 (dept, dept_sub, job_type_code, client_class_code)
3 VALUES
4 ('TOA', 'SIS', 'RetailTOAINAnnuity', NULL)
5 ('TOA', 'SIS', 'RetailTOAINDRIP', NULL)
6 ('TOA', 'SIS', 'RetailTOAInNonACAT', NULL)
7 /
('TOA', 'SIS', 'RetailTOAINDRIP', NULL)
*
ERROR at line 5:
ORA-00933: SQL command not properly ended

Report message to a moderator

Re: Can't INSERT INTO table [message #270583 is a reply to message #270577]

Thu, 27 September 2007 11:00

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use multiple inserts.
Or
insert into ...
( select <list of values1> from dual union all select <list of values2> from dual union all ...)

Regards
Michel

Report message to a moderator

Re: Can't INSERT INTO table [message #270603 is a reply to message #270583]

Thu, 27 September 2007 11:29

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Or use INSERT ALL (if available):

SQL> create table test (id number, name varchar2(20));

Table created.

SQL> insert all
  2    into test values (1, 'Little')
  3    into test values (2, 'Foot')
  4    into test values (3, 'Mala')
  5    into test values (4, 'Capa')
  6  select * from dual;

4 rows created.

SQL>

Report message to a moderator

Re: Can't INSERT INTO table [message #270617 is a reply to message #270583]

Thu, 27 September 2007 11:55

simeet
Messages: 3
Registered: September 2007

Junior Member

Thanks so much.

The UNION ALL DUAL thingy worked a charm!

Report message to a moderator

Previous Topic:	Explain Plan in different Tablespace
Next Topic:	Generating Insert scripts for BLOB

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 03 08:36:30 CST 2025