Home » SQL & PL/SQL » SQL & PL/SQL » null values inserted through 'INSERT ALL' option (DB:Oracle 9i,9.2.0.1.0,RHEL 2.1 AS)
null values inserted through 'INSERT ALL' option [message #400021] Sat, 25 April 2009 01:13 Go to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends

I'have problem with multiple insert into a table at a time.here i'm sending my sample script for that. i don't understand why it is inserting null values.

SQL> show parameter cursor_sharing;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           [B]SIMILAR[/B]

SQL> create table temp (x int,y int, z int);

Table created.

SQL> insert all                     
  2   into temp(x,y,z) values(1,1,1)
  3   into temp(x,y,z) values(2,2,2)
  4   into temp(x,y,z) values(3,3,3)
  5   select * from dual;           

3 rows created.

SQL> commit;

Commit complete.

SQL> select * from temp;

         X          Y          Z
---------- ---------- ----------



3 rows selected.



valuse inserted but all are null valuse.why it happen.is that the connection with cursor_sharing parameter.please suggest me what to do.

Regards

Jimit
Re: null values inserted through 'INSERT ALL' option [message #400023 is a reply to message #400021] Sat, 25 April 2009 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why it happen

Most likely a bug.

Quote:
.please suggest me what to do.

First upgrade to the latest patchset then if it is still there raise a SR or if you have no support contract upgrade to a newer release.

Regards
Michel
Re: null values inserted through 'INSERT ALL' option [message #400026 is a reply to message #400021] Sat, 25 April 2009 01:28 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
If this is a bug from oracle than it is a blunder of them.how one can work like that, and the amazing thing i have a same configuration on dev server and it is working nice.Means there are no other solution to this query????please suggest me which patch i have to update,e.g.9.2.0.8.0 or change the realease, from 9i to 10g.

Regards

Jimit
Re: null values inserted through 'INSERT ALL' option [message #400027 is a reply to message #400026] Sat, 25 April 2009 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
please suggest me which patch i have to update,e.g.9.2.0.8.0 or change the realease, from 9i to 10g.

It depends only on you.
Standard answer is latest version: 10.2.0.4 or 11.1.0.7

Regards
Michel
Re: null values inserted through 'INSERT ALL' option [message #400028 is a reply to message #400021] Sat, 25 April 2009 01:46 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

You are absolutely right.That it depends on me only,but i personally ask to all of you ,if oracle once document this as syntax of insert and then it is not working means it's feels like i am betrayed by Oracle. Everyone is not able purchase now and then.

Regards and Thanks for your support.
Re: null values inserted through 'INSERT ALL' option [message #400029 is a reply to message #400021] Sat, 25 April 2009 01:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>it's feels like i am betrayed by Oracle
Get a grip.
Folks should test & then deploy.
Do you employ Ready, Fire, Aim procedures?
If so, you get what you deserve.
What is YOUR responsibilty?
Re: null values inserted through 'INSERT ALL' option [message #400030 is a reply to message #400021] Sat, 25 April 2009 02:05 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Blackswan

I have tested this on my development server which is ditto to my live server.Then i put the code on live server.Now how can i tell to my boss that it is not working or i required higher version for that. try to understand my situation. it is not possible to convince the management overnight. I feel sorry if you hurt by my comments,you all moderators are genius than me.I respect you and your comments.

Regards

Re: null values inserted through 'INSERT ALL' option [message #400065 is a reply to message #400028] Sat, 25 April 2009 11:43 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If your application is correctly written, you don't need other setting than cursor_sharing=exact.
There are many and many bugs in all versions with other values (far less with "similar" than with "force") but once again, a correctly written program doesn't need to change this parameter.

Regards
Michel
Previous Topic: virtual columns
Next Topic: Select table based on condition
Goto Forum:
  


Current Time: Thu Dec 08 22:27:12 CST 2016

Total time taken to generate the page: 0.49089 seconds