I need to find the last index of a string (e.g. "-") within another string (e.g. "JD-EQ-0001" in Oracle (version 8i). Is there a way to get the INSTR function to do this or is there another function? Thanks James ...
I am trying to parse a column of strings in Oracle (version 8i) to an integer. I am accessing the results through Oracle.DataAccess library I'm already using TO_NUMBER with a mask to convert the string into a number with no decimal places. The problem is that the value in the client code is being retrieved as a decimal rather than an ...
Hi, I was wondering how to go about exporting a query from PL/SQL to an text file or csv file. The query I have in mind exports a huge amount of data (about 1 gig). So I'd also like the data split across multiple files; out1.csv out2.csv out3.csv I'd like to be able to decide how many files to split it across. Anyone have any idea ho...
I need to select an entity that is valid at the midth of the validity of different entity. As a simplified example, I want to do something like this: select * from o1, o2 where o1.from < (o2.to - ((o2.to - o2.from) /2) ) and o1.to > (o2.to - ((o2.to - o2.from) /2) ) How can I compute "(o2.to - ((o2.to - o2.from)...
As part of our ANT based deploys we run some sql against an Oracle database. The deploys are set to fail if any sql errors occur. (We do this using WHENEVER SQLERROR EXIT FAILURE ROLLBACK in our SQL files and using the ant exec command with failonerror=true to run the sql). We would like to add to our deploy sql files some sql that ...
Given these two tables: CREATE TABLE TEST1 (TEST VARCHAR2(1 BYTE)) CREATE TABLE TEST2 (TEST VARCHAR2(1 BYTE)) Where TEST1 has two rows both with the value 'A' and TEST2 has one row with the value 'B'. When I run this command: SELECT TEST FROM TEST1 MINUS SELECT TEST FROM TEST2 I get the output: Test ----- A It appears that MI...
Is there any way of copying a database from oracle to sqlserver 2005? Thanks ...
Using the standard Delphi dbexpress dbexpora.dll + oci.dll (10g) (the oracle instantclient is installed). When directly on the Oracle Database box we can run dbexpress apps just fine. The local dbxconnections.ini alias used in that case simply specifies our DB service name ORCL as the "database" parameter. We are trying to connect to t...
I need to run a couple of relatively simple SQL update statements to update a single column in an Oracle table with 14.4 million rows. One statement runs a function written in Java and the JVM runs out of memory as I’m doing the update on all 14.4 million rows. Have you written a kind of batch PL/SQL routine that can break this simple u...
Disclaimer: I just started work on a new contract, which forced me to switch from MSSQL to Oracle. So I am a complete newcomer to Oracle and probably bring many bad MSSQL practices with me. Okay, on to the question... According to an answer in this thread, semicolons are bad and should be avoided. I realized this firsthand after spendin...
I'm new to Oracle and to SQL Developer. I'm trying to optimize a query but don't quite understand some of the information returned from Explain Plan. Can anyone tell me the significance of the OPTIONS and COST columns. In the OPTIONS column, I only see the word FULL. In the COST column, I can deduce that a lower cost means a faster query...
Question: Is it possible to have a column name in a select statement changed based on a value in it's result set? For example, if a "year" value in a result set is less than 1950, name the column "OldYear", otherwise name the column "NewYear." The year value in the result set is guaranteed to be the same for all records. I'm thinking...
I am trying to calculate the total rows in a table called DRAWING with the following query: Select field, platform, count(doc_id) as total from drawing group by field, platform; but I also need to display the total of attachments/non-attachments for each platform SQL: select field,platform,count(doc_id) as attached from drawing where ...
I have a table with columns user_id, email, default. Default stores 'Y' or 'N' depending if the email is the users default email. Each user can have only one default email. When a user is doing an update or insert on the table, in my SP I check if the user has passed isDefault as 'Y'. If so, I need to update all the entries for that us...
I have a PL/SQL function (running on Oracle 10g) in which I update some rows. Is there a way to find out how many rows were affected by the UPDATE? When executing the query manually it tells me how many rows were affected, I want to get that number in PL/SQL. ...
Hello, I've tried to install "Oracle10g Release 2 ODAC (64-bit) 10.2.0.3 for Windows x64" from: http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html on Windows 2008 server 64bit, but it crushes right after i run the setup.exe with the following error: Problem signature: Problem Event Name: APPCRASH ...
When I search the web for inserting BLOBs into Oracle database with jdbc thin driver, most of the webpages suggest a 3-step approach: insert empty_blob() value. select the row with for update. insert the real value. This works fine for me, here is an example: Connection oracleConnection = ... byte[] testArray = ... PreparedStateme...
Hi, I am using Spring persistence framework for my project. I want to call oracle function or stored procedure from this framework. Can anybody suggest how can I achieve this. Please give solution for both * oracle function and *stored procedure. Thanks. ...
I have a string. In this string i need replace all special characters (0-31 codes) with chosen representation. Representations may be of different formats. May be \x??, or \0???, or 10,13 -> \n, 9 -> \t and all others characters are converting to null. Summary - i need find all symbols with 0-31 codes and replace all of them for appropri...
I have an application that connects via a DSN to an Oracle database. If the initial attempt to connect fails, then I make sure their DSN exists. If it does not exist, then I create it using the SQLConfigDataSource command. That command requires the driver name as one of its arguments. On my machine, I have the 11g driver, so the follo...