How can I write an insert statement which includes the & character? For example, if I wanted to insert "J&J Construction" into a column in the database. I'm not sure if it makes a difference, but I'm using Oracle 9i. ...
I'm trying to use SQL developer, but it won't connect using the proxy I specify in the preferences. I guess it's because of some kind of certificate error? Not sure. I'm getting the error: No HTTP response received. javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.p...
I recently upgraded my oracle client to 10g (10.2.0.1.0). Now when I try to connect to a legacy 8.0 database, I get ORA-03134: Connections to this server version are no longer supported. Is there any workaround for this problem, or do I have to install two clients on my local machine? ...
I am coming from the SQL server world where we had uniqueidentifier. Is there an equivalent in oracle? This column will be frequently queried so performance is the key. I am generating the GUID in .Net and will be passing it to Oracle. For a couple reasons it cannot be generated by oracle so I cannot use sequence. ...
I've recently come across a feature of doing a large query in oracle, where changing one thing resulted in a query that used to take 10 minutes taking 3 hours. To briefly summarise, I store a lot of coordinates in the database, with each coordinate having a probability. I then want to 'bin' these coordinates into 50 metre bins (basicall...
The application uses Oracle DataAccess ver. 1.1. , VS 2008, .Net Framework 3.5 w/SP1 OracleConnection connection = new OracleConnection(ConnectionStringLogon); connection.Open(); OracleParameter selectParam = new OracleParameter(":applicationName", OracleDbType.Varchar2, 256); selectParam.Value = applicationName; ...
Change Data Capture is a new feature in SQL Server 2008. From MSDN: Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log ...
Java & Oracle both have a timestamp type called Date. Developers tend to manipulate these as if they were calendar dates, which I've seen cause nasty one-off bugs. For a basic date quantity you can simply chop off the time portion upon input, i.e., reduce the precision. But if you do that with a date range, (e.g.: 9/29-9/30), the diffe...
I need to pad numbers with leading zeros (total 8 digits) for display. I'm using oracle. select to_char(1011,'00000000') OPE_NO from dual; select length(to_char(1011,'00000000')) OPE_NO from dual; Instead of '00001011' I get ' 00001011'. Why do I get an extra leading blank space? What is the correct number formatting string to accompl...
In my environment here I use Java to serialize the result set to XML. It happens basically like this: //foreach column of each row xmlHandler.startElement(uri, lname, "column", attributes); String chars = rs.getString(i); xmlHandler.characters(chars.toCharArray(), 0, chars.length()); xmlHandler.endElement(uri, lname, "column"); The XM...
I'm using a medical records system built on an Oracle database. Our vendor just told us that we need to shut down Oracle's Enterprise Manager service when we're not using it, because it uses too much of the system's resources. I know I can get actual numbers by checking Sysinternals Process Explorer, but I was hoping that someone can giv...
This page (http://www.techonthenet.com/oracle/functions/trunc_date.php) mentions how to trunc a timestamp to minutes/hours/etc. in Oracle. How would you trunc a timestamp to seconds in the same manner? ...
It there a tool (preferably free) which will translate Oracle's PL/SQL stored procedure language into Postgresql's PL/pgSQL stored procedure language? ...
With SQLServer, it seems to be generally accepted that adding a SELECT SCOPE_IDENTITY() to the end of your insert is the best way to return the PK of the newly-inserted record, assuming you're using an auto-increment field for the pk. However, I can't seem to find the equivalent for Oracle. Best practice seems to be to use a sequence...
What does it indicate to see a query that has a low cost in the explain plan but a high consistent gets count in autotrace? In this case the cost was in the 100's and the CR's were in the millions. ...
I would like to handle an OracleException thrown when my network/database connection is interrupted, where can I find out what error codes I might can receive? I guess since we are talking about a connection interruption these would be technically TNS errors such as ORA-12560 "TNS:protocol adapter error." But I have noticed a couple oth...
I have a complex query with group by and order by clause and I need a sorted row number (1...2...(n-1)...n) returned with every row. Using a ROWNUM (value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation) gives me a non-sorted list (4...567...123...45...). I cannot...
I have to write a component that re-creates SQL Server tables (structure and data) in an Oracle database. This component also has to take new data entered into the Oracle database and copy it back into SQL Server. Translating the data types from SQL Server to Oracle is not a problem. However, a critical difference between Oracle and S...
I have a table that looks like that: The rows are sorted by CLNDR_DATE DESC. I need to find a CLNDR_DATE that corresponds to the highlighted row, in other words: Find the topmost group of rows WHERE EFFECTIVE_DATE IS NOT NULL, and return the CLNR_DATE of a last row of that group. Normally I would open a cursor and cycle from top to ...
What's the best way, using SQL, to check the maximum number of connections that is allowed for an Oracle database? In the end, I would like to show the current number of sessions and the total number allowed, e.g. "Currently, 23 out of 80 connections are used". ...