Hi Salahuddin,
I know this but the example given below was for Sachi, because he said
in his reply that putting select inside a values clause of insert works
in Oracle 9i. Since I couldn't get the thing working, I thought maybe I
was missing something here.
Thanks and regards,
Vikas Bhat
Senior Software Engineer,
FBSI Pvt Ltd.,
Embassy Golf Links Business Park,
Intermediate Ring Road,
Bangalore - 560071
Email - vikas.[Email address protected]
Ph:91-80-56916482
Mob:91-9845589149
Those who aspire for the light of sun; need to bear its heat.
Any comments or statements made in this email are not necessarily those
of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity
Investments group companies. The information transmitted is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material. If you have received this in
error, please contact the sender and delete the material from any
computer. All e-mails sent from or to Fidelity Business Services India
Pvt. Ltd. may be subject to our monitoring procedures.
-----Original Message-----
From: Salahuddin
[mailto:oracledba-ezmlmshield-x65995950.[Email address protected]
Sent: Wednesday, September 28, 2005 6:30 PM
To: LazyDBA Discussion
Subject: RE: Insert problem
Try this
insert into t1(a,b) select 1, 2 from dual;
Regards,
Salahuddin
-----Original Message-----
From: Bhat Vikas
[mailto:oracledba-ezmlmshield-x98456815.[Email address protected]
Sent: Wednesday, September 28, 2005 18:18
To: LazyDBA Discussion
Subject: RE: Insert problem
Hi Sachi,
I work on Oracle 9i DB and it is not allowed in Oracle 9i also. Here is
a sample test that I did.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production PL/SQL
Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.5.0 - Production NLSRTL Version 9.2.0.5.0
- Production
SQL> set line 100
SQL> desc t1
Name Null? Type
----------------------------------------------------- --------
------------------------------------
A NUMBER
B NUMBER
SQL> insert into t1(a) values(select 1 from dual);
insert into t1(a) values(select 1 from dual)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> insert into t1(a,b) values(select 1, 2 from dual);
insert into t1(a,b) values(select 1, 2 from dual)
*
ERROR at line 1:
ORA-00936: missing expression
Am I missing something here ? Can you send me a sample of how it works
in Oracle 9i ?
Warm Regards,
Vikas Bhat
Senior Software Engineer,
FBSI Pvt Ltd.,
Embassy Golf Links Business Park,
Intermediate Ring Road,
Bangalore - 560071
Email - vikas.[Email address protected]
Ph:91-80-56916482
Mob:91-9845589149
Those who aspire for the light of sun; need to bear its heat.
Any comments or statements made in this email are not necessarily those
of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity
Investments group companies. The information transmitted is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material. If you have received this in
error, please contact the sender and delete the material from any
computer. All e-mails sent from or to Fidelity Business Services India
Pvt. Ltd. may be subject to our monitoring procedures.
-----Original Message-----
From: Sachidananda_T [mailto:oracledba-ezmlmshield-x53891923.[Email
address protected]
Sent: Wednesday, September 28, 2005 5:29 PM
To: LazyDBA Discussion
Subject: RE: Insert problem
Hi Vikas,
In Oracle 9i you can use SELECT statements in VALUES clause.
I have used it many times.
Regards
Sachi
-----Original Message-----
From: Bhat Vikas [mailto:Vikas.[Email address protected]
Sent: Wednesday, September 28, 2005 5:04 PM
To: Sachidananda_T
Subject: RE: Insert problem
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret **from
this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
Hi
The syntax you are using to insert is incorrect. The syntax is -
INSERT INTO EMP(EMPID,ENAME)
SELECT MAX(EMPID),'DFGH'
FROM emp_reg
WHERE ENAME='SACHI'
Note that you cannot use select within a values clause.
Vikas Bhat
Senior Software Engineer,
FBSI Pvt Ltd.,
Embassy Golf Links Business Park,
Intermediate Ring Road,
Bangalore - 560071
Email - [Email address protected]
Ph:91-80-56916482
Mob:91-9845589149
Those who aspire for the light of sun; need to bear its heat.
Any comments or statements made in this email are not necessarily those
of Fidelity Business Services India Pvt. Ltd. or any of the Fidelity
Investments group companies. The information transmitted is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material. If you have received this in
error, please contact the sender and delete the material from any
computer. All e-mails sent from or to Fidelity Business Services India
Pvt. Ltd. may be subject to our monitoring procedures.
-----Original Message-----
From: Sachidananda_T
[Email address protected]
Sent: Wednesday, September 28, 2005 11:12 AM
To: LazyDBA Discussion
Subject: Insert problem
Hi,
A statement like this sometimes gives me Cannot insert null into EMPID
column even though EMP_REG has a record with ename as SACHI.
This is very strange with oracle.
INSERT INTO EMP(EMPID,ENAME) VALUES( (SELECT MAX(EMPID) FROM EMP_REG
WHERE ENAME='SACHI'),'DFGH');
Of course I can replace this as
DECLARE
MAX_EMP NUMER:=0;
BEGIN
SELECT MAX(EMPID) INTO MAX_EMP FROM EMP_REG);
INSERT INTO EMP(EMPID,ENAME) VALUES( MAX_EMP,'DFGH');
END;
And this works fine.
But I would like to know why the above INSERT has failed.
Regards
Sachi
DISCLAIMER:
This email (including any attachments) is intended for the sole use of
the intended recipient/s and may contain material that is CONFIDENTIAL
AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or
copying or distribution or forwarding of any or all of the contents in
this message is STRICTLY PROHIBITED. If you are not the intended
recipient, please contact the sender by email and delete all copies;
your cooperation in this regard is appreciated.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
DISCLAIMER:
This email (including any attachments) is intended for the sole use of
the intended recipient/s and may contain material that is CONFIDENTIAL
AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or
copying or distribution or forwarding of any or all of the contents in
this message is STRICTLY PROHIBITED. If you are not the intended
recipient, please contact the sender by email and delete all copies;
your cooperation in this regard is appreciated.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page