Tag: oracle

How to implement an Enterprise Search

We are searching disparate data sources in our company. We have information in multiple databases that need to be searched from our Intranet. Initial experiments with Full Text Search (FTS) proved disappointing. We’ve implemented a custom search engine that works very well for our purposes. However, we want to make sure we are doing “the […]

PreparedStatement executing successfully in oracle but throwing exception in Microsoft SQL

I have this below query that I execute using java PreparedStatement: String dml=insert into users(name, addr, city, sex, dob) values(?,?,?,?,?); PreparedStatement stmt = conn.prepareStatement(dml); stmt.setString(1,”abcd”); stmt.setString(2,”def”); stmt.setString(3,”ghij”); stmt.setString(4,”m”); stmt.setString(5,”1-Jan-1987″); stmt.executeQuery(); It executes successfully when the database is Oracle, but when the database is Microsoft SQL, then it throws an exception “java.sql.SQLException: The executeQuery method must […]

Is it really better to use normalized tables?

I heard my team leader say that in some past projects they had to do away with normalization to make the queries faster. I think it may have something to do with table unions. Is having more lean tables really less efficient than having few fat tables?

Oracle equivalent to SQL Server/Sybase DateDiff

We are now using NHibernate to connect to different database base on where our software is installed. So I am porting many SQL Procedures to Oracle. SQL Server has a nice function called DateDiff which takes a date part, startdate and enddate. Date parts examples are day, week, month, year, etc. . . What is […]

What are locking issues in OLAP?

In one local financial institution I was rebuked by their programmers for expressing them my opinion that (their programmers’ obsession with) (b)locking issues in their MS SQL Server 2005 OLAP (SSAS) database(s) did not make much sense to me. (The OLTP databases are SQL Server, Oracle and non-RDBMS ERP). What are locking issues in OLAP […]

Connect MS SQL Server database to Oracle 11g database

I am struggling to create a db link from a Oracle Database to one SQL Server. For that, I have added the followings: In file tnsnames.ora: sqlUserConn = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SID=sqlUserConn)) (HS=OK) ) in file listener.ora: (SID_DESC= (SID_NAME=sqlUserConn) (ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server) (PROGRAM=dg4odbc) ) in file sqlnet.ora: SQLNET.AUTHENTICATION_SERVICES = (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) in file initsqlUserConn.ora: HS_FDS_CONNECT_INFO […]

SQL: Mark which WHERE condition matched

This is a theoretical question, I was wondering if there is a good way of finding out which condition in the WHERE statements matched. Say I have a query like this: SELECT * FROM table WHERE COND1 OR (COND2 AND COND3) OR COND4 Is there any way of knowing which of the conditions made a […]

SQL query to get the precision value of a column

I need a sql query that gets the precision value for certain columns.. I am mainly concerned with a decimal type column and I need the precision value for the same. I realise that it is possible to do so in certain versions and vendors of database servers. It would be nice if you could […]

Convert MS SQL script to Mysql and Oracle

One of the applications I develop gets installed locally on the customer’s site. This means that we have to support MySQL, MSSQL and Oracle as not all the customers use the same database engine. I’m writing a patch for the application, part of which involves executing a 5000 line sql script to make modifications to […]

Does “select for update” prevent other connections inserting when the row is not present

I’m interested in whether a select for update query will lock a non-existent row. e.g. Table FooBar with two columns, foo and bar, foo has a unique index Issue query select bar from FooBar where foo = ? for update If query returns zero rows Issue query insert into FooBar (foo, bar) values (?, ?) […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.