Compiled Messages: ------------------------------------------------------------ Message no. 7075 Posted by Bikash Adhikari (badhikari) on Wednesday, March 29, 2006 12:18am Subject: Drop Column Can one drop a column from a table? ------------------------------------------------------------ Message no. 7076[Branch from no. 7075] Posted by Rohini Shrestha (rshrestha) on Wednesday, March 29, 2006 12:20am Subject: Re: Drop Column yes, we can drop a column fro a table by using this command ALTER TABLE table_name DROP COLUMN column_name; Rohini ------------------------------------------------------------ Message no. 7077[Branch from no. 7075] Posted by Sagun Piya (srpiya2) on Wednesday, March 29, 2006 12:27am Subject: Re: Drop Column Yes, Bikash we can drop column from the table under certain condition. Sagun ------------------------------------------------------------ Message no. 7078 Posted by Rohini Shrestha (rshrestha) on Wednesday, March 29, 2006 12:30am Subject: Trigger!!! Can any one tell me the difference between trigger and procedure? Is it better to put code on trigger or on procedures? Rohini ------------------------------------------------------------ Message no. 7079[Branch from no. 7078] Posted by Bikash Adhikari (badhikari) on Wednesday, March 29, 2006 12:30am Subject: Re: Trigger!!! In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers. Hope it was help. Bikash ------------------------------------------------------------ Message no. 7083[Branch from no. 7079] Posted by Krishnamurth Ashwini (kashwini) on Wednesday, March 29, 2006 1:06am Subject: Re: Trigger!!! Wow bikash...that was pretty fast....how did you do that?? Ash ------------------------------------------------------------ Message no. 7084[Branch from no. 7077] Posted by Venkat Munagala (vrmunagala) on Wednesday, March 29, 2006 1:16am Subject: Re: Drop Column Yes, we can drop a column frm a table. ------------------------------------------------------------ Message no. 7085[Branch from no. 7075] Posted by Waseem Manzoor (wmanzoor) on Wednesday, March 29, 2006 11:30am Subject: Re: Drop Column Yes we can drop the column and I think that column should not have any value. Waseem ------------------------------------------------------------ Message no. 7086[Branch from no. 7078] Posted by Waseem Manzoor (wmanzoor) on Wednesday, March 29, 2006 11:40am Subject: Re: Trigger!!! A trigger is a special PL/SQL procedure that fires, or executes, in response to a specific triggering event. So Actually there is no difference between these two.Both are same. Trigger works for big objects and stored procedure works for small object. Triggers are back dated and stored procedures are new. Hope this will help. Waseem Manzoor ------------------------------------------------------------ Message no. 7089[Branch from no. 7085] Posted by Rhonda Nichols (renichols2) on Wednesday, March 29, 2006 12:50pm Subject: Re: Drop Column Test it out. I did and was surprised what I found. I didn't think you would be able to if there was data or constraints but this is what I found: Yes you can drop columns from a table that you own or have the system privelege "drop any table". If there is data, it will be gone. If there are constraints, they will be gone... -Rhonda ------------------------------------------------------------ Message no. 7090 Posted by Rhonda Nichols (renichols2) on Wednesday, March 29, 2006 12:57pm Subject: JOKE includes attachment Before DSL days.... ------------------------------------------------------------ Message no. 7092[Branch from no. 7075] Posted by Imran Pathan (ipathan) on Wednesday, March 29, 2006 1:38pm Subject: Re: Drop Column Yes surely we can drop a column, but the condition is that the column should not have any value. That is the column should not effect any changes. Imran ------------------------------------------------------------ Message no. 7093[Branch from no. 7075] Posted by Bikash Adhikari (badhikari) on Wednesday, March 29, 2006 3:36pm Subject: Re: Drop Column Thank you guys.....i did tried doing it and was also kinna suprised to see the results.... BIkash ------------------------------------------------------------ Message no. 7094[Branch from no. 7079] Posted by Gnaneshwar Bukka (gbukka) on Wednesday, March 29, 2006 5:48pm Subject: Re: Trigger!!! Hey Bikash.....thks for the information and you were lightening fast...wow..... Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 7095[Branch from no. 7090] Posted by Gnaneshwar Bukka (gbukka) on Wednesday, March 29, 2006 5:51pm Subject: Re: JOKE Nasty customer care....anyways..nice joke... Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 7099[Branch from no. 7093] Posted by Abhash Bhandary (abhandary) on Wednesday, March 29, 2006 8:17pm Subject: Re: Drop Column yea I was surprised too the first time i found out that we actually could Abhash ------------------------------------------------------------ Message no. 7101[Branch from no. 7089] Posted by Harold Harris (hjharris2) on Wednesday, March 29, 2006 10:16pm Subject: Re: Drop Column Excellent explanation Rhonda. I also wondered if a column could be dropped containing data. HH ------------------------------------------------------------ Message no. 7102[Branch from no. 7086] Posted by Rohini Shrestha (rshrestha) on Thursday, March 30, 2006 3:50am Subject: Re: Trigger!!! Thankx Bikash and Waseem for nice explanation. rohini ------------------------------------------------------------ Message no. 7103[Branch from no. 7079] Posted by Antoinette Lockett (arlockett) on Thursday, March 30, 2006 2:17pm Subject: Re: Trigger!!! BIKASH, THANK YOU FOR THE INFORMATION IT WAS VERY HELFPFUL. ------------------------------------------------------------ Message no. 7104 Posted by Rohini Shrestha (rshrestha) on Thursday, March 30, 2006 2:17pm Subject: project 7 hey guys, I hope everyone has finished project 7. Even I did, but I am still quite confuse with schema part. Can anyone provide me detail exlanation of question number 1? when I use the following syntax....it did not work..can anyone provide me example of the following syntax? create table [schema.] table_name .......... rohini PS: I did not mean to ask what is schema and also I did not mean to ask any code :-) ------------------------------------------------------------ Message no. 7105[Branch from no. 7102] Posted by Venkat Munagala (vrmunagala) on Thursday, March 30, 2006 2:40pm Subject: Re: Trigger!!! Waseem that was a great explanantion. ------------------------------------------------------------ Message no. 7106[Branch from no. 7104] Posted by Krishnamurth Ashwini (kashwini) on Thursday, March 30, 2006 2:45pm Subject: Re: project 7 Hi Rohini, As per question 1..We are required to create tables customer and cust_order in scott/tiger schema. Explanation: Customer is a name of a table which can be present in any schema other than scott. In order to uniquely identify the customer table which is within the scott schema you need to use a standard notation. That is: CREATE TABLE schema.table_name (you do not need paranthesis here...as you have in your code) Ash ------------------------------------------------------------ Message no. 7107[Branch from no. 7106] Posted by Rohini Shrestha (rshrestha) on Thursday, March 30, 2006 3:01pm Subject: Re: project 7 Ash, I have logged in as 'system' but still it says insufficient privileges. Why is that? rohini ------------------------------------------------------------ Message no. 7108[Branch from no. 7099] Posted by Rohini Shrestha (rshrestha) on Thursday, March 30, 2006 3:03pm Subject: Re: Drop Column I tried to drop a table with foreign key constraint in it with a command 'drop any table' but it did not work. I think, we have to drop constraints( foreign but not primary) before executing this code. Rohini ------------------------------------------------------------ Message no. 7109[Branch from no. 7107] Posted by Krishnamurth Ashwini (kashwini) on Thursday, March 30, 2006 3:05pm Subject: Re: project 7 Rohini, Did you try it by logging in as 'SCOTT'??? Try it once and see and please let me know what you get.. Ash ------------------------------------------------------------ Message no. 7110[Branch from no. 7109] Posted by Rohini Shrestha (rshrestha) on Thursday, March 30, 2006 3:18pm Subject: Re: project 7 Well, it works fine now. thankx Ash...but shouldn't we be able to do it thorugh system? Rohini ------------------------------------------------------------ Message no. 7111[Branch from no. 7110] Posted by Krishnamurth Ashwini (kashwini) on Thursday, March 30, 2006 3:20pm Subject: Re: project 7 Well, It should be...because I was able to create the customer table in scott schema by logging in as SYSTEM too..I am not sure why you weren't able to do it.. Ash ------------------------------------------------------------ Message no. 7112[Branch from no. 7078] Posted by Sagun Piya (srpiya2) on Thursday, March 30, 2006 9:35pm Subject: Re: Trigger!!! Helpful information. U guys are awesome Sagun ------------------------------------------------------------ Message no. 7115[Branch from no. 7075] Posted by Gwendolyn Mobley (gdmobley) on Thursday, March 30, 2006 11:36pm Subject: Re: Drop Column yes by using the ALTER TABLE command ------------------------------------------------------------ Message no. 7118[Branch from no. 7079] Posted by Gwendolyn Mobley (gdmobley) on Thursday, March 30, 2006 11:39pm Subject: Re: Trigger!!! great info...thanks ------------------------------------------------------------ Message no. 7119[Branch from no. 7106] Posted by Gwendolyn Mobley (gdmobley) on Thursday, March 30, 2006 11:44pm Subject: Re: project 7 Ash, thanks...this is the best describtion I've seen yet. G ------------------------------------------------------------ Message no. 7124[Branch from no. 7108] Posted by Alex Auffenorde (aaauffenorde) on Friday, March 31, 2006 3:31am Subject: Re: Drop Column You should try to use the CASCADE command when dropping tables with constraints. The CASCADE will ensure the tables are dropped. Alex ------------------------------------------------------------ Message no. 7128[Branch from no. 7119] Posted by Abhash Bhandary (abhandary) on Friday, March 31, 2006 10:42am Subject: Re: project 7 Thanx for the good explanation Ash Abhash ------------------------------------------------------------ Message no. 7129[Branch from no. 7102] Posted by Abhash Bhandary (abhandary) on Friday, March 31, 2006 10:42am Subject: Re: Trigger!!! Good one Abhash ------------------------------------------------------------ Message no. 7133[Branch from no. 7111] Posted by Bikash Adhikari (badhikari) on Friday, March 31, 2006 4:35pm Subject: Re: project 7 Thanka Ash ... this discussion has been a great help ... BIkash ------------------------------------------------------------ Message no. 7135 Posted by Kelsey Pooley (kjpooley) on Friday, March 31, 2006 10:34pm Subject: Schema vs. User According to the text we read from the website, the schema and the user are basically the same thing. If so then how can we all use the Scott/Tiger schema to create these tables? Wouldn't they already exist from the time the first person created them? Why wouldn't we just use our own usernames and passwords like we normally do? ------------------------------------------------------------ Message no. 7136[Branch from no. 7135] Posted by Krishnamurth Ashwini (kashwini) on Friday, March 31, 2006 11:41pm Subject: Re: Schema vs. User Dear Kelsey, I am not sure what you mean by our own username and password (your last line). I am assuming this is what you are asking about.... We are not creating a table on a central server...We all have installed oracle on our own systems and I hope you have it too. When you login in through SQL plus essentially there is no table created yet. Ash ------------------------------------------------------------ Message no. 7142[Branch from no. 7075] Posted by Muhammad Latif (mlatif) on Saturday, April 1, 2006 2:21am Subject: Re: Drop Column Hey Bikash: you can drop a column from a table using the ALTER table command but if you have constraints enforced it will give an error. Arsal ------------------------------------------------------------ Message no. 7143[Branch from no. 7112] Posted by Muhammad Latif (mlatif) on Saturday, April 1, 2006 2:23am Subject: Re: Trigger!!! Hey Bikash: Nice and fast! Arsal ------------------------------------------------------------ Message no. 7145[Branch from no. 7135] Posted by Waseem Manzoor (wmanzoor) on Saturday, April 1, 2006 3:36am Subject: Re: Schema vs. User When we develop a project we want to manage our tables. So we create our own schema so that we can put all our database related to that project in our specific created schema. That is the main reason I think we do not use bydefault scott/tiger schema that oracle provides. Waseem Manzoor ------------------------------------------------------------ Message no. 7146[Branch from no. 7128] Posted by Waseem Manzoor (wmanzoor) on Saturday, April 1, 2006 3:42am Subject: Re: project 7 you can do in two ways. one is either logged as scott/ tiger and the other is mention in user code to connect schema scott/tiger and the syntax is CONNECT SCOTT/TIGER; both way it will work. if u r logged as scott/tiger then u do not need to mention schema name there it is just optional. Hope it will help Waseem Manzoor ------------------------------------------------------------ Message no. 7153 Posted by Kelsey Pooley (kjpooley) on Saturday, April 1, 2006 1:34pm Subject: Strange Problem I have been trying since yesterday afternoon to connect to SQL Plus....when I enter my username, password and SSID the program locks up and I have to ctrl- alt-delete to get out of it. I was able to connect without any problem on Tuesday. I am using the same username, password and SSID that I have always used (it doesn't work with Scott/Tiger either). I am completely stumped. Any ideas? Thanks, Kelsey ------------------------------------------------------------ Message no. 7155[Branch from no. 7143] Posted by Venkat Munagala (vrmunagala) on Saturday, April 1, 2006 1:51pm Subject: Re: Trigger!!! Great information. ------------------------------------------------------------ Message no. 7160[Branch from no. 7085] Posted by Phanindra Maddhi (pmaddhi) on Saturday, April 1, 2006 4:44pm Subject: Re: Drop Column Yaaa one can definitely drop a column from a table, but the column should not have any value and it should be free of any constraints. ------------------------------------------------------------ Message no. 7161[Branch from no. 7153] Posted by Sagun Piya (srpiya2) on Saturday, April 1, 2006 4:47pm Subject: Re: Strange Problem by entering through system you have to type the command Try ALTER USER ACCOUNT UNLOCK; then you will be able to enter through Scott/tiger Sagun ------------------------------------------------------------ Message no. 7162[Branch from no. 7079] Posted by Phanindra Maddhi (pmaddhi) on Saturday, April 1, 2006 4:47pm Subject: Re: Trigger!!! Procedures are faster as the triggers are to be recompiled every time before execution.But basically both are same. ------------------------------------------------------------ Message no. 7164[Branch from no. 7146] Posted by Phanindra Maddhi (pmaddhi) on Saturday, April 1, 2006 4:48pm Subject: Re: project 7 I agree with Waseem. ------------------------------------------------------------ Message no. 7170[Branch from no. 7090] Posted by Imran Pathan (ipathan) on Saturday, April 1, 2006 4:55pm Subject: Re: JOKE good joke. Imran ------------------------------------------------------------ Message no. 7171[Branch from no. 7145] Posted by Imran Pathan (ipathan) on Saturday, April 1, 2006 4:56pm Subject: Re: Schema vs. User Good answer.I agree with waseem Imran ------------------------------------------------------------ Message no. 7175[Branch from no. 7161] Posted by Kelsey Pooley (kjpooley) on Saturday, April 1, 2006 7:55pm Subject: Re: Strange Problem I can't even get to the sql> prompt. As soon as I enter my username, password and SID the program locks up. I have even tried reinstalling Oracle10g thinking that might help but it didn't. Maybe there is something wrong with my computer? Thanks for your suggestion Sagun. Kelsey ------------------------------------------------------------ Message no. 7176[Branch from no. 7175] Posted by Gwendolyn Mobley (gdmobley) on Saturday, April 1, 2006 8:15pm Subject: Re: Strange Problem Kelsey, by now you may have corrected your problem, but I was wondering. When you reinstalled Oracle, did you change any of the username/passwords? If your computer were the porblem I don't think you would even be able to access Sql. Just thinking out loud. Gwen ------------------------------------------------------------ Message no. 7177[Branch from no. 7146] Posted by Gwendolyn Mobley (gdmobley) on Saturday, April 1, 2006 8:42pm Subject: Re: project 7 Waseem, this information REALLY cleared up the final problem I had....so thanks a million for the info. Gwen ------------------------------------------------------------ Message no. 7180[Branch from no. 7075] Posted by Swarna Bangaru (sbangaru) on Saturday, April 1, 2006 10:09pm Subject: Re: Drop Column yes we can drop a column until there are not constraints attached. ------------------------------------------------------------ Message no. 7181[Branch from no. 7078] Posted by Swarna Bangaru (sbangaru) on Saturday, April 1, 2006 10:11pm Subject: Re: Trigger!!! Thanks for the info guys, I learnt a lot. ------------------------------------------------------------ Message no. 7182[Branch from no. 7146] Posted by Swarna Bangaru (sbangaru) on Saturday, April 1, 2006 10:13pm Subject: Re: project 7 It was very helpful waseem, thank you ------------------------------------------------------------ Message no. 7184[Branch from no. 7170] Posted by Venkat Munagala (vrmunagala) on Saturday, April 1, 2006 10:31pm Subject: Re: JOKE good one ------------------------------------------------------------ Message no. 7185[Branch from no. 7182] Posted by Venkat Munagala (vrmunagala) on Saturday, April 1, 2006 10:32pm Subject: Re: project 7 Good information Waseem ------------------------------------------------------------ Message no. 7190[Branch from no. 7084] Posted by Kelli Lemon (kmlemon) on Saturday, April 1, 2006 11:58pm Subject: Re: Drop Column I agree columns can be dropped from tables ------------------------------------------------------------ Message no. 7193[Branch from no. 7176] Posted by Krupa Kandavalli (kkandavalli) on Sunday, April 2, 2006 1:20am Subject: Re: Strange Problem I dont think its the problem with the computer, once I had the same problem but when I reisntalled oracle10g, it worked fine and I was able to login. ------------------------------------------------------------ Message no. 7194[Branch from no. 7184] Posted by Krupa Kandavalli (kkandavalli) on Sunday, April 2, 2006 1:24am Subject: Re: JOKE Good joke. ------------------------------------------------------------ Message no. 7195[Branch from no. 7193] Posted by Kelsey Pooley (kjpooley) on Sunday, April 2, 2006 8:40am Subject: Re: Strange Problem I tried reinstalling Oracle and when I did I changed the login info. It still seems to be doing the same thing....I go to start, Oracle Home, application development, sql plus. I get the login pop up box, enter my information and hit enter. It goes to the white SQL plus screen where I normally get the sql> prompt, but it just sits there, never giving me the prompt. I have to go to the task manager and end task to get out of it. I think I will try running my recovery disks for my computer and installing Oracle again. It seems strange that it worked a few days ago and now it won't. Thanks for all of your suggestions. Kelsey ------------------------------------------------------------ Message no. 7200[Branch from no. 7185] Posted by Abhash Bhandary (abhandary) on Sunday, April 2, 2006 9:14am Subject: Re: project 7 Thanx for the info Waseem Abhash ------------------------------------------------------------ Message no. 7201[Branch from no. 7171] Posted by Abhash Bhandary (abhandary) on Sunday, April 2, 2006 9:15am Subject: Re: Schema vs. User good one Waseem..thanx Abhash ------------------------------------------------------------