Agree with Fred that your source in the Remedy view is going to be all upper case. I think what you need is simply db_name.table_name. I have views set up this way.
On queries I have table_name@db_name and all of my statements are lower case. Mark -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Ben Chernys Sent: Friday, February 03, 2012 10:24 AM To: [email protected] Subject: Re: Create a View form of a remote oracle DB on ARSystem (MS SQL) That is as I said in my email: that tables, fields, views in the Oracle data dictionary are stored in uppercase. <snip> and seLECt disTINCT table_nAMe from all_tAB_columns where oWNer = 'ARADMIN' and taBLe_name like 'a%' will return no records. <snip> But for Oracle queries any table, column etc can be in mixed case. The SQL parser handles case conversions. I do not use ARS View forms instead getting the data directly into ARS through my own queries. Cheers Ben -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Grooms, Frederick W Sent: February-03-12 15:32 To: [email protected] Subject: Re: Create a View form of a remote oracle DB on ARSystem (MS SQL) Ben, For normal queries you are correct, but I believe ARS queries the all_user_tables or all_user_views to validate you have entered an existing view or table name when you are creating a view form. The result of this is that the name you put in for the view form's source must be in all uppercase for Oracle. i.e. The following will never find a record as CONTROL is stored all uppercase in the database. Select table_name from all_user_tables where table_name = 'control' Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Ben Chernys Sent: Friday, February 03, 2012 2:58 AM To: [email protected] Subject: Re: Create a View form of a remote oracle DB on ARSystem (MS SQL) Just an FYI Oracle tables names, view names, columns names, functions, etc are NOT case sensitive. Oracle data is. So, mixing case on view names is not important unless the view name is being treated as data (as in an Oracle dictionary table) where it is indeed stored in uppercase. So, using a non-linked database query as an example: select company from com_company is entirely equivalent to SeleCT COMpany from COM_company So, seLECt disTINCT table_nAMe from all_tAB_columns where oWNer = 'ARADMIN' and taBLe_name like 'A%' will return all ARS tables and views starting with 'A' and seLECt disTINCT table_nAMe from all_tAB_columns where oWNer = 'ARADMIN' and taBLe_name like 'a%' will return no records. BTW if you do not have an Oracle client, queries on this table (all_tab_columns) is a good way to get a "DESCRIBE". Cheers Ben -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Grooms, Frederick W Sent: February-02-12 22:59 To: [email protected] Subject: Re: Create a View form of a remote oracle DB on ARSystem (MS SQL) I believe it is Case sensitive. All Oracle table and view names are uppercase. Try link.db.OWNER.TABLE You may find it cleaner to create a local database view that does the remote query. Then you should not have to worry about the case sensitivity from Remedy. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Reiser, John J Sent: Thursday, February 02, 2012 3:50 PM To: [email protected] Subject: Create a View form of a remote oracle DB on ARSystem (MS SQL) Hello Listers, MS SQL 2005 on a SAN MS Windows 2003 Enterprise AR System 7.6.03 Build 001 Our system connects to a remote Sybase DB via the DB Link for ARS Croom Consulting. The Sybase DB is being replaced by an Oracle DB. We only need to read data so I am trying to make the oracle connection with a View form. The MS SQL dba created a Linked server to the Oracle DB and confirmed the connectivity. In the View form should I use the MS SQL connection string or an Oracle connection string? <linkname>.<db_name>.<owner>.<table> or <db_name>.<table>@<linkname> The value that I tried to load was the ORAORQ10.propmgt.propmgt.equipment. That gives an ARERR 552 message I have also tried propmgt.equipment@ORAORQ10 That gives ARERR 481 but I'm not sure if the "table not found" is because of case sensitivity or just the wrong connection string format. I've successfully connected to a different linked db but it is a remote MS SQL so I use the link.db.owner.table format for that. Thanks in advance. --- John J. Reiser Remedy Developer/Administrator Senior Software Development Analyst Lockheed Martin - MS2 The star that burns twice as bright burns half as long. Pay close attention and be illuminated by its brilliance. - paraphrased by me ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" This e-mail is the property of NaviSite, Inc. It is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. Distribution or copying of this e-mail, or the information contained herein, to anyone other than the intended recipient is prohibited. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
