Compiled Messages: ------------------------------------------------------------ Message no. 836 Posted by Paras Pradhan (ppradhan) on Thursday, November 16, 2006 4:38pm Subject: upper and lower cases Hi Once again the problem with upper and lower cases. Don't know if it is with me only. Here is the problem: select object_name, policy_name, sql_text, statement_type from DBA_FGA_AUDIT_TRAIL; works.. but not select object_name, policy_name, sql_text, statement_type from dba_fga_audit_trial; and says table or view doesnot exists. anyone getting this issue? Paras. ------------------------------------------------------------ Message no. 837[Branch from no. 836] Posted by Lakshmikar Padmaraju (lpadmaraju) on Thursday, November 16, 2006 5:46pm Subject: Re: upper and lower cases Dear Paras, Upper or lower case of object names or column names should not matter in Oracle database, Case restriction only matters with data contained in columns. For example If you have table employee with column names: emp_id, emp_name and it has data like 4000 PARAS 4002 Raju If you write a query select EMP_ID, EMP_NAME from Employee where Emp_NAME = 'PARAS'; select emp_ID, emp_NAME from EMPloyee where EMP_name = 'PARAS'; select EMP_ID, EMP_NAME from Employee where Emp_NAME = 'Raju'; select emp_id, emp_name from EMPLOYEE where Emp_NAME = 'Raju'; all the above querrys will fetch one record. if you chang case of 'PARAS' to 'paras' will not fetch any records select EMP_ID, EMP_NAME from Employee where Emp_NAME = 'Paras'; select EMP_ID, EMP_NAME from Employee where Emp_NAME = 'RAJU'; both the above queries will not return any rows because data in table( 'PARAS' and 'Raju') do not match with 'Paras' or 'RAJU' In your case you might have different problem, I tested the query it is working with either case on my work database. Then I did cut and pasted your statements here is the results ---------------------------------------------------- Connected to: Oracle Database 10g Release 10.2.0.1.0 - 64bit Production SQL> select object_name, policy_name, sql_text, statement_type from 2 DBA_FGA_AUDIT_TRAIL; no rows selected SQL> select object_name, policy_name, sql_text, statement_type from 2 dba_fga_audit_trial; dba_fga_audit_trial * ERROR at line 2: ORA-00942: table or view does not exist SQL> select object_name, policy_name, sql_text, statement_type from 2 dba_fga_audit_trail; no rows selected SQL> ------------------------------------------ When I pasted your second query it failed, then I copied first line and typed table name it worked. That means you might have wrote this query in Word or WordPad which are non compatible editors or you might have copied this code from any document some times oracle don't like cut and paste. If you are lazy like me in typing best thing to do is copy any query first into notepad and then copy from notepad and paste on to SQL Plus it will work fine. I have seen lot of these things in 10 years of my Oracle experience. Hope I explained well. Raju. ------------------------------------------------------------ Message no. 838[Branch from no. 837] Posted by Ravinder Gaur (rgaur) on Thursday, November 16, 2006 6:49pm Subject: Re: upper and lower cases Raju, Thanks - great explanation with examples! I totally agree with you, sometimes you encounter issues with cut-n-paste, non-printable characters during file transfers from one platform into others and so on. However, here Paras seems to be hitting a very common problem (typos). Paras, please note the typo in the table name in your 2nd query -- dba_audit_trial Versus DBA_AUDIT_TRAIL ("trial" versus "trail"). - Ravi ------------------------------------------------------------ Message no. 839[Branch from no. 838] Posted by Sagun Piya (srpiya2) on Thursday, November 16, 2006 6:59pm Subject: Re: upper and lower cases Thanks Raju and Ravi for explaining in detail about the problems. You guys are doing great help for whole class. Sagun ------------------------------------------------------------ Message no. 843[Branch from no. 836] Posted by Gnaneshwar Bukka (gbukka) on Thursday, November 16, 2006 8:28pm Subject: Re: upper and lower cases Thank you very much Ravi and Raju, you guys have given an excellent explanation for so called simple problem, I would have never thought about that, atleast during this course. I am sure that students like me, in the sense, inexperienced people are getting to know a lot of things from you guys. Thanks once again. Regards, Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 847[Branch from no. 843] Posted by Rhonda Nichols (renichols2) on Thursday, November 16, 2006 8:37pm Subject: Re: upper and lower cases Great job guys! I have learned to only use notepad for all my sql creation because of same problem. Another sql editor I use is included with TOAD. -Rhonda ------------------------------------------------------------ Message no. 850[Branch from no. 838] Posted by Paras Pradhan (ppradhan) on Thursday, November 16, 2006 10:14pm Subject: Re: upper and lower cases Raju not the copy paste issue.. damn it's spelling mistake. thanks ravi Paras. ------------------------------------------------------------ Message no. 852[Branch from no. 838] Posted by Lakshmikar Padmaraju (lpadmaraju) on Friday, November 17, 2006 8:25am Subject: Re: upper and lower cases Thanks Ravi, I didn't notice the typo, when i ran the query when i typed table name i thought it was cut and paste issue. any way good observation. Raju ------------------------------------------------------------ Message no. 854[Branch from no. 837] Posted by Anjana Divakar (adivakar) on Friday, November 17, 2006 10:03am Subject: Re: upper and lower cases Thanks guys!All your explanations are very helpful. ------------------------------------------------------------ Message no. 855[Branch from no. 836] Posted by Krishnamurth Ashwini (kashwini) on Friday, November 17, 2006 10:27am Subject: Re: upper and lower cases :-)..typos can drive you crazy during coding. Ash ------------------------------------------------------------ Message no. 857[Branch from no. 838] Posted by Suresh Methuku (smethuku) on Friday, November 17, 2006 12:03pm Subject: Re: upper and lower cases thanks guys you are helping a lot. ------------------------------------------------------------ Message no. 859[Branch from no. 855] Posted by Naziya Shaik (snaziya) on Friday, November 17, 2006 2:50pm Subject: Re: upper and lower cases yup thats true ash...for one project i almost spent two days to figure out the problem... finally i understood that it was simple typo Naz ------------------------------------------------------------ Message no. 864[Branch from no. 859] Posted by Anjana Divakar (adivakar) on Friday, November 17, 2006 7:38pm Subject: Re: upper and lower cases Every time i run my program, the number of rows selected increases by 3. Can anyone please tell me why or what it is im doing wrong. This is the query that Im using. SELECT OBJECT_NAME,POLICY_NAME,SQL_TEXT FROM DBA_FGA_AUDIT_TRAIL; thanks,anjana ------------------------------------------------------------ Message no. 866[Branch from no. 859] Posted by Kelsey Pooley (kjpooley) on Friday, November 17, 2006 10:43pm Subject: Re: upper and lower cases Sometimes it helps to walk away from the project for awhile and come back with a fresh outlook. I too have spent many hours trying to figure out problems with my code when the answer was very simple. Sometimes if you give your eyes a break, you have better luck. Kelsey ------------------------------------------------------------ Message no. 867[Branch from no. 864] Posted by Ravinder Gaur (rgaur) on Friday, November 17, 2006 10:44pm Subject: Re: upper and lower cases Anjana, Every time you run the whole thing, it adds 3 audit records -- one for SELECT, one for UPDATE and one for DELETE. Check the TIMESTAMP column to see the time records get added. BTW, I'm using this to query the dba_fga_audit_trail view -- select OBJECT_NAME,substr(SQL_TEXT,1,30) "SQL_TEXT",DB_USER,OS_USER,to_char(TIMESTAMP,'dd-mm-yyyy hh24:mi:ss') "TIMESTAMP" from DBA_FGA_AUDIT_TRAIL; If you don't want to see old audit records, you may truncate FGA_LOG$ table (owned by SYS). Hope this helps. - Ravi ------------------------------------------------------------ Message no. 868[Branch from no. 866] Posted by Daniel Thurston (dsthurston) on Saturday, November 18, 2006 8:46am Subject: Re: upper and lower cases >Sometimes it helps to walk away from the project for awhile Yeah, during undergrad I always solved the hardest code problems on the walk home from the computer lab. Cheers DanT ------------------------------------------------------------ Message no. 869[Branch from no. 867] Posted by Anjana Divakar (adivakar) on Saturday, November 18, 2006 12:02pm Subject: Re: upper and lower cases Thank you Ravi! That definitely cleared some doubts. I used truncate table sys.aud$; after creating the truncate procedure but I think for this project we need to follow only the steps given in the guidelines. I would have never thought of truncating otherwise and looked up some information on that. thanks, anjana ------------------------------------------------------------ Message no. 870[Branch from no. 868] Posted by Venkat Munagala (vrmunagala) on Saturday, November 18, 2006 4:45pm Subject: Re: upper and lower cases I do agree with u DanT. In case of programming I too get fed up with that in some cases. I turnoff my laptop, go for a walk and then come and sit again. Things work well then. Rohit ------------------------------------------------------------ Message no. 880 Posted by Daniel Thurston (dsthurston) on Monday, November 20, 2006 9:54pm Subject: The Week of Oracle Database Bugs I saw this come across bugtraq today thought it may be of interest. cheers DanT ============================ The Week of Oracle Database Bugs Based on the great idea of H D Moore "Month of Browser Bugs" and LMH "Month of Kernel Bugs", we are proud to announce that we are starting on December the "Week of Oracle Database Bugs" (WoODB). What is the WoODB about? An Oracle Database 0day will be released every day for a week on December. Why are you doing this? We want to show the current state of Oracle software ("in")security also we want to demostrate Oracle isn't getting any better at securing its products (you already know the history: two years or more to fix a bug, not fixing bugs, failing to fix bugs, lying about security efforts, etc, etc, etc.). Why are you targeting only Oracle? We have 0days for all Database software vendors but Oracle is "The #1 Star" when talking about lots of unpatched vulnerabilities and not caring about security. Why not the Month of Oracle Database Bugs? We could do the Year of Oracle Database Bugs but we think a week is enough to show how flawed Oracle software is, also we don't want to give away all our 0days:), anyways if you want to contribute send your Oracle 0days so this can be extended for another week or more. See: http://www.argeniss.com/woodb.html Cesar. ------------------------------------------------------- ------------------------------------------------------------ Message no. 881[Branch from no. 880] Posted by Ravinder Gaur (rgaur) on Tuesday, November 21, 2006 6:52am Subject: Re: The Week of Oracle Database Bugs Interesting read. Thanks Dan. - Ravi ------------------------------------------------------------ Message no. 882 Posted by Naziya Shaik (snaziya) on Thursday, November 23, 2006 5:56pm Subject: PROJ 9 ERROR ORA-04098 I tried to create trigger...it went fine the first time.. but later when i tried to create another trigger ( with similar code) then it was giving me some error message "ORA-04098: trigger SYSTEM.TRIG_BEFORE_CREATE is invalide and failed re-validation'. I even tried to drop the created trigger and do it again. But it did not work. Did anyone else get the same kind of error. what did you do for that? Thanks Naz ------------------------------------------------------------ Message no. 883[Branch from no. 882] Posted by Naziya Shaik (snaziya) on Thursday, November 23, 2006 6:25pm Subject: Re: PROJ 9 ERROR ORA-04098 ok I understood what the problem was. I went in to error files and checked what the error implies. it gave "PLS-00103: Encountered the symbol "=" when expecting one of the following: . ( * @ % & - + / at mod remainder rem then and or || The symbol "* was inserted before "=" to continue. " so its simple typo. so i replaced it again and now it works. Thanks Naz ------------------------------------------------------------ Message no. 884 Posted by Naziya Shaik (snaziya) on Sunday, November 26, 2006 10:52pm Subject: Proj 9 audit function Hi, I was trying to audit the alter,drop or create statements, when these statements are issued by 'dbsec' user. But when I view the content of dba_audit_trail..for auditing records, i get 0 rows. I am missing something in here? SQL> SHOW USER USER is "SYSTEM" SQL> AUDIT TABLE BY DBSEC; Audit succeeded. SQL> CONN DBSEC Enter password: ******** Connected. SQL> DROP TABLE TB; Table dropped. SQL> CONN SYSTEM/REXONA Connected. SQL> SELECT * FROM DBA_AUDIT_TRAIL; no rows selected -------------------- Naz ------------------------------------------------------------