Column, table, view names in Oracle by default is case insensitive.. If you notice, Oracle stores all its meta data in upper case, and yet you can write a perfectly legal SQL query to Oracle in lower case and it will work.
On the other hand, MS-SQL in my past experience with it, is case sensitive when referencing the same names.. Then I guess again it depends on how your two databases have been setup.. Try issuing queries in MS-SQL's query analyzer using mixed or the opposite case and check your results.. And implementing the solution in the article below will not really alter your tables or your data, so it may be worth trying. You may need to restart MS-SQL or run RECONFIGURE.. or run RECONFIGURE and then restart.. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of Victor Sent: Wednesday, December 16, 2009 12:48 PM To: arslist@ARSLIST.ORG Subject: Re: View form from Oracle based AR system to remote MS SQL server On Tuesday 15 December 2009 18:21:37 Joe D'Souza wrote: > Victor, > > This article might help you and your DBA to implement the change.. I would > suggest backing up the database before you do it although it is not > necessary since it doesn't really alter the database or its table but just > the collation.. Do let me know if it helps. > > http://bytes.com/topic/sql-server/answers/80144-change-case-sensitivity-aft >er-database-set-up > > Joe > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org]on Behalf Of Victor Olufowobi Sent: Tuesday, > December 15, 2009 7:09 AM > To: arslist@ARSLIST.ORG > Subject: Re: View form from Oracle based AR system to remote MS SQL > server > > > ** > Conny/Joe, > > Thanks very much for your suggestions and instructions - I've finally got > the view form working as needed. It was exacly as Conny explained - I > included "Next-ID-Commit: T" in ar.conf and the problem was solved!. > > Just one more question Joe, you wrote that there's a way of dealing with > case sensitivity problem. Could you write more about that?. I need to > access multiple tables from MSSQL server and would hate to ask the DBA to > redo all field names to upercase. > > Thank you all very much for your help. > > Victor > > > > > On Sun 13/12/09 21:16 , Joe D'Souza jdso...@shyle.net sent: > > > ** > Victor, > > Try that suggestion from Conny before the stored procedure I suggested. > Maybe that is what is required to insert that missing commit. > > If Conny's suggestion still doesn't work, then write a stored procedure > that performs that update, with a commit at the end like I suggested a > couple of emails ago.. I'll copy the design of the body of that stored > procedure just for your benefit again because previously I suggested update > but it looks like its an insert you need to create a stored procedure for.. > BEGIN > insert into tablen...@..... ( ) values (); > commit; > END; > > Maybe before you try all this (either mine or Conny's suggestion) SQL > logging may indicate where a commit is missing.. That is worth a try too.. > Oracle SQL logging would also help you find where a commit is missing.. > > Joe > -----Original Message----- > From: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org]on Behalf Of Conny Martin Sent: Sunday, > December 13, 2009 7:57 AM > To: arslist@ARSLIST.ORG > Subject: AW: View form from Oracle based AR system to remote MS SQL > server > > > ** > Victor, > > running only the insert statement from sqlplus is not the whole > story. > > If you create an entry through ARS there are a bunch of statements > which gets executed. Immediately before the insert are 2 statements to > generate the request_id. You should see something like this in your > sql-logfile. > > update arschema set nextid = nextid + 1 where schemaid = xxxx; > select nextid from arschema where schemaid = xxxx; > insert into Txxxx (cxxx,cyyy,czzz) values ('x','y','z'); > > Try to run these 3 in sqlplus. IMHO error ORA-02047 indicates some > problem with handling distributed transactions. If this sequence of > statements generates an error in sqlplus try a commit; after the first 2 > statements. If this solves the problem you can set "Next-ID-Commit: T" in > ar.conf. This causes ARS to issue a commit after generating the nextid. > > HTH > > Kind Regards Conny > > > > > > -------------------------------------------------------------------------- > Von: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org] Im Auftrag von Victor Olufowobi Gesendet: > Sonntag, 13. Dezember 2009 09:41 > An: arslist@ARSLIST.ORG > Betreff: Re: View form from Oracle based AR system to remote MS SQL > server > > > ** > Thanks again Joe, > > I will try what you suggested and have the outcome posted - but I > want you to consider the following: - I can update without problem using > the view form created. It's when I'm inserting (CREATE operation) the error > occurs - I can successfull run the INSERT command ARS is trying to run from > sqlplus > > Victor > > On Sat 12/12/09 14:56 , Joe D'Souza jdso...@shyle.net sent: > > > Victor, > > Well that's half the battle won then.. Yes MS-SQL can be weird when > it comes to case sensitivity of view names and columns within views even.. > For e.g. if your ARS was hosted on a MS-SQL server, and the view name was > in small case and you tried creating a view form in upper case, it would > not recognize that name. There is a way to override that case sensitivity > but we won't deal with that since you already updated the view to have it > upper cased.. > > This is what I found for ORA-02047. > > http://ora-02047.ora-code.com/ > > Based on the clue provided with the explanation of the error code, > can you the Run Process Application-Release-Pending just an action before > updating the MS-SQL view data in your workflow - and if that does not work > the same action after the update action to that DB-Link? I'm assuming that > will force any pending commits before (or if necessary after) you try > updating the external view using that DB-Link in light of what the article > in the above link says.. > > If that does not work (and the commit is required while and not > before or after the update), try writing a stored procedure that has a > commit inside of it - e.g.. > > BEGIN > update tablen...@..... set ... where ...; > commit; > END; > > Use that stored procedure in a direct SQL at the point where you > want to run that update to the foreign database. Make sure that you declare > all the variables that you need while defining the stored procedure. > > Let me know how it goes.. > > Cheers > > Joe > > -----Original Message----- > From: Action Request System discussion list(ARSList) > [arsl...@arslist.org]on Behalf Of Victor > Sent: Saturday, December 12, 2009 3:48 AM > To: arslist@ARSLIST.ORG > Subject: Re: View form from Oracle based AR system to remote MS SQL > server > > On Thursday 10 December 2009 21:16:14 Joe D'Souza wrote: > > Hello Victor, > > > > That is what I suspected with the invalid identifier error.. if > > your table was not recognized it would be invalid table or view > > name error which is not your case.. Which is why the later part > > of my previous email holds good.. Take a SQL log, you may find > > that offending column name or > > columns.. > > > There is something in the way that ARS is naming the offending > > column or columns causing that error.. > > > > When you find what column it is email the list or me directly and > > we'll > > try > > > to figure it out.. you might need to create a independent view in > > MS-SQL > > of > > > that table having names of fields that are legal to use in case > > there is a column name there that the ARS converts into something > > else.. This is just a speculative solution, we'll know more when > > you know more.. > > > > Cheers > > > > Joe > > > > -----Original Message----- > > From: Action Request System discussion list(ARSList) > > [arsl...@arslist.org]on Behalf Of Victor > > Sent: Thursday, December 10, 2009 3:00 PM > > To: arslist@ARSLIST.ORG > > Subject: Re: View form from Oracle based AR system to remote MS > > SQL server > > > > > > Thanks Joe, > > > > I will try all you said and have you posted tomorrow(I'm out of > > office at the moment) but I want to point out that will creating > > the view form I was able to load the table and the pick the > > fields required for the form. Only when I pressed "Create" the > > error occurred. > > > > .. and furthermore the ms sql username and password was > > hard-cored into > > the > > > public DB link created > > > > Victor > > > ___________________________________________________________________________ > > >____ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > > Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the > > Answers Are" > > Joe, > > Thank you for pointing me to the right direction!. > > I set on SQL log as suggested and found out that while SELECTing > fields from MSSQL server to create a view in Oracle for the view form, ARS > was unable to parse fields created in Oracle db with fields from MSSQL > server. This is because the field names in MSSQL were in lowercases while > in Oracle they were all in uppercases! (can this behaviour be changed?) > I redo the fields in SQL server to uppercases and I was able to > complete the creation of the view form!. > Thanks a lot for that. > > However, when I tried to submit to the form I received this error: > "ARERR [552] Failure during SQL operation to the database: > ORA-02047: cannot join the distributed transaction in progress" > > Once again I set on the SQL log. > ARS was trying to INSERT the required values to the view created > for the view form when the error occurred. > > I ran the command ARS was trying to run in sqlplus and it was > executed successfully. > > Do you have an Idea what might be the cause of the error in ARS? > > Thanks very much for you suggestions so far. > > Victor > > ___________________________________________________________________________ >____ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" Joe, The problem is not with MSSQL server - it is definitively case-insensitive, the problem is rather with the ARS Oracle server. I was hoping there is a way to solve the fields name matching problem while creating view form to MSSQL tables without redoing the fields name to uppercase in MSSQL server or setting DB- Insensitive parameter to True in ARS configuration file.... If there's no other way I'll manage anyway. So thank you all for your help Victor _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
