Re: Issues with building a view over a DB Link
Patrick, I did what you suggested and I was am able to run any select statement from the link and get valid returns. But, when I try to create the View form in ARS I am getting the ORA-00904 Invalid Identifier on the key field. I have verified that the Key field on the SQL table is set up properly, 6 to 15 characters, Unique, and No NULL, the Column name starts with a Alpha character. I am at a loss, any ideas? Thanks Gary Dries On 4/2/08, patrick zandi [EMAIL PROTECTED] wrote: ** Just create a view on the db side, create a dblink, then create a synomn on the ars side. Test link.. select * from synonm... ( I know I cannot spell .. ;-) then create a view form.. watch out of Date fields.. and use an escalation or a filter to write back.. voila .. works great.. been doing this for years.. .. Day 1 since the view form came out.. Hope that helps.. ARS DB ARS VIEW ~~ Synonmn ~~ DBlink ~~ View of tables ~~ Tables test your link ! ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Issues with building a view over a DB Link
Gary, Croom Consulting's Link to External Databases can do what you are looking at and with significant less headache. Contact me off list if you would like to arrange an evaluation. Regards...Gidd _ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Gary Dries Sent: Wednesday, April 02, 2008 12:14 PM To: arslist@ARSLIST.ORG Subject: Issues with building a view over a DB Link ** I have created a Oracle (10g) to MSSQL (2003) dblink, I am able to create the view, but I get sever fields that retrun the error Failure during SQL operation to the database : ORA-00904: ADDRESS1: invalid identifier (ARERR 552). We are researching several possible issues, such as permissions, and the build of the table that I am creating the view of. One suggetion I found searching the list is to create the view in Oracle to the SQL db and then create the Remedy view to the Oracle view. Is it possible to create a view from a view, and if it is, can the data in the SQL table be modifed from Remedy in this view of a view of a SQL table? -- Gary Dries __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Issues with building a view over a DB Link
Gary, The short answer is yes a view of a view can be modified. There are a couple caviates here. One. The request ID field in remedy. I suspect from your error message that you have the address1 column matched to column 1 on the view form. even with view forms, Remedy needs a unique identifier field, like the request ID. Two. You have to make sure that the link is setup for you to be able to write to the table from your oracle database. Basically, if it can be done in the database, then you can make Remedy do it very easily. HTH, Brian Goralczyk On Wed, Apr 2, 2008 at 2:13 PM, Gary Dries [EMAIL PROTECTED] wrote: ** I have created a Oracle (10g) to MSSQL (2003) dblink, I am able to create the view, but I get sever fields that retrun the error Failure during SQL operation to the database : ORA-00904: ADDRESS1: invalid identifier (ARERR 552). We are researching several possible issues, such as permissions, and the build of the table that I am creating the view of. One suggetion I found searching the list is to create the view in Oracle to the SQL db and then create the Remedy view to the Oracle view. Is it possible to create a view from a view, and if it is, can the data in the SQL table be modifed from Remedy in this view of a view of a SQL table? -- Gary Dries __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Issues with building a view over a DB Link
Gary, I understand the budget crunch !! Solution cost is $4999.00 + support Here is a link for more detailed information. http://www.buoyantsolutions.net/External_DB.html BTW: There are many on the ARS List that use this solution and swear by it ... not at it !! HTH Regards...Gidd _ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Gary Dries Sent: Wednesday, April 02, 2008 1:16 PM To: arslist@ARSLIST.ORG Subject: Re: Issues with building a view over a DB Link ** Gidd, I am not in a position to purchase any tool, apps, or anything for that matter. To evaluate something and recommend to management would take longer than I have to resolve this issue. I will talk to my Mgr. to see if he is interested, what is the cost of this solution? Brian, The field mentioned in the error is not the key, the field we have identified as a key is unique, no null and between 6 to 15 characters, that field is not giving us any problems and successfully maps to the view form when it is created. Now the write access issue is another thing I am working on, what I do not understand is the fields that do get mapped in the view form have the same permissions as the fields that error. Fellas, I appreciate the quick response Gary __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Issues with building a view over a DB Link
What I've had to do in the past in a similar situation is to have the view form referencing a view on my DB that was using a linked server. If you get that part, you can at least see the data. However, if you have issues modifying or submitting the data, unfortunately the next best solution is to build active links on buttons that run SQL commands for insert and update. Thanks, Shawn Pierson From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Gary Dries Sent: Wednesday, April 02, 2008 2:14 PM To: arslist@ARSLIST.ORG Subject: Issues with building a view over a DB Link ** I have created a Oracle (10g) to MSSQL (2003) dblink, I am able to create the view, but I get sever fields that retrun the error Failure during SQL operation to the database : ORA-00904: ADDRESS1: invalid identifier (ARERR 552). We are researching several possible issues, such as permissions, and the build of the table that I am creating the view of. One suggetion I found searching the list is to create the view in Oracle to the SQL db and then create the Remedy view to the Oracle view. Is it possible to create a view from a view, and if it is, can the data in the SQL table be modifed from Remedy in this view of a view of a SQL table? -- Gary Dries __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ Private and confidential as detailed here: http://www.sug.com/disclaimers/default.htm#Mail . If you cannot access the link, please e-mail sender. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Issues with building a view over a DB Link
Brian, I saw this also and sent it to my DBA, he found it interesting also. The only quick explanation we could come up with is that something is happening to the Column Name in translation via the DBLink. Now this has one flaw, and that is the fact that the field that do not error have very similar names, like Address1 errors but Address2 does not. We are working on the process of building the view in oracle and then the view in Remedy, I will keep in mind the naming issue you mentioned. Thanks Gary ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Issues with building a view over a DB Link
You know, I am wonderingSometimes I have seen Oracle return the wrong piece of code for the error. Is there any field on the list that might be a reserved word for Oracle? Were you going with just the error, or did you turn on server sql logging and capture the sql code that Remedy was generating? That might help you identify where the actual error is. as well as it should help your dba create the view. On Wed, Apr 2, 2008 at 4:25 PM, Gary Dries [EMAIL PROTECTED] wrote: ** Brian, I saw this also and sent it to my DBA, he found it interesting also. The only quick explanation we could come up with is that something is happening to the Column Name in translation via the DBLink. Now this has one flaw, and that is the fact that the field that do not error have very similar names, like Address1 errors but Address2 does not. We are working on the process of building the view in oracle and then the view in Remedy, I will keep in mind the naming issue you mentioned. Thanks Gary __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Issues with building a view over a DB Link
Just create a view on the db side, create a dblink, then create a synomn on the ars side. Test link.. select * from synonm... ( I know I cannot spell .. ;-) then create a view form.. watch out of Date fields.. and use an escalation or a filter to write back.. voila .. works great.. been doing this for years.. .. Day 1 since the view form came out.. Hope that helps.. ARS DB ARS VIEW ~~ Synonmn ~~ DBlink ~~ View of tables ~~ Tables test your link ! On 4/2/08, Brian Goralczyk [EMAIL PROTECTED] wrote: ** I queried the error on google and came up with a bit from someone else. The amusing part is that they state that Oracle claims that the error doesn't happen in 10g which caused me to immediately check and notice you are on. So I file that under interesting. Personally, I would first make the database view in your database to the one that you are connecting. Make sure that everything works that way. Then you have the choice of either creating the view form and attaching to your view or attempting to create the same process that your view is creating. One note of caution. If you create your view, you CAN NOT use the same name as the view form. And vice versa, you CAN NOT create a view form with the same name as a view that was created in the database. Remedy tries to create a database view to match the form and errors out. I have included the piece that the individual wrote, mostly cause it amuses me with the contridiction. It came from this site.. http://www.dba-oracle.com/t_ora_00904_string_invalid_identifier.htm The Oracle docs note this on the ora-00904 error*: *ORA-00904 string: invalid identifier* *Cause:* The column name entered is either missing or invalid. *Action: * Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word. *According to Oracle documentation, ORA-00904 does not occur in Oracle 10g When ORA-00904 occurs, you must enter a valid column name as it is either missing or the one entered is invalid. To avoid ORA-00904, column names cannot be a reserved word, and must contain these four criteria to be valid: - begin with a letter - be less than or equal to twenty characters - consist only of alphanumeric and the special characters ($_#); other characters need double quotation marks around them Another important factor in correcting ORA-00904 is remembering to run catproc.sql You can also check your trace file to find the particular error which is causing the ORA-00904 to occur. On Wed, Apr 2, 2008 at 3:37 PM, Pierson, Shawn [EMAIL PROTECTED] wrote: ** What I've had to do in the past in a similar situation is to have the view form referencing a view on my DB that was using a linked server. If you get that part, you can at least see the data. However, if you have issues modifying or submitting the data, unfortunately the next best solution is to build active links on buttons that run SQL commands for insert and update. Thanks, Shawn Pierson *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] *On Behalf Of *Gary Dries *Sent:* Wednesday, April 02, 2008 2:14 PM *To:* arslist@ARSLIST.ORG *Subject:* Issues with building a view over a DB Link ** I have created a Oracle (10g) to MSSQL (2003) dblink, I am able to create the view, but I get sever fields that retrun the error Failure during SQL operation to the database : ORA-00904: ADDRESS1: invalid identifier (ARERR 552). We are researching several possible issues, such as permissions, and the build of the table that I am creating the view of. One suggetion I found searching the list is to create the view in Oracle to the SQL db and then create the Remedy view to the Oracle view. Is it possible to create a view from a view, and if it is, can the data in the SQL table be modifed from Remedy in this view of a view of a SQL table? -- Gary Dries __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ Private and confidential as detailed herehttp://www.sug.com/disclaimers/default.htm#Mail. If you cannot access hyperlink, please e-mail sender. __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ -- Patrick Zandi Dev Technology Group -- www.devtechnology.com Exceeding your Expectations ! By Design... ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Issues with building a view over a DB Link
do you have encryption turned on between the links ? this could be your issue as well. I have seen a required on one side and a requested on the other.. cause issues.. On 4/2/08, patrick zandi [EMAIL PROTECTED] wrote: Just create a view on the db side, create a dblink, then create a synomn on the ars side. Test link.. select * from synonm... ( I know I cannot spell .. ;-) then create a view form.. watch out of Date fields.. and use an escalation or a filter to write back.. voila .. works great.. been doing this for years.. .. Day 1 since the view form came out.. Hope that helps.. ARS DB ARS VIEW ~~ Synonmn ~~ DBlink ~~ View of tables ~~ Tables test your link ! On 4/2/08, Brian Goralczyk [EMAIL PROTECTED] wrote: ** I queried the error on google and came up with a bit from someone else. The amusing part is that they state that Oracle claims that the error doesn't happen in 10g which caused me to immediately check and notice you are on. So I file that under interesting. Personally, I would first make the database view in your database to the one that you are connecting. Make sure that everything works that way. Then you have the choice of either creating the view form and attaching to your view or attempting to create the same process that your view is creating. One note of caution. If you create your view, you CAN NOT use the same name as the view form. And vice versa, you CAN NOT create a view form with the same name as a view that was created in the database. Remedy tries to create a database view to match the form and errors out. I have included the piece that the individual wrote, mostly cause it amuses me with the contridiction. It came from this site.. http://www.dba-oracle.com/t_ora_00904_string_invalid_identifier.htm The Oracle docs note this on the ora-00904 error*: *ORA-00904 string: invalid identifier* *Cause:* The column name entered is either missing or invalid. * Action:* Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word. *According to Oracle documentation, ORA-00904 does not occur in Oracle 10g When ORA-00904 occurs, you must enter a valid column name as it is either missing or the one entered is invalid. To avoid ORA-00904, column names cannot be a reserved word, and must contain these four criteria to be valid: - begin with a letter - be less than or equal to twenty characters - consist only of alphanumeric and the special characters ($_#); other characters need double quotation marks around them Another important factor in correcting ORA-00904 is remembering to run catproc.sql You can also check your trace file to find the particular error which is causing the ORA-00904 to occur. On Wed, Apr 2, 2008 at 3:37 PM, Pierson, Shawn [EMAIL PROTECTED] wrote: ** What I've had to do in the past in a similar situation is to have the view form referencing a view on my DB that was using a linked server. If you get that part, you can at least see the data. However, if you have issues modifying or submitting the data, unfortunately the next best solution is to build active links on buttons that run SQL commands for insert and update. Thanks, Shawn Pierson *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] *On Behalf Of *Gary Dries *Sent:* Wednesday, April 02, 2008 2:14 PM *To:* arslist@ARSLIST.ORG *Subject:* Issues with building a view over a DB Link ** I have created a Oracle (10g) to MSSQL (2003) dblink, I am able to create the view, but I get sever fields that retrun the error Failure during SQL operation to the database : ORA-00904: ADDRESS1: invalid identifier (ARERR 552). We are researching several possible issues, such as permissions, and the build of the table that I am creating the view of. One suggetion I found searching the list is to create the view in Oracle to the SQL db and then create the Remedy view to the Oracle view. Is it possible to create a view from a view, and if it is, can the data in the SQL table be modifed from Remedy in this view of a view of a SQL table? -- Gary Dries __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ Private and confidential as detailed herehttp://www.sug.com/disclaimers/default.htm#Mail. If you cannot access hyperlink, please e-mail sender. __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___
