I am writing a C# Windows Forms application which calls Oracle stored procedures. I chose to use typed datasets in the application, these correctly populate various datagrids, but I am having trouble when invoking the UpdateCommand or the InsertCommand. I have manually coded these commands because a) I am using Oracle stored procedures...
On the latest Ubuntu, I have a functioning PHP 5.2.4 installation. I want to use a remote Oracle server from PHP using OCI. I've downloaded the "Instant Client Package - Basic Lite" from http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html. I've unzipped the package containing the OCI libraries to a di...
We have an x-files problem with our .NET application. Or, rather, hybrid Win32 and .NET application. When it attempts to communicate with Oracle, it just dies. Vanishes. Goes to the big black void in the sky. No event log message, no exception, no nothing. If we simply ask the application to talk to a MS SQL Server instead, which has ...
I am using Oracle 10g R2. Recently, after rebooting the server, I started having a problem where I couldn't connect to the instance. I am only connecting locally on the server itself. Oddly enough, the issue corrects itself if I start the Database Administration Assistant, and select my instance to supposedly change its settings. Do...
I'd like to use Oracle's utl_match.edit_distance function. It supposed to compare two strings and return the Levenshtein distance. select utl_match.edit_distance('a','b') from dual returns 1 as expected, but select utl_match.edit_distance('á','b') from dual returns 2. Obviously I'd like to get 1. It seems to be, it does not work c...
Why is Oracle's to_char() function adding spaces? select length('012'), length(to_char('012')), length(to_char('12', '000')) from dual 3 , 3, 4 ...
I need a date formula in Oracle SQL or T-SQL that will return a date of the previous week (eg Last Monday's date). I have reports with parameters that are run each week usually with parameter dates mon-friday or sunday-saturday of the previous week. I'd like to not have to type in the dates when i run the reports each week. The data ...
We have a POST to a PL/SQL database procedure that (a) does some database operations based on the POST parameters and (b) redirects the user to a page showing the results. The problem is, when the user does a browser "refresh" of the results page, that still has the original request, so it calls the database procedure and resends the pa...
I have the following SQL-statement: SELECT DISTINCT name FROM log WHERE NOT name = '' AND name LIKE '%.EDIT%'; It works fine on Postgres (returns all different names from log, which aren't empty and contain the string '.EDIT'). But on Oracle this statement doesn't work. Any idea why? ...
I've got a lot of similar oracle jobs I need to create, and I'd like to do it programatically. Where does the Oracle store the job library (schema/table)? (yes, I know I might be running with scissors) ...
Can I call a stored procedure in Oracle via a database link? The database link is functional so that syntax such as... SELECT * FROM myTable@myRemoteDB is functioning. But is there a syntax for... EXECUTE mySchema.myPackage.myProcedure('someParameter')@myRemoteDB ...
I've been wondering this for a while but since it hasn't come up much I've left it in the 'mysterious wizardy' column. It seems to me that there is some sort of relationship between a connection identifier like ABC and ABC.DEFG and I don't quite get what it is. For example, a machine I was setting up just now I was having problems with...
I am not as familiar with Oracle as I would like to be. I have some 250k records, and I want to display them 100 per page. Currently I have one stored procedure which retrieves all quarter of a million records to a dataset using a data adapter, and dataset, and the dataadapter.Fill(dataset) method on the results from the stored proc. ...
I have an Oracle database and the boolean fields are CHAR(1) with Y/N stored. How can I map this to a bool type using nHibernate? ...
How can I create an Oracle stored procedure which accepts a variable number of parameter values used to feed a IN clause? This is what I am trying to achieve. I do not know how to declare in PLSQL for passing a variable list of primary keys of the rows I want to update. FUNCTION EXECUTE_UPDATE ( <parameter_list> value IN int) R...
Does anyone know what is wrong with this query? SELECT DISTINCT c.CN as ClaimNumber, a.ItemDate as BillReceivedDate, c.DTN as DocTrackNumber FROM ItemData a, ItemDataPage b, KeyGroupData c WHERE a.ItemTypeNum in (112, 113, 116, 172, 189) AND a.ItemNum = b.ItemNum AND b.It...
We're looking at using Oracle Hierarchical queries to model potentially very large tree structures (potentially infinitely wide, and depth of 30+). My understanding is that hierarchal queries provide a method to write recursively joining SQL but they it does not provide any real performance enhancements over if you were to manually write...
I have this table in an Oracle DB which has a primary key defined on 3 of the data columns. I want to drop the primary key constraint to allow rows with duplicate data for those columns, and create a new column, 'id', to contain an auto-incrementing integer ID for these rows. I know how to create a sequence and trigger to add an auto-inc...
What are some things I can do to improve query performance of an oracle query without creating indexes? Here is the query I'm trying to run faster: SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath FROM items a, itempages b, keygroupdata c WHERE a.ItemType IN (112,115,189,241) AND a.ItemNum = b.ItemNum AND b.ItemNum = c.ItemNum ORDER...
Hi, I'm looking for an UPDATE statement where it will update a single duplicate row only and remain the rest (duplicate rows) intact as is, using ROWID or something else or other elements to utilize in Oracle SQL or PL/SQL? Here is an example duptest table to work with: CREATE TABLE duptest (ID VARCHAR2(5), NONID VARCHAR2(5)); run ...