Home » Other » Client Tools » Can't INSERT INTO table
Can't INSERT INTO table [message #270572] Thu, 27 September 2007 10:07 Go to next message
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.

Re: Can't INSERT INTO table [message #270573 is a reply to message #270572] Thu, 27 September 2007 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Can't INSERT INTO table [message #270574 is a reply to message #270572] Thu, 27 September 2007 10:24 Go to previous messageGo to next message
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.

Re: Can't INSERT INTO table [message #270576 is a reply to message #270574] Thu, 27 September 2007 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So remove "commit;" from your statements.

Regards
Michel
Re: Can't INSERT INTO table [message #270577 is a reply to message #270572] Thu, 27 September 2007 10:49 Go to previous messageGo to next message
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
Re: Can't INSERT INTO table [message #270583 is a reply to message #270577] Thu, 27 September 2007 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Can't INSERT INTO table [message #270603 is a reply to message #270583] Thu, 27 September 2007 11:29 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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>
Re: Can't INSERT INTO table [message #270617 is a reply to message #270583] Thu, 27 September 2007 11:55 Go to previous message
simeet
Messages: 3
Registered: September 2007
Junior Member
Thanks so much.

The UNION ALL DUAL thingy worked a charm!
Previous Topic: Explain Plan in different Tablespace
Next Topic: Generating Insert scripts for BLOB
Goto Forum:
  


Current Time: Thu Mar 28 08:09:46 CDT 2024