Compiled Messages: ------------------------------------------------------------ Message no. 338 Posted by Krishnamurth Ashwini (kashwini) on Monday, September 18, 2006 5:49pm Subject: Small doubt-Project-4

Hi all,

I know this may seem like a stupid doubt...but I still want to get it cleared before I submit my Project-4. The second question in Project - 4 is:
2. Change the password of the user "DBSEC" to "sec#1new." (10 points)

I am not sure if the "dot" after "sec#1new" belongs to the password...or is it just a FULL STOP that is misplaced!!

Thanks
,Ash

------------------------------------------------------------ Message no. 339[Branch from no. 338] Posted by Naziya Shaik (snaziya) on Monday, September 18, 2006 10:03pm Subject: Re: Small doubt-Project-4 I dont think that is included in the password. I just changed password to "sec#1new" Naz ------------------------------------------------------------ Message no. 343[Branch from no. 339] Posted by Krishnamurth Ashwini (kashwini) on Monday, September 18, 2006 10:41pm Subject: Re: Small doubt-Project-4 Naziya, Thanks a lot..I was just worried since Dr.Liu had specifically mentioned about correct spelling. I too will ignore the dot. Thanks again, Ash ------------------------------------------------------------ Message no. 350[Branch from no. 339] Posted by Rhonda Nichols (renichols2) on Tuesday, September 19, 2006 11:49am Subject: Re: Small doubt-Project-4 I felt the same way...did not include the . -Rhonda ------------------------------------------------------------ Message no. 351[Branch from no. 338] Posted by Krishnamurth Ashwini (kashwini) on Tuesday, September 19, 2006 3:59pm Subject: Order table Hi again, While creating the order table for project-4 I got this error. ERROR at line 1: ORA-00903: invalid table name There are some suggesstions for this error like: - The table name must begin with a letter. - The table name can not be longer than 30 characters. - The table name must be made up of alphanumeric characters or the following special characters: $, _, and #. - The table name can not be a reserved word. The first three did not make sense in this case..so I checked with the 4th one. When I checked for this suggesstion, it said: This can be due to the uasge of a reserved word. Because of this, it is highly recommended that the names of tables or columns used in a query are enclosed in square brackets, so that the query continues to work even if a name of some table or column it refers to becomes a reserved word. By the way, ORDER is one of the reserved words,as per this website. http://exchange.manifold.net/manifold/manuals/manifold/sql_reference_guide/sql_reserved_words_index.htm Did anyone else have the same problem?? Thanks Ash ------------------------------------------------------------ Message no. 352[Branch from no. 351] Posted by Lakshmikar Padmaraju (lpadmaraju) on Tuesday, September 19, 2006 5:02pm Subject: Re: Order table Aswini, Order is a reserved word in Oracle database, so it won't accept any reserved word as an object name, so create table Order Put “ before and after table name like "order" it will take it. Don’t worry I did the same thing. This is only way you can create table Order. This applies if you have to use reserved word for table columns also. Dr. Liu uses this to test our knowledge. Take care Raju. ------------------------------------------------------------ Message no. 353[Branch from no. 352] Posted by Naziya Shaik (snaziya) on Tuesday, September 19, 2006 6:23pm Subject: Re: Order table Thank you raju, it helped me too Naz ------------------------------------------------------------ Message no. 354[Branch from no. 352] Posted by Gnaneshwar Bukka (gbukka) on Tuesday, September 19, 2006 6:52pm Subject: Re: Order table Hi Raju, I had the same problem, it was working for 'orders' but not for 'order', thank you very much for the information. Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 355[Branch from no. 352] Posted by Krishnamurth Ashwini (kashwini) on Tuesday, September 19, 2006 8:51pm Subject: Re: Order table Thanks Raju..I'll try that. Ash ------------------------------------------------------------ Message no. 356 Posted by Kelsey Pooley (kjpooley) on Wednesday, September 20, 2006 2:18pm Subject: Problem Running Script Hello Everyone, I am having some trouble running my script when I try to run the entire thing. All of my code works when I copy and paste it in sections from notepad to SQL Plus. When I try to run the entire thing, it gets hung up in a couple of places, like in the middle of a command line. The first place it gets stuck is where the PRODUCT_SUPPLIER table is being created. The next place is about half way through inserting the information into the SUPPLIER table. I have tried waiting to see what happens but it just sits there. If I hit enter, it gives the little > symbol as if it is waiting for more information. If I hit enter again, it gives me the sql> prompt. I am wondering if this is just because the script is very long? Did anyone else have this problem? I tried running it on my laptop and my desktop, with the same results. Any ideas? Thanks, Kelsey ------------------------------------------------------------ Message no. 357[Branch from no. 356] Posted by Naziya Shaik (snaziya) on Wednesday, September 20, 2006 2:50pm Subject: Re: Problem Running Script Kelsey, did you check all spellings, its not the same as it was in notepad. we have to change according the given description by professor. check even in the alter statements. Check for the end of the statements. May be you missed some end of statement character at the end of one or two statements. probably, when it is waiting you can hit / or ; just to symbolize end of statement character. Naz ------------------------------------------------------------ Message no. 358[Branch from no. 357] Posted by Kelsey Pooley (kjpooley) on Wednesday, September 20, 2006 3:31pm Subject: Re: Problem Running Script I went through and changed the spellings, but I will take a closer look. Thanks Naziya! Kelsey ------------------------------------------------------------ Message no. 359[Branch from no. 356] Posted by Lakshmikar Padmaraju (lpadmaraju) on Wednesday, September 20, 2006 3:59pm Subject: Re: Problem Running Script There may be syntax error in the script, like when you run on SQLPLUS even if you don't enter semicolon at the end of all sql statement you can execute the statement with \ . But in sql file you have to terminate SQL Sentence with “ ; ” not sqlPlus statements( like PROMPT, set TERMOUT ON etc). I got similar problem lot of times, when i checked the code I found some syntax problem and fixed it. Then it worked well. Also close SQLplus File—Close and restart and see if runs successfully or not. What I do normally is I add part by part code to SQL file and execute, if it worked then I will add next part and check again, like that I continue till I come to end; Hope this helps. Raju. ------------------------------------------------------------ Message no. 360[Branch from no. 359] Posted by Kelsey Pooley (kjpooley) on Wednesday, September 20, 2006 4:49pm Subject: Re: Problem Running Script Thanks Raju. That makes sense. I will give it a try. Kelsey ------------------------------------------------------------ Message no. 361 Posted by Naziya Shaik (snaziya) on Wednesday, September 20, 2006 5:14pm Subject: forgot password This must be a stupid problem --forgetting passwords... I forgot password for SYS user....how can i change or retrieve that password? is there any way or do i have to reinstall 10g( dun tell me that please). ------------------------------------------------------------ Message no. 362[Branch from no. 361] Posted by Naziya Shaik (snaziya) on Wednesday, September 20, 2006 5:30pm Subject: Re: forgot password ok I got it. No worries Thank you ------------------------------------------------------------ Message no. 363[Branch from no. 359] Posted by Daniel Thurston (dsthurston) on Wednesday, September 20, 2006 8:13pm Subject: Re: Problem Running Script I had some of the same issues with my first script. I had everything spellec correctly but no luck. what I did was I think I removed some spaces in a few places. I also restarted sqlplus and eventully got it to work. So you are not alone in experienceing this. DanT ------------------------------------------------------------ Message no. 367[Branch from no. 363] Posted by Ravinder Gaur (rgaur) on Thursday, September 21, 2006 4:34am Subject: Re: Problem Running Script One other thing I had to do was arranging/ordering the drop statements correctly (otherwise the constraints won't let a few tables drop). - Ravi ------------------------------------------------------------ Message no. 368 Posted by Rhonda Nichols (renichols2) on Thursday, September 21, 2006 9:16am Subject: Confused Ok...this is Week #5, Chapter #3, and Project #4. In the project, it wants us to modify user and create 17 tables. Dr. liu said we can use the files provided in the course download, which I have downloaded. Only I do not see a reference to Chapter 03, Only 4,5,6,8, and 10. I suppose I should use chapter 04 and I understand I need to change the names but do we need to add all the constraints/keys? In the directions it says to just create the tables and insert data into one table. So I would say no but what is everyone else doing? -Rhonda ------------------------------------------------------------ Message no. 369[Branch from no. 368] Posted by Lakshmikar Padmaraju (lpadmaraju) on Thursday, September 21, 2006 9:40am Subject: Re: Confused Rhonda, I created all the tables with primary keys and foreign keys as per downloaded files from course website. I am not sure it is required that way or not, but I did any way, it took little time to figure it out the order of tables drop statements. Dr. LIU not specific about Constraints, may be somebody in the campus can talk to him and post the Answer here, it may help somebody who still working on project4. Here is the order you have to drop the table in case you created primary and foreign keys. Hope this helps. SALES_COMMISSION PRODUCT_SUPPLIER PRODUCT_PRICE PRODUCT_INVENTORY; ORDER_LINE EMPLOYEE_RANK ORDER ------ use double quote arround table name because Order is reserved word SHIPMENT_METHOD PAYMENT_METHOD PROMOTION CUSTOMER EMPLOYEE PRODUCT CATEGORY; SUPPLIER DEPARTMENT JOB Raju. ------------------------------------------------------------ Message no. 370[Branch from no. 368] Posted by Krishnamurth Ashwini (kashwini) on Thursday, September 21, 2006 9:48am Subject: Re: Confused Hi Rhonda, I don't think we are asked to add constraints to all tables. We are just asked to create tables and populate only Supplier table. I haven't added any constraints to the tables. As Raju suggessted, somebody on campus can actually talk to Dr.Liu and post us his reply. Ash ------------------------------------------------------------ Message no. 371 Posted by Kelsey Pooley (kjpooley) on Thursday, September 21, 2006 10:26am Subject: Dropping FK Constraint In this project the foreign key constraints don't have actual names. How do you drop the foreign key constraint without naming it? All of the examples I have found deal with foreign keys with names such as: ALTER TABLE chicken ADD CONSTRAINT chickenREFegg FOREIGN KEY (eID) REFERENCES egg(eID); where the name of the constraint is "chickenREFegg" In this case you would just use: ALTER TABLE egg DROP CONSTRAINT eggREFchicken; What if the constraint doesn't have an actual name? Kelsey ------------------------------------------------------------ Message no. 372[Branch from no. 368] Posted by Kelsey Pooley (kjpooley) on Thursday, September 21, 2006 10:37am Subject: Re: Confused Rhonda, The files are in the chapter 4 folder. I am not sure about using the constraints. I have been using them because they are already provided in the script, but Dr. Liu never really mentioned constraints. Kelsey ------------------------------------------------------------ Message no. 373[Branch from no. 369] Posted by Naziya Shaik (snaziya) on Thursday, September 21, 2006 10:58am Subject: Re: Confused Raju, Can't we drop using CASCADE CONSTRAINTS. should that be in an order to drop if we use this kind of drop?? By the way, I will meet Dr.Liu today and will let you people know about constraints issue. Cheers, Naz ------------------------------------------------------------ Message no. 374[Branch from no. 372] Posted by Naziya Shaik (snaziya) on Thursday, September 21, 2006 12:31pm Subject: Re: Confused I went to meet Dr.Liu, but he was not there. I left a message, but i will try to contact him again after 2:00pm or so. Naz ------------------------------------------------------------ Message no. 375[Branch from no. 374] Posted by Rhonda Nichols (renichols2) on Thursday, September 21, 2006 12:43pm Subject: Re: Confused Thanks everybody. Hopefully we will get this all straightened out. Naz-if you get an answer, will you post it. Thanks! -Rhonda ------------------------------------------------------------ Message no. 376[Branch from no. 375] Posted by Naziya Shaik (snaziya) on Thursday, September 21, 2006 2:19pm Subject: Re: Confused ok here is the solution for our problem... professor said "Ideally, we should consider the constraints. If you the scripts provided for chapter 4, that will be fine." By the way..cant we use DROP TABLE tablename CASCADE CONSTRIANTS? do we have to drop in tht order as raju gave? Cheers, Naz ------------------------------------------------------------ Message no. 377[Branch from no. 376] Posted by Krishnamurth Ashwini (kashwini) on Thursday, September 21, 2006 2:58pm Subject: Re: Confused Hi Naziya, Thanks for the information. Yes, you can actually use DROP TABLE tablename CASCADE CONSTRAINTS; I have been using this in all my previous semester's scripts. Ash ------------------------------------------------------------ Message no. 378[Branch from no. 376] Posted by Gnaneshwar Bukka (gbukka) on Thursday, September 21, 2006 3:51pm Subject: Re: Confused Hi Naziya, Thank you for the information and I have used the DROP TABLE table_name CASCADE CONSTRAINTS. My drop commands are not in the order, it should be working. Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 379[Branch from no. 371] Posted by Sagun Piya (srpiya2) on Thursday, September 21, 2006 6:55pm Subject: Re: Dropping FK Constraint Usually when we create a constraint, we give a constraint name if not then the constraint will get an automatic name of the form. we can drop the foreign constraint by giving name of it while creating and it will be easy to drop. Sagun ------------------------------------------------------------ Message no. 380[Branch from no. 351] Posted by Sagun Piya (srpiya2) on Thursday, September 21, 2006 7:00pm Subject: Re: Order table Thanks Raju I will try it your way. ------------------------------------------------------------ Message no. 385[Branch from no. 376] Posted by Lakshmikar Padmaraju (lpadmaraju) on Friday, September 22, 2006 9:09am Subject: Re: Confused Hi, You can use DROP TABLE tablename CASCADE CONSTRIANTS, but it is not preferred practice. In LIVE databases you should never use CASCADE CONSTRIANTS because you going to destroy the referential integrity of the table if one exists, and it defeat the purpose it intended for. If you try to delete the table if you get the error then you have to review the reason for the error and fix that one first and delete the table later. Raju. ------------------------------------------------------------ Message no. 386[Branch from no. 385] Posted by Daniel Thurston (dsthurston) on Friday, September 22, 2006 9:30am Subject: Re: Confused >If you try to delete the table if you get the error then you have to review the reason for the error real good point DanT ------------------------------------------------------------ Message no. 388 Posted by Lakshmikar Padmaraju (lpadmaraju) on Friday, September 22, 2006 1:23pm Subject: URGENT and very Important message for Project 5 for ALL Hello every body, There is a change in the project 5 specs, I pointed out to Dr. LIU that IF we use system account to create password Policy Function(tec5363_verify_function ) and try to add to Profile using PASSWORD_VERIFY_FUNCTION, it will give an error saying that the function do not exist, even though function exists in SYSTEM's user_objects table. When I did some research on Oracle Forums I found that Policy function and profile shoud be created in SYS schema not SYSTEM schema because SYSTEM schema also should be protected by the same policy. I conveyed this Dr. Liu and he agreed with me and asked to convey to everybody through COMUNITY SOLUTIONS discussion. Here is Dr. Liu's E-mail ---------------------- Message no. 5 [Reply of: no. 4] Author: Peter Ping Liu (TEC5363PL) Date: Friday, September 22, 2006 11:48am Dear Raju: I am glad to hear your information. That was my problem. Please change to SYS and alert everyone in Community and Solutions. Regards. ---------------------- So everybody should make note of it and login as SYS user and complete the project. Any body has questions please contact Dr.Liu. Regards Raju. ------------------------------------------------------------ Message no. 389[Branch from no. 388] Posted by Naziya Shaik (snaziya) on Friday, September 22, 2006 1:55pm Subject: Re: URGENT and very Important message for Project 5 for ALL Thank you Raju In the example provided by Dr.Liu for the password profile creating...in the comments I found out that we should have SYSDBA privileges to create such profile. So i logged in with SYS/password as SYSDBA; This is what you are talking about right? Naz ------------------------------------------------------------ Message no. 390[Branch from no. 385] Posted by Naziya Shaik (snaziya) on Friday, September 22, 2006 1:56pm Subject: Re: Confused Thanks raju ------------------------------------------------------------ Message no. 393[Branch from no. 389] Posted by Lakshmikar Padmaraju (lpadmaraju) on Friday, September 22, 2006 2:57pm Subject: Re: URGENT and very Important message for Project 5 for ALL Naziya and others, Project 5 step 1 says " Connect to SYSTEM user. Now everybody should connect as SYS user. rest of the steps are same. Also Dr. Liu changed step 3 on 21st, he added PASSWORD_VERIFY_FUNCTION, so anybody completed project 5 before that you have to retrieve and add this to create profile code. One more thing most people might know this, if not while loging in as SYS is little bit different than SYSTEM you have to use "as sysdba" like connect sys/lraju@orcl as sysdba; where lraju is password and orcl is SID name or also you can use like connect sys/lraju as sysdba; I thought it will reduce some anxiety for somebody who logging into SYS account first time. I completed project 5 no problems, if anybody need help let me know. Take care Raju. ------------------------------------------------------------ Message no. 397[Branch from no. 371] Posted by Paras Pradhan (ppradhan) on Friday, September 22, 2006 7:09pm Subject: Re: Dropping FK Constraint hi: I am not sure but may be the oracle enterprise manager GUI can help in this case. Paras. ------------------------------------------------------------ Message no. 401[Branch from no. 390] Posted by Rhonda Nichols (renichols2) on Friday, September 22, 2006 9:14pm Subject: Re: Confused Thanks Everybody!!! Great information. -Rhonda ------------------------------------------------------------ Message no. 402[Branch from no. 397] Posted by Ravinder Gaur (rgaur) on Friday, September 22, 2006 9:25pm Subject: Re: Dropping FK Constraint You could query "dba_constraints" view to find out all the constraints for a table (whether named explicitly or system named). - Ravi ------------------------------------------------------------ Message no. 404[Branch from no. 393] Posted by Naziya Shaik (snaziya) on Friday, September 22, 2006 11:22pm Subject: Re: URGENT and very Important message for Project 5 for ALL ya i too connected as sys/pwd as sysdba... wht is the diffrence between those both types of connect statemetn?? now i changed tht profile name to tec5363dbsec_prof thts all we have to change right??...anything more to change in this script?? ya i too dint have any problem running this script. thanks raju Naz ------------------------------------------------------------ Message no. 405[Branch from no. 388] Posted by Venkat Munagala (vrmunagala) on Friday, September 22, 2006 11:39pm Subject: Re: URGENT and very Important message for Project 5 for ALL Thanks a lot Raju for information. ------------------------------------------------------------ Message no. 407[Branch from no. 402] Posted by Anjana Divakar (adivakar) on Saturday, September 23, 2006 8:19am Subject: Re: Dropping FK Constraint I have the same doubt as Kelsey. Do we have to name the constraints as we add them and then drop them? ------------------------------------------------------------ Message no. 411 Posted by Anjana Divakar (adivakar) on Saturday, September 23, 2006 3:08pm Subject: doubt regarding user creation I have a doubt regarding step 2 of the tasks given to us. I want to know if we need to create a user DBSEC with a password in the script and then change the password right aftewards to sec#1new or if we have to assume that the DBSEC is already created i.e. dr. liu will run our scripts after logging on as DBSEC. Im unclear as to whether or not the user needs to be created.Can anyone please let me know. ------------------------------------------------------------ Message no. 414[Branch from no. 385] Posted by Eric Knuth (elknuth2) on Saturday, September 23, 2006 4:23pm Subject: Re: Confused You are correct in your reasoning, but in a test environment I like to use the "cascade constraints" option to remove everything back to pristine before trying again. Thanks Eric ------------------------------------------------------------ Message no. 415[Branch from no. 338] Posted by Eric Knuth (elknuth2) on Saturday, September 23, 2006 4:29pm Subject: Re: Small doubt-Project-4 When it comes to using textbook SQL examples, I am following the rule of "use the textbook". Dr. Liu's instructions vary slightly from the textbook, but I have found an occasional typo. Since I am sure that we would never be expected to use a reserved word in the corporate world (probably never allowed), I am sticking with the sql script that uses "ORDERS" not "ORDER" for the naming convention. Thanks Eric ------------------------------------------------------------ Message no. 416[Branch from no. 411] Posted by Naziya Shaik (snaziya) on Saturday, September 23, 2006 5:19pm Subject: Re: doubt regarding user creation DBSEC user is already created. NOw you just need to change that user's password to sec#1new. that password change is done when u are logged in as system. first write connect system/password then you can write the change password things. after granting the permissions to dbsec like create session and create table, then you connect to dbsec....(not externally, you can write the connect statement in the same script).....after connecting to dbsec, the table things go on. Hope this helps Naz ------------------------------------------------------------ Message no. 417[Branch from no. 411] Posted by Kelsey Pooley (kjpooley) on Saturday, September 23, 2006 7:03pm Subject: Re: doubt regarding user creation Anjana, I wondered the same thing. I know we already created the DBSEC user in the last assignment but if Dr. Liu wants to re-run the script, I thought we needed reset it to the original password each time (just like we dropped all the tables so they could be created again). I put a drop user command, then I copied and pasted the create user portion from Project 3 and put it into my script, followed by alter user to change the password so that each time the script is run, the change is made from the beginning. I'm not sure if this is was necessary, but I wanted to be sure. Kelsey ------------------------------------------------------------ Message no. 418[Branch from no. 417] Posted by Naziya Shaik (snaziya) on Saturday, September 23, 2006 7:13pm Subject: Re: doubt regarding user creation No, you dont have to run the whole proj3 script again. you can use 'alter user xxxx identified by yyyyy' which is mentioned in text book(alter statement). Hope this helps Naz ------------------------------------------------------------ Message no. 419[Branch from no. 411] Posted by Venkat Munagala (vrmunagala) on Saturday, September 23, 2006 8:57pm Subject: Re: doubt regarding user creation Anjana, First I created user with name DBSEC with a password. Then changed the password of the user with the follwing statement. alter user user_name identified by new_password; Hope this helps Rohit ------------------------------------------------------------ Message no. 423[Branch from no. 352] Posted by Suresh Methuku (smethuku) on Saturday, September 23, 2006 10:38pm Subject: Re: Order table Thanks for your post raju. It helped me a lot suresh ------------------------------------------------------------ Message no. 426[Branch from no. 419] Posted by Anjana Divakar (adivakar) on Saturday, September 23, 2006 11:34pm Subject: Re: doubt regarding user creation thanks guys! ------------------------------------------------------------ Message no. 427[Branch from no. 352] Posted by Anjana Divakar (adivakar) on Saturday, September 23, 2006 11:36pm Subject: Re: Order table Hi Raju, thanks for all the information. Was really helpful. ------------------------------------------------------------ Message no. 428[Branch from no. 415] Posted by Lakshmikar Padmaraju (lpadmaraju) on Sunday, September 24, 2006 10:50am Subject: Re: Small doubt-Project-4 Dear Eric, As you said we never use reserved word in real world, but in this class we have to use what Dr. Liu project4 instructions says, otherwise when he test our SQL file with his script it shows that you didn’t created ORDER table and you probably loose some points for it, it happened in earlier Advanced database class, that's why he warns specifically spelling matter. Raju. ------------------------------------------------------------ Message no. 429[Branch from no. 407] Posted by Lakshmikar Padmaraju (lpadmaraju) on Sunday, September 24, 2006 11:00am Subject: Re: Dropping FK Constraint Anjana, It's not mandatory, it best practice to use naming conventions for all objects you created with, it will really helpful if you want to search all constraints for a particular table using like % % in where clause. for example if you created all table constraints started with table name and extension like EMP_PK and EMP_FK then you can search dba_constrains table using WHERE Constaint_name like '%EMP_%, so you can get all constraints for EMP Table. Most of systems departments have specific naming convention you have to follow when you work with any organization for standardization purpose. Raju. ------------------------------------------------------------