I'm having a really weird problem with a third party web service that uses an ODBC connection to Oracle 10.2.0.3.0. I've written a .NET client that generates the same SQL as the web service so I can find out what's going on. The web service is hosted by IIS 6 that's in x64 mode so we use Oracle x64 client. The oracle client version is 1...
We have a customer who has both a SharePoint 2007 SP 2 and an Oracle Portal 10.1.4.2.0. They would like to search the content of the Oracle portal from SharePoint. Is this a supported configuration? We have tried the various authentication methods. The only one which we have been able to get to work is the cookie authentication sending ...
I want to go through a table, and change all instances of 'notify4-N' to 'notify5-N', where N is a number from 1-9. Is there a way to do that in SQL? It would be easy in perl, but I'm not sure the customer even has perl on his servers. ...
What is a good way to prevent a table with 2 columns, a (unique) and b, from having any record where column b is equal to any value in column a? This would be used for a table of corrections like this, MR -> Mr Prf. -> Prof. MRs -> Mrs I can see how it could be done with a trigger and a subquery assuming no concurrent activity but ...
Here's my query: SELECT my_view.* FROM my_view WHERE my_view.trial in (select 2 as trial_id from dual union select 3 from dual union select 4 from dual) and my_view.location like ('123-%') When I execute this query it returns results which do not conform to the my_view.location like ('123-%') condition. It's as if that condition is b...
Quick question playing with PL SQL it seems that the tables column was named as the data type NUMBER so trying to perform a query fails since the column is being recognized as a datatype instead of a column name. Anyone know how to get around this without modifying the schema? EDIT: Thanks everyone for the help yeah the issue was it ha...
Hi all, I am creating test cases and I need to cover circular dependencies. So far I have been able to create two tables such that Table A has a FK to B and B has a FK to A. What other circular dependencies exist / are allowed between objects? I tried to create cycles between Views but Oracle successfully rejected that. ...
I have a psql script that looks like this: -- first set of statements begin sql statement; sql statement; sql statement; exception when others then rollback write some output (here I want to exit the entire script and not continue on to the next set of statements) end / -- another set of statements begin sql statement; s...
I have a Oracle table whose column is a CLOB datatype. I want to read the content of this table as a text. I tried select dbms_lob.substr( sqltext, 4000, 1 ) from test but this one selects only the first 4000 bytes. How to read the entire content ? there are more than 4000 characters in the sqltext column. Please advise. ...
I'd like to know why most Banks or Financial companies prefer Oracle than other RDBMS for their core systems (the absolutely minimum features that a Bank must support). I found a few answers that didn't satisfy me. For example: Oracle has more features. But features for what? Can't you implement that in application level if you were not ...
This is a multipart Indes question: Is there a way one can create index on index? Why would one wish to to so? And if so, are ther any examples? ...
My understanding is that ROWID is a unique value for each row in the result returned by a query. why we need this ROWID ?( there is already ROWNUM is ORACLE). Have any one used ROWID in sql query? ...
I know that drop removes the data as well as the table structure whereas truncate retains table structure. Does drop/truncate write to roll back segment ? ...
I'm working on a web service that fetches data from an oracle data source in chunks and passes it back to an indexing/search tool in XML format. I'm the C#/.NET guy, and am kind of fuzzy on parts of Oracle. Our Oracle team gave us the following script to run, and it works well: SELECT ROWID, [columns] FROM [table] WHERE ROWID IN ( ...
My ASP.NET website while trying to connect to the database for first time after a period of inactivity throws an time out exception. I understand the connections in the connection pool get terminated after some idle time for some reason (Firewall or Oracle settings) and the pool or app doesn't have a clue about it. Is there any way to v...
I am trying to Query a remote Oracle database from Windows Server(production environment) through a .net app. I keep getting this exception. I have Oracle XE client installed on the system. I tried to install Oracle 64 bit connectors but the jvm won't allow me to do so. Exception: System.BadImageFormatException: An attempt was made to lo...
We had our oracle server choking during processing a select statement with close to 3500(!!) bind variables. This select is, obviously, built dynamically by code that we can't change. During the execution of this select the db server went to 100% cpu usage and our system almost halted. We know how to reproduce this problem. So we can p...
Hi I want to create a trigger that execute on update of a table. in particular on update of a table i want to update another table via a trigger but if the trigger fails (REFERENTIAL INTEGRITY-- ENTITY INTEGRITY) i do not want to execute the update anymore. Any suggestion on how to perform this? Is it better to use a trigger or do it...
I have a problem understanding read consistency in database (Oracle). Suppose I am manager of a bank . A customer has got a lock (which I don't know) and is doing some updating. Now after he has got a lock I am viewing their account information and trying to do some thing on it. But because of read consistency I will see the data as ...
Why can't a subquery of a view have an order by clause?Similarly why one cant change/delete a row through a view when 1)group by is used in view 2)distnct is used in view ...