Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00936 when trying to insert
ORA-00936 when trying to insert [message #353939] Wed, 15 October 2008 14:02 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
CREATE TABLE MYTBL(ID NUMBER,NAME VARCHAR2(200))
 /
 INSERT INTO MYTBL VALUES(1,'ONE')
 /
 INSERT INTO MYTBL VALUES(2,'TWO')
 /


i want to find out the maximum value of id, add 1 to it,
and then insert into table

SQL>  insert into MYTBL(ID,NAME) values(select max(ID) +1 from MYTBL,'' pkg.f_MY(ENUM,'sometext')='2
''');
 insert into MYTBL(ID,NAME) values(select max(ID) +1 from MYTBL,'' pkg.f_MY(ENUM,'sometext')='2''')
                                   *
ERROR at line 1:
ORA-00936: missing expression



this is what i get, also, how to insert this value?

pkg.f_MY(ENUM,'sometext')='2'

i put '' '' at beginning and ending, but didnt work
Re: ORA-00936 when trying to insert [message #353942 is a reply to message #353939] Wed, 15 October 2008 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either:
- insert values
- insert select

Regards
Michel
Re: ORA-00936 when trying to insert [message #353944 is a reply to message #353939] Wed, 15 October 2008 15:04 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@ora1980,
Hint For Insert With Select:
SQL> create table table_test
  2  (
  3  Col_1 number,
  4  Col_2 Varchar2(100)
  5  );

Table created.

SQL> insert into table_test values(2, 'AC');

1 row created.

SQL> insert into table_test values(3, 'BD');

1 row created.

SQL>  select * from table_test;

     COL_1 COL_2
---------- ---------------------------------------------------
         2 AC
         3 BD

2 rows selected.

SQL> select (select max(Col_1)+1 from table_test), 'BC''D' Test_Value from Dual;

(SELECTMAX(COL_1)+1FROMTABLE_TEST) TEST
---------------------------------- ----
                                 4 BC'D

1 row selected.


Try to go through INSERT Statement in Oracle Documentation

Hope this helps.

Regards,
Jo
Re: ORA-00936 when trying to insert [message #353966 is a reply to message #353939] Wed, 15 October 2008 22:52 Go to previous message
halim
Messages: 100
Registered: September 2008
Senior Member

hello

You just add a first bracket at the ID values query. like

(select max(ID) +1 from MYTBL)

insert into MYTBL(ID,NAME) values( (select max(ID) +1 from MYTBL ),'' pkg.f_MY(ENUM,'sometext')='2
''');



Previous Topic: SQl Server 2005 to Oracle 10g Proc conversion
Next Topic: ORA-01799
Goto Forum:
  


Current Time: Fri Dec 02 21:05:55 CST 2016

Total time taken to generate the page: 0.15659 seconds